Passing arrays between functions

[hargy]

Hi

I am having a few difficulties understanding the intricasies of passing arrays between functions in my VBA code

Firstly I wanted to use the Array() function to add entries to an array called strManager.

Dim strManager as String
strManager = Array("Line", "Technical", "Finance&quot

I thought from reading the MS Helpfile, that this would set the upper bound of the array to the size of the data placed in the array, in this case 3. It seems more useful to do it this way, so that if the system, needs to develop, then I can add to the line above.

Instead I had to do it this way

Dim strManager as String
ReDim strManager(conLp)
strManager(1) = "Line"
strManager(2) = "Technical"
strManager(3) = "Finance"

This array stores information about managers who have signed a form. Later in another function, if the manager has signed the form, then their name is crossed off the list (ie deleted from the array filling the field with "&quot

The problem is passing this array to the new function

I was also under the impression that the array could be passed parameterless

ie FindStatus intStatus, intRequestNo, strManager()

Again, I had to define this with a constant conLp (=3)

FindStatus intStatus, intRequestNo, strManager(conLp)

OK. Now MS say that to receive the array, you need to do the following

Public Sub FindStatus(intStatus As Integer, intRequestNo As Integer, ParamArray strManager() As Variant)

To make it parameterless - it must use ParamArray and be Variant and be the last argument in the procedure definition.

I have tried this and using the Ubound function both before the call and inside the new function. Ubound starts at 3 (as expected), however when received by the new function it is 0 - I presume this indicates that the array strManager has not been passed correctly. I have tried removing the ParamArray command, howvever, this gives an error when calling the function.

Can someone, give any clues as to what I could do to make this work better. A previous note said the Help gave good info on this topic, but I feel it is rather sketchy - MS seem to assume that most things will be coded within one sub or function, when in reality, more emphasis should be on how to move data around within the code.

Thank you

 

[Steve101]

Hargy,

Perhaps this code example will help you. Copy and past this into a new module; then run the Test function.

Function Test()
'---------------------------------------------
'declare one dimensional array with 5 elements
'and load values into elements.
'---------------------------------------------
Dim ThisArr(5)
ThisArr(1) = "A"
ThisArr(2) = "B"
ThisArr(3) = "C"
ThisArr(4) = "D"
ThisArr(5) = "E"

'--------------------------------
'Now pass the array to a function
'--------------------------------
x = PassArr(ThisArr)

End Function

Function PassArr(Arr)
'-------------------------------------
'This function receives the array, and
'prints each element.
'-------------------------------------
NumElements = UBound(Arr) 'In this example, will = 5
For i = 1 To NumElements
Debug.Print Arr(i)
Next i
End Function