VBA读取Excel表赋值全局数组丢失引用报下标越界问题求助

• 关键字使用错误：Set仅用于VBA中给对象变量赋值，XL.Transpose返回的是值类型的数组，使用Set赋值会强制将数组包装为临时对象引用，函数退出后临时引用被释放，全局数组没有被正确赋值，最终触发下标越界。
• 外部实例依赖问题：你调用的是自行创建的外部Excel实例的Transpose方法，返回的数组和该实例存在绑定关系，当你执行xls.Quit销毁实例后，对应数组的内存会被回收，即使赋值逻辑正确也无法正常读取。
• 类型匹配问题：全局变量MyArr声明为Variant类型的动态数组Dim MyArr() As Variant，但pushToArray的参数Arr声明为单个Variant变量ByRef Arr As Variant，隐式类型转换也会提升赋值异常的概率。

将数组作为参数传入的方式可以解决该问题，配合以下修正即可正常运行：

1. 删除Set关键字，数组赋值直接写Arr = XL.Transpose(tmpArr)，建议改用VBA内置的WorksheetFunction.Transpose，避免数组和外部Excel实例绑定。
2. 调整pushToArray的参数声明，将Arr明确声明为动态数组：ByRef Arr() As Variant，和全局数组类型匹配。
3. 不需要提前执行ReDim Arr(c, r)，Transpose返回数组时会自动分配对应维度和大小的内存，提前重定义的结果会被直接覆盖。

' ----- 主模块 ----
' 全局数组声明保持不变
Dim MyArr() As Variant

Call ReadXLFileIntoArray(excelFileAddress, excelFileSheet)
Debug.Print MyArr(1, 1)  ' 可正常输出

' ----- Excel数据处理模块 ----
Function ReadXLFileIntoArray(addr As String, sheet As Integer)
    Dim xls     As Excel.Application
    Dim wkb     As Excel.Workbook
    Dim wks     As Excel.Worksheet

    Set xls = New Excel.Application
    Set wkb = xls.Workbooks.Open(addr, ReadOnly:=True)
    Set wks = wkb.Worksheets(sheet)

    ' 调用调整后的pushToArray
    Call pushToArray(wks, "excelTableName", MyArr)

    wkb.Close True
    Set wks = Nothing
    Set wkb = Nothing
    xls.Quit
    Set xls = Nothing
End Function

' 修正后的pushToArray
Function pushToArray(ByRef wks As Worksheet, tableName As String, ByRef Arr() As Variant)
    Dim tmpArr As Variant
    tmpArr = wks.ListObjects(tableName).DataBodyRange.Value
    ' 用内置Transpose，无需依赖外部Excel实例
    Arr = WorksheetFunction.Transpose(tmpArr)
    Debug.Print "Loaded from Excel:  Records: " & wks.ListObjects(tableName).DataBodyRange.Rows.Count & " " & tableName
    Debug.Print Arr(1, 1)
End Function

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