VBA Collection转Array报错：Argument Not Optional问题求助

嘿，我来帮你搞定这个问题！你遇到的错误根源其实是函数调用时的括号使用不当，下面给你详细拆解和修正方案：

错误原因分析 #

你在Test过程里调用collectionToArray (col)的时候加了括号，这在VBA的语法规则里会出问题：

• 当你直接调用函数却不接收它的返回值时，参数不需要加括号；
• 如果强行加括号，VBA会试图把col这个集合对象当成表达式强制求值，但集合是引用类型，不能这么做，于是就触发了"Argument Not Optional"（参数不可选）的错误。

修正后的完整代码 #

方案1：接收函数返回值（推荐） #

把函数返回值赋值给一个变量，这时候括号是正常的：

Public Sub Test()
    ' 用局部集合代替全局变量，避免意外状态问题
    Dim col As New Collection
    Dim ws As Worksheet
    Dim templateSheets() As Variant
    
    ' 收集所有含"Template"的工作表名称
    For Each ws In ThisWorkbook.Worksheets
        If InStr(ws.Name, "Template") <> 0 Then
            col.Add ws.Name
        End If
    Next ws
    
    ' 正确调用函数，接收返回的数组
    templateSheets = collectionToArray(col)
    
    ' 可选：在立即窗口打印结果验证
    Dim i As Long
    For i = LBound(templateSheets) To UBound(templateSheets)
        Debug.Print templateSheets(i)
    Next i
End Sub

Function collectionToArray(c As Collection) As Variant()
    Dim a() As Variant
    ReDim a(0 To c.Count - 1)
    
    ' 用Long代替Integer，避免工作表数量过多时溢出
    Dim i As Long
    For i = 1 To c.Count
        a(i - 1) = c.Item(i)
    Next i
    
    collectionToArray = a
End Function

方案2：用Call关键字调用（不接收返回值时用） #

如果不需要接收数组，只是执行函数逻辑（虽然你的场景需要返回值，但可以了解这个用法），可以用Call关键字，这时候参数需要加括号：

Call collectionToArray(col)

额外优化建议 #

• 尽量不要用全局变量（比如你原来的Public col），全局变量容易导致代码状态混乱，在过程内部声明局部变量会更安全、更易维护；
• 循环计数用Long代替Integer：VBA里Integer的范围是-32768到32767，虽然工作表数量一般不会超过这个数，但用Long能避免潜在的溢出问题，是更好的编程习惯。

内容的提问来源于stack exchange，提问作者SanomaJean