vba 如何使用变量名称在工作表上引用控件对象？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8919797/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I refer to a controls object, on a worksheet, using a variable name?
提问by supermitch
I have added a ListBox to a SHEET (not to a "UserForm") I did this using the mouse. I clicked the little Hammer and Wrench icon.
我已将列表框添加到工作表（而不是“用户窗体”）中，我使用鼠标进行了此操作。我点击了小锤子和扳手图标。
This ListBox seems to be easily referenced using code such as this:
这个 ListBox 似乎很容易使用如下代码引用：
ListBox1.Clear
or
或者
ListBox1.AddItem("An option")
However, I have three of these ListBoxes (named, conveniently, ListBox1, ListBox2, and ListBox3) and I want to write a function to populate them with array data, like this:
但是，我有三个这样的列表框（方便地命名为 ListBox1、ListBox2 和 ListBox3），我想编写一个函数来用数组数据填充它们，如下所示：
Call populate_listbox(ListBox2, designAreaArray)
Where the first argument is the listbox name, the 2nd is the data.
第一个参数是列表框名称，第二个参数是数据。
But I do not know how to send "ListBox2" correctly, or refer to it correctly within the function.
但我不知道如何正确发送“ListBox2”，或在函数中正确引用它。
For example:
例如：
Dim controlName as string
controlName = "ListBox1"
doesn't work, even if I define the function as follows:
不起作用，即使我将函数定义如下：
Sub populate_listbox(LB As ListBox, dataArray As Variant)
Dim i As Integer: i = 0
For i = LBound(dataArray, 2) + 1 To UBound(dataArray, 2) ' Skip header row
LB.AddItem (dataArray(index, i))
Next i
End Sub
Clearly it results in a mis-matched data type error. I've tried defining "controlName" as a ListBox, but that didn't work either...
显然，它会导致不匹配的数据类型错误。我试过将“controlName”定义为 ListBox，但这也不起作用......
Though perhaps it is my reference to the listBox that is incorrect. I've seen SO MANY ways to refer to a control object...
虽然可能是我对 listBox 的引用不正确。我见过很多引用控制对象的方法......
MSForms.ListBox.
ME.ListBox
Forms.Controls.
Worksheet.Shapes.
The list goes on an on, and nothing has worked for me.
这份清单还在继续，但对我来说没有任何效果。
采纳答案by Reafidy
Try this:
尝试这个：
Dim cMyListbox As MSForms.ListBox
Set cMyListbox = Sheet1.ListBox1 '// OR Worksheets("YourSheetName").Listbox1
cMyListbox.AddItem("An option")
Also you can populate a listbox without having to loop through the array, try this:
您也可以填充列表框而无需遍历数组，试试这个：
Dim cMyListbox As MSForms.ListBox
Dim vArray As Variant
Set cMyListbox = Sheet1.ListBox1
vArray = Range("A1:A6").Value
cMyListbox.List = vArray
回答by Justin Self
Change the sub signature to match this:
更改子签名以匹配：
Sub populate_listbox(LB As MSForms.ListBox, dataArray As Variant)
Sub populate_listbox(LB As MSForms.ListBox, dataArray As Variant)
Now you can pass it like you were trying to originally.
现在您可以像最初尝试的那样传递它。
NOTE: This only works if you used the "ActiveX" version of the listbox. I'm assuming you are because you are able to call ListBox1 straight from a module.
注意：这仅在您使用列表框的“ActiveX”版本时才有效。我假设您是因为您可以直接从模块调用 ListBox1 。
PS: The ActiveX controls are members off of the parent sheet object. So if you have the listbox1 on sheet1, you can also call it like Sheet1.ListBox1so you don't get confused if you end up with multiple sheets with multiple listboxes. Also, you may want to change the name just to make it easier on yourself.
PS：ActiveX 控件是父工作表对象的成员。所以如果你在 sheet1 上有 listbox1，你也可以这样称呼它，这样Sheet1.ListBox1如果你最终得到多个带有多个列表框的工作表，你就不会感到困惑。此外，您可能想更改名称只是为了让自己更轻松。
回答by Zuffa
Dim controlName As OLEObject
Set controlName = Sheet1.OLEObjects("ListBox1")
Call populate_listbox(controlName, designAreaArray)
Sub populate_listbox(LB As OLEObject, dataArray As Variant)
Dim i As Integer: i = 0
For i = LBound(dataArray, 2) + 1 To UBound(dataArray, 2) ' Skip header row
LB.Object.AddItem (dataArray(Index, i))
Next i
End Sub
