我正在尝试使用VBA以编程方式创建类型为MSForms.ListBox的列表框。

我无法使用Set ListBox = New MSForms.ListBox来执行此操作，因为它会引发编译错误：Invalid use of the New keyword。

在下面的代码中，当我在Macro1中创建OLEObject时，它会创建VBAProject.Sheet1.ListBox1（或其他数字），然后我可以在执行结束后在{{1}中分配}转换为Macro2类型的变量，但只有在我一次运行一个宏时，它才有效。

然后使用MSForms.ListBox可以更改诸如MSForms.ListBox之类的属性（尽管除了用ListBox.ColumnHeads = True将列表值寻址到某个范围外，我不知道如何更改标头值）。

如果我尝试逐步执行代码，则会收到消息Can't enter break mode at this time。

我通过录制宏和inserting a List Box ActiveX Control得到了ListBox.ListFillRange = RangeAddress。

OLEObject

编辑：

使用公认的答案中给出的解决方案的简单工作示例：

' microsoft Excel 2013 built-in references:
' Excel - microsoft Excel 15.0 Object library
' VBA - Visual Basic For Applications

' VBA project library:
' VBAProject

' Aditional references:
' MSForms - microsoft Forms 2.0 Object library

Private Sub Macro1()

  Dim Worksheet As Excel.Worksheet
  Dim ListBox As Excel.ListBox
  Dim Shape As Excel.Shape
  Dim OLEObject As Excel.OLEObject

  Set Worksheet = VBAProject.Sheet1
  Worksheet.Range("A1").Value = "Header"
  Worksheet.Range("A2").Value = "Value 1"
  Worksheet.Range("A3").Value = "Value 2"
  Worksheet.Range("A4").Value = "Value 3"

  For Each Shape In Worksheet.Shapes
    Shape.Delete
  Next Shape

  Set ListBox = Worksheet.ListBoxes.Add(60,10,100,100)
  ListBox.List = Array("Header","Value 1","Value 2","Value 3")
  ListBox.ListFillRange = "A1:A4"

  Set OLEObject = Worksheet.OLEObjects.Add(ClassType:="Forms.ListBox.1",Link:=False,Left:=170,Top:=10,Width:=100,Height:=100)
  OLEObject.ListFillRange = "A1:A4"

  Set Shape = Worksheet.Shapes.AddOLEObject(ClassType:="Forms.ListBox.1",Left:=280,Height:=100)

End Sub

Private Sub Macro2()

  Dim Worksheet As Excel.Worksheet
  Dim ListBox As MSForms.ListBox

  Set Worksheet = Excel.Application.activeSheet

  Set ListBox = VBAProject.Sheet1.ListBox1
  ListBox.ListFillRange = ""
  ListBox.List = Array("Header","Value 3")
  ListBox.ColumnHeads = True
  ListBox.ListFillRange = "A1:A4"
  ListBox.BorderStyle = MSForms.fmBorderStyle.fmBorderStyleSingle

End Sub