running speed

[eggpap]

Hello,

I have a workbook with many sheets, cells linked to validation lists,
external data, many udfs etc.
The speed of calculation of the results is very much low. Any tips to
increase that speed?

Emiliano

 

[RyanH]

Get a faster computer! lol. Just kidding. Do you have any samles of your
code to look at?

 

[ytayta555]

external data, many udfs etc.
The speed of calculation of the results is very much low. Any tips to
increase that speed?

Be carefully with udfs , them are volatile functions , any change in
data
recalculate all udfs ; how many udfs you have ?

 

[Gord Dibben]

Check out tips at Charles Williams' Excel Pages.

http://www.decisionmodels.com/


Gord Dibben MS Excel MVP

 

[Jim Thomlinson]

UDF's are only volatile if you include Application.Volatile in the function.
Otherwise they use smart calc like any other function in XL.

 

[eggpap]

Thanks,

I 'll prepare the sample code asap.

Emiliano

 

[eggpap]

Thanks,

so if I want the udf not volatile I must use the statement
Application.Volatile False before the udf code?
I don't find the message of ytayta555, so I use this to reply to him:
I have 4 sheets with about 150 udfs in it.

Emiliano

 

[ytayta555]

so if I want the udf not volatile I must use the statement
Application.Volatile False before the udf code?

In case that you have this line of code in the code of UDF , you
must delete all this line of code , not ,,Application.Volatile
False ,,
after SubUDF ....

I don't find the message of ytayta555, so I use this to reply to him:
I have 4 sheets with about 150 udfs in it.

You don't have too much UDF , but If you macro handle with the
range which UDF are reffering in call, UDF recalculate every time and
slow
your macro ;
I think is a goog thing to post the code of your UDF tipe , maybe
can be some improvements in it ; according with what your macro
is doing , maybe you can handle with Application .Calculation =
xlManual
or xlAutomatic .