VBA manipulation of PivotTables

[Raesu]

Hi all,


My question can basically be simplified to asking the difference between a couple ways of writing code, which seem identical to me but one works and one does not.


Adding a field called "User Id" to the table, Excel usually makes this a sum because it is all numbers. However, sometimes it will have a cell of text, and then excel will default the field to count. So, trying to make sure "Count" is always its function:

[pre]

With .PivotFields("User Id")
.Orientation = xlDataField
.Function = xlCount
End With

The above code works.  The below code does not:


.PivotFields("User Id").Orientation = xlDataField
.PivotFields("User Id").Function = xlCount

[/pre]
This is because the pivot field's name changes to "Sum of User Id". I tried to loop through PivotFields looking for "Sum of", but I see with the MsgBox function that it is looping through the items in the Pivot menu...which is still "User Id" of course.


Can someone please clarify how VBA distinguishes pivotfields being used in the table, and the list of pivotfields in the list to the right? When I use the .Orientation method the list on the right is used, but trying .Function creates an error since its only looking at whats used in the table.


Thanks for any explanation.

 

[asa]

I am not experienced with manipulating pivot tables in code, but you can find your answers by using debugging techniques like breakpoints, stepping through your code a line at a time, and monitoring the properties of the relevant object using the Watch window or using Debug.Print commands in the Immediate window.


The version of your code that doesn't work will also be a little slower even if it did, and is generally bad practice.


Better practice is to assign an variable as a reference to the object of interest, like:

[pre]

Dim Ws as Worksheet
Dim P as PivotTable
Dim F as PivotField

Set Ws = ThisWorkbook.Worksheets("wsname")
Set P = Ws.PivotTables("ptname") ' or Ws.PivotTables(1)
Set F = P.PivotFields("User Id")
With F
.Orientation = xlDataField
.Function = xlCount
End With
Set F = Nothing
Set P = Nothing
Set Ws = Nothing[/pre]
This version will actually work the same with or without a [code]With

block, because you already have an object reference set. Without the Set F=...[/code] line, your code has to find the field, then create and destroy a temporary reference to the object for each line of code that refers to it. Some objects can behave differently or don't work at all without being assigned to variables, although that may not be an issue with common objects within Workbooks (I don't have a ton of experience programming in Excel).


Some help on debugging macros:

http://chandoo.org/forums/topic/debugging-custom-vba-function

http://www.cpearson.com/excel/debug.htm

http://www.eng.auburn.edu/~tplacek/courses/3600/notes8c.pdf

http://pubs.logicalexpressions.com/pub0009/lpmarticle.asp?id=410


Asa

 

[akashbest@gmail.com]

Its simple - Use PT.DataFields(1).Function = xlCount. PT being your PivotTable object.

You can use DataFields Index to loop through multiple data fields.