VBA – Control Arrays

What is a Control Array?

A Control Array is a group of controls that share the same name type and the same event procedures. They are a convenient way to handle groups of controls (Same Type) that perform a similar function. All of the events available to the single control are available to the array of controls.

Can we create Control Arrays in VBA?

Even though, VBA doesn’t allow us to to create Control Array like in vb6 and vb.net, we can still create Control Array in VBA using Class Module.

Why do we need Control Arrays in VBA?

Control Arrays mainly have these advantages

1.     Controls in a Control Array share the same set of event procedures. This results in you writing less amount of code.

2.     Control Arrays uses fewer resources.

3.     You can effectively create new controls at design time, if you need to.

Let’s say you have 10 textboxes in your UserForm (see image below)

And you want all 10 to be numeric textboxes. Numeric textboxes are those text boxes where you can only type numbers.

Now imagine writing this code 10 times for each and every textbox?

This is where we will use Control Array of Textboxes and assign them the same procedure.

To start with add a new Class and name it Class1. Now paste this code in the Code area of Class1 module

Public WithEvents TextBoxEvents As MSForms.TextBox

Private Sub TextBoxEvents_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

    Select Case KeyAscii

    '<~~ 48 to 57 is AscII code for numbers. 127 is for 'Delete' and 8 is for 'Backspace'

    Case 48 To 57, 127, 8

    Case Else

        KeyAscii = 0

    End Select

End Sub

and in the UserForm Initialize event, paste this code.

Option Explicit

Dim TextArray() As New Class1

Private Sub UserForm_Initialize()

    Dim i As Integer, TBCtl As Control

    For Each TBCtl In Me.Controls

        If TypeOf TBCtl Is MSForms.TextBox Then

            i = i + 1

            ReDim Preserve TextArray(1 To i)

            Set TextArray(i).TextBoxEvents = TBCtl

        End If

    Next TBCtl

    Set TBCtl = Nothing

End Sub

 For Reference:

http://siddharthrout.wordpress.com/2011/08/05/vba-control-arrays/