slow response on worksheet calculate

[twd000]

I have a somewhat complex worksheet. Some macros, some dynamic named ranges, some formulas, etc

I also have a VBA-initiated call to Solver for optimization purposes.

I have run into very long runtimes when initiating the Solver call because Excel is re-calculating the entire sheet every time it makes a new guess for Solver solution.

I really only need a very limited range on the worksheet to be recalculated, so I tried setting the worksheet to Manual Calculation and then doing a manual Range.Calculate at the end of each trial solution. However, Solver won't even initialize, I suppose since it assumes it will not see any dependency on a manually-calculated sheet.

So I have gone back to Auto Calculation. Is there way to selectively tell Excel not to re-calculate a certain Range even though the greater sheet is set to AutoCalc?

In a related question, does a Calculation event also trigger updates to dynamically-defined named ranges? I have several ranges that are defined in terms of COUNTA. Do you think it is re-calculating all these named ranges every time a cell changes?

[twd000]

bump for a little help

specifically interested in the autocalc behavior of named ranges:

In a related question, does a Calculation event also trigger updates to dynamically-defined named ranges? I have several ranges that are defined in terms of COUNTA. Do you think it is re-calculating all these named ranges every time a cell changes?

does it matter whether a range is defined in the GUI or in VBA code?

[broro183]

hi twd000,

I imagine that one of the sections (see links under the page header) of the below page will give you all your answers & more!

When you start reading, you'll know why I haven't tried to answer you directly - my responses would be straight from Charles' page. I'm not sure (from memory) if he discusses Solver specifically or not.

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

In particular, check out the links called "Calculation Methods" & "Calculating from VBA".

hth
Rob