VBA calculate manually

[Jan]

dear all,

I have the following problem. I have an excel file with a
lot of Vlookup's. I also run in VBA-macro in this excel
file. This macro take a long time because these vlookup's.
When I calculate my excel manually (adaption in tools-
options-calculations-manual) then my macro is much faster.
Do you have an idea how I can take this into account in my
macro? Otherwise I always have to click on F9 and run my
macro.

Thanks for any response,
Jan

 

[Harald Staff]

Hi Jan

Insert into your macro

Application.Calculation = xlManual
' your dramatic actions here, and
Application.Calculation = xlAutomatic

HTH. Best wishes Harald

 

[Guest]

Set the calculation in the macro using

Private Sub MakeBig Change()
On Error Goto ErrorHandler

application.caculation = xlManual
'Do that voodoo that you do

application.calculation = xlAutomatic

exit sub
ErrorHandler:
application.calculation = xlAutomatic
end sub

I highly recommend an error handler any time you change an application level
setting such as calculation or EnableEvents, to reset the system when
something crashes, because if you do enough of this stuff eventually
something will crash.