OrderedMembers Property

Microsoft Office Web Components Object Model

OrderedMembers Property

       

Returns or sets a Variant that determines how the members of a field are sorted when the SortDirection property is set to plSortDirectionCustom, plSortDirectionCustomAscending, or plSortDirectionCustomDescending. Set this property to an array of members that is ordered in the way that you want them to appear in the PivotTable list. When setting this property, you can pass an array of member names, member unique names, or member object references. When retrieving this property, the array will always contain PivotMember object references. Read/write.

expression.OrderedMembers

expression   Required. An expression that returns a PivotField object.

Remarks

Any members in the field that are not listed in this array will appear below the last member listed in the array.

Setting this property replaces the current list. To add items to an existing list, you must retrieve the list and add add members to it, or use a variable to hold the current list, append to it, and reset this property.

It is allowable to pass member names or unique names that currently do not exist in the data results. If you pass a name or unique name that cannot be resolved to a resulting member, it will be converted to a PivotMember object with its IsValid property set to False.

To clear this list, set this property to Empty or an array of zero elements.

Example

This example creates a custom sort order in the Title field.

Sub CustomSort()

   Dim ptConstants
   Dim ptView
   Dim pfTitle

   Set ptConstants = PivotTable1.Constants

   Set ptView = PivotTable1.ActiveView

   ' Set a variable to the Title field.
   Set pfTitle = ptView.FieldSets("Title").Fields("Title")

   ' Specify the ordering for some of the items in the Title field.
   pfTitle.OrderedMembers = Array("Sales Representative", _
                                  "Sales Manager", "Vice President, Sales")

   ' Set the sort direction.
   pfTitle.SortDirection = ptConstants.plSortDirectionCustom

End Sub