Excel Macros Tutorial – Displaying Updates For The End-User

Excel macros

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials
***************

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


***************************************************

Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.