Array passed to Sub Procedure but returned with wrong dimension size-CodePudding

I have an issue with the code below. Appreciate your helps.

I have created a sub ("GetListItems") that receives an Array as an argument. The sub populates the array with the data from a group of Non-contiguous Ranges in the first sheet of my excel file. Sub performs as expected and the array in the sub, after the date is assigned to it, has 2 dimensions with correct number of columns in the second dimension.

However, When I pass an array to this sub which is by reference, the returned array has only 1 dimension. I have been struggling with this the whole day and do not understand the reason. Appreciate your help.

Sub GetListItems(ByRef arrArray() As Variant)

Dim WS As Worksheet
Dim LastRow As Integer
Dim SelectedCols As String

' Initialize our variable and get the last row in the database
Set WS = Worksheets("Database")
LastRow = WS.Cells(WS.Rows.Count, 1).End(xlUp).Row

' select ranges for copying
SelectedCols = "A1:A" & LastRow & ", C1:C" & LastRow & _
             ", F1:F" & LastRow & ", H1:H" & LastRow & _
             ", V1:V" & LastRow & ", W1:W" & LastRow & _
             ", X1:X" & LastRow & ", Z1:Z" & LastRow

'Copy and paste data from defined range
WS.Range(SelectedCols).Copy
Set WS = Worksheets.Add(, Sheets(Sheets.Count))
With WS
    .Visible = xlSheetHidden
    .Paste WS.Range("A1")
End With

' Initialize array
arrArray = WS.Range("A1").CurrentRegion

' Delet the hidden sheet
With Application
    .CutCopyMode = False
    .DisplayAlerts = False
    WS.Delete
    .DisplayAlerts = True
End With

End Sub

==============================================================

Sub test()

Dim MyArray() As Variant
Dim Dimension As Integer, Temp As Integer
Application.Run "GetListItems", MyArray

    On Error GoTo Err
Do While True
    Dimension = Dimension   1
    Temp = UBound(MyArray, Dimension)
Loop

Err:
MsgBox "MyArray variable has " & Dimension & " dimensions!", vbInformation And vbOKOnly


End Sub

CodePudding user response：

The reason is that Application.Run always handles variables as ByVal.

You have to call your sub like this: GetListItems MyArray - then ByRef will work.

After adding this answer I found this SO-thread which is really worth reading: Variable not updating passed ByRef