Tips for Faster Procedures

Short procedures usually are over in the blink of an eye. However, the longer your procedures get, and the more they interact with application objects, the more time they take to complete their tasks. For these more complex routines, you need to start thinking not only about what the procedure does, but how it does it. The more efficient you can make your code, the faster the procedure will execute. This section gives you a few tips for writing efficient code that runs quickly.

Turn Off Screen Updating

One of the biggest drags on procedure performance is the constant screen updating that occurs. If your procedure uses many statements that format text, enter formulas, or cut and copy data, the procedure will spend most of its time updating the screen to show the results of these operations. This not only slows everything down, but it also looks unprofessional. It's much nicer when the procedure performs all its chores behind the scenes and then presents the user with the finished product at the end of the procedure.

You can do this with the Application object's ScreenUpdating property. Set ScreenUpdating to False to turn off intermediate screen updates that you don't want the user to see, and set it back to True to resume updating.

Hide Your Documents

If your procedure does a lot of switching between documents, you can speed things up by hiding the documents while you work with them. To do this, set the document's Visible property to False. You can work with hidden documents normally, and when your procedure is done, you can set Visible to True to display the results to the user.

Tip: As soon as you've hidden an active document,VBA deactivates it.Therefore, if your procedures reference the active document, you need to activate the document (using the Activate method) right after hiding it.

Don't Select Data Unless You Have To

Two of VBA's slowest methods are Activate and Select, so you should use them sparingly. In the majority of cases, you can indirectly work with ranges, worksheets, text, and other data. In Excel, for example, you can work with a Range object by referencing it as an argument in the Range method (or in any other VBA statement that returns a Range object) and the Worksheets collection.

In Excel, Don't Recalculate Until You Have To

As you know, manual calculation mode prevents Excel from recalculating a worksheet until you say so. This saves you time when you're using sheets with complicated models-models in which you don't necessarily want to see a recalculation every time you change a variable.

You can get the same benefits in your procedures by using the Application object's Calculation property. Place Excel in manual calculation mode (as described earlier in this tutorial) and then, when you need to update your formula results, use the Calculate method.

Optimize Your Loops

One of the cornerstones of efficient programming is loop optimization. Because a procedure might run the code inside a loop hundreds or even thousands of times, a minor improvement in loop efficiency can result in considerably reduced execution times.

When analyzing your loops, make sure that you're particularly ruthless about applying the preceding tips. One Select method is slow; a thousand will drive you crazy. Also, make sure that you define any counter used in your loops as Integer variables, which use the least memory of the numeric types.

Also, weed out from your loops any statements that return the same value each time. For example, consider the following procedure fragment:

For i = 1 To 50000
    Application.StatusBar = "Value: " & Worksheets("Sheet1").[A1].Value
Next 'i

The idea of this somewhat useless code is to loop 50,000 times, each time displaying in the status bar the contents of cell A1 in the Sheet1 worksheet. The value in cell A1 never changes, but it takes time for Excel to get the value, slowing the loop considerably. A better approach would be the following:

currCell = Worksheets("Sheet1").[A1].Value
For i = 1 To 50000
    Application.StatusBar = "Value: " & currCell
Next I

Transferring the unchanging currCell calculation outside the loop and assigning it to a variable means that the procedure has to call the function only once.

To test the difference, Listing below shows the TimingTest procedure. This procedure uses the Timer function (which returns the number of seconds since midnight) to time two For...Next loops. The first loop is unoptimized, and the second is optimized. On my system, the unoptimized loop takes about nine seconds, and the optimized loop takes only three seconds-a third of the time.

A Procedure That Tests the Difference Between an Optimized and an Unoptimized Loop

Sub TimingTest()
    Dim i As Long, currCell As Variant
    Dim start1 As Long, finish1 As Long
    Dim start2 As Long, finish2 As Long
    '
    ' Start timing the unoptimized loop
    '
    start1 = Timer
    For i = 1 To 50000
    Application.StatusBar = "The value is " & Worksheets("Sheet1
    ").[A1].Value
    Next i
    finish1 = Timer
    '
    ' Start timing the optimized loop
    '
    start2 = Timer
    currCell = Worksheets("Sheet1").[A1].Value
    For i = 1 To 50000
	Application.StatusBar = "The value is " & currCell
    Next i
    finish2 = Timer
    MsgBox "The first loop took " & finish1 - start1 & "
    seconds." & _ 
	   vbCrLf & _
	   "The second loop took " & finish2 - start2 & " seconds."
    Application.StatusBar = False
End Sub