Losing Reference to an Assigned array - Subscript out of Range

Question

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

Answer 1

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...