Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Passing paramArray

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi all...
    Whats the correct way to pass paramArray?? When I run test() the first msgbox says 3, the second one says 0. Thanks in advance.

    John

    eg. my code here

    Sub CreateReport(xlBook As Workbook, dataReference As String, ParamArray State() As Variant)

    Dim i
    xlBook.Worksheets.Add.Name = "MIS" ' Create new sheet

    msgbox UBOUND(State)

    For i = 1 To 30

    ==> LINE OF INTEREST WriteTable xlBook.Application.Range("A3").Offset(i * 2, 0), "S" & Format(i, "00"), dataReference, State
    Next i

    End Sub

    Sub WriteTable(objStart As Range, SVCode As String, dataReference As String, ParamArray State() As Variant)

    Dim i, db, str1, str2

    MsgBox UBound(State)
    end sub

    sub test()

    CreateReport ActiveSheet.Application, "reference", "f26", "m26", "h26"

    end sub


  2. #2
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-18 17:36, John Yazou wrote:
    Hi all...
    Whats the correct way to pass paramArray?? When I run test() the first msgbox says 3, the second one says 0. Thanks in advance.

    John

    eg. my code here

    Sub CreateReport(xlBook As Workbook, dataReference As String, ParamArray State() As Variant)

    Dim i
    xlBook.Worksheets.Add.Name = "MIS" ' Create new sheet

    msgbox UBOUND(State)

    For i = 1 To 30

    ==> LINE OF INTEREST WriteTable xlBook.Application.Range("A3").Offset(i * 2, 0), "S" & Format(i, "00"), dataReference, State
    Next i

    End Sub

    Sub WriteTable(objStart As Range, SVCode As String, dataReference As String, ParamArray State() As Variant)

    Dim i, db, str1, str2

    MsgBox UBound(State)
    end sub

    sub test()

    CreateReport ActiveSheet.Application, "reference", "f26", "m26", "h26"

    end sub
    When you pass a ParamArray to a second function that asks for a ParamArray, it assigns your first array to the first position of of the new ParamArray (it's an array inside the first position of the second array). That probably sounds confusing. Anyway, the way around this is to just ask for a Variant in your second procedure. Here is an example:

    Code:
    Sub testParam1(ParamArray state() As Variant)
        MsgBox UBound(state)
        Call testParam2(state)
    End Sub
    
    Sub testParam2(ByVal state As Variant)
        MsgBox UBound(state)
    End Sub
    
    Sub test()
        testParam1 "ab", "cd", "ef"
    End Sub
    Hope this helps,

    Russell

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Russell... works perfectly..

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •