Function/Procedure dependence & order of execution

[Jon L]

My spreadsheet has a Procedure that creates an array of data locations. A
worksheet Function1 uses this array to perform it's calculation. Moreover,
Function2 uses the same Procedure and Function1 results. Also, Function1 and
Function2 are used in many different cells. The only arguement for the
functions is the cell coordinate. I would post file but don't know how!

My problem is that the they must calculate in the following order to work
properly (Procedure, Function1, Function2) - except this seems impossible to
corrdinate. The procedure always runs last and the many instances of the two
functions calculate in a bizarre order (they have no arguments outside of
their own cell reference). I've tried combinations of volitile, calling the
procedure from the functions, changing the module order of the three
procedure/functions, worksheet events to trigger the Procedure calculation -
nothing seems to work to make the Procedure calculate first.

Also, the function sometimes doesn't update becuase it's arguments are in an
array, not in it's brackets.

#1 How can I get my Procedure to calculate first?
#2 What's the order of execution of procedure and functions (all arguments
being equal)?
#3 Assuming I can get my Procedure to calulcate first, how can I then
trigger ALL my Function1 cells to calculation, then all my Function2 cells?

Help!

 

[Charles Williams]

Hi Jon,

difficult problem to solve without some redesign (which would imply better
understanding of the problem you are trying to solve) into a more
Excel-friendly solution.

some suggestions:

- running the procedure first: the procedure sets a global variable true
after it has run, each function checks the global variable and if false
calls the procedure. function 1 & 2 need to be volatile since they depend on
values that are not visible as function parameters.
- to make all multiple randomly placed instances of function1 calculate
beforeall multiple randomly placed instances of function2 you could make
every function1 dependent on something1 and every function2 dependent on
something2 and then change something 1 and calculate, change something 2 and
calculate.

another approach would be to daisy-chain the dependencies (2nd function 1
depends on 1st function 1 ... all function 2s depend on the last function1.

You might also be able to use simpler approaches (Sheet.calculate,
range.calculate etc) if you (or a program) have some knowledge of where the
functions are.

see also http://www.Decisionmodels.com/calcsecretsj.htm for a discussion of
UDF calculations and the other pages on the site for a description of
excel's calculation sequence.


Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com