Access Cookbook - Ken Getz [174]
Don't worry about comments. In VBA the use of comments exacts no measurable performance penalty, so there's no excuse for omitting them!
Use If...Then...Else instead of the IIf function. By replacing IIf statements with the equivalent If...Then...Else statement, your code will run faster. For example, instead of:
MsgBox IIf(intX = 1, "One", "Not One")
use:
If intX = 1 Then
MsgBox "One"
Else
MsgBox "Not One"
End If
When initializing a string so that it's empty, don't use a literal value (""). Instead, use the built-in vbNullString constant. You'll get better performance, as the test demonstrates.
Discussion
Many optimizations that apply to other languages can also apply to VBA. For example, checking for blank strings using the Len function is a common optimization in other languages. Don't be afraid to try new techniques. The small performance improvements you get from optimizing VBA code can add up if you are running code in a repetitive loop, and many small improvements may result in a noticeable overall difference in your application.
Optimization techniques for programming languages are a vital part of your toolbox. But don't sacrifice other vital elements for the sake of speed. First, make sure your code works correctly before you optimize. Second, write your code so that it's easily understood; it can be very difficult to optimize code you don't understand. Finally, don't break working code when optimizing it. By optimizing code that works correctly (albeit slowly), you may introduce bugs. Follow the three rules of optimization:
Make it right before you make it faster.
Make it clear before you make it faster.
Keep it right as you make it faster.
You may find that there are no easy optimizations for a particular piece of code. No matter what you do, it just won't run fast enough. A favorite saying in software design is "Don't diddle code to make it faster; find a better algorithm." Often you need to step back from a piece of slow code. Maybe there is a better overall approach or a better algorithm you can employ. A good way to get over a hurdle such as this is to ask other programmers how they handle the same situation. Overall, you will find that code optimizations have a much smaller impact on your application's performance than optimizations to your data access; for example, adding one extra index can have a greater impact than hours and hours of time spent optimizing VBA.
TIP
As they say in the auto commercials, "Your mileage may vary." Don't assume anything is faster until you've proven it yourself on the machine that will run your application!
8.6. Test the Comparative Benefits of Various Optimization Techniques
Problem
Now that you've tried the optimization techniques in this chapter, you'd like to test some additional optimization ideas. How can you test various VBA optimization techniques in a standardized fashion?
Solution
By using a Windows API call, some simple math, and a wrapper function, you can easily compare the performance of two optimization techniques with relatively high accuracy. This solution shows you how to create a form to compare the performance of two functions. It runs the functions and then displays how long each took to execute.
Open and run frmTestOptimize from 08-06.MDB. The form shown in Figure 8-15 allows you to enter the names of two functions and test their performance relative to each other. The 08-06.MDB database contains two sample functions that show the relative performance of integer division and floating-point division. (This optimization was discussed in the Solution in Recipe 8.4.) To run the test, enter:
FloatDivision( )
into the Function 1 text box, and enter:
IntegerDivision( )
into the Function 2 text box. Press the Test button. The form will run each function,