CODEWITHSUNDEEP
vbaruntime-errorexcel-2010pivot-table
alexander
alexander

Reputation: 57

Excel 2010 VBA: Invalid procedure call when adding data field to pivot table

I want to create multiple pivot tables by encapsulating the complexity into functions and procedures (I did already manage to create pivots with VBA, but one module for each pivot, meaning lots of repetitive code...)

I managed to create procedures and functions to create the pivot, add a filter, and a row field. But when it comes to adding a data field, I get an invalid procedure call; the strange thing however is that I get that error only when I use variables to pass info: looking at the line prodicung the error, the first line works perfectly fine, whereas I cannot get the second line running (the variables contain the correct values):

 pivotName.addDataField pivotName.PivotFields("sdID"), "SD ID number", xlCount 'works fine
 pivotName.addDataField pivotName.PivotFields(fieldName), fieldDescription, calcMethod 'produces Invalid procedure call error

As I am running out of ideas, any help would be highly appreciated!

Thank you very much,

Alexander

Upvotes: 0

Views: 347

Answers (2)

Gareth
Gareth

Reputation: 5243

The reason for the error is due to calcMethod being declared earlier in the code as a string.

The .addDataField method accepts the following parameters:

.AddDataField(Field, Caption, Function)

The parameter Function is of the XLConsolidationFunction enum and therefore should be declared and assigned like below:

Dim calcMethod As XlConsolidationFunction

calcMethod = xlCount

Once declared and assigned as above, you can use it within your method in the following way where fieldName and fieldDescription are both strings:

pivotName.addDataField pivotName.PivotFields(fieldName), fieldDescription, calcMethod

Upvotes: 1

alexander
alexander

Reputation: 57

As you can see in the comment from Gareth, the problem was to declare calcMethod (optional function) as string, not as XLConsolidatedFunction.

Thank you once again!

Upvotes: 0

Related Questions