[VBA] Control Arrays Imitation - Classes

[LaVolpe]

Ever wish you can have control arrays in VBA like we do in VB6? If you had 10 comboboxes that basically all have the same code in their Click events, wouldn't it be nice to have a single Click event for all 10 comboboxes?

Well, if you use the attached re-usable classes, you could with very little extra effort on your part. Of course another solution is to create a separate sub/function that you call from each of the 10 comboboxes Click events, but that still requires you to write 10 ComboBox#_Click events, doesn't it? I am not suggesting that 10 one-liners is inferior to adding 3 classes to your project, but control arrays do have some huge advantages when it comes to debugging & condensing code for like-controls.

The attached zip includes classes for textboxes, comboboxes, listboxes, checkboxes, option buttons and command buttons. You can use them as templates for creating classes for other control types that you want to fake as control arrays. There are 3 classes for each type of control. But you reference only one of the three classes in your form's code.

Setting it up can't be much easier: just declare the class using the keyword WithEvents, instantiate the class and then call its .SetReference method; three lines of code.

Example 1: Five comboboxes that you want to array

Dim WithEvents myComboArray As clsArray_ComboBox

Private Sub Form_Load()
    Set myComboArray = New clsArray_ComboBox
    myComboArray.SetReference Combo0, Combo1, Combo2, Combo3, Combo4
End Sub

Private Sub myComboArray_Click(theComboBox As ComboBox)
    ' add your code here
    ' If you need to Select Case on the combobox, use its name
    Select Case theComboBox.Name
        Case "Combo0"
        Case "Combo1"
        Case "Combo2"
        Case "Combo3"
        Case "Combo4"
    End Select
End Sub

Example 2: You want two arrays of the same control type. You want Text0, Text1, Text2 to be one array and Text3, Text4, Text5, Text6 to be another array

Dim WithEvents myTextArray1 As clsArray_TextBox
Dim WithEvents myTextArray2 As clsArray_TextBox

Private Sub Form_Load()
    Set myTextArray1 = New clsArray_TextBox
    myTextArray1.SetReference Text0, Text1, Text2

    Set myTextArray2 = New clsArray_TextBox
    myTextArray2.SetReference Text3, Text4, Text5, Text6
End Sub

Private Sub myTextArray1_GotFocus(theTextBox As TextBox)
    ' add your code here
End Sub
Private Sub myTextArray2_GotFocus(theTextBox As TextBox)
    ' add your code here
End Sub

Example 3: Checking one checkbox in your array should uncheck all other checkboxes in the same array

Dim WithEvents myChkBoxArray As clsArray_CheckBox

Private Sub Form_Load()
    Set myChkBoxArray = New clsArray_CheckBox
    myChkBoxArray.SetReference Check0, Check1, Check2, Check3, Check4, Check5
End Sub

Private Sub myChkBoxArray_Click(theCheckBox As CheckBox)
    Dim Index As Long
    If theCheckBox.Value = -1 Then
        For Index = 1 To myChkBoxArray.Count 
            If Not myChkBoxArray.Control(Index) Is theCheckBox Then
                myChkBoxArray.Control(Index).Value = 0
            End If
        Next
    End If
End Sub

And remember, should you want an event triggered with VBA, you'll need to set the event to "[Event Procedure]" in the property sheet of the control.


Edited: The classes in the zip were created from controls used in MS Access. I don't know if the other Office products have the same exact properties/methods for like controls. If not, obviously the classes need to be tweaked so that they have the same properties/methods used by your Office product.

Edited: 18 Jan 2010. Added ability to call another control's event (any event) if the control is in one of the arrays you created. Array items can now be referenced by Index or Name. Also indexes are now 1-bound, not 0-bound. See Post #2 for an example.