Chapter 10: VBA Programming Fundamentals
409
Private Sub cmdWith_Click()
Dim i As Integer
Dim c As Control
For i = 0 To Me.Controls.Count - 1
Set c = Me.Controls(i) ‘Grab a control
If TypeOf c Is CommandButton Then
With c
‘Set a few properties of the control:
.FontName = “Arial”
.FontBold = True
.FontSize = 8
End With
End If
Next
End SubThe code in this example (cmdWith_Click) executes somewhat faster than the previous example
(cmdOld_Click). Once Access has a handle on the control (With c), it’s able to apply all the
statements in the body of the With without having to fetch the control from the controls on the
form as in cmdOld_Click.
In practical terms, however, it’s highly unlikely that you’ll notice any difference in execution times
when using the With construct as shown in this example. However, when working with massive
sets of data, the With statement might contribute to overall performance. In any case, the With
statement reduces the wordiness of the subroutine, and makes the code much easier to read and
understand.
Think of the With statement as if you’re handing Access a particular item and saying “Here, apply
all these properties to this item.” The previous example said, “Go get the item named x and apply
this property to it” over and over again. The speed difference in these commands is considerable.
The For Each statement
The code in cmdWith_Click is further improved by using the For Each statement to traverse
the Controls collection. For Each walks through each member of a collection, making it avail-
able for examination or manipulation. The following code shows how For Each simplifies the
example.
Private Sub cmdForEach_Click()
Dim c As Control
For Each c In Me.Controls
If TypeOf c Is CommandButton Then
With c
.FontName = “MS Sans Serif”
.FontBold = False
.FontSize = 8
End With
End If
Next
End Sub