VBA Excel 中的对象数组或集合

object array or collection in VBA Excel

提问by driveguy

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.

我想在 excel 中有一组对象来调用一个事件处理程序。具体来说，我有多个按钮对不同的单元格执行相同的功能，并且为了避免重复代码，我想通过索引简单地引用这些按钮对象（就像我以前在 VB 6.0 中所做的那样）......通过找到哪个按钮被点击我想填充特定的单元格等所以问题是：excel VBA 中的一组按钮？我在使用集合的 VB.net 中做了一些工作，效果很好......但似乎我不能在 VBA 中做到这一点。

采纳答案by David Glass

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

回答by Dick Kusleika

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

VBA 中没有像 VB 中那样的控件数组。对于某些控件，您可以创建一个自定义类来处理事件。例如，假设您有一个带有两个命令按钮的用户窗体。在用户表单模块中，把这段代码

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

然后创建一个名为 CEventButton 的自定义类模块并将此代码

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.

变量维度中的 WithEvents 关键字轮询命令按钮的事件并触发，就像它绑定到特定控件和用户窗体模块一样。

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.

这就是你所做的：你创建了一个 Collection 来保存你的自定义类的实例，只要用户表单处于活动状态。这可确保这些实例保持在范围内。然后，您创建了该类的一个新实例，为其分配了一个特定按钮，并将其保存在集合中。你对下一个按钮做了同样的事情。在这个例子中只有两个按钮，但如果你有更多按钮，你可以继续这样做，直到内存用完。

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.

我在自定义类模块中创建了一个 RangeAddress 属性作为示例。您需要存储哪些信息取决于您最终要完成的工作。

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.

要使此示例正常工作，您需要将用户窗体的 ShowModal 属性设置为 FALSE，必须使用名为 CommandButton1 和 CommandButton2 的命令按钮，具有代号为 Sheet1 的工作表，以及可能还有其他一些内容。