我在主類（VBA 模塊）中宣告了一個陣列。但是，我正在嘗試呼叫一個函式，該函式本質上是讀取 Excel 作業表，在特定作業表上查找特定表定義并回傳一個填充有 Excel 表內容的陣列。
我的函式似乎不想更新定義的陣列。請幫忙。將定義的陣列作為函式輸入傳遞會更好嗎？
代碼如下：
' ----- main Module ----
'declare my Array
Dim MyArr() As Variant
Call ReadXLFileIntoArray(excelFileAddress, excelFileSheet)
Debug.Print (MyArr(1, 1)) ' raises Subscript out of range error
'- Excel Data Processing Module
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)
Call pushToArray(xls, wks, "excelTableName", MyArr)
wkb.Close True
Set wks = Nothing
Set wkb = Nothing
xls.Quit
Set xls = Nothing
End Function
Function pushToArray(ByRef XL As Object, ByRef wks As worksheet, tableName As String, ByRef Arr As Variant)
Dim tmpArr As Variant
Dim x As Integer, y As Integer
r = wks.ListObjects(tableName).DataBodyRange.Rows.Count - 1
c = wks.ListObjects(tableName).DataBodyRange.Columns.Count - 1
'ReDim Arr(c, r) ' do i need to call this?
tmpArr = wks.ListObjects(tableName).DataBodyRange.Value
Set Arr = XL.Transpose(tmpArr)
Debug.Print ("Loaded from Excel: " & " Records: " & wks.ListObjects(tableName).DataBodyRange.Rows.Count & "" & tableName)
Debug.Print (Arr(1, 1)) ' works!
End Function
uj5u.com熱心網友回復：
我會更像這樣安排它：
' ----- main Module ----
Sub Tester()
Dim MyArr As Variant, excelFileAddress As String, excelFileSheet As Long
'...
'...
MyArr = ReadXLListIntoArray(excelFileAddress, excelFileSheet, "excelTableName")
Debug.Print MyArr(1, 1)
End Sub
'- Excel Data Processing Module
Function ReadXLListIntoArray(addr As String, sheet As Long, listName As String)
Dim xls As Excel.Application
Dim wkb As Excel.Workbook
Set xls = New Excel.Application
Set wkb = xls.Workbooks.Open(addr, ReadOnly:=True)
ReadXLListIntoArray = wkb.Worksheets(sheet).ListObjects(listName).DataBodyRange.Value
wkb.Close False
xls.Quit
End Function
不知道你是否需要那個Transpose...
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/339742.html