Access Cookbook - Ken Getz [173]
8.5. Accelerate VBA Code
Problem
You've optimized your forms and queries, but now you need to look at the entire application. Your application contains a lot of VBA code. What optimizations can you perform to make it run faster?
Solution
This solution demonstrates seven specific programmatic techniques you can apply to accelerate your code. The improvement can range from modest increases to increases of several orders of magnitude in performance.
To see the optimizations in action, open and run frmShowOptimizations from 08-05.MDB, shown in Figure 8-14. Click the Run Tests button, and the tests will run one by one, displaying the results in milliseconds. The tests compare two different methods of using VBA to achieve a result.
Figure 8-14. The frmShowOptimizations form
Follow these steps to apply the optimizations suggested by these tests to your applications:
When dividing integers, use integer division. A majority of the division operations performed by your application are probably done on integer values. Many developers use the slash (/) operator to divide two numbers, but this operator is optimized for floating-point division. If you're dividing integers, you should use the backslash (\) integer division operator instead. With \, Access works at the integer level instead of the floating-point level, so computation is faster. (Of course, this is useful only if you're assigning the results of the division operation to an integer. If you care about the fractional portion of the division, you'll need to use floating-point math and the / operator after all.) For example, instead of:
intX = intY / intZ
use:
intX = intY \ intZ
Use specific data types instead of variants. Variants offer convenience at the expense of performance. Every time you refer to a variant, Access needs to perform type conversion to ensure the data is in the correct format. By using the data type that matches your variable, you eliminate the need for this type conversion, and your code runs faster. In addition, a variant variable is twice as large as an integer (on a 32-bit operating system) and thus takes longer to manipulate.
Test for blank strings using the Len function. You probably have code that tests for blank strings by comparing them to an empty string (""). However, because Access stores the length of the string as the first byte in the string, testing for a length of zero using the Len function is always faster. Instead of:
If strTemp = "" Then
MsgBox "The string is blank"
End If
use:
If Len(strTemp) = 0 Then
MsgBox "The string is blank"
End If
If you refer to an object more than once in a section of code, assign it to an object variable. Every time you reference an object, Access has to perform some work to figure out which object you are referring to. This adds overhead to your code each time the object is referenced. But if you assign the object to an object variable, Access "finds" the object once and caches the reference in memory. So after the first reference, you can refer to the object through the object variable and your code will run faster. For example, instead of this code:
Dim strTmp As String
Dim lngCount As Long
For lngCount = 0 To acbcMaxIterations / 2
strTmp = DBEngine.Workspaces(0).Groups(0).Name
Next lngCount
use:
Dim grp As DAO.Group
Dim strTmp As String
Dim lngCount As Long
Set grp = DBEngine.Workspaces(0).Groups(0)
For lngCount = 0 To acbcMaxIterations / 2
strTmp = grp.Name
Next lngCount
We created two variations of this test. First, we changed the function to refer to a control on an open form instead of a DAO group. The cached reference version of the code was 2.8 times faster—significantly improved, but not of the same magnitude as the DAO group comparison. Second, we compared