Slow response and calculate times with large spreadsheet

[Les Anthony]

I have a 2.19 GHZ Pentium 4 processor with 1 GB of ram
and have created a spreadsheet which is about 62 mb in
size. This is a very computationally intense spreadsheet
and the model requires about 1 minute to calculate. I
would be interested in knowing what the most effective
way of decreasing calculate time would be, short of
making the spreadsheet smaller. I have tried increasing
the priority level to high in task manager for
excel.exe. If additional memory would solve the trick, I
can add it, but I would like to know if this would really
help.

Thanks

 

[Nick Hodge]

Les

Excel has a finite resource pool no matter what the memory in your system.
You may want to check this out

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

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Lars Hammarberg]

You could look through your functions and make sure you don't fall into some
traps.
This goes for your own functions created in VBA... the ones built-in are a
bit more diffcult to edit.
If you want to avoid having all your user-defined functions (udf) calculate
whenever something happens in _any_ cell on the spreadsheet, you can add the
line
Application.Volatile False
a the top of your function - this ensures that the function will calculate
only when cells used as input parameters to that function changes - if not
(the default is True) the function will re-calculate whenever something
happens somewhere.
The function will re-calculate in spite of the Volatile False if you've got
some special functions on the sheet involved in a calculation step - can't
remember most of them, but =Date() is one of them... which will insure
almost any udf will re-calculate most of the time if that's the starting
point of some calculations.
A work-around is to create an udf like
Public Function MyTime() As Date
Application.Volatile True
MyTime = Now()
End Function

and use that instead.

/Lars Hammarberg
www.camako.se

 

[Charles Williams]

Hi Lars,

the default for UDFs is that they are NOT volatile.
If you add application.volatile they become volatile.

You do not need to add Application.volatile false

Charles
______________________
Decision Models
FastExcel Version 2
www.DecisionModels.com