用数组元素填充 VBA 中的组合框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15414619/
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
populate combobox in VBA with array elements
提问by Sam
I have a VBA procedure (in Excel 2007) where I aspire to set the ListFillRange property of a combobox styled as a list using an array.
我有一个 VBA 程序（在 Excel 2007 中），我希望在其中设置使用数组样式为列表的组合框的 ListFillRange 属性。
I know this works if I right click the combobox and write "Sheet1!$F2:$F17" next to the "ListFillRange" property. I can also do this in code. However, I am interested in dynamically setting the value of this property by assigning it an array.
我知道如果我右键单击组合框并在“ListFillRange”属性旁边写下“Sheet1！$F2：$F17”，这会起作用。我也可以在代码中做到这一点。但是，我对通过为其分配一个数组来动态设置此属性的值感兴趣。
I know for sure the array works as I tested it; there is probably a syntax error here:
我确信该阵列在我测试时可以正常工作；这里可能有语法错误：
ThisWorkbook.Worksheets("Sheet1").OLEObjects("cmbS").ListFillRange = ar
when I do this I get: "Type mismatch" error.
当我这样做时，我得到：“类型不匹配”错误。
The result of this action should be that the component is populated with the array elements, from element(0) ... to the last element (n-1) of the array. Any pointers, thank you very much!
此操作的结果应该是使用数组元素填充组件，从 element(0) ... 到数组的最后一个元素 (n-1)。任何指点，非常感谢！
I also tried:
我也试过：
ThisWorkbook.Worksheets("Sheet1").cmbS.list = ar
and it says "permission denied"
它说“权限被拒绝”
Here are the combobox properties in case it helps:
以下是组合框属性，以防万一：
After testing and trying, I found this works:
经过测试和尝试，我发现这有效：
ThisWorkbook.Worksheets("Sheet1").cmbS.ListFillRange = ""
Dim i As Integer
For i = LBound(ar) To UBound(ar)
ThisWorkbook.Worksheets("Sheet1").cmbS.AddItem (ar(i))
Next
However, I am interested in populating with all values at oncefor faster effect, not just adding element by element.
但是，我有兴趣一次填充所有值以获得更快的效果，而不仅仅是逐个添加元素。
回答by olr
I know its late but maybe it is going to help someone else. At least the following code works (much faster than element for element) for me.
我知道它晚了，但也许它会帮助别人。至少以下代码对我有用（比元素对元素快得多）。
dim arr() as variant
arr = Worksheets("Total").Range("C2:"&lrow).Value
Worksheets("Menu").ComboBox2.List = arr
回答by Netloh
The only way you can populate a combobox with the content of an array is by doing it element by element. I find it hard to believe that it would be a notably slow process no matter how large your array is.
使用数组内容填充组合框的唯一方法是逐个元素地进行。我发现很难相信无论您的阵列有多大，这都会是一个非常缓慢的过程。
