Slow response and calculate times with large spreadsheet

  • Thread starter Thread starter Les Anthony
  • Start date Start date
L

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
 
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
 
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
 
Back
Top