限定工作簿和工作表时VBA数组赋值遇类型不匹配错误求助

核心原因 #

你遇到的运行时错误'13'类型不匹配，本质是动态数组声明Dim arr()的约束与非活动工作表上下文下的Range赋值机制冲突：

• Dim arr()声明的是带维度约束的动态Variant数组，它要求赋值的数组维度必须与自身当前维度匹配（若已初始化）。
• 未限定工作表的Range能正常运行，是因为此时Range属于活动工作表，VBA对其Value（二维数组）的维度识别直接，自动适配了动态数组的要求。
• 而在With块中，即使.Range的Value也是二维数组，但由于工作表处于显式指定的非活动上下文（即便之前激活过），VBA的维度校验逻辑更严格，导致无法将二维数组正确赋值给arr()，触发类型不匹配。

你提到的“两种场景下数据类型一致”是误解：两个场景的Range对象类型一致，但Dim arr()的数组约束比普通Variant变量更严格，在非活动工作表的上下文下，这种约束会触发校验错误。

解决方法 #

将数组声明改为Dim arr As Variant，Variant变量无维度约束，可灵活接收单个值或任意维度的数组，无论是否限定工作表都能正常运行：

Public Sub test()
    Dim i As Long, stRow As Long, length As Long
    Dim arr As Variant ' 修改此处声明
   
    stRow = 2
    length = 256

    ThisWorkbook.Worksheets(2).Activate
    arr = Range("b2:b257")                                        ' 正常运行
    arr = Range(Cells(stRow, 2), Cells(stRow + length - 1, 2))    ' 正常运行

    With ThisWorkbook.Worksheets(2)
        i = .Range("b2:b257").Count
        i = .Range(.Cells(stRow, 2), .Cells(stRow + length - 1, 2)).Count
        arr = .Range("b2:b257")                                       ' 正常运行
        arr = .Range(.Cells(stRow, 2), .Cells(stRow + length - 1, 2)) ' 正常运行
    End With
End Sub

补充说明 #

• Dim arr()是动态数组，若已被赋值为某维度的数组，后续赋值必须是同维度数组；而Dim arr As Variant是无类型约束的变量，可自动适配任意维度的数组或单个值。
• 多单元格Range的Value始终是二维数组（即使是单列或单行），动态数组在非活动工作表上下文下的维度校验会失败，而Variant变量则不会触发该校验。

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