object array or collection in VBA Excel

Question

i would like to have an array of objects in excel that call one event handler. specifically I have multiple buttons which perform the same function to different cells, and to keep from duplicating code I would like to simply reference these button objects via an index (like I used to do in VB 6.0).... by finding which button was clicked I would like to populate specific cells etc. so the question is: an array of buttons in excel VBA? I've done a little work in VB.net where I used collections, and that worked well... but it appears I can't do that in VBA.

Answer 1

There are no control arrays in VBA like there are in VB. For certain controls you can create a custom class to handle the events. For example, assume you have a userform with two command buttons. In the userform module, put this code

Private mcolEventButtons As Collection

Private Sub UserForm_Initialize()

    Dim clsEventButton As CEventButton

    Set mcolEventButtons = New Collection

    Set clsEventButton = New CEventButton
    Set clsEventButton.EventButton = Me.CommandButton1
    clsEventButton.RangeAddress = "A1"
    mcolEventButtons.Add clsEventButton, Me.CommandButton1.Name

    Set clsEventButton = New CEventButton
    Set clsEventButton.EventButton = Me.CommandButton2
    clsEventButton.RangeAddress = "A10"
    mcolEventButtons.Add clsEventButton, Me.CommandButton2.Name

End Sub

Then create a custom class module named CEventButton and put this code

Private WithEvents mctlEventButton As MSForms.CommandButton
Private msRangeAddress As String

Public Property Set EventButton(ctlButton As MSForms.CommandButton)

    Set mctlEventButton = ctlButton

End Property

Public Property Get EventButton() As MSForms.CommandButton

    Set EventButton = mctlEventButton

End Property

Private Sub mctlEventButton_Click()

    Sheet1.Range(Me.RangeAddress).Value = "Something"

End Sub

Public Property Get RangeAddress() As String

    RangeAddress = msRangeAddress

End Property

Public Property Let RangeAddress(ByVal sRangeAddress As String)

    msRangeAddress = sRangeAddress

End Property

The WithEvents keyword in the variable dimensioning polls the commandbutton's events and fires just as if it was tied to a particular control and in the userform module.

Here's what you did: You created a Collection to hold instances of your custom class for as long as the userform is active. This ensures that those instances stay in scope. Then you created a new instance of the class, assigned a particular button to it, and saved it in the collection. You did the same for the next button. In this example there are only two buttons, but if you had more you could continue doing this until you run out of memory.

I create a RangeAddress property in the custom class module as an example. What information you'll need to store will depend on what you're ultimately trying to accomplish.

For this example to work, you need to set the ShowModal property of the userform to FALSE, have to commandbuttons named CommandButton1 and CommandButton2, have a sheet with a codename of Sheet1, and probably some other stuff.

Answer 2

Separate the common code into a single method and pass the cell as the parameter. Assign each button it's own event method, which in turn calls the common method with the specific cell to edit as a parameter. Something like this:

Private Sub CommonMethod(someCell as String)
  ' Do your stuff
  Range(someCell).Value = something
End Sub

So each button could be assigned to it's own method. This is already built in so don't try to recreate it, keep it simple.

Private Sub Button1_Click()
  CommonMethod("A1");
End Sub

Private Sub Button2_Click()
  CommonMethod("A2");
End Sub