Gaining Performance and Polish with Screen Updating

The ScreenUpdating property is a read/write Boolean property. This means you can set it to be true or false. By default, ScreenUpdating = true, and as your code executes, Excel constantly updates the display as it goes about its actions. If you set ScreenUpdating to false, the screen doesn't update as your procedures run.

It can be very satisfying to watch the screen flicker as your procedures run and your code completes some task in a matter of seconds or minutes that used to take you 10, 20, or 60 minutes or more. After you do this a few times however, you can achieve even more satisfaction by turning ScreenUpdating off at the beginning of your procedure and then turning it back on at the end. Two significant benefits stem from this. First, your code runs considerably faster if ScreenUpdating is turned off. How much faster? Well that depends on how much your procedure performs actions that trigger Excel's need to update the display. Second, your application looks more professional—and we all want to look good, right?

Listing 5.1 demonstrates how to use the ScreenUpdating property and performs a simple test to help quantify the performance impact of turning on/off ScreenUpdating. The first procedure, the subroutine named TimeScreenUpdating, is responsible for calling the function TestScreenUpdating and displaying the result via a message box. TimeScreenUpdating uses the Format function to display the result formatted with only two decimal places. Without the format function, you'd see a bunch of digits after the decimal.

Listing 5.1: Performance Implications of Screen Updating

Sub TimeScreenUpdating() Dim dResult As Double

' Test with screen updating turned on dResult = TestScreenUpdating(True)

MsgBox Format(dResu1t, "0.00") & " seconds.", vbOKOnly

' Test with screen updating turned off dResult = TestScreenUpdating(False)

MsgBox Format(dResu1t, "0.00") & " seconds.", vbOKOnly End Sub

Function TestScreenUpdating(bUpdatingOn As Boolean) As Double Dim nRepetition As Integer Dim ws As Worksheet Dim dStart As Double

' Record the start time dStart = Timer

' Turn screen updating on or off Application.ScreenUpdating = bUpdatingOn

' Loop through each worksheet ' in the workbook 250 times For nRepetition = 1 To 250

For Each ws In ThisWorkbook.Worksheets ws.Activate



' Turn screen updating on Application.ScreenUpdating = True

' Return elapsed time since procedure started TestScreenUpdating = Timer - dStart

' Clean up Set ws = Nothing

End Function

The real workhorse of Listing 5.1 is the function TestScreenUpdating. TestScreenUpdating takes a Boolean (true/false) input parameter named bUpdatingOn and returns a Double (double precision floating point number) that represents the time it took to execute the procedure. To determine the elapsed time, you use the Timer function. Timer returns the fractional number of seconds that have elapsed since midnight.

Next, screen updating is turned on or off depending on the value of the input parameter. To give the procedure something to do, simply loop through all of the worksheets in the workbook 250 times, activating each worksheet as you get to it. This section of code utilizes another property of the application object—ThisWorkbook, which returns a workbook object that represents the workbook in which your code is running.

Finally, the function turns screen updating on and returns the elapsed time by subtracting the start time from the finish time. On my computer, the trial through the worksheets with ScreenUpdating turned on took 6.41 seconds. With ScreenUpdating turned off, the process only took 1.14 seconds.

Was this article helpful?

0 0

Post a comment