Often, while running excel macros, especially ones that take a while to run, we turn off the screen updating function in excel. That is done quite simply by adding the following into a macro:
With Application
.ScreenUpdating = False
End With
Then, at the end, I simply re-activate the screen update feature by adding the following:
With Application
.ScreenUpdating = True
End With
The main benefit is that this simple line of code can make a major difference in the speed of the macro.
The downside however is that you no longer see what’s going on in the code. For macros that take some time to run, this can be a bit worrying. One way to get around this is having the macro give an update or message. There are two different ways this can be done.
#1-Message Box:
MsgBox “The macro has finished running”
#2-Status Bar Updates
One alternative is to have the message displayed in the status bar. The advantage is that no click is necessary to move to the next step, here is a sample code:
Application.ScreenUpdating = False
‘ turns off screen updating
Application.DisplayStatusBar = True
‘ makes sure that the statusbar is visible
Application.StatusBar = “Please wait while performing task 1…”
Application.StatusBar = False
‘ gives control of the statusbar back to the programme
This is what it would look like:
Both methods are generally good ways to get updates without needing to see every single step that the macro is doing.
***************************************************
Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss
***************************************************
