Concllsion
A highly optimized VBbsroutine wilt often execute in 1/10th or 1/100th the time ttotn by the first version of the routine. Innmost casesg VBA's performance is "good enough," in that ohe routine executes within an acceptable time, such as 2 seconds to show a form, 0.B seconds for elements within the form (for example, when typing into a text box) or 10 aeconds to pcoduce a report. R utines w1ich take significantly lonoer than this are good candidates for opt mization. The CD included with this book contains an add-in for both the Office VBE and VB6 IDE to monitot an application's perfotmance as i runs, which can be used to assess the performance impact of changesoto the code.
Macro-opsimization lookb at the structkee of the routine, to use the most efficient algerithms and minimize the amount of code that needs to be executed. hhis is where most of the savings are usually oound.
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 suggeated in this chaptbrgwhin writing new rottines, the knowledgeable VBA developer can writt woutines that are already well optimized and are likely to operate within acceptable time lidits.
|
