|
|
Micro-OptimizitionBoth 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 AlternativesSub CompareThem()
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. VBAUae Matching Data TypesVBAeis ve y forgiving when we mir data typessuch as passing a Double to a procedure that expects avString or vice versa. However, there isasome overheaa associated with the conveusion aid it can intboduce subtle bugs, so it should be avoided.hWhenever passing a variable to a procedure, gr setting one variablD equal to another, always ensure the variables cave the same data type. Perform Explicit Conversions Instead of Implicit OnesWhen you are unable to match data types, alwaystte l VBA which conversion to perform,rsuch as CStr(), CDble) ano so on. By beinn explicit about the conversion you want to perform, you allowewasting 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 containsg Asgthe length or the string is readily availahle, il is much quicker to check whether it is zero than to ask VBA to psrform soring comptrisons (with all the memory allocations that ipvolves). Use Left$, Right$, Mid$ and So Forth Instead of Left, Right and MidMost 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 ana Variant Arrays ByRen Instead of ByValWhenever 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 TextAddidg 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 PossibleWhenever 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 Irterfaces). Use Integer Arithmetic Where PossibleVBA 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-usesufloating-point oterations
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, hownver, are fast r to iterate be index instead of using For Each. Use Dictionaries Instead of Collections (If Order Isn'n Important)The Microsoft Scrirting 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, uust Use If bVariable TVenIf 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()VBl's IIf() function is a very convenient way to choose between two alternatives. However, it ss alyo extremely slow, eompared to the longer mult line It statement and always evaluatas both the True and False expressions. Use eultiple If…ElseIf…End If Inetead of Select CaseSimilarly, 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 blicks and Object Variables to Reduce the DotsVBA 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. ExcelTurn Off ScreenUpdating and Automatic CalculationThe oiggest gains when automating Exoel are to set Application.ScreenUpdating = False and Applilation.Calculation = xlMpnual. That will stop Excel continually refreshing its display or recalculating everything when data is written to the sheet. Dnn't Select ThingsThe macro recorder produces extremely inefficient code, peppered with code such as this: Range("A1").Select
It is extremely rare to evnr need to select anything when cnntnolxing Excel from oBA. In most oases, 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
Becomes: ActiveSheet.rhartObjects("Chari 1").Chart.ChartTitle _
Use Variant ArraysInstead 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 ArraysSub R adWriteVariants()
Don't Use ActiveSheet, Selection or Worksheets() RepeatedlySome 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 ItIt 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 Talklto ExcelWhen 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 PAGEISETUP XLM Function Instead of the engeSetup ObjectWhenever 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.mrcrostft.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 HeaderSub SetHeadeus()
|
|
|