C H A P T E R    13 Microsoft Office 97/Visual Basic Programmer's Guide

Optimizing for Size and Speed

Contents

Visual Basic is an extremely flexible programming language: there are often several ways to accomplish the same task. When you first start to program, or when you write a macro that will run only once, you'll probably be satisfied with simply "getting the job done." When you write a macro that will be used many times — such as a macro that prepares a weekly report, or an Auto_Open macro that runs every time you open a workbook or document — or when you write a macro that will be used by other people, you'll probably want to optimize the macro so that it requires less time and memory to run. The techniques described in this chapter will help you write smaller, faster macros.

Note   For information about optimizing Visual Basic in Microsoft Access, see Building Applications with Microsoft Access 97, available in Microsoft Access 97 and Microsoft Office 97, Developer Edition. An online version of Building Applications with Microsoft Access 97 is available in the Value Pack on CD­ROM in Microsoft Access 97 and Microsoft Office 97, Professional Edition.

General Optimization Strategies

Use the following techniques for optimizing your Microsoft Excel, Word, and PowerPoint code.

Note   Most of the example code in this section was written in Microsoft Excel, but the principles also apply to Word and PowerPoint. For information about optimizing Visual Basic in Microsoft Access, see Building Applications with Microsoft Access 97.

Minimizing OLE References

Every Visual Basic method or property call requires one or more calls through the OLE IDispatch interface. These OLE calls take time. Minimizing the number of method or property calls is one of the best ways to make your macro run faster.

Because you use a period (a "dot") to separate the parts of a Visual Basic statement, an easy way to keep track of the number of method and property calls is to "count the dots." For example, the following statement contains three dots. 

Workbooks(1).Sheets(1).Range("c5").Value = 10

The following statement contains only one dot. 

ActiveWindow.Left = 200
The examples in the following sections demonstrate how reducing the number of dots creates faster­running code.

Using Object Variables

If you find that you're using the same object reference over and over, you can set a variable for the object and subsequently use the variable in place of the object reference. This way, you'll only need to call the object accessor once, when you set the variable, instead of calling it each time you want to refer to the object. The following example calls the Workbooks method and the Sheets method twice each. 

Workbooks(1).Sheets(1).Range("c5").Value = 10
Workbooks(1).Sheets(1).Range("d10").Value = 12

You can optimize the preceding example by setting an object variable. The following example calls the Workbooks method and the Sheets method only once each. 

Set sheet = Workbooks(1).Sheets(1)
sheet.Range("c5").Value = 10
sheet.Range("d10").Value = 12

Using the With Statement

You can use the With statement to eliminate the need for repetitive object references, without setting an explicit object variable. The example in the preceding section could be rewritten as follows, using the With statement. The following example calls the Workbooks method and the Sheets method only once each. 

With Workbooks(1).Sheets(1)
	.Range("c5").Value = 10
	.Range("d10").Value = 12
End With

Using the With statement eliminates the need for the intermediate variable used in the example in the preceding section; otherwise, this code is the same as in that example.

Using a For Each...Next Loop

Using a For Each...Next loop to iterate through a collection or array is faster than using an indexed loop. In most cases, using a For Each...Next loop is also more convenient and makes your macro smaller and easier to read and debug.

The following example is slow because it sets the row variable thisRow by calling r.Rows(i) each time through the loop. 

Set r = Worksheets(1).Range("a1:a200")
For i = 1 To r.Rows.Count
	Set thisRow = r.Rows(i)
	If thisRow.Cells(1, 1).Value < 0 Then
		thisRow.Font.Color = RGB(255, 0, 0)
	End If
Next
The following example is faster and smaller than the preceding one because the For Each...Next loop keeps track of the row count and position. 
For Each thisRow In Worksheets(1).Range("a1:a200").Rows
	If thisRow.Cells(1, 1).Value < 0 Then
		thisRow.Font.Color = RGB(255, 0, 0)
	End If
Next

Keeping Properties and Methods Outside Loops

Your code can get variable values faster than it can get property values. Therefore, if your code gets the value of a property within a loop, it will run faster if you assign the property to a variable outside the loop and use the variable instead of the property inside the loop. The following example is slow because it gets the Value property each time through the loop. 

For iLoop = 2 To 200
	Cells(iLoop, 1).Value = Cells(1, 1).Value
Next

The following example is faster than the preceding one because the value of one property has been assigned to the variable cv before the loop begins. Visual Basic must therefore access only one property value (instead of two) each time through the loop. 

cv = Cells(1, 1).Value
For i Loop = 2 To 200
	Cells(iLoop, 1).Value = cv
Next
If you're using an object accessor inside a loop, try to move it outside the loop. The following example calls the ActiveWorkbook property, the Sheets property, and the Cells property each time through the loop. 
For c = 1 To 1000
	ActiveWorkbook.Sheets(1).Cells(c, 1) = c
Next

Rewriting this example by using the With statement moves the ActiveWorkbook property and Sheets property calls outside the loop. You could also move these calls outside the loop by using an object variable. 

With ActiveWorkbook.Sheets(1)
	For c = 1 To 1000
		.Cells(c, 1) = c
	Next
End With

Using Collection Index Numbers

With most object accessor methods and properties, you can specify an individual object in a collection either by name or by number. Using the object's index number is usually faster. If you use the object's name, Visual Basic must resolve the name to the index value; if you use the index value, you avoid this extra step.

There are, however, some significant advantages to specifying an object in a collection by name. One advantage is that using an object's name makes your code easier to read and debug. In addition, specifying an object by name is safer than specifying it by index number, because the index value for an object can change while your code is running. For example, a menu's index number represents the menu's position on the menu bar; therefore, the index number can change if menus are added to or deleted from the menu bar. This is one instance where faster isn't necessarily better. You should use this technique only when you're sure that the index value cannot change.

Minimizing Object Activation and Selection

Most of the time, your code can operate on objects without activating them. If you learned Visual Basic programming by using the macro recorder, you're probably accustomed to activating or selecting an object before you do anything to that object. The macro recorder does this because it must follow your keystrokes as you activate windows and select their contents. However, you can usually write much simpler and faster Visual Basic code that produces the same results without activating or selecting each object before working with it. For example, filling cells C1:C20 on Sheet5 with random numbers (using the AutoFill method) produces the macro recorder output shown in the following example. 

Sheets("Sheet5").Select
Range("C1").Select
ActiveCell.FormulaR1C1 = "=RAND()"
Selection.AutoFill Destination:=Range("C1:C20"), Type:=xlFillDefault
Range("C1:C20").Select
All of the Select method calls are unnecessary. You can use the With statement to write code that operates directly on the worksheet and the range, as shown in the following example. 
With Sheets("Sheet5")
	.Range("C1").FormulaR1C1 = "=RAND()"
	.Range("C1").AutoFill Destination:=.Range("C1:C20"), _
		Type:=xlFillDefault
End With

Keep in mind that the macro recorder records exactly what you do — it cannot optimize anything on its own. The recorded macro uses the AutoFill method because that's how the user entered the random numbers. This isn't the most efficient way to fill a range with random numbers. You can do the same thing with a single line, as shown in the following example. 

Sheets("Sheet5").Range("C1:C20").Formula = "=RAND()"

When you optimize recorded code, think about what you're trying to do with the macro. Some of the operations you can perform in the user interface (such as dragging a formula from a single cell into a range) are recorded as methods (such as AutoFill) that can be eliminated in the optimized code because there's a faster way to perform the same operation in Visual Basic.

Removing Unnecessary Recorded Expressions

Another reason why the macro recorder produces inefficient code is that it cannot tell which options you've changed in a dialog box. The recorder therefore explicitly sets all available options when you close the dialog box. For example, selecting cells B2:B14 and then changing the font style to bold in the Format Cells dialog box produces the recorded macro shown in the following example. 

Range("B2:B14").Select
With Selection.Font
	.Name = "Arial"
	.FontStyle = "Bold"
	.Size = 10
	.Strikethrough = False
	.Superscript = False
	.Subscript = False
	.OutlineFont = False
	.Shadow = False
	.Underline = xlNone
	.ColorIndex = xlAutomatic
End With

You can set the font style for the specified cell to bold with a single line of code and without selecting the range, as shown in the following example. 

Range("B2:B14").Font.FontStyle = "Bold"

Again, if you think about what you're trying to do with the macro and you look through the lists of properties and methods that apply to the Font object, you'll see that you could also write this macro using the Bold property, as shown in the following example. 

Range("B2:B14").Font.Bold = True
You can also experiment with the macro recorder by recording the same operation performed different ways in the user interface. For example, if you format a range by using the Bold button on the Standard toolbar, the macro recorder uses the Bold property.

Minimizing the Use of Variant Variables

Although you may find it convenient to use Variant variables in your code, Visual Basic requires more time to process a value stored in a Variant variable than it needs to process a value stored in a variable declared with an explicit data type. Your code can perform mathematical computations that don't involve fractional values faster if you use Integer or Long variables rather than Variant variables. Integer or Long variables are also the best choice for the index variable in For...Next loops. The speed you gain using explicit variable types can come at the expense of flexibility. For example, when using explicit data types, you may encounter cases of overflow that Variant variables handle automatically.

Using Specific Object Types

References to objects and their methods and properties are resolved either when your macro is compiled or when it runs. References that are resolved when the macro is compiled are faster than references that must be resolved while the macro is running.

If you declare variables and arguments as specific object types (such as Range or Worksheet), Visual Basic can resolve references to the properties and methods of those objects when your macro is compiled. For a list of specific object types, see the Object Browser.

In addition, you should use fully qualified object references. This eliminates ambiguity and ensures that the variable has the intended type. A fully qualified object reference includes the library name, as shown in the following examples. 

Dim wb As Excel.Workbook
Dim dc As Word.Document, cb As MSForms.CommandButton

If you declare variables and arguments with the generic Object data type, Visual Basic may have to resolve references to their properties and methods when it encounters them at run time, resulting in a significantly slower process.

Using Constants

Using constants in an application makes the application run faster. Constants are evaluated once and are stored when your code is compiled. Variables can change, though, so Visual Basic must get the current variable value each time the macro runs. Constants also make your macros more readable and easier to maintain. If there are strings or numbers in a macro that don't change, declare them as constants.

Turning Off Screen Updating

A macro that makes changes to the appearance of a document — such as a macro that changes the color of every other cell in a large range or that creates a large number of graphic objects — will run faster when screen updating is turned off. You won't be able to watch the macro run (the changes will appear all at once when you turn screen updating back on), but it will run much faster. You may want to leave screen updating turned on while you write and debug the macro, and then turn it off before you run the macro.

To turn off screen updating, set the ScreenUpdating property to False, as shown in the following example. 

Application.ScreenUpdating = False
Remember to set the ScreenUpdating property back to True when your macro finishes running (older versions of Microsoft Excel automatically reset this property, but Microsoft Excel 97 and Word 97 don't).

Tip   You can sometimes achieve the same effect by not activating the object you're changing. For example, if you create graphic objects on a sheet without first activating the document, you don't need to turn screen updating off because the changes won't be visible anyway.

Strategies for Optimizing Microsoft Excel

In addition to the general information discussed in this chapter, you can use the following techniques to create smaller and faster macros in Microsoft Excel.

Using Worksheet Functions

A Microsoft Excel worksheet function that operates on a range of cells is usually faster than a Visual Basic macro that accomplishes the same task. For example, the SUM worksheet function is much faster than Visual Basic code that iterates a range and adds the values in the range's cells. For example, the following code runs relatively slowly. 

For Each c In Worksheets(1).Range("A1:A200")
	totVal = totVal + c.Value
Next

The following code runs faster than the preceding example. 

totVal = Application.WorksheetFunction.Sum(Worksheets(1).Range("a1:a200"))
Function that produce aggregrate results (such as PRODUCT, COUNT, COUNTA, and COUNTIF) are good candidates for replacing slower Visual Basic code, as are worksheet functions (such as MATCH and LOOKUP) that can take a range as an argument.

Using Special­Purpose Visual Basic Methods

There are also several special­purpose Visual Basic methods that offer a concise way to perform a specific operation on a range of cells. Like worksheet functions, these specialized methods are faster than the general­purpose Visual Basic code that accomplishes the same task.

The following example changes the value in each cell in the range A1:A200 in a relatively slow way. 

For Each c In Worksheets(1).Range("a1:a200").Cells
	If c.Value = 4 Then c.Value = 4.5
Next

The following example, which uses the Replace method, performs the same operation much faster. 

Worksheets(1).Range("a1:a200").Replace "4", "4.5"

The following example shows a relatively slow way to add a blue oval to each cell in the range A1:A500 that contains the value 4. 

For Each c In Worksheets(1).Range("a1:a500").Cells
	If c.Value = 4 Then
		With Worksheets(1).Ovals.Add(c.Left, c.Top, c.Width, c.Height)
			.Interior.Pattern = xlNone
			.Border.ColorIndex = 5
		End With
	End If
Next

The following example, which uses the Find and FindNext methods, performs the same task much faster. 

With Worksheets(1).Range("a1:a500")
	Set c = .Find(4)
	If Not c Is Nothing Then
		firstAddress = c.Address
		Do
			With Worksheets(1).Ovals.Add(c.Left, c.Top, _
					c.Width, c.Height)
				.Interior.Pattern = xlNone
				.Border.ColorIndex = 5
			End With
			Set c = .FindNext(c)
		Loop While Not c Is Nothing And c.Address <> firstAddress
	End If
End With

For more information about special­purpose Visual Basic methods, see the topic in Help that pertains to the object you're working with, and examine the list of that object's methods. You can also examine the list of all Visual Basic methods on the Contents tab in the Help Topics dialog box.

Strategies for Optimizing Microsoft Word

In addition to the general information discussed in this chapter, you can use the following techniques to create smaller and faster macros in Word.

Using Range Objects

Working with Range objects is faster than working with the Selection object. You can define and use multiple Range objects, which are invisible to the user.

Using Next and Previous

Whenever possible, use Next and Previous to return the next or previous item in a collection. For example, using myRange.Next Unit:=wdWord is faster than indexing the collection of words (myRange.Words(10)).

Avoiding Using the WordBasic Object

Methods of the WordBasic object are slower than methods and properties of other Visual Basic objects. For example, WordBasic.FileOpen is slower than Documents.Open.

Executing Built­in Dialog Boxes

A With statement is an efficient way to set many properties of a single object. Another technique for setting multiple properties is to set the properties of a built­in dialog box and then execute the dialog box. Executing a built­in dialog box is faster than using the With statement because the built­in dialog box stores the property values and then sets them all at once (using the Execute method), whereas the With statement sets properties one at a time. The following example sets a number of paragraph formatting properties by using a With statement. 

With Selection.ParagraphFormat
    .Alignment = wdAlignParagraphCenter
    .KeepWithNext = True
    .LeftIndent = InchesToPoints(0.5)
End With
The following example sets the same properties as the preceding example, but runs faster because it executes a built­in dialog box. 
Set dlg = Dialogs(wdDialogFormatParagraph)
dlg.Alignment = wdAlignParagraphCenter
dlg.KeepWithNext = True
dlg.LeftIndent = "0.5"
dlg.Execute