The Axes and Elements collections, and the Axis and Element objects, are used to define the structure of a table, as described in Understanding axes and Navigating the axis tree. However, the order of elements displayed in a table can differ from the order in which they were defined for the table. This happens when a table is sorted, nested, or contains hidden elements.

When you need to work with the elements displayed in a table, as opposed to the elements in the original structure defined for the table, use the AxisHeadings and ElementHeadings collections, and the AxisHeading and ElementHeading objects. These objects are automatically updated when a table is populated, and always reflect the sorted order and the status of hidden elements. This ensures that they match the order of the rows and columns in the ADO recordset, accessed using the Table.CellValues property. For more information, see Working with the ADO recordset.

The following example shows the structure of a simple table, before and after sorting:

The Elements collection for the table contains:

Gender: Elements - Base, Male, Female

Biology: Elements - Base, Yes, No, Not Answered

The ElementHeadings collection for the table is the same at this stage:

Gender: ElementHeadings - Base, Male, Female

Biology: ElementHeadings - Base, Yes, No, Not Answered

If the table is sorted by the numbers of those holding a qualification in biology (MyTable.SortRow = "biology{yes}"), the order of elements in the top axis changes:

This is not reflected in the Elements collection, which retains the original order defined for the table. However, the order of elements in the ElementHeadings collection is automatically updated to reflect the displayed order of the headings:

Gender: ElementHeadings - Base, Female, Male

Biology: ElementHeadings - Base, Yes, No, Not Answered

When a table is nested, elements occur more than once in the displayed table. The following example shows the side axis of a table with Gender nested within Entrance:

In the list of elements in the Elements collection, the elements occur only once:

Entrance: Elements - Base, Main, Side

Gender: Elements - Base, Male, Female

In the ElementHeadings collection, the list of elements matches that of the displayed table:

Entrance: Elements - Base, Main, Side

Gender: Elements - Base, Male, Female, Base, Male, Female, Base, Male, Female

If the table is sorted, the ElementHeadings collection is automatically updated to reflect the new order.

Differences between the defined structure of a table and the displayed structure of a table also occur if you create a table containing a hidden element. The Elements collection contains the hidden element, but the ElementHeadings collection reflects the displayed table and does not contain the hidden element.