In writing code you often need to retrieve or set properties of the various Forms, Reports, Controls, or other Objects in your application. Generally, you refer to these Objects with statements like:
- strCaption = Forms!("frmTest").cmdButton1.Caption
-
For a single reference to an Object, there's not much you can do to speed up the reference. If, on the other hand, you are going to be referring to many of the properties of that Object in a loop of some sort, you can achieve a substantial speed increase by pointing an Object Variable at that Object and using that Variable to reference the Object. For example, if you were going to reference many of a specific Control's properties, you would be well served to use code like this rather than to refer to the Control with the full syntax each time:
-
Dim ctl As CommandButton
-
Set ctl = Forms("frmTest").cmdButton1
-
-
Debug.Print ctl.Name
-
Debug.Print ctl.Width
-
'etc...
-
In another example, I'll use a particular Field within an ADO Recordset to prove my point:
-
'This would be the slooooower case
-
Set rst = New ADODB.Recordset
-
Set rst.ActiveConnection = CurrentProject.Connection
-
rst.Source = "tblTests"
-
rst.Open
-
-
For intCounter = 1 to lngSomeReallyBigNumber
-
strName = rst.Fields(0).Name
-
Next intCounter
-
-
'The much faster procedure caches the reference to the Field, as such:
-
Set rst = New ADODB.Recordset
-
Set rst.ActiveConnection = CurrentProject.Connection
-
rst.Source = "tblTests"
-
rst.Open
-
-
Set fld = rst.Fields(0)
-
For intCounter = 1 to lngSomeReallyBigNumber
-
strName = fld.Name
-
Next intCounter
-
-
Even in this simple case, in which only a single dot was removed from within the expression, this faster code will take about 40% of the time it took in the slooooower version.
In addition to the previously stated, using VBA's With..End With syntax affords the same improvements in code execution time.
In closing, the crucial point of this Tip is:
Dots in Object References will always slow down your code. You should take whatever effort you can to reduce the number of dots! 
