Excel VBA - Using a variable to set the pivot table datafield function

Question

I'm trying to create custom reports using VBA created pivot tables and one of the parts I am having trouble with is setting the datafield to be based on what he user desires.

For example: I have a drop down menu that displays each type of PivotFields function (sum, min max etc.) and depending on what the user selects it is stored as a string variable.

When I run my code:

Dim valueXL As String
valueXL = xlSum

With Worksheets(sheetPivotName).PivotTables(sheetPivotName).PivotFields(valueType)
.Function = valueXL
.NumberFormat = "$#,##0.00"
End With

I get an error, however, if I replace .Function = valueXL with .Function = xlSum it works correctly.

Is there any way to dynamically set the .Function value?

Thanks

INOPIAE · Accepted Answer · 2016-05-05 05:09:21Z

3

As xlSum is a numeric Excel constant you should change valueXL to integer

Dim valueXL As Integer
valueXL = xlSum

or use direct the constant in your code

With Worksheets(sheetPivotName).PivotTables(sheetPivotName).PivotFields(valueType)
  .Function = xlSum
  .NumberFormat = "$#,##0.00"
End With

answered May 5, 2016 at 5:09

INOPIAE

2931 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dirk Reichel Over a year ago

or use CInt(valueXL)... still it makes no sense that valueXL is defined as string :/

TMP4 Over a year ago

Thanks for this , I had no idea xlSum and those values were treated as integers and not strings!

TMP4 · Accepted Answer · 2016-05-05 04:55:11Z

0

I found a possible solution, which is using the Evaluate function, however I was wondering if there were any other solutions?

If not I can select this as the answer.

answered May 5, 2016 at 4:55

TMP4

235 bronze badges

Collectives™ on Stack Overflow

Excel VBA - Using a variable to set the pivot table datafield function

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related