I have an Array declared in my Main Class (VBA Module). however, i'm trying to call a function that essentially reads an Excel sheet, looks for a specific table definition on a specific sheet and returns an array populated with the contents of the Excel Table.
My Function does not seem to want to update the defined array. please help. would passing the defined array as a function input work better?
code below:
' ----- 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
CodePudding user response:
I would arrange it more like this:
' ----- 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
Not sure if you need that Transpose
or not...