There are two very simple ways to make your code run faster, and they are both properties of the Application object: ScreenUpdating and Calculation.
ScreenUpdating, set to False, causes your macro to run without refreshing the screen. This property can be useful if you don’t want the user to see the screen flicker as you write to cells or do other things. Even if you don’t care about flicker, refreshing the screen takes time and turning ScreenUpdating off allows Excel to devote its time to running you macro.
Calculation determines when Excel calculates as defined in Tools>Options>Calculation. If your code writes to cells, setting Calculation to xlCalculationManual can greatly reduce the execution time of your code. There are times when you need to calculate in your macro, such as if your macro uses Excel to get an intermediate calculation, but usually you can leave it set to manual until your done.
Don’t forget to set ScreenUpdating to True and Calculation back to xlCalculationAutomatic at the end of your code.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
‘Code here
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Just to be a little more user friendly, I try to set the calculation back to what they had it at instead of assuming they were on automatic. I know a few users who keep it on manual when working on some intensive workbooks.
Dim intOldState As Integer
intOldState = Application.Calculation
Application.Calculation = xlCalculationManual
‘some code here
Application.Calculation = intOldState
If I have to calculate in between, I just force it with a calculate statement. That brings up a question, though. If you use cell calculations in your code and your user had calculation set to manual before running it, won’t the whole thing go off the track? Maybe I need to make sure to force calculations all the time instead of assuming it’s on automatic?
Also, do you really need to turn screenupdating back on? It seems to take care of itself on exit.
rzf: Good point. I generally set it back to whatever it was also. As for cell calculations, if it’s in an add-in, for instance, I use a sheet in there to not affect the user’s workbook. If it’s my template, then I generally have more control over whether it’s automatic or manual. If I need a calculation, I use the Calculate method to make sure things are up-to-date before I get the number.
As for screen updating – yes it takes care of itself, but I don’t rely on MS.
rzf: I too, don’t trust MS for that. In fact, in XP I’ve noticed that there are some instances when Excel doesn’t set it back, I think they’re going for a more “drastic” approach in coding styles…
Also, even MS is saying that you should (From XP help file:)
“Remarks
Turn screen updating off to speed up your macro code. You won’t be able to see what the macro is doing, but it will run faster.
Remember to set the ScreenUpdating property back to True when your macro ends.”
Speaking of speeding up your code, I have a question:
These tree lines seems to do just the very same thing:
Range(“A1?).FormulaR1C1 = “1234?
Range(“A1?).Formula = “1234?
Range(“A1?).Value = “1234?
Does Excel executes them all with the same speed, or is one of them faster than the others???
Dan,
On some simple tests on my machine, the “.Value” method runs faster than the other two, while “.Formula” and “.FormulaR1C1? use the same time.
Hi,
Sometimes the screenupdating=False doesnt work.
When i step into code, it shows the false as true.
Further when I run the code, still the screen is refreshing.
Any idea why this is ?
Thanks,