Passing Arrays to SUBs

[Bill Martin]

I have code which seems to work properly, but I'm slightly nervous as I
haven't seen it actually written anywhere how this works. Basically the
question is how do arrays get passed as parameters to a SUB() ?

I've set up a toy routine that near the top says: Dim TestVector(10) As
Long

And later I pass it to a SUB like this: Call TestRoutine(TestVector)

Apparently the TestRoutine gets compiled as if it also had that same
typing/sizing for TestVector. In fact if I try to force it to some
conflicting typing it compiles ok, but crashes with an error as the CALL
gets executed.

From putzing around with this toy setup a bit I've sort of concluded that
VBA passes parameters to it's SUBs using "call by reference" rather than
"call by value". Is this true? Am I safe to pass arrays to subroutines as
in the CALL example above without specifying typing or sizing anywhere?
Those things magically pass themselves? And if it were a large array, then
all the data cells aren't really getting passed but rather only the
reference to the array?

I figured I should ask about this before I dig myself too big a hole and
THEN find out there's a gotcha somewhere in there. Thanks...

Bill

 

[Tom Ogilvy]

Basically Arrays are passed by reference. However, you can make the
argument a variant (no an array) and pass you array byval.

Sub Tester1()
Dim v(1 To 10)
For i = 1 To 10
v(i) = i
Next
HandleArray v
s = ""
For i = 1 To 10
s = s & v(i) & ","
Next
MsgBox s
End Sub

Sub HandleArray(ByVal arr)
s = ""
For i = 1 To 10
s = s & arr(i) & ","
Next
MsgBox "Enter HandleArray: " & s
s = ""
ReDim arr(0 To 4)
For i = LBound(arr) To UBound(arr)
arr(i) = 20
Next
For i = LBound(arr) To UBound(arr)
s = s & arr(i) & ","
Next
MsgBox "Leaving HandleArray: " & s
End Sub

as long as you don't do that, it should be byref. Probably best to
explicitly declare the argument as byref.

 

[Chip Pearson]

Bill,

It is perfectly safe to pass arrays to functions or subs. Arrays are ALWAYS
passed ByRef. You'll get a compiler error if you try to pass an array ByVal.
If you're working with dynamic arrays (those that are not sized in the Dim
statement -- sizing is done with Redim), you'll need to ensure that the
array is allocated before attempting to access one of its elements.

I use the following functions with arrays:

Public Function IsArrayEmpty(Arr() As Variant) As Boolean
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' IsArrayEmpty
' This returns TRUE if the array is dynamic and has not been allocated with
a Redim statement.
' Returns FALSE if the array is static or has been allocated with a Redim
statement.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim N As Long
On Error Resume Next
N = UBound(Arr)
If Err.Number = 0 Then
IsArrayEmpty = False
Else
IsArrayEmpty = True
End If
End Function

Public Function NumberOfArrayDimensions(Arr() As Variant) As Integer
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' NumberOfArrayDimensions
' This function returns the number of dimensions of an array. An
uninitialized dynamic array
' has 0 dimensions. This condition can also be tested with IsArrayEmpty.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Ndx As Integer
Dim Res As Integer
On Error Resume Next
If IsArrayEmpty(Arr) = True Then
NumberOfArrayDimensions = 0
Exit Function
End If
Do
Ndx = Ndx + 1
Res = UBound(Arr, Ndx)
Loop Until Err.Number <> 0
NumberOfArrayDimensions = Ndx - 1
End Function

Public Function IsArrayDynamic(ByRef Arr() As Variant) As Boolean
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' IsArrayDynamic
' This function return TRUE or FALSE indicating whether Arr is a dynamic
array.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim LUBound As Long

' if we weren't passed an array, get out now with a FALSE result
If IsArray(Arr) = False Then
IsArrayDynamic = False
Exit Function
End If

' if the array is empty, it hasn't been allocated yet, so we know
' it must be a dynamic array.
If IsArrayEmpty(Arr) = True Then
IsArrayDynamic = True
Exit Function
End If

' save the UBound(A_7_AB_1_Arr)
LUBound = UBound(Arr)
On Error Resume Next
Err.Clear

' try to increae the number of elements. if this causes an error,
' the array was static. if no error is raised, the array is dynamic
ReDim Preserve Arr(LBound(Arr) To LUBound + 1)
If Err.Number <> 0 Then
' static array
IsArrayDynamic = False
Else
' dynamic array
IsArrayDynamic = True
' restore the original UBound
ReDim Preserve Arr(LBound(Arr) To LUBound)
End If

End Function



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)

 

[Bill Martin]

Thanks for the pointer Tom. I hadn't stumbled onto the ByRef and ByVal
declarations. I'll have to read up on them.

Bill
----------------------------------------------------------

 

[Bill Martin]

Thanks Chip. I do use dynamic arrays infrequently, so I'll have to watch
out for that one.

Bill
---------------------------------------