none
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.

     

     

     

     

     

     

    Thursday, March 09, 2006 5:34 PM

Answers

  • 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

    Thursday, March 09, 2006 7:39 PM

All replies

  • 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

    Thursday, March 09, 2006 7:39 PM
  • 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.

    Thursday, March 09, 2006 7:42 PM
  • 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

    Wednesday, April 26, 2006 12:37 AM