Conclusion
A highly optimized VBA routine will often execute in 1/10th or 1/100th the time taken by the first version of the routine. In most cases, VBA's performance is "good enough," in that the routine executes within an acceptable time, such as 2 seconds to show a form, 0.5 seconds for elements within the form (for example, when typing into a text box) or 10 seconds to produce a report. Routines which take significantly longer than this are good candidates for optimization. The CD included with this book contains an add-in for both the Office VBE and VB6 IDE to monitor an application's performance as it runs, which can be used to assess the performance impact of changes to the code.Macro-optimization looks at the structure of the routine, to use the most efficient algorithms and minimize the amount of code that needs to be executed. This is where most of the savings are usually found.Micro-optimization ensures the most efficient VBA statements and data types are used within the code. These account for the final few percentage points and usually only have an impact where loops are executing thousands of times.The trade-off is complexity. A QuickSort is much faster than a bubble sort and a binary search in a sorted array is much faster than looping through a collection, but they are also more complex and therefore slightly harder to debug and maintain.By using the techniques suggested in this chapter when writing new routines, the knowledgeable VBA developer can write routines that are already well optimized and are likely to operate within acceptable time limits. |
لطفا منتظر باشید ...
