CODEWITHSUNDEEP
vbaexcelpivot-table
vega69
vega69

Reputation: 41

How to use variables to set pivot table Function parameters with VBA?

I would like to use variables to specifying parameters of pivot table. Most of them are working correctly, but 2 of them not. The “function” parameters “xli” and “ValueFilterQuant(pos)” don’t come up with any values. I tried it write between quotation marks and quotation marks-& but nothing happened. Is there any method to set Function parameters with variables? This is the snippet of code:

For pos = 1 To UBound(ValueQuant)
    If ValueQuant(pos) = "Work" Then
       xli = "xlSum"
       Label = "Sum of "
    Else
       xli = "xlCount"
       Label = "Count of "
    End If
ActiveSheet.PivotTables("PivotTable1").CubeFields.GetMeasure "[database].[" & ValueQuant(pos) & "]" _
    , xli, "" & Label & "" & ValueQuant(pos) & ""
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    " PivotTable1").CubeFields("[Measures].[" & Label & "" & ValueQuant(pos) & "]"), "" & Label & "" & ValueQuant(pos) & ""
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
    "[Measures].[" & Label & "" & ValueQuant(pos) & "]")
    .Caption = "" & ValueFilterQuantaf(pos) & "" & ValueQuant(pos) & ""
    .Function = ValueFilterQuant(pos)
End With
Next

Upvotes: 0

Views: 698

Answers (1)

MacroMarc
MacroMarc

Reputation: 3324

The code is incomplete. I don't know where ValueFilterQuant(pos) is coming from.

I suspect that like xli = "xlSum" it is returning a string variable, but the thing is that those parameters should not be strings. They are numbers that are derived from an XlConsolidationFunction enumeration.

So something like:

If ValueQuant(pos) = "Work" Then
   xli = XlConsolidationFunction.xlSum '-4157
   Label = "Sum of "
Else
   xli = XlConsolidationFunction.xlCount '-4112
   Label = "Count of "
End If

Same thing needs to happen for ValueFilterQuant(pos).

Note that it is more work to derive a dynamic enumeration at runtime through a string. I guess that you are reading a string value in off a worksheet or similar process.

You could set them up in a dictionary beforehand, and pick out the numbers that way. The list of values is here: https://msdn.microsoft.com/en-us/library/office/ff837374.aspx

Upvotes: 1

Related Questions