Skip to: Site menu | Main content

Login

Name: 
Password:
Remember me?
Register

Passing arrays to and from functions

written by Helen Toomik - Last updated Oct 2004

Functions that take arrays as arguments

It is very straightforward to create a simple function that takes an array as an argument. In the function header, simply add parentheses () after an argument name to indicate that the argument should be an array. This is easiest to demonstrate with a simple example. This small IsInArray() function will check whether a certain item occurs in a 1-dimensional array.

 
Public Function IsInArray(aryIn() As Variant, item As Variant) As Boolean 
    Dim i As Integer 
    For i = LBound(aryIn) To UBound(aryIn) 
        If aryIn(i) = item Then 
            IsInArray = TRUE 
            Exit Function 
        End If 
    Next i 
    IsInArray = FALSE 
End Function 

When writing a function that handles arrays, remember that arrays may be 1-dimensional, 2D or more. The function above will only work for a 1D array. A 2D array will require two nested loops. If you anticipate arrays of even more dimensions, things get more complicated.

You will also need to take into account the possibility of uninitialised dynamic arrays, that have no elements. Trying to get UBound() for an uninitialised array will raise an error (9: Subscript out of range). Add error checking to your function to avoid that:

 
On Error Resume Next 
i = UBound(aryIn) 
If Err.Number <>0 Then 
    IsInArray = FALSE 
    Exit Function 
End If 
On Error Goto 0 

Variant arrays and arrays of variants

Compare these two variable declarations and assignments. Will A be exactly the same as B?

<Dim A() As Variant: A = Array(1, 2, 3) 
Dim B As Variant: B = Array(1, 2, 3) 

The answer is No. A is an array of variants. B is a variant that holds an array for now, but could hold anything. In VB/VBA, an array of variants is not the same thing as a variant containing an array.

If your function is like the IsInArray function above and expects an array of variants (i.e. A) and you pass it a variant holding an array (i.e. B) then the function will complain.

You might think that the easiest way to fix the problem would be make the function accept all variants as arguments, by removing the parentheses after aryIn:

Public Function IsInArray (aryIn As Variant, item As Variant) As Boolean 

Believe me, this is a bad idea that will cause more problems in the long run than it fixes. You would be removing the safeguards around the function, and it would now accept any kind of variable, including object variables. The code inside the function is expecting an array, so make sure that nothing else gets in there. So declare your variables as arrays to start with.

Functions that return arrays

Now that you can pass an array into a function, it's only a small step to functions that also return arrays. Again, add parentheses after the return variable, to specify that it will be an array. The following simple ReverseArray function will reverse a 1D array, putting the first item last and the last item first, etc. Again, we add error handling to deal with uninitialised arrays.

 
Public Function ReverseArray(aryIn() As Variant) As Variant() 
    Dim i As Integer 
    Dim temp() As Variant 
     
    On Error Resume Next 
    i = UBound(aryIn) 
    If Err.Number <> 0 Then 
        ReverseArray = aryIn() 
        Exit Function 
    End If 
    On Error Goto 0 
     
    ReDim temp(LBound(aryIn) To UBound(aryIn)) 
    For i = LBound(aryIn) To UBound(aryIn) 
        temp(i) = aryIn(UBound(aryIn) + LBound(aryIn) - i) 
    Next i 
    ReverseArray = temp() 
End Function