Unable to add CubeField/PivotField as a Data Field in VBA

Question

I'm working on a very long Macro for my job and I am just about finished, but this is the last part and no matter what I do, I can't seem to figure out the fix. In this PivotTable, I need to have "Managers" as the columns and everything else (i.e. all of the months) in the Values section, with "Values" as the rows. After tinkering around, it seems as though all of the fields registered as CubeFields instead of PivotFields. When I run this, as soon as it gets to .Orientation = xlDataField, it throws a "Run-Time error 5...Invalid procedure call or argument." I also need to make sure that those DataFields are averaged and in a specific number format. Nothing I do works, and any guidance/fixes/workarounds would be greatly appreciated! Below are the macro and a download link to the file for tinkering.

Dim pvtTable As PivotTable
Dim cubField As CubeField
Dim i As Long
Dim cubName As String

Set pvtTable = ActiveSheet.PivotTables(1)
For Each cubField In pvtTable.CubeFields
    For i = 1 To pvtTable.CubeFields.Count
        With pvtTable.CubeFields(i)
            If .Name = "[effRent_perBed].[Manager]" Then
                .Orientation = xlColumnField
           Else:
                .Orientation = xlDataField
                'has to be averaged
                'has to have number format of ##0.00
            End If

        End With
    Next
Next

This is an edit that I tried making based on the macro recorder. It puts the field in the values field, but there's no way to change it to an average. It simply lists all of the values as "1 (Check image for a visual). When I get to .Function = xlAverage, it says Error 1004: Unable to set the Function property of the PivotField class.

                If Name = "[effRent_perBed].[Manager]" Then
                    .Orientation = xlColumnField
                Else:
                    With ActiveSheet.PivotTables(1)
                        .AddDataField ActiveSheet.PivotTables(1) _
                        CubeFields(cubName), _
                        "Average of " & cubName
                    End With

                    With ActiveSheet.PivotTables(1).PivotFields(cubName)
                        .Caption = "Average of " & cubName
                        .Function = xlAverage
                    End With

When I use the Macro recorder to add something to the datafield, I get this:

ActiveSheet.PivotTables("effRent_perBed_Pivot").CubeFields.GetMeasure _
    "[effRent_perBed].[Jan-16]", xlSum, "Sum of Jan-16"
ActiveSheet.PivotTables("effRent_perBed_Pivot").AddDataField ActiveSheet. _
    PivotTables("effRent_perBed_Pivot").CubeFields("[Measures].[Sum of Jan-16]"), _
    "Sum of Jan-16"
With ActiveSheet.PivotTables("effRent_perBed_Pivot").PivotFields( _
    "[Measures].[Sum of Jan-16]")
    .Caption = "Average of Jan-16"
    .Function = xlAverage
End With

This is a copy of the file I'm working on. It shows the raw pivot table, and then the finished product. I have to do this for 3 sheets, so I have to cycle through each one. https://drive.google.com/uc?export=download&id=1NLGg8DVEMHnB2Ad7NAKWySevq8naqFjr

This is also posted on other forums (I need to get as many eyes on this as soon as possible) https://www.excelforum.com/excel-programming-vba-macros/1222588-unable-to-add-cubefield-pivotfield-as-a-data-field-in-vba.html#post4856940

https://www.mrexcel.com/forum/excel-questions/1046038-unable-add-cubefield-pivotfield-data-field-vba.html

https://www.ozgrid.com/forum/node/1200403

Answer 1

Your question could still do with fleshing out further. For instance, you don't say if you are creating a PivotTable from scratch and then adding fields, or whether this gets run on an existing PivotTable that might already have fields in it.

Somebody just pointed me to the Rubber Duck Problem Solving/ blog post, and I'm going to point you to it too, because it helps people to answer if they know everything relevant.

At https://msdn.microsoft.com/en-us/vba/excel-vba/articles/pivottable-adddatafield-method-excel it says the syntax for .adddatafield is AddDataField( Field , Caption , Function ) with the function being optional.

So what happens if you try this?

If Name = "[effRent_perBed].[Manager]" Then
    .Orientation = xlColumnField
 Else:
    With ActiveSheet.PivotTables(1)
        .AddDataField ActiveSheet.PivotTables(1) _
            CubeFields(cubName), _
            "Average of " & cubName, _
            xlAverage
    End With
End if

There's some issues with your original code block. I don't understand why you are both iterating through the CubeFields collection and also iterating through a count of cubefields. i.e. this bit:

For Each cubField In pvtTable.CubeFields
    For i = 1 To pvtTable.CubeFields.Count

Isn't that just going to iterate through everything by the square of Cubefields.count? You should be able to ditch that For i = 1 To pvtTable.CubeFields.Count bit and simply use the cubField reference directly.

I think the reason your code failed to set the summarisation to XLAverage is because once you add a field to the Data area, the name effectively changes. You can see this if you leave the DataField in place, and run this code:

Sub Macro1()
'
' Macro1 Macro
'
Dim pvtTable As PivotTable
Dim cubField As CubeField
Dim i As Long
Dim cubName As String

Set pvtTable = ActiveSheet.PivotTables(1)
For Each cubField In pvtTable.CubeFields
    Debug.Print cubField.Name
Next

End Sub

That will print the names of all the fields to the immediate window (assuming you have it open) at which point you will see that while you will have a result for [effRent_perBed].[Manager], this is NOT the datafield. The DataField will be something like [Measures].[Average of effRent_perBed]

And that's why your code failed: you were still referring to the field of interest as if it was still a CubeField. But as soon as you try it add it to the DataFields area, a new DataField gets created, and that's the one you needed to change the aggregation for.

Like I said above though, you can do this at the time you create the DataField.