Re: Function/Procedure dependence & order of execution

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Charles Williams (Charles_at_DecisionModels.com)
Date: 10/03/04 

Date: Sun, 3 Oct 2004 10:06:48 +0100

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

"Jon L" <JonL@discussions.microsoft.com> wrote in message
news:BE83A7EE-9CF8-4696-8A04-3F5EC4F32FEC@microsoft.com...
> 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!

Relevant Pages

  • Re: Error writing value to cell - continued
    ... You can't trick excel by having your function call a sub - that is correct. ... Possibly you could have a static array and store the values there. ... The Excel calculation engine doesn't go in and analyze what your function ... see why -- If the worksheet had 100 cells that called this function, ...
    (microsoft.public.excel.programming)
  • Re: Excel IF/LARGE Function problem
    ... Your 'rounding' solution seems to ... >work ONLY if a 'majority' of cells in the points array contain numbers ... While I have a tendency to give mostly single cell or as few cells as ... Adding divisional subtotals complicates the calculation ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Matching values in different columns/workbooks
    ... make sure you turn off calculation and screen updating before running your code: ... works fine for a 100 cells, but the final version will check 600 rows ... Would it be better to use an array (not sure how to do ...
    (microsoft.public.excel.programming)
  • Adding array brackets {} to all cells in a column??
    ... I've got a very complicated spreadsheet which includes columns of array ... regular formatting - i.e. the were removed and the calculation ... there are lots of blank cells scattered throughout that would have to ...
    (microsoft.public.excel.worksheet.functions)
  • RE: excel formulas
    ... converts a number from euros to a euro member ... Counts the cells that contain numbers in a database ... Specifies a logical test to perform ... Looks in the top row of an array and returns the value of the indicated cell ...
    (microsoft.public.excel.misc)