kansaskannan
12/18/2006 7:50:00 PM
Works like a charm, Jim.
Thank you so much. Again!
Jim Thomlinson wrote:
> Just throw the term ooptional in front of the variable something like this...
>
> Public Function CreatePvt(snm, ptn, rfd, cfd, dfd, fun, _
> cal, nft, cap, pos, stl, Optional pfd)
>
> Set pt = _
> PC.CreatePivotTable(TableDestination:=ActiveSheet.Cells(dr + 5, 1),
> TableName:=ptn)
>
> '-------------add row column and page fields
> if pfd = "" then
> pt.AddFields RowFields:=rfd, ColumnFields:=Array(cfd)
> else
> pt.AddFields RowFields:=rfd, ColumnFields:=Array(cfd), PageFields:=pfd
> end if
>
> '-------------add data fields
> With pt.PivotFields(dfd)
> .Orientation = xlDataField
> .Function = fun
> .Caption = cap
> .Calculation = cal
> .Position = pos
> .NumberFormat = nft
> End With
>
> Set pt = Nothing
>
> End Function
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "kansaskannan@gmail.com" wrote:
>
> > I created a VBA function to generate a number of pivot tables. For
> > each pivot table required, I supply 12 arguments used by the function.
> > For some pivot tables, however, I do not need a page field. How do I
> > make this pagefield optional? I give the code below. 'pfd' is the
> > pagefield which I want to make optional.
> >
> > Thank you!
> > ===============================================================
> > Public Function CreatePvt(snm, ptn, rfd, cfd, dfd, fun, cal, nft, cap,
> > pos, stl, pfd)
> >
> > Set pt = _
> > PC.CreatePivotTable(TableDestination:=ActiveSheet.Cells(dr + 5, 1),
> > TableName:=ptn)
> >
> > '-------------add row column and page fields
> > pt.AddFields RowFields:=rfd, ColumnFields:=Array(cfd), PageFields:=pfd
> >
> > '-------------add data fields
> > With pt.PivotFields(dfd)
> > .Orientation = xlDataField
> > .Function = fun
> > .Caption = cap
> > .Calculation = cal
> > .Position = pos
> > .NumberFormat = nft
> > End With
> >
> > Set pt = Nothing
> >
> > End Function
> >
> >