对象数组或VBA Excel中的集合 [英] object array or collection in VBA Excel

问题描述

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

解决方案

将公共代码分离为单一方法，并将单元格作为参数传递。分配每个按钮它是自己的事件方法，这反过来调用通用方法与特定单元格作为参数进行编辑。这样的东西：

  Private Sub CommonMethod（someCell as String）
'做你的东西
范围someCell）.Value = something 
 End Sub 
  

所以每个按钮都可以分配给自己的方法。这是已经内置的，所以不要尝试重新创建它，保持简单。

  Private Sub Button1_Click（）
 CommonMethod（A1）; 
 End Sub 
 
 Private Sub Button2_Click（）
 CommonMethod（A2）; 
 End Sub 
  

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.

解决方案

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

这篇关于对象数组或VBA Excel中的集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！