Re: Passing Arrays to SUBs

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

Bill
---------------------------------------
"Chip Pearson" <chip@xxxxxxxxxxxx> wrote in message news:OU0nswT%23GHA.4544@xxxxxxxxxxxxxxxxxxxxxxx
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" <Martin_SpamTrap@xxxxxxxxxxx> wrote in message news:ezarRVT%23GHA.3312@xxxxxxxxxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: Is Array Empty
    ... ' Dim lAddressArrayDescriptor As Long ... IsArrayEmpty = True ... 'trap error on uninitialized array ...
    (microsoft.public.vb.general.discussion)
  • Re: Dynamic Array
    ... cannot redim an array and at the same time change the base datatype of the ... Dim TheArray() As String ... Dim n As Integer 'integer used with dynamic array ... ReDim Preserve TheArray ...
    (microsoft.public.access.modulesdaovba)
  • Re: Variant array is empty problem
    ... The IsArrayEmpty function is one of about 25 array ... Public Function IsArrayEmpty(Arr As Variant) As Boolean ... Dim Var As Variant ...
    (microsoft.public.excel.programming)
  • Re: How can I quickly copy a CONST to a typed VAR
    ... Or an invalid pointer ... A dynamic array is lain out like this in memory: ... ElementCount aren't there. ... without the bookkeeping information that a dynamic array has. ...
    (alt.comp.lang.borland-delphi)
  • Static Array versus Dynamic Array, Multi-Dimensional Memory Layout and speed.
    ... dynamic array access could be faster than static array access. ... I think what Dr.Bob might be seeing is a different layout for static array ... sequantially, but more like randomly, which has performance penalties. ...
    (alt.comp.lang.borland-delphi)