VBA Tips

Mark Mudron offered this response to Steve Hansen’s programming article from last week’s issue:

Referring to the Essential Office Programming section of your most recent news letter regarding Application.ScreenUpdating…
I discovered this handy command quite awhile ago while working on large spreadsheets. The performance gain, as was pointed out, was enormous. My problem is, how does the user know that the macro is doing something? If the macro takes too long to run, the user may get impatient and try to terminate the macro or the application.

In the past, I have taken a bit of a performance hit to highlight the first cell of the row the macro is working on (or every nth row if you want to write an if/then statement). This allows the user to know something is happening without having to show every update.

Is there a better way to do this? Is there a way to display and update a progress box while still allowing the macro to run in the background?

You bet there is, Mark. Thanks for the question. This week Steve provides the answer.

- - -

A couple of weeks ago, I discussed the use of Application.ScreenUpdating in Excel to improve the performance of your macros and prevent screen flicker while your macro executes. When you turn screen updating off, it is helpful to provide some sort of feedback to the user so that they know that their computer isn’t locked up.

The method I prefer to use is using Application.StatusBar, which allows you to change the information appearing in the status bar. While the visibility of the status bar can be changed by the user, it is shown by default and in my experience, very few people ever hide it. It is a very non-intrusive way to display progress information.

While some programmers occasionally worry that “my users won’t notice it”, trust me -– they will. Based on solutions I’ve deployed, I’ve never had any feedback to the contrary. When it comes to progress information, subtle is good.

One of the reasons subtle is good when it comes to progress information is that providing progress information has a performance cost. If the reason you are turning screen updating off is to maximize performance, you probably don’t want to implement a progress information system that consumes all of the performance gain. The key is to find a refresh rate that provides reasonable progress information without a noticeable impact on performance.

Listing 1 provides an example of how to use Application.StatusBar in a common scenario –- providing progress information as you process individual rows in Excel.

Listing 1: A demonstration of Application.StatusBar

Sub StatusBarExample()
Dim lRow As Long
Dim ws As Worksheet
Dim lFrequency As Long

' Turn screen updating on and reset the status bar
Application.ScreenUpdating = True
Application.StatusBar = False
Set ws = Nothing
End Sub

To experiment with the performance impact of using Application.StatusBar, run the StatusBarExample a few times with different values for lFrequency. For example try 10, 100, and 1000. This example uses the Mod function to determine if the status bar should be updated. Mod divides two numbers and returns the remainder of the division.

Since you can write arbitrary text to the StatusBar, you can use lots of other schemes besides rows. For example, a batch processing routine may use the filename of the file it is currently processing. A routine that harvests information from numerous worksheets may display the name of the current worksheet. A process that reads data from a database may display a primary key or other identifying attribute (perhaps combined with a frequency modifier such as every nth row).

In summary, Application.ScreenUpdating and Application.StatusBar are very complimentary when used together to maximize the performance of a macro while providing some basic progress information so that the computer doesn’t appear locked-up.