Passing arrays

[tiredofit]

According to Chip Pearson:

http://www.cpearson.com/excel/passingandreturningarrays.htm

arrays are ALWAYS passed ByRef. If you attempt to pass ByVal, you'll encounter a compilation error.

Here is his code:

Option Explicit
Sub AAATest()
    Dim StaticArray(1 To 3) As Long
    Dim N As Long
    StaticArray(1) = 1
    StaticArray(2) = 2
    StaticArray(3) = 3
    PopulatePassedArray Arr:=StaticArray
    For N = LBound(StaticArray) To UBound(StaticArray)
        Debug.Print StaticArray(N)
    Next N
End Sub
Sub PopulatePassedArray(ByRef Arr() As Long)
    ''''''''''''''''''''''''''''''''''''
    ' PopulatePassedArray
    ' This puts some values in Arr.
    ''''''''''''''''''''''''''''''''''''
    Dim N As Long
    For N = LBound(Arr) To UBound(Arr)
        Arr(N) = N * 10
    Next N
End Sub

In my worksheet, I have the following:

1
2
3

This is my code:

Sub a()
    Dim MyArray() As Variant
    
    MyArray = Cells(1, 1).CurrentRegion.Value
    
    Call b(MyArray)
    
    Debug.Print MyArray(1, 1)
    
End Sub

Sub b(ByRef abc() As Variant)

    abc(1, 1) = 10
    
End Sub

which returns the value of 10 because the Sub b was passed ByRef and has changed the array's value.

If I wrote this, it would NOT compile, as per Chip Pearson's article:

Sub a()

    Dim MyArray() As Variant
    
    MyArray = Cells(1, 1).CurrentRegion.Value
    
    Call b(MyArray)
    
    Debug.Print MyArray(1, 1)
    
End Sub

Sub b(ByVal abc() As Variant)

    abc(1, 1) = 10
    
End Sub

However, if I omitted the brackets, like this:

Sub a()

    Dim MyArray() As Variant
    
    MyArray = Cells(1, 1).CurrentRegion.Value
    
    Call b(MyArray)
    
    Debug.Print MyArray(1, 1)
    
End Sub

Sub b(ByVal abc As Variant)

    abc(1, 1) = 10
    
End Sub

it returns the value of 1, as "sort of" expected because Sub b was passed ByVal.

So in my last example, have I passed an array ByVal or have I passed something else ByVal?

Thanks

 

[Norie]

You've passed a Variant.

 

[tiredofit]

Thanks

Examples of data types include Integer, Long, Single, Double, String, Boolean, Object, etc.

My understanding is a Variant is no specific data type.

An Array is a data structure, as is a dictionary or collection.

So what does it mean to "pass a variant"?

 

[Norie]

As far as I know a variant can hold any type of data, and I would assume that would include an array.

 

[tiredofit]

Thanks that's what I'm uneasy about.

Variant is data type (though nothing specific) and an array is a data structure, so it seems it's possible to not only pass a data type as an argument but a data structure too.

Therefore I assume it's possible to pass a dictionary or a collection.

 

[Norie]

Don't see a problem passing a dictionary or collection.