公式与VBA自定义函数的数组比较行为不一致问题

问题场景 #

你用Excel公式能正常找到数组中首个超过指定值的元素索引：

=MATCH(TRUE,INDEX({0,5,10,15,20}>7,0),0)

预期结果为3，但转成VBA代码时触发Compile Error: Type Mismatch：

Dim myArray(): myArray = [{0,5,10,15,20}]
myVar=WorksheetFunction.Match(1, WorksheetFunction.Index(myArray > 7, 0), 0)

核心问题是VBA无法像工作表公式那样，直接通过myArray > 7生成布尔数组{FALSE, FALSE, TRUE, TRUE, TRUE}，以下是无需遍历数组的解决方法：

方法1：用Evaluate调用工作表引擎实现数组比较 #

利用Evaluate方法让Excel的工作表引擎处理数组元素级比较，直接生成符合要求的布尔数组，再传入Match函数：

Dim myArray As Variant
myArray = Array(0, 5, 10, 15, 20)
Dim threshold As Integer
threshold = 7

' 生成布尔数组并匹配首个TRUE的位置
Dim result As Integer
result = WorksheetFunction.Match(True, Evaluate("{" & Join(myArray, ",") & "}" & ">" & threshold), 0)
MsgBox result ' 输出3

这种写法通过Join把数组转为字符串，再拼接成工作表式的数组表达式，兼容性更强，避免直接数组转字符串的潜在问题。

方法2：在Index中嵌套Evaluate处理比较 #

如果你想保留类似原公式的结构，可以在Index内部用Evaluate完成数组比较：

Dim myArray(): myArray = [{0,5,10,15,20}]
Dim myVar As Integer
myVar = WorksheetFunction.Match(True, WorksheetFunction.Index(Evaluate(myArray & ">7"), 0), 0)
MsgBox myVar

关键说明 #

VBA原生不支持数组的元素级批量比较，而工作表公式的数组运算依赖Excel的工作表计算引擎，Evaluate方法正是用来调用这个引擎，实现和工作表公式一致的数组操作逻辑，从而避免遍历数组的繁琐。

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