Calculate doesn't work right

[Maria J-son]

Hi,

My earlier threads might just be symphtoms of the workbooks unwillingness to
calculate.

LINKS DOESN'T RECALCULATE
If I change a value in Sheet10, it's link in sheet11 doesn't recalculate.
It does only recaculate if I force a sheet10.calculate and sheet11.calculate
or run application.calculate (but I have a lot of links and sheets, that
will take to much time to do in a user perspective)

TEST WITH A NEW EMPTY WB
If I start a new empty workbook and make ONE single link between sheet1 and
sheet2, the sheet2 value will recalculate VERY SLOWLY - (4 seconds, seeing
the 10...30...50% in the bottom of the application) when I change the value
in sheet1.

TEST WHIT/WITHOUT MY WB
If I close my workbook excelfile, the new workbook with only one link will
work o.k. again. If I start the workbook excelfile, the slow calculation
starts again in the New Empty "one link workbook".

Even if I take away all startcode in ThisWorkbook the probelm remain.

It doesn't change even if I restart excel. I have Excel 2003 SP1 running on
windows xp sp2 and computer is 3 Ghz and 1,5 GB RAM ...

Pleeease bring light in this mess ...!

/Regards

[flummi]

Did you look at the Task Manager (Tab "Processes") to see what process
is consuming all that time?

I would guess there is some macro permanently running.

When you have the problem, press ctrl-break and then hit "Debug" to see
where it's working.

Hans

[Charles Williams]

To clear up some possible confusions:

when Excel calculates (F9 or Application.calculate or Calculation is
Automatic) it calculates all open workbooks not just the active book. (there
is no option in Excel to only calculate the active workbook)

Calculation settings like calculation=automatic/manual are set at
application level rather than workbook level (if calculation is manual it is
manual for all the open workbooks), and the setting is controlled by the
first workbook opened until you change it through Excel Options or VBA.

Normally Excel will only calculate things that have changed and formulae
that depend on things that have changed, but you can force a complete
calculation of all formulae in all open workbooks by pressing Ctrl Alt F9
(all at once).

Sometimes Excel gets it dependency trees messed up in a particular workbook
and this may cause a recalculation to not work properly: pressing Ctrl Alt
Shift F9 (all at once) will rebuild the dependency trees and do a full
calculate.

Other possible sources of problems are:
- not being in automatic calculation mode when you think you are
- visual basic User defined functions
- circular references
- calculation being interrupted by something external to Excel


--
Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

"Maria J-son" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> My earlier threads might just be symphtoms of the workbooks unwillingness
> to calculate.
>
> LINKS DOESN'T RECALCULATE
> If I change a value in Sheet10, it's link in sheet11 doesn't recalculate.
> It does only recaculate if I force a sheet10.calculate and
> sheet11.calculate or run application.calculate (but I have a lot of links
> and sheets, that will take to much time to do in a user perspective)
>
> TEST WITH A NEW EMPTY WB
> If I start a new empty workbook and make ONE single link between sheet1
> and sheet2, the sheet2 value will recalculate VERY SLOWLY - (4 seconds,
> seeing the 10...30...50% in the bottom of the application) when I change
> the value in sheet1.
>
> TEST WHIT/WITHOUT MY WB
> If I close my workbook excelfile, the new workbook with only one link will
> work o.k. again. If I start the workbook excelfile, the slow calculation
> starts again in the New Empty "one link workbook".
>
> Even if I take away all startcode in ThisWorkbook the probelm remain.
>
> It doesn't change even if I restart excel. I have Excel 2003 SP1 running
> on windows xp sp2 and computer is 3 Ghz and 1,5 GB RAM ...
>
> Pleeease bring light in this mess ...!
>
> /Regards
>

[Maria J-son]

Hi Charles Williams,

I have now pasted all sheets to a new workbook and have no vba yet into it.
I find still the calculation to around 4 seconds after each cell change.
This is caused by all columns and rows with array calculations, and more of
them are to come. Therfore do I need a way to inhibit calculation and only
calculate the ranges needed. Do you know if this is possible:

A/ Inhibit the common Application.Calculate that run after every change?
B/ Just calculate specific ranges in specific sheets after a
worksheet_change?
C/ Be sure to cover every aspect to swicht it on again when leaving the
workbook, and then continue with "the inhibited way" when returning to the
workbook?

I put this on a new tread also.

Thanks for the effort,
Regards


"Charles Williams" <[email protected]> skrev i meddelandet
news:[email protected]...
> To clear up some possible confusions:
>
> when Excel calculates (F9 or Application.calculate or Calculation is
> Automatic) it calculates all open workbooks not just the active book.
> (there is no option in Excel to only calculate the active workbook)
>
> Calculation settings like calculation=automatic/manual are set at
> application level rather than workbook level (if calculation is manual it
> is manual for all the open workbooks), and the setting is controlled by
> the first workbook opened until you change it through Excel Options or
> VBA.
>
> Normally Excel will only calculate things that have changed and formulae
> that depend on things that have changed, but you can force a complete
> calculation of all formulae in all open workbooks by pressing Ctrl Alt F9
> (all at once).
>
> Sometimes Excel gets it dependency trees messed up in a particular
> workbook and this may cause a recalculation to not work properly: pressing
> Ctrl Alt Shift F9 (all at once) will rebuild the dependency trees and do a
> full calculate.
>
> Other possible sources of problems are:
> - not being in automatic calculation mode when you think you are
> - visual basic User defined functions
> - circular references
> - calculation being interrupted by something external to Excel
>
>
> --
> Charles
> ______________________
> Decision Models
> FastExcel 2.2 Beta now available
> www.DecisionModels.com
>
> "Maria J-son" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> My earlier threads might just be symphtoms of the workbooks unwillingness
>> to calculate.
>>
>> LINKS DOESN'T RECALCULATE
>> If I change a value in Sheet10, it's link in sheet11 doesn't recalculate.
>> It does only recaculate if I force a sheet10.calculate and
>> sheet11.calculate or run application.calculate (but I have a lot of links
>> and sheets, that will take to much time to do in a user perspective)
>>
>> TEST WITH A NEW EMPTY WB
>> If I start a new empty workbook and make ONE single link between sheet1
>> and sheet2, the sheet2 value will recalculate VERY SLOWLY - (4 seconds,
>> seeing the 10...30...50% in the bottom of the application) when I change
>> the value in sheet1.
>>
>> TEST WHIT/WITHOUT MY WB
>> If I close my workbook excelfile, the new workbook with only one link
>> will work o.k. again. If I start the workbook excelfile, the slow
>> calculation starts again in the New Empty "one link workbook".
>>
>> Even if I take away all startcode in ThisWorkbook the probelm remain.
>>
>> It doesn't change even if I restart excel. I have Excel 2003 SP1 running
>> on windows xp sp2 and computer is 3 Ghz and 1,5 GB RAM ...
>>
>> Pleeease bring light in this mess ...!
>>
>> /Regards
>>
>
>