Introduction:

Programmers accustomed to Visual Studio, can find themselves frustrated by the absence of control arrays when working in the Microsoft Office programming environment. Experienced VBA developers too are frequently 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.

Althoughthe fact that MS Office does not support true control arrays remains, a technique does exist which harnesses this functionality advantage.

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. It's therefore recommended that those who feel less than confident with the topic of Class creation should first refer to the MSDN article Custom Classes and Objects.

By way of example, I'll illustrate a couple of scenarios over the next two articles, each using one of the most popular applications in the MS Office Suite:

• Spreadsheet controls in MS Excel
• 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 highlight the types of idiosyncrasies peculiar to each application within MSO.

Lastly, it is worth remembering that although there are published workarounds for the scenarios provided here which do not involve the use of a control array, it's the concept itself that remains important, since it is this which may be transferred to scenarios not having a conventional workaround.

Excel Spreadsheet Control Arrays:

Assumption:

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 menu option 'View > Toolbars > Forms' 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. Insert three checkbox controls onto the active sheet
4. Open the VB Editor window
5. Add a standard VB Module
6. Add a Class Module
7. Use the properties window to 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 the class will represent) using the keyword 'WithEvents' within the class module. If you are not familiar with this technique, you should read and understand the MSDN article Using Events with the Application Object before proceeding.

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

Note: The explicit reference to the MSForms object library prevents accidental reference to checkbox controls from the Excel toolbox, which do not implement the methods and properties we intend to use. Purists use this "best practice" in order to avoid unforeseen conflicts between project references containing identically named objects; e.g. "Excel.CheckBox" v "MSForms.CheckBox".

For this simple example, we will make use of the checkbox '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 measures to prevent cascading events. A cascading event occurs when an event procedure executes a command that causes the same event procedure to fire again 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:

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 well 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 being declared outside of any procedure, it will maintain its value so long as the file remains open. This is the means that enables the checkbox events to be monitored persistently.

Although these checkbox controls are MSForms ActiveX controls, Excel wraps each within an object called an 'OLE Object' when they are embedded in a worksheet.

Note: You can explore the OLEObject object in greater detail by reading its VB help topic or by examining an assigned variable using the Locals debug window in Break Mode.

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, PowerPoint 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.

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

1. Searches the target spreadsheet for OLEObjects
2. Tests each OLEObject to ensure that it is a CheckBox control
3. Creates a new clsCheckBox object
4. Appoints the clsCheckBox as the CheckBox control
5. Adds the clsCheckBox object to the public Collection for later reference

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 leaks (space "lost" until you computer is re-started).

Now return to "clsCheckBox" and add the following event procedure to the Class:

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

Note: If this class was to be properly implemented for use with controls embedded on worksheets, you would initialise the class when 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 idenfy that the 'SheetActivate' event referred to above, also implements the multi-use event principle that this exercise is based upon!