Understanding ByRef when passing arrays to subroutine 1

[nasar]

Hi, I hope you can provide some help with an issue I am having. I have a two-dimensional array which I wish to sort. I have created a subroutine to the sorting as i will be calling this a number of times. The problem is that I (think) I am passing the array as a parameter by reference and expect the sorted array to be assigned by the sort procedure. However this is not working.
I print the array before the sort procedure, within the sort procedure and after the sort procedure. The array is the same before and after, but is sorted within the sort procedure. Any help and guidance would be appreciated.
I include the code sections below:

Private Sub cmdTest_Click()

Dim Risks() As Variant
Dim i, j As Long
Dim rgRisks As Range

With Sheets("Sheet1")

    Set rgRisks = .Cells(1, 22).CurrentRegion

    Risks = rgRisks
    Debug.Print "Before"
    For i = LBound(Risks) To UBound(Risks)
        For j = LBound(Risks, 2) To UBound(Risks, 2)
            Debug.Print Risks(i, j)
        Next j
    Next i
    
    ArraySort (Risks)

    Debug.Print "After"
    For i = LBound(Risks) To UBound(Risks)
        For j = LBound(Risks, 2) To UBound(Risks, 2)
            Debug.Print Risks(i, j)
        Next j
    Next i
End With
End Sub

Sub ArraySort(ByRef inputArray As Variant)

' This routine sorts the (two dimensional) inputArray in descending order based on the second column
Dim rgArray As Range
Dim rgScore As Range
Dim i, j As Integer

With Sheets("Sheet1")

    ' clear any data that is columns R and S
    .Cells(1, 18).CurrentRegion.ClearContents
    
    ' write inputArray to cells starting R1. Resize the region to be the right number of rows and 2 columns
    Set rgArray = .Cells(1, 18).Resize(UBound(inputArray) - LBound(inputArray) + 1, 2)
    rgArray = inputArray
    
    Set rgScore = rgArray.Cells(1, 2) ' need to specify the column that will be used for the sort key
    
    ' sort the range
    rgArray.Sort key1:=rgScore, Order1:=xlDescending, Header:=xlNo
    
    ' read the worksheet into the array
    Set rgArray = .Cells(1, 18).CurrentRegion
    inputArray = rgArray.Value
    
    Debug.Print "During"
    For i = LBound(inputArray) To UBound(inputArray)
        For j = LBound(inputArray, 2) To UBound(inputArray, 2)
            Debug.Print inputArray(i, j)
        Next j
    Next i
End With
End Sub

 

[strongm]

You'll kick yourself. Your problem is this line:

[tt]ArraySort (Risks)[/tt]

It is not doing what you think it is. By calling the sub this way Risks is being evaluated BEFORE being passed to the function, which means that VB passes a reference to a private, internal array that it has created on the fly to the sorting routine, not the Risks array that you intended.

So either

[tt]ArraySort Risks[/tt]

or

[tt]Call ArraySort(Risks)[/tt]

should fix the issue.

By the way

[tt]Dim i, j As Long[/tt]

isn't doing what you think it is either ...

 

[nasar]

Thanks strongm
I thought it might be something straightforward and your explanation is very helpful in aiding understanding.