VBA行内构造连续数值数组传参报类型错误问题咨询

问题现象 #

目标是构造从n到n2的连续数值数组，直接嵌入IF判断语句调用ArrayContainsItem函数，期望写法如下：

If ArrayContainsItem([transpose(row(320:420))], WidthArray) Then
    '执行逻辑
End If

上述行内直接写数组、不提前声明数组变量的写法，会触发*array or user defined type expected（需要数组或用户定义类型）*报错。
提前声明Variant类型数组arr，将[transpose(row(320:420))]赋值给arr后再传参可正常运行，参考代码：

dim arr() as variant
arr = [transpose(row(320:420))]

'主逻辑
If ArrayContainsItem(arr, WidthArray) Then
    '执行逻辑   
End If

End Sub

但需求是实现行内直接构造数组传参，无需提前声明中间变量，且现有ArrayContainsItem函数不允许做任何修改，函数源码如下：

Function ArrayContainsItem(ArrayBig() As Variant, ArraySmall() As Variant) As Boolean

'声明变量
Dim iOption As Long

'初始化返回值
ArrayContainsItem = False

'主逻辑
For iOption = 2 To UBound(ArraySmall)

    For Each Item In ArrayBig
        If CStr(Item) = ArraySmall(iOption) Then
            ArrayContainsItem = True
        End If
    Next Item    
    ArrayContainsItem = False
Next iOption

End Function

此前尝试将函数签名修改为Function ArrayContainsItem(ArrayBig As Variant, ArraySmall() As Variant)，会在访问ArrayBig时返回错误2015，遍历得到的Item为空值。
可复现问题的完整测试代码：

Sub ArrayTesting()

'变量声明
Dim WS_MS As Worksheet
Set WS_MS = ThisWorkbook.Worksheets("Machine Specification")
Dim LowerFilmWidthArray() As Variant
Dim CurrentParameter As Range
Dim ParametersColumn As Long
ParametersColumn = 2
Dim LastColumn As Long
LastColumn = 4


'读取宽度参数
Set CurrentParameter = WS_MS.Cells.Find("Width", lookat:=xlWhole)
WidthArray = Application.Transpose(Application.Transpose(WS_MS.Range(Cells(CurrentParameter.Row, ParametersColumn), Cells(CurrentParameter.Row, LastColumn)).Value))


'主判断逻辑
If ArrayContainsItem([transpose(row320:420)], WidthArray) Then
    
End If

End Sub

报错根因 #

• 方括号[]是VBA中Evaluate方法的简写，其返回值为Variant类型：当表达式计算结果为数组时，返回的是存储了数组的Variant类型值，而非函数参数要求的显式Variant()强类型数组，类型不匹配因此直接传参报错。
• 提前声明arr() as variant再赋值时，VBA会自动做隐式类型转换，将存储数组的Variant值转换为显式Variant()数组，因此传参可正常运行。
• 测试代码中的行内写法还存在笔误：row320:420缺少括号，正确写法为row(320:420)，否则表达式本身会返回错误值。

可行方案 #

无需修改原有ArrayContainsItem函数，通过VBA.Array做一层包装即可实现行内传参，写法如下：

If ArrayContainsItem(VBA.Array([transpose(row(320:420))])(0), WidthArray) Then
    '执行逻辑
End If

写法说明 #

• VBA.Array()方法会返回下标从0开始的显式Variant()类型数组，将Evaluate得到的数组作为第0个元素存入后，通过(0)取出该元素时，会自动适配为函数要求的显式Variant()数组类型，不需要额外声明中间变量。
• 如果需要构造其他数值区间的连续数组，仅需修改row(起始值:结束值)中的参数即可，例如构造1到100的连续数组写为[transpose(row(1:100))]。

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