vba - 将 VBA 集合转换为数组

Question

我正在尝试创建一个数组，其中包含工作簿中包含"template"一词的所有工作表名称。我认为最简单的方法是先创建一个集合，然后将其转换为数组，但我遇到了麻烦。现在我收到的错误是在

collectionToArray (col)

线。我收到了

Argument Not Optional error

非常困难，非常感谢任何帮助。谢谢!!

Public col As New Collection
Public Sub Test()
For Each ws In ThisWorkbook.Worksheets
    If InStr(ws.Name, "Template") <> 0 Then
        col.Add ws.Name
    End If
Next ws

collectionToArray (col)
End Sub

Function collectionToArray(c As Collection) As Variant()
    Dim a() As Variant: ReDim a(0 To c.Count - 1)
    Dim i As Integer
    For i = 1 To c.Count
        a(i - 1) = c.Item(i)
    Next
    collectionToArray = a
End Function

Answer 1

collectionToArray (col)

注意到函数名称和参数列表之间的空格了吗？ VBE 告诉您:

我将采用该参数，将其评估为一个值，然后将其 ByVal 传递给您正在调用的过程，即使该过程明确或不明确地表示 ByRef。

这种“无关的括号”习惯不可避免地会让您在某一时刻遇到奇怪的“Object required”运行时错误:丢失它。

在我看来，Function 做得太过了:Variant 可以完美地包装数组，因此我会更改其签名以返回 Variant > 而不是 Variant()。

Integer 是 16 位有符号整数类型(即其他语言中的 short)，使用 Long 可能是更好的主意> 代替(32 位有符号整数，即其他语言中的 int) - 这样，当您需要处理超过 32,767 个值时，您将避免遇到“溢出”问题(尤其是常见的情况)涉及工作表)。

Public col As New Collection

这使得 col 成为自动实例化的对象变量，并且它具有潜在的令人惊讶的副作用。考虑这段代码:

Dim c As New Collection
c.Add 42
Set c = Nothing
c.Add 42
Debug.Print c.Count

您希望这段代码做什么？如果您认为“错误 91，因为对象引用为 Nothing”，那么您已经被自动实例化所困扰。最好避免它，并将声明和赋值作为单独的指令。

除此之外，CLR's answer有您的解决方案:Function 应该返回一个值，调用代码应该使用该值。

result = MyFunction(args)

您会注意到 VBE 清除了您可能想在 MyFunction 和 (args) 之间添加的任何空格:这就是 VBE 告诉您的信息:

我将获取该参数，将其传递给 MyFunction，并将函数的返回值分配给 result。