VBA 将列表框传递给函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22765039/
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
VBA passing listbox to a function
提问by teepee
I am attempting to populate a listbox by passing my listbox object to a subfunction, but whenever I run it I get a type mismatch error. I pass the object MAIN.BoxY1 to the function FillListBox, where MAIN is the codename for the sheet the listbox is on, and BoxY1 is my listbox name (ActiveX). When I alter the FillListBox function to include MAIN.BoxY1 instead of MyBox for every instance it will work fine. What is the proper method of passing a listbox to another function?
我试图通过将我的列表框对象传递给子函数来填充列表框，但是每当我运行它时，我都会收到类型不匹配错误。我将对象 MAIN.BoxY1 传递给函数 FillListBox，其中 MAIN 是列表框所在工作表的代号，BoxY1 是我的列表框名称 (ActiveX)。当我更改 FillListBox 函数以包含 MAIN.BoxY1 而不是 MyBox 时，它会正常工作。将列表框传递给另一个函数的正确方法是什么？
Sub FillListBox(MyBox As ListBox, DataList As Variant)
MyBox.MultiSelect = 1
For j = 1 To NumOutputs
MyBox.AddItem DataList(j)
Next j
End Sub
Sub BoxY1_Fill()
FillListBox MAIN.BoxY1, TheData
End Sub
?
?
回答by Tim Williams
There are two types of listbox in Excel: the "built-in" type and the ActiveX version. Depending on which type you are dealing with, you need to set up your function parameter differently:
Excel 中有两种类型的列表框：“内置”类型和 ActiveX 版本。根据您处理的类型，您需要以不同的方式设置您的函数参数：
Sub Testing()
test1 Sheet1.ListBox1 ' <<ActiveX Listbox
test2 Sheet1.ListBoxes("ListBox2") ' <<Forms Listbox
test2 Sheet1.Shapes("ListBox2").OLEFormat.Object ' <<Forms Listbox (avoiding
' deprecated 'Listboxes')
End Sub
'ActiveX listbox
Function test1(lb As msforms.ListBox)
Debug.Print lb.ListCount
End Function
'Forms listbox
Function test2(lb As ListBox)
Debug.Print lb.ListCount
End Function
