如何将Excel筛选后的数据作为参数传递给VBA函数

假设Excel中有如下数据表：

需要使用FILTER函数筛选出VarA="A"对应的VarB和VarC，并将结果传递给自定义VBA函数Test，单元格公式为：

=Test(FILTER(VarB,VarA="A"),FILTER(VarC,VarA="A"))

但尝试以下VBA代码时，无论将参数声明为Object还是Variant，都返回#VALUE!错误：

Function Test(vector1 As Object, vector2 As Object)
   a=vector1(1)
   b=vector2(1)
   Test=a*b
End function

问题根源 #

FILTER函数返回的是二维垂直数组（即使结果是单列数据），而非一维数组或单个对象。直接用vector1(1)访问会因数组维度不匹配触发错误。

正确实现：取第一个元素计算乘积 #

如果只需要筛选结果的第一个元素乘积，需指定二维数组的行列索引（格式为array(行号, 列号)）：

Function Test(vector1 As Variant, vector2 As Variant)
    Dim a As Double, b As Double
    a = vector1(1, 1)
    b = vector2(1, 1)
    Test = a * b
End Function

扩展：处理整个筛选数组（返回全量乘积结果） #

如果需要对每一组筛选出的VarB和VarC计算乘积，并返回结果数组（比如同时得到1*3和3*9的结果），可通过循环遍历数组实现：

Function Test(vector1 As Variant, vector2 As Variant) As Variant
    Dim resultArr() As Double
    Dim i As Long
    Dim rowCount As Long
    rowCount = UBound(vector1, 1)
    
    ReDim resultArr(1 To rowCount, 1 To 1)
    
    For i = 1 To rowCount
        resultArr(i, 1) = vector1(i, 1) * vector2(i, 1)
    Next i
    
    Test = resultArr
End Function

使用该版本时，需选中与筛选结果行数一致的单元格区域，输入公式后按Ctrl+Shift+Enter（旧版Excel）或直接回车（动态数组版本Excel）即可填充所有结果。

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