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 a single string. I want to know how I can have the strings separated by 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 wrote:
>
> 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 a
> single string. I want to know how I can have the strings separated by
> comma recogonised as separate arguments for Sub 2 to accept.
>
> --
> blatham
> ------------------------------------------------------------------------
> blatham's Profile: http://www.excelforum.com/member.php...o&userid=19441
> View this thread: http://www.excelforum.com/showthread...hreadid=491996

--

Dave Peterson

[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 wrote:
>
> 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
>
> --
> blatham
> ------------------------------------------------------------------------
> blatham's Profile: http://www.excelforum.com/member.php...o&userid=19441
> View this thread: http://www.excelforum.com/showthread...hreadid=491996

--

Dave Peterson

[blatham]

OK thanks for your suggestions.

Ben