Part II: Programming Microsoft Access
430
Comparing implicit and explicit variables
The default data type for VBA variables is the variant. This means that, unless you specify other-
wise, every variable in your application will be a variant. As you read earlier in this chapter,
although useful, the variant data type is not very efficient. Its data storage requirements are
greater than the equivalent simple data type (a string, for instance) and the computer spends
more time keeping track of the data type contained in a variant than for other data types.
Here’s an example of how you might test for the speed difference when using implicitly declared
variant variables and explicitly declared variables. This code is found behind frmImplicit-
Test in Chapter11.accdb:
‘Use a Windows API call to get the exact time:
Private Declare Function GetTickCount _
Lib “kernel32” () As Long
Private Sub cmdGo_Click()
Dim i As Integer
Dim j As Integer
Dim sExplicit As Single
txtImplicitStart = GetTickCount()
For o = 1 To 10000
For p = 1 To 10000
q = i / 0.33333
Next p
Next o
txtImplicitEnd = GetTickCount()
txtImplicitElapsed = txtImplicitEnd - txtImplicitStart
DoEvents ‘Force Access to complete pending operations
txtExplicitStart = GetTickCount()
For i = 1 To 10000
For j = 1 To 10000
sExplicit = i / 0.33333
Next j
Next i
txtExplicitEnd = GetTickCount()
txtExplicitElapsed = txtExplicitEnd - txtExplicitStart
DoEvents
End Sub
In this small test, the loop using implicitly declared variables required approximately 7.2 seconds
to run while the loop with the explicitly declared variables required only 5.6 seconds. This is a
performance enhancement of approximately 20 percent just by using explicitly declared variables.
The actual execution time of this — or, any — VBA procedure depends largely on the relative
speed of the computer and the tasks the computer is executing at the time the procedure is run.
Desktop computers vary a great deal in CPU, memory, and other resources, making it quite impos-
sible to predict how long a particular bit of code should take to execute.
