传递工作表Range时如何避免VBA动态数组被自动调整大小？

背景说明 #

梳理数组相关基础概念：

• 静态数组：定义时固定维度和大小，无法调整，示例：

  Dim myStArray(1 To 3, 1 To 3) As Integer
  

• 动态数组：可通过ReDim调整大小，若需保留数据需搭配Preserve（仅支持保留最后一维），Variant类型支持这类操作，示例：

  Dim myDyArray() As Variant
  ReDim MyDyArray(3, 3)
  

• 交错数组：即数组的数组，是一种特殊的嵌套数组结构。

实际测试发现，将工作表区域直接赋值给已初始化的动态数组时，数组会自动调整为区域对应的尺寸，而非保留原有大小：

MyDyArray = ThisWorkbook.Worksheets(1).Range("A3:C3")

执行后原本(3,3)的数组会缩小为(1,3)，访问MyDyArray(2,1)会触发「下标越界」错误。虽然直接赋值区域是最快的单元格取值方式，但这种自动调整特性不符合固定数组大小的需求。

核心问题 #

如何将工作表数据传递给数组时，避免动态数组被自动调整大小？是否只能通过逐单元格遍历传递数据？

方案分析与实现 #

1. 逐单元格遍历赋值（可行方案） #

直接遍历目标数组的每个位置，根据区域数据范围赋值，超出区域的位置可设置默认值（比如0），示例代码：

Dim MyDyArray() As Variant
ReDim MyDyArray(1 To 3, 1 To 3)

With ThisWorkbook.Worksheets(1)
    For row = 1 To 3
        For col = 1 To 3
            If row <= .Range("A3:C3").Rows.Count Then
                MyDyArray(row, col) = .Cells(row + 2, col).Value ' A3对应第3行，需偏移2行
            Else
                MyDyArray(row, col) = 0
            End If
        Next col
    Next row
End With

2. 临时数组中转（更高效的方案） #

先将区域数据存入临时数组，再把临时数组的内容复制到已初始化的目标数组中，既保留直接赋值区域的高效性，又能维持目标数组的固定大小：

Dim MyDyArray() As Variant
Dim tempArray() As Variant

' 初始化目标数组为指定大小
ReDim MyDyArray(1 To 3, 1 To 3)
' 用临时数组接收区域数据
tempArray = ThisWorkbook.Worksheets(1).Range("A3:C3").Value

' 复制临时数组内容到目标数组
Dim row As Long, col As Long
For row = 1 To UBound(tempArray, 1)
    For col = 1 To UBound(tempArray, 2)
        MyDyArray(row, col) = tempArray(row, col)
    Next col
Next row

' 对目标数组中超出临时数组的部分设置默认值
For row = UBound(tempArray, 1) + 1 To UBound(MyDyArray, 1)
    For col = 1 To UBound(MyDyArray, 2)
        MyDyArray(row, col) = 0
    Next col
Next row

关于「先初始化再直接赋值」的疑问 #

如果先将数组初始化为0再执行MyDyArray = Range(...)，数组依然会被自动调整大小。因为直接赋值区域的操作本质是让变量MyDyArray指向一个全新的数组对象，完全替换了之前初始化的数组，原有大小会被覆盖。

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