如何将VBA定义数组传入Excel预定义函数（Evaluate方法实现）

• 核心诉求：验证VBA定义的内存数组能否不经过Range/Cells对象中转，直接传入Excel预定义动态数组函数（如FILTER）调用，该方案需适配所有Excel内置函数，而非仅针对FILTER。
• 现存问题：VBA原生内置的Filter函数仅支持一维字符串数组的模糊匹配，多条件适配、复杂逻辑支持能力远低于Excel动态数组版本的FILTER函数；虽然可通过For/While循环手写实现同类逻辑，但直接复用Excel内置函数可大幅提升开发效率。
• 现有方案局限：目前公开参考案例仅支持传入Range对象地址完成调用，无法直接传入VBA内存数组。
• 测试代码报错点：测试代码中尝试调用内存数组的.Address属性（内存数组无该属性，该属性仅属于Range对象），且存在布尔值拼写错误（Fales应为False），导致无法正常返回计算结果。

原测试代码如下：

Sub Test()
    ' How to use/pass VBA defined array into excel functions (using evaluate)
    ' without using Range or Cells
    
    Dim SA(1 To 5, 1 To 1) As String
    Dim SB(1 To 5, 1 To 1) As Boolean
    SA(1, 1) = "Apple"
    SA(2, 1) = "Orange"
    SA(3, 1) = "Berry"
    SA(4, 1) = "Apple"
    SA(5, 1) = "Appricot"

    SB(1, 1) = True
    SB(2, 1) = True
    SB(3, 1) = Fales
    SB(4, 1) = True
    SB(5, 1) = Fales
    
    ex = "=FILTER(" & SA.Address & ", " & SB.Address & ")"     ' this line has error
    RST = Evaluate(ex)
    
    Worksheets("Add").Range("AG8:AG" & UBound(RST) + 7).Value2 = RST
End Sub

无需将数组写入工作表单元格做中转，可直接通过WorksheetFunction调用Excel内置函数，直接传入VBA内存数组作为参数，这是效率最高的实现方式，适配所有支持数组参数的Excel内置函数。

修正后可运行代码 #

Sub Test()
    Dim SA(1 To 5, 1 To 1) As String
    Dim SB(1 To 5, 1 To 1) As Boolean
    Dim RST As Variant
    Dim ws As Worksheet
    
    ' 赋值测试数组
    SA(1, 1) = "Apple"
    SA(2, 1) = "Orange"
    SA(3, 1) = "Berry"
    SA(4, 1) = "Apple"
    SA(5, 1) = "Appricot"

    ' 修正布尔值拼写错误，原代码写为Fales会触发编译/运行错误
    SB(1, 1) = True
    SB(2, 1) = True
    SB(3, 1) = False
    SB(4, 1) = True
    SB(5, 1) = False
    
    ' 直接调用WorksheetFunction的FILTER，传入VBA内存数组，无需拼接公式、无需引用单元格
    RST = WorksheetFunction.Filter(SA, SB)
    
    ' 按返回数组的实际行列数匹配目标区域，批量写入结果
    Set ws = Worksheets("Add")
    ws.Range("AG8").Resize(UBound(RST, 1), UBound(RST, 2)).Value2 = RST
End Sub

备选方案（适配必须使用Evaluate拼接公式的场景） #

如果业务场景必须通过Evaluate执行公式字符串，可先将VBA内存数组转换为Excel能识别的数组常量字符串，再拼接公式执行。注意该方案对大数组的处理效率低于直接调用WorksheetFunction的方式，仅适合小数组场景。
数组转常量字符串的规则：

• 一维横向数组用逗号分隔元素，整体包裹在{}中
• 纵向二维数组（如测试代码中的5行1列数组）用分号分隔行元素，同一行的多列元素用逗号分隔
• 字符串类型元素需要用双引号包裹，布尔值直接写TRUE/FALSE，数值直接写原值

注意事项 #

• 传入Excel函数的VBA数组维度必须和函数要求的参数维度匹配，例如FILTER的待筛选数组和条件数组的行列数必须一致
• Excel动态数组函数返回的多值结果会自动存储为二维VBA数组，写入工作表时建议用Resize按返回数组的实际行列数匹配目标区域，避免固定范围导致的溢出或数据缺失
• 不要尝试给VBA内存数组调用.Address属性，该属性是Range对象的专属属性，内存数组不存在工作表地址概念

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