Passing arrays to and from functions
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
