Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Micro-Optimization

Both VBA and Excel often provide many ways to do the same thing, some of which are always faster than the others, but some of which are sometimes faster and sometimes slower depending on the data being processed. This section identifies many of the common alternatives. Before blindly using the recommended alternative, you should always confirm the behavior using your own data. This can usually be done in a quick-and-dirty manner, using the code shown in Listing 17-11.

Listing 17-11. A Simple Routine to Compare Two Alternatives

Sub CompareThem()
Dim dStart As Double
Dim lCounter As Long
'We often need lots of loops to get a measurable result
Const 1LOOP_COUNT As Long = 10000
dStart = Timer
For lCounter = 1 To 1LOOP_COUNT
'The code for one alternative
Next
Debug.Print "Version 1 took " & (Timer - dStart) & " seconds"
dStart = Timer
For lCounter = 1 To 1LOOP_COUNT
'the code for the second alternative
Next
Debug.Print "Version 2 took " & (Timer - dStart) & " seconds"
End Sub

VBA's built-in Timer call is fairly slow and not very accurate, so we usually have to do each version many, many times to get a measurable result, proving these micro-optimizations will only have a noticeable effect if they're executed many times over.

VBA

Use Matching Data Types

VBA is very forgiving when we mix data typessuch as passing a Double to a procedure that expects a String or vice versa. However, there is some overhead associated with the conversion and it can introduce subtle bugs, so it should be avoided. Whenever passing a variable to a procedure, or setting one variable equal to another, always ensure the variables have the same data type.

Perform Explicit Conversions Instead of Implicit Ones

When you are unable to match data types, always tell VBA which conversion to perform, such as CStr(), CDbl() and so on. By being explicit about the conversion you want to perform, you allow wasting the time required by VBA to make the decision itself.

Use Len(string)=0 Instead of string=""

VBA stores strings in memory by storing the length of the string, followed by the characters it contains. As the length of the string is readily available, it is much quicker to check whether it is zero than to ask VBA to perform string comparisons (with all the memory allocations that involves).

Use Left$, Right$, Mid$ and So Forth Instead of Left, Right and Mid

Most of VBA's string-handling functions have both a variant (for example, Left, Right, Mid) and a string (Left$, Right$, Mid$) version. If you use the variant versions with string variables, VBA has to convert the inside string to a variant, pass it to the function, get the result (as a variant) and convert the result back to a string. By using the string version, VBA doesn't need to do the two variant-to-string conversions, which can be relatively slow, particularly with large strings.

Pass Strings and Variant Arrays ByRef Instead of ByVal

Whenever strings and arrays are passed to a procedure by value (ByVal), VBA has to take a copy of the entire string or array and pass the copy to the procedure. If the string or array is passed by reference (ByRef), VBA only has to pass a pointer to the procedure, which is much quicker.

Don't Use Option Compare Text

Adding Option Compare Text to the top of a module forces VBA to perform all string comparisons in a case-insensitive way. In the majority of cases, this will not be required and only wastes time. Instead, every module should have Option Compare Binary set and you should use the CompareMethod parameter of StrComp, Instr and so on to specify when case-insensitive comparisons are required. If you need to use a function that doesn't have a CompareMethod (such as Like), you should either force both strings to upper- or lowercase and do a normal binary compare, or have a specific routine to do the comparison and place it in its own module with Option Compare Text set.

Use Early Binding Wherever Possible

Whenever you declare a variable As Object, VBA doesn't know anything about it until runtime. Every time you call a property or method of the object, VBA has to check whether the method exists, check its parameters and confirm your code can call it. All of that takes time and should be avoided by giving all your variables specific types. If you are using As Object to be able to call the same property (for example, Name) on a number of your own classes, you should implement a custom interface in those classes instead (see Chapter 11 Interfaces).

Use Integer Arithmetic Where Possible

VBA can perform integer arithmeticparticularly divisionmuch faster than floating-point arithmetic. You can tell VBA to use integer arithmetic by declaring your variables As Long, or by using the integer division operator, \:

'Slower-uses floating-point operations
dMid = (dLow + dHigh) / 2
'Faster-uses integer operations
lMid = (lLow + lHigh) \ 2

Use For Each to Iterate Collections (Not by Index)

VBA's Collection object is designed to be iterated most efficiently using the For Each construct, instead of For Next.

Use For … Next to Iterate Arrays (Not For Each)

VBA's arrays, however, are faster to iterate by index instead of using For Each.

Use Dictionaries Instead of Collections (If Order Isn't Important)

The Microsoft Scripting Runtime library, scrrun.dll, contains a very fast and lightweight Dictionary object, which can be used just like a VBA Collection. As well as being faster, it exposes both the items and the keys used to store them and supports the Exists property to check whether a key exists in the collection. Its biggest drawback is it does not allow items be inserted into the middle of the list, so can't be used when re-ordering is required.

Don't Use If bVariable = True Then, Just Use If bVariable Then

If you have a Boolean variable, adding the extra step of comparing it to True in an If statement is just wasting processing cycles. The redundant comparison to True should be removed.

Don't Use IIf()

VBA's IIf() function is a very convenient way to choose between two alternatives. However, it is also extremely slow, compared to the longer multiline If statement and always evaluates both the True and False expressions.

Use Multiple If…ElseIf…End If Instead of Select Case

Similarly, Select Case is a convenient, clear and easy-to-read construct for choosing between multiple alternatives, but is also slower than the equivalent If…ElseIf construct.

Use With blocks and Object Variables to Reduce the Dots

VBA enables us to navigate through object model hierarchies using the dot ( . ) operator to access an object's properties or methods. Think of every dot as a small pause in your application and reduce them by using With blocks or an object variable.

Excel

Turn Off ScreenUpdating and Automatic Calculation

The biggest gains when automating Excel are to set Application.ScreenUpdating = False and Application.Calculation = xlManual. That will stop Excel continually refreshing its display or recalculating everything when data is written to the sheet.

Don't Select Things

The macro recorder produces extremely inefficient code, peppered with code such as this:

Range("A1").Select
Selection.Font.Bold = True

It is extremely rare to ever need to select anything when controlling Excel from VBA. In most cases, these two lines can be combined by removing the Select/Selection:

Range("A1").Font.Bold = True

You will occasionally need to insert an extra object between the Select and Selection, particularly when charts or drawing objects are involved, such as changing a chart's title:

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartTitle.Select
Selection.Characters.Text = "Hello"

Becomes:

ActiveSheet.ChartObjects("Chart 1").Chart.ChartTitle _
.Characters.Text = "Hello"

Use Variant Arrays

Instead of reading and writing cells one by one, it is much quicker to read a range of cells into a Variant variable, then process the variable as a 2D array, or populate a Variant array then write it to a range of cells, as shown in Listing 17-12.

Listing 17-12. Reading and Writing Variant Arrays

Sub ReadWriteVariants()
Dim vaData As Variant
Dim lRow As Long
Dim lCol As Long
'Read the data from the sheet in one go
vaData = ActiveSheet.Range("A1:B10").Value
'Process the data within VBA
For lRow = 1 To UBound(vaData)
For lCol = 1 To UBound(vaData, 2)
If IsNumeric(vaData(lRow, lCol)) And _
Not IsEmpty(vaData(lRow, lCol)) Then
vaData(lRow, lCol) = vaData(lRow, lCol) * 2
End If
Next
Next
'Write the data to the sheet in one go
ActiveSheet.Range("D1:E10").Value = vaData
End Sub

Don't Use ActiveSheet, Selection or Worksheets() Repeatedly

Some of the more commonly used properties in the Excel object modelsuch as ActiveSheet, Selection or Worksheetsreturn the generic Object type, so all calls that use these objects will be late-bound and slow. For best performance, you should declare a variable of the specific data type and set it to be the ActiveSheet, Selection and so forth.

Test a Property Before Setting It

It is often much faster to read a property than to write it. It can save time to only update a property when it needs to change, by checking whether it is the required value first. This contradicts the general rule of reducing the amount of code you write, but it can be readily observed that reading the value of an Excel object's property (such as Range.Font.Bold) makes it much quicker to subsequently set the same property.

Use Doubles to Talk to Excel

When passing numbers to Exceleither to populate a worksheet cell or as parameters to Excel functionsit is usually most efficient to pass variables declared As Double. This is because Excel generally uses the Double data type internally and so avoids type conversions. When populating a cell, Excel will also try to apply cell formatting if other data types (such as Date or Currency) are used. Using Double's throughout avoids Excel's autoformatting and so improves performance.

Use the PAGE.SETUP XLM Function Instead of the PageSetup Object

Whenever you change any of the properties of the PageSetup object, Excel repaginates the page, to check whether the automatic zooming or automatic page breaks need to change. To do this, Excel has to communicate with the printer drivers, which is extremely slow. This can be avoided by using the PAGE.SETUP XLM function, which is fully documented in the macrofun.hlp file available from [ http://support.microsoft.com/?kbid=128175 ], as shown in Listing 17-13. Note that the PAGE.SETUP function always applies the settings to the active sheet.

Listing 17-13. Using PAGE.SETUP to Set a Page Header

Sub SetHeaders()
'Set the header using the slow PageSetup object
With ActiveSheet.PageSetup
.LeftHeader = "Hello"
.RightHeader = "World"
End With
'Set the header using the faster PAGE.SETUP XLM function
ExecuteExcel4Macro "PAGE.SETUP("&LHello &RWorld")"
End Sub