Passing arrays as parameters in VBA Excel

Question

Hello.  This is my first post here. 

I have googled and rtfm'd the following to death to no avail.

Excel VBA under WinXP Pro

Problem:

I have an array of variants declared thus -

Dim avarMyArrray() as variant

I have a subroutine into which I want to pass the array.

 I have attempted the suroutine call thus -

Call myRoutine(avarMyArray())

Call myRoutine(avarMyArray)

 I have attempted to receive the call thus -

Sub myRoutine(avarNewArray() as variant)

Sub myRoutine(avarNewArray())

Sub myRoutine(avarNewArray)

No matter what I try the array is not passed to the receiving routine.  The VBA docs relative to this are non-existent. 

I can work around this by adding a LOT of code...but I'd rather be able to pass the array in to keep the additional level of abstraction and flexibility.

Question: How does one pass an array into a routine as a parameter in VBA?

Any help is greatly appreciated.

 

 

 

 

 

 

Answer 1

Here's an example

Public Sub Test()
    Dim arry(10) As Variant
    For i = 0 To UBound(arry, 1)
        arry(i) = i * 2
    Next
         
    ParseArray arry
  End Sub

Public Sub ParseArray(arrValues() As Variant)
    For i = 0 To UBound(arrValues, 1)
        ActiveSheet.Cells(i + 1, 1) = arrValues(i)
    Next
End Sub

Answer 2

Thank you for the response.

I was using a variant type pointer as a workaround.  I was simply not careful in my declarations.

 

I appreciate the assistance.

Answer 3

Hi GWild

Just cruising the net and saw your post. Since no one else replied, guess I'll jump in.

You were close. Try this code

Sub PassArray()
    Dim avarMyArrray(5) As Variant
    avarMyArrray(0) = "We"
    avarMyArrray(1) = "have"
    avarMyArrray(2) = "passed"
    avarMyArrray(3) = "the"
    avarMyArrray(4) = "test"
   
    Call myRoutine(avarMyArrray)

End Sub
   
Sub myRoutine(avarNewArray As Variant)
    Dim i As Integer
    For i = 0 To UBound(avarNewArray)
        Debug.Print avarNewArray(i) & " "
    Next i
End Sub