Excel Calculation Issue

[Sean Howard]

Firstly I am not even sure if this is a programming issue or not.

I have a spreadsheet that contains both standard Excel functions and
functions in an external spreadsheet (which is ALWAYS referenced and
thus open)

Sometimes I can see in the worksheet that the results are incorrect and
calculation the workbook does not make any difference.

HOWEVER if I make a copy worksheet, delete that copy, and THEN
calculate. All the results are correct.

Now, I do not think that the answer is to copy/delete a worksheet but
something must be happening during this process to force Excel to fully
complete and calculations and what question is exactly what is
happening.

The problem for my users is that because of the nature of the data in
the workbook, it is impossible for them to tell is it has fully
calculated or not.

I have considered making the function VOLATILE, however there are just
too many functions and I am worried about slowing down the whole
calculation process.

Any ideas

Sean

 

[Guest]

You must be using some functions written in VB Script. And you are passing
some values to it and taking other values by reference into this function.
Now any function or Macro will not Re-Run unless the values passed to it are
changed. That is the reason why your function is not refreshing and the
calculations are done partially with new values and partially with old.
Functions / Macros need some kind of event to execute. Now opening an excel
file is such event and it refreshes these functions / Macros. Frankly I too
faced this problem sometime back and the remedy I used then was to hyperlink
my calculation sheet which used Macros to the input sheet. Thus the
calculation sheet was opened each time refreshing my macros.

Don't worry there is an easier solution I learnt later. Here is what you can
do. First find such functions/Macros. Then put them in an event like a button
click or something. If you don't know programming at all get someone who
knows it.

If none of this is possible you can try my old method. Create an input sheet
and pass the values to your calculation sheet and hyperlink calculation sheet
from input sheet. This way clacultion sheet will open each time the input
values change.

Vikrant

 

[Sean Howard]

erm. thanks but I think you've jumped way of base here.

VBScript. No
Hyperlinks. No

I understand your point about using HyperLinks, but even though I can be
sure my "macro/function" workbook will always be open (and thus can be
referenced) I cannot be sure where it is actually stored. Also if the
"macro/function" workbook has to be re-opened chances are the users
macro security setting will kill it (presently the "macro/function"
workbooks are loaded on Excel startup, that's how I know they are always
there)

Thanks Vikrant but that solution will not work for me

Sean

 

[Tim Williams]

Do any of your functions use information not passed via range
arguments? For example do they look at values in other sheets
directly? That can lead to behaviour where recalculation becomes a
problem: they will only be refreshed when one of the input arguments
changes.

Tim