Passing paramArray

[John Yazou]

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

 

[Russell Hauf]

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:

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

 

[John Yazou]

Thanks Russell... works perfectly..