Unwanted dependency

[robw]

Hi,

Essentially, I have a workbook containing three sheets (“Constants”, “Isa”, ”Ccard”).

The Constants sheet simply contains constant values that are used throughout the workbook

The Ccard sheet contains a “table” of around 500 rows. The values in one of the columns of this table are calculated via a UDF called “sav” that has been “filled-down” the whole table. The arguments in each UDF call are taken either from the Constants sheet or from other cells in the appropriate row of the Ccard sheet… The UDF does not use the Application.Volatile call.

The Isa sheet contains a “table” of around 300 rows and a single button which launches a macro called “Equint”. The macro uses the “Goal seek” method to perform a calculation for each of the rows, but only uses values from the Isa and Constants sheets. Neither this sheet , nor the macro, use the “sav” function.


My problem is that the Equint macro takes a very long time to run (though it does eventually finish, with the expected resultes). If I interrupt the execution and choose the “Debug” option I almost always find the execution point is somewhere within the “sav” UDF. !!! Why??

Even more curiously, if I then reset execution and run the macro again I find that the macro runs through quickly (giving the expected results), but I find that the values calculated by sav on the Ccard sheet are now #VALUE .


In summary, I appear to have some unwanted dependency between Equint and sav. I cannot see anything which obviously connects them. Any suggestions of things to check would be most welcome.


Many Thanks
Rob

 

[Andrew Poulsom]

I'm not sure, but have you tried setting Calculation to manual before performing the GoalSeek?

 

[robw]

Andrew,

A good thought.... I think I have already tried this (but will try it again when I get home) ... I seem to recall it did not work ... and I seem to recall thinking this was reasonable, because the relationship between the "Set cell" and the "By changing cell" needs to be allowed to "express itself" during GoalSeek's internal iteration...and this cannot happen if automatic calculation is switched off. (If that makes sense?)

Rob

 

[tusharm]

It is possible that GoalSeek uses a 'Force complete recalculation' in its own work. If so, you are stuck...I think.

To check for any unintended dependencies in your own work, try this test. Use a copy of your workbook. Don't use the original workbook. Delete the ccard worksheet. Doest Equint still work? In reverse, delete the isa worksheet. Does sav still work?

{snip}
My problem is that the Equint macro takes a very long time to run (though it does eventually finish, with the expected resultes). If I interrupt the execution and choose the “Debug” option I almost always find the execution point is somewhere within the “sav” UDF. !!! Why??

Even more curiously, if I then reset execution and run the macro again I find that the macro runs through quickly (giving the expected results), but I find that the values calculated by sav on the Ccard sheet are now #VALUE .


In summary, I appear to have some unwanted dependency between Equint and sav. I cannot see anything which obviously connects them. Any suggestions of things to check would be most welcome.