MS Excel VBA单元素数组场景下出现类型不匹配错误

这个问题我太熟了！核心原因是VBA对单个单元格和多单元格Range的赋值逻辑不一样：

• 当Range("rngDeptX")包含多个单元格时，直接赋值给Variant变量会自动返回一个二维数组（哪怕是单列，结构也是(1 To N, 1 To 1)），和你后续的数组操作逻辑兼容；
• 但当Range只有1个单元格时，VBA不会生成数组，而是直接把单元格的值赋值成一个单一的Variant值——这时候你把单个值往数组变量d()里塞，自然就会触发「类型不匹配」错误。

修复方案（兼容所有元素数量） #

不用改太多代码，只需要先把Range的值赋值给一个普通Variant变量，再判断是否是数组，不是的话手动转换成和多元素时一致的二维数组结构就行：

Dim arrSheets() As String, DptCnt As Long 'array of Department names, size of array
'determine required size of arrays, set size of arrays
DptCnt = WorksheetFunction.CountA(Range("rngDeptX"))
ReDim arrSheets(1 To DptCnt)

Dim d As Variant '这里不要声明成数组，用Variant接收Range的值
d = Range("rngDeptX").Value

'处理单元素的特殊情况：把单个值转成二维数组
If Not IsArray(d) Then
    Dim tempArr(1 To 1, 1 To 1) As Variant
    tempArr(1, 1) = d
    d = tempArr
End If

'接下来你就可以像之前一样正常遍历d数组了，比如：
Dim i As Long
For i = 1 To DptCnt
    arrSheets(i) = d(i, 1) '因为Range返回的是二维数组，行索引在前
Next i

为什么这样可行？ #

这样处理后，不管你的部门数量是1、11还是12，d都会是结构统一的二维数组，后续遍历、赋值的代码完全不用修改，完美兼容所有场景。

另外补充个小细节：你调试时看到报错行的表达式能返回正确名称，是因为VBA在即时窗口里会自动解析单个值，但运行时代码里的数组变量只能接收数组类型，所以才会报错。

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