Control Arrays

Introduction:

Programmers accustomed to Visual Studio, are frequently frustrated by the absence of control arrays when working in the Microsoft Office programming environment.
Experienced VBA developers too, are routinely exasperated by the need to create an event procedure for each member of a group of identical controls, when logic suggests that just one generic procedure could be shared by the group.
Although that MS Office does not support control arrays cannot remains a fact, a technique does exist that addresses the functionality advantages of VB control arrays.

Concept:

This article will focus on the use of a custom Class to achieve the benefits of a VB control array. Readers should be familiar with the concepts involved in the use of Class objects and how to create a Class using a Class Module.
Although some readers may find it unnecessary to grasp these concepts in order to practice the specific techniques demonstrated here, that approach will almost certainly cause problems where these techniques are applied in even slightly altered scenarios.
By way of example, two scenarios will be considered, each based within one of the two most popular applications in the MS Office Suite:
(1) creating a control array Class for spreadsheet controls in MS Excel
(2) creating a control array Class for form controls in MS Access
The choice of these applications is intended both to demonstrate the powerful set of options presented by this technique across all the MSO Suite, and also to raise awareness of the type of idiosyncrasies peculiar to each application within MSO.
Finally, it is worth remembering that although there are published workarounds for the scenarios provided here that do not involve the use of a control array, it's the concept itself which remains important, since it is this that may be transferred to another scenario which does not have a conventional workaround.

Excel Spreadsheet Control Arrays:

Assumptions: all controls referred to in relation to Excel Spreadsheets are MS Forms controls. That is, the Excel menu option 'View>Toolbars>Control Toolbox' is used to generate the required controls. This is because controls generated by the 'View>Toolbars>Forms' toolbar are not ActiveX objects and do not support the extended functionality of their counterparts.

Consider a spreadsheet containing a set of mutually exclusive Checkbox controls. When any control in the group is changed to 'True', each of the other checkboxes must be set to 'False'.

The obvious solution might be to create a generic procedure that is called by the 'Click' event of each control, passing the name of the client checkbox as an argument. The procedure would then

loop through the group of controls,
compare each name property against the string argument,
set the checkbox values to 'False' if a match was not found.

However, there are two critical penalties for using this approach:

A new event procedure must be written for each checkbox that is added to the group
If the name of any of the controls is changed, the procedure will cease to behave as intended until the code is altered to accommodate the change.

The alternative technique we are about to practice, fully addresses these significant maintainability issues.

To illustrate this scenario:

1. open a new instance of Excel,
2. create a fresh workbook
3. and insert three checkbox controls onto the active sheet.
4. Additionally, open the VB Editor window,
5. add a standard VB Module
6. and also add a Class Module,
7. then using the properties window, change the name of the class module to "clsCheckbox".

This Class will behave as a generic event "listener" for all the controls in the group of checkboxes.
This is achieved by declaring the object type (which we want the class to represent) using the keyword 'WithEvents' within the class module.

As demonstrated in that article, declare the class object by adding this declaration to the very top of the Class module, "clsCheckBox"

Public WithEvents chk As MSForms.CheckBox 

Note the explicit reference to the MSForms object library. This prevents the use of checkbox controls from another library, which may not implement the methods and properties that we intend to use.
For this simple example, we will make use of the checkbox's 'Click' event; however there are a number of other events that could be used for other purposes.
You can explore these using the drop-down box at the top-right of the Class Module code window.
Since code within the Class may change the values of the checkboxes, we must take care to prevent cascading events.
A cascading event occurs when an event procedure executes a command that causes the same event procedure to fire without pause, creating an infinite loop that may cause the application, or even the computer itself, to crash.
MSForms controls are not members of the Excel Application object, therefore setting the 'Application.EnableEvents' property to False will have no effect in this case.
Our work-around will be to make use of a public variable which can be evaluated prior to executing any commands within the checkbox Click event.
Move to the regular code module, 'Module1' and add these declarations:

Public blnHaltEvents As Boolean 
Public colCheckBox As New Collection 

Because Boolean variables default to False, we can be confident that our events will always execute fully unless we specify otherwise.
Observe also, the public Collection object. Collections are ideally suited to the purpose of this exercise, but you should be confident of how they behave and are implemented before continuing.
Since the Collection is a member of the VBA object library, you can refer to the help file for the topic in any of the MS Office applications.
This Collection will contain a "clsCheckBox" object for each checkbox control found on the worksheet, and is the means that enables the checkbox events to be monitored persistently.
Although the checkbox controls on the spreadsheet are MSForms ActiveX controls, Excel wraps each within an Excel object called an OLEObject.
This configuration means that our code must first refer to each OLEObject on the worksheet, before referencing the checkbox control that it contains.
All MSO applications which feature mixed text/object interfaces exhibit this architecture, e.g. Excel and Word, but not Access since Access is entirely form-based.
The following procedures illustrate how to manage this within Excel, however the hierarchy concept is the same within other mixed text/object interface MSO applications such as Word.

The first procedure, "Class_Init" initialises our Class module "clsCheckBox". That is, it:

searches the target spreadsheet for OLEObjects
tests each OLEObject to ensure that it is a CheckBox control
creates a new clsCheckBox object
appoints the clsCheckBox as the CheckBox control
adds the clsCheckBox object to the public Collection for later reference

Public Sub Class_Init() 
Dim oleO As Excel.OLEObject 
Dim cls As clsCheckbox 

    ' loop all the OLE Objects on the Worksheet
    For Each oleO In Sheet1.OLEObjects 
        
        ' test that only the required controls are included
        If TypeName(oleO.Object) = "CheckBox" Then 
        
            ' create a new object from our custom class
            Set cls = New clsCheckbox 
            ' assign the class to the OLE object found on the sheet
            Set cls.chk = oleO.Object 
            ' add the populated class to our custom Collection so that we can refer
            '   to it later
            colCheckBox.Add cls, cls.chk.Name 
        End If 
        
    Next oleO 
    
End Sub 

Public Sub Class_Terminate() 

    'loop through our custom collection of controls
    For Each ch In colCheckBox 
        'remove the object from the collection and release the associated memory
        colCheckBox.Remove ch.chk.Name 
        Set ch.chk = Nothing 
    Next ch 
End Sub 

The second procedure, "Class_Terminate" removes each object that was created by "Class_Init" from memory when the process is ended.
This is an important process that will prevent memory space from being otherwise "lost" until you computer is re-started.
Now return to "clsCheckBox" and add the following event procedure to the Class:

Private Sub chk_Click() 

    ' prevent cascading events caused by changing the value of each checkbox
    '   the 'EnableEvents' property of the Application cannot be used in this case
    If blnHaltEvents Then Exit Sub 
    blnHaltEvents = True 
    
    ' since an option much be selected at all times within an option group,
    '   prevent any checkbox from being manually set to False
    If Not Me.chk.Value Then 
        Me.chk.Value = True 
        ' skip the main actions
        GoTo Finish 
    End If 
    
    ' the name of the active control provides visible proof of the relationship
    Application.StatusBar = Me.chk.Name 
    
    ' loop all the checkbox objects in the previously assigned collection object
    For Each ch In colCheckBox 
        If Not ch.chk.Name = Me.chk.Name Then ch.chk.Value = False 
    Next ch 

' use a termiating subroutine to make any critical settings fail-safe
Finish: 
    blnHaltEvents = False 
    Exit Sub 

' good practice advocates the use of a proper error-handler in every procedure, however the
'   point here is simply to force execution of commands in the terminating subroutine under
'   all circumstances
Error_H: 
    Resume Finish 
    
End Sub 

This is the procedure that actually creates the effect of exclusive selection.
When any of the check box control are clicked, this event fires for that single control, and sets the value for all the other check boxes to False.

Notes

If this class was to be properly implemented for use with controls embedded on worksheets, you would initialise the class as the relevant worksheet was activated, using the 'SheetActivate' event of the 'ThisWorkbook' object.
This event would first test the 'colCheckBox' collection to check if it had been previously initialised, and if so it should call the 'Class_Terminate' procedure before reloading the collection using 'Class_Init'.
Observant readers will realise that the 'SheetActivate' event referred to above, also implements the multi-use event principle that this exercise is based upon!