Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Arrays to SUBs
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Arrays to SUBs
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. -- Regards, Tom Ogilvy "Bill Martin" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Arrays to SUBs
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Arrays to SUBs
Thanks for the pointer Tom. I hadn't stumbled onto the ByRef and ByVal
declarations. I'll have to read up on them. Bill ---------------------------------------------------------- "Tom Ogilvy" wrote in message ... 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. -- Regards, Tom Ogilvy "Bill Martin" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Arrays to SUBs
Thanks Chip. I do use dynamic arrays infrequently, so I'll have to watch
out for that one. Bill --------------------------------------- "Chip Pearson" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing the value of listbox to other Subs | Excel Programming | |||
General Question regarding passing cells, ranges, cell values through subs & functions | Excel Programming | |||
General Question regarding passing cells, ranges, cell values through subs & functions | Excel Programming | |||
General Question regarding passing cells, ranges, cell values through subs & functions | Excel Programming | |||
Passing values between 2 subs ? | Excel Programming |