[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.

[LaVolpe]

Per request, included the ability to call a control's event. In the process of updating the project, I also changed the key property Reference and renamed it Control. Internally, quite a bit of the classes changed.

Post #1 above has the most recent updates.

Example 4: Calling an event

Dim WithEvents myButtonArray As clsArray_CommandButton

Private Sub Form_Load()
    Set myButtonArray = New clsArray_CommandButton
    myButtonArray.SetReference Command0, Command1, Command2
End Sub

Private Sub Check1_Click()
    If Check1.Value = True Then
        myButtonArray.ControlEvent("Command1").Click
    End If
End Sub

Private Sub myButtonArray_Click(theCmdButton As CommandButton)
     MsgBox theCmdButton.Name & " was clicked thru code"
End Sub

In above example, if the checkbox is checked, the command button will be clicked internally, even if the command button's event property is not [Event Procedure]

[krazykarter]

Thanks LaVolpe, this makes the code much easier to read in places.

A question: What extra control does clsArray_TextBox give you over simply creating an array of clsTextBox in the form module?

A suggestion: If you create a new clsArray_TextBox object (myTextArray), you must add all of the items to the object at the same time myTextArray.setReference text0, text2...text10. If, later in the program, you wish to add another element, myTextArray.setReference text12 deletes the previous elements, resulting in the only element contained in the object being text12.
If you change the setReference sub to the following, it allows the user to add elements to the end of the array:

Public Sub SetReference(ParamArray theTextboxes() As Variant)
On Error GoTo errHandle
    Dim Count As Integer
    Dim I As Integer

    'Check if array is empty
    If (UBound(myObjects) = -1) Then
        Count = 0
    Else
        Count = UBound(myObjects) + 1
    End If
    
    'Adjust the size of the array
    ReDim Preserve myObjects(Count + UBound(theTextboxes))
    
    'Add the objects
    For I = Count To (Count + UBound(theTextboxes))
        Set myObjects(I) = New clsTextBox
        myObjects(I).SetReference theTextboxes(I - Count), Me
    Next
    
exitSub:
    Exit Sub
errHandle:
    If Err.Number = 9 Then
        Resume Next
    Else
        Resume exitSub
    End If
    
End Sub

Of course, you could split it up into a setReference sub and a separate add sub.

Edit: This change is based on the original method

[LaVolpe]

Regarding adding more controls to the array after initializing it, that is up to you. I didn't take into consideration the possibility of adding controls during runtime (i.e., Me.Controls.Add...). Good point and modify to your heart's content.

Regarding arrays. There are a few ways to get a control's event on your form
1. Create the control during design time and place it on your form
2. Declare a control or object using the WithEvents keyword. This will work only if that object/control raises events.
Ex: Dim WithEvents myTextBox As TextBox
Ex: Dim WithEvents myTextArray As clsArray_TextBox
3. Implement an interface using the keyword Implements
Ex: Implements IPicture

The WithEvents has one major restriction also. It can't be used with arrays
Ex: Dim WithEvents myTextBoxes() As clsTextBox will cause an error.
Because of that restriction, implementation has been a common workaround, similar to posted classes.

Now regarding your mods you posted above. That code would have worked for the previous version of the classes. With the new version, arrays aren't used.

And last but not least. Since the control classes now have Public events to allow an arrayed control to have its event called directly, the interface classes (i.e., ITextBox) could be done away with, but the remaining two classes per control would need to be tweaked. I'll leave that as an exercise for anyone that wants to do that. There is always room for improvement and enhancement.