How To: Display Pivot Table methods and properties in VBA intellisense

  • Article

When you are coding and use a compact method of referring to a pivot table such as:

ActiveSheet.PivotTables(1).

You would expect VBA's intellisense to display the methods and properties of the PivotTables collection when adding the period, however in Excel 2007 and 2010 you get no intellisense whatsoever.

There is a workaround for displaying these methods and properties. Instead of using a compact method in your coding, use the following:

Dim pt as PivotTable
Set pt = ActiveSheet.PivotTables(1)
pt.

when you hit the period after pt, you get intellisense and can see what methods and properties are available for this collection. This method also works for other collections that display no intellisense.