arrays - 将数组传递给 VBA 中的函数

Question

我有一个接受数组的程序。因此我可以像这样传递一个数组:

Public Sub MySub (Something as String, ByRef Arr() as Variant)
  'Stuff
End Sub


Public Sub Test()

   Dim Columns() As Variant
   Columns = Array(1, 2, 3, 4, 5, 6)

   MySub "sth", Columns

End Sub

问题是当我想内联时

MySub "sth", Array(1, 2, 3, 4, 5, 6)

或

MySub "sth", Cvar(Array(1, 2, 3, 4, 5, 6))

我收到一个编译错误，指出类型不匹配:需要数组或用户定义的类型。

如何将内联数组传递给函数？

Answer 1

将数组参数从一个变体数组更改为一个变体

Public Sub MySub(Something As String, ByRef Arr As Variant)
  'Stuff
End Sub


Public Sub Test()

   Dim Columns() As Variant
   Columns = Array(1, 2, 3, 4, 5, 6)

   MySub "sth", Columns
   MySub "sth", Array(1, 2, 3, 4, 5, 6)
   'both worked in my test
End Sub