Excel - Evaluation Order 2

[BNPMike]

I'm looking at using Excel in a more general programming context, specifically for web applications. I know it sounds bizarre, but it's not the first time people have tried to view Excel as a functional programming platform, and no animals will get harmed in the making of this.

Initially I was assuming that I would need to keep some Excel instances running and have a manager connect to an available one for each incoming web call. However I noticed that Excel does load really rather fast so I may be able to get away with calling an new instance each time.

So this leads me to my question - does anyone know how Excel chooses to evaluate cells on any change event? Does it evaluate all sheets with dependencies(ie not just the visible ones) , or just those that the visible sheet depends on etc, or can you control this?.

 

[SkipVought]

Hi,

Not sure I understand your question. And since you are referring to VBA, you will probably get better responses in forum707.

A worksheet change event occurs whenever a cell value change occurs in a worksheet. Nothing happens in the Worksheet_Change event code logic, unless you have code in that event.

Or were you referring to the Calculation event? That depends on what calculation mode has been selected or if the user has chosen to calculate the sheet only, or if VBA code is calculating a range only, for instance.

So it seems we may need some clarification in order to answer your question.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BNPMike]

I'm not referring to VBA.

If you load a workbook and change a cell - and let's assume you have set the sheet to calculate immediately - Excel will need to evaluate all the dependencies on the visible sheet.

Since the workbook might contain many sheets, it might decide to only evaluate the sheet showing, and then extend the calculation each time a new sheet is activated. Obviously it would have to evaluate any dependencies in a 'hidden' sheet if the visible sheet had a dependancy in turn on that sheet, but it could ignore changes in other sheets if they didn't affect the sheets being displayed.

I'm trying to get an idea of how much Excel will slow down if you have lots of sheets.

 

[Gruuuu]

Excel definitely calculates all sheets that have values that may change on the regular calculation event, including those on other sheets.

There are other calculation methods though, but they are all manual (or can be coded with VBA). You can accomplish the effect you're seeking through VBA and event triggered calculations.

 

[SkipVought]

A Sheet Calculate does not force dependencies on other sheets to calculate. Only a full calculate will.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Have a look at:

http://www.decisionmodels.com/optspeedb.htm

http://www.decisionmodels.com/calcsecrets.htm

Gavin