Passing Variable Number of Arguments to a Sub

[blatham]

Does anyone know how I can do the above.

I am constructing a list of arguments in one sub to pass to another.
Something along the following lines:

Sub One

Dim sArgs() As String
j = 0
ReDim Preserve sArgs(j)
sArgs(j) = Chr(34) & "Arg1" & chr(34)

j = 1
ReDim Preserve sArgs(j)
sArgs(j) = Chr(34) & "Arg2" & chr(34)

Call Sub2(Join( Args(), ",")

End Sub


Sub2(ParamArray Args())

'Sub 2 Code

End Sub

I know the problem lies in the fact that the join statement returns
single string. I want to know how I can have the strings separated b
comma recogonised as separate arguments for Sub 2 to accept

 

[Dave Peterson]

How about just passing that array to the second sub.

Here's an example that passes an array, a range and a string to the second sub:

Option Explicit
Sub One()

Dim sArgs() As String
Dim iCtr As Long

ReDim Preserve sArgs(1 To 3)
For iCtr = LBound(sArgs) To UBound(sArgs)
sArgs(iCtr) = iCtr & "--A"
Next iCtr

Call sub2(sArgs, ActiveSheet.Range("a1"), "test33")

End Sub


Sub sub2(ParamArray Args())

Dim myElement As Variant
Dim iCtr As Long
Dim myCell As Range

For Each myElement In Args
If IsArray(myElement) Then
For iCtr = LBound(myElement) To UBound(myElement)
MsgBox myElement(iCtr)
Next iCtr
ElseIf TypeOf myElement Is Range Then
For Each myCell In myElement.Cells
MsgBox myCell.Value
Next myCell
ElseIf VarType(myElement) = vbString Then
MsgBox myElement
Else
'do nothing
End If
Next myElement

End Sub

 

[blatham]

Thanks for that. What I'm really trying to do is construct the
arguments for the GetPivotData method in a function. I want the
function to accept the same arguments as the regular worksheet function
but my version will be able to handle Grand Totals for a particular
field. The regular version doesn't do this - you have to include lots
of different IF and GETPIVOTDATA functions depending on what you are
trying to retrieve. Hope I'm making sense! This is what I have at the
moment:

Function GPD3(sDataField As String, rPivotTable As Range, ParamArray
FieldValPairs())

Dim sArgs As Variant
Dim i As Integer
Dim j As Integer
j = 0
sArgs(j) = sDataField
For i = 0 To UBound(FieldValPairs()) Step 2
If FieldValPairs(i + 1) <> "Grand Total" Then
sArgs(j) = FieldValPairs(i)
sArgs(j + 1) = FieldValPairs(i + 1)
j = j + 2
End If
Next i

GPD3 = rPivotTable.PivotTable.GetPivotData(Join(sArgs, ","))

End Function

 

[Dave Peterson]

..getpivottable isn't looking for one string. So the join() won't work.

I was hoping that I could pass an array to that function, but it didn't work for
me.

I think the way I'd approach it is to keep track of how many parms I want to use
and then base the calls on that:

select case myParms
'for example
case is = 3
GPD3 = rPivotTable.PivotTable.GetPivotData(parm1, parm2, parm3)
case is = 5
GPD3 = rPivotTable.PivotTable _
.GetPivotData(parm1, parm2, parm3, parm4, parm5)
'etc
end select

But there might be an easier way--but I don't know it.

 

[blatham]

OK thanks for your suggestions.

Ben