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
Code:
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
Code:
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
Code:
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.
Last edited by LaVolpe; Feb 18th, 2010 at 10:48 PM.
Reason: Added simple array enumeration to the classes. See Example #3
Insomnia is just a byproduct of, "It can't be done"
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
Code:
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]
Last edited by LaVolpe; Feb 19th, 2010 at 09:14 AM.
Reason: code typo
Insomnia is just a byproduct of, "It can't be done"
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:
Code:
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
Last edited by krazykarter; Feb 19th, 2010 at 04:17 PM.
Reason: Added edit note
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.
Last edited by LaVolpe; Feb 19th, 2010 at 05:28 PM.
Insomnia is just a byproduct of, "It can't be done"