CODEWITHSUNDEEP
vbaexcel
mopwop
mopwop

Reputation: 41

Fundamental Excel VBA Classes & Objects

There is something I am not understanding and I guess it must be pretty basic.

Can someone please be so kind to explain to me the relationship between Classes and Objects enough so that I can understand what is happening below? Both Location and Name are properties of the PivotTable Class and return strings. Why do the first statements work but the last 4 give the error "Object doesn't support this action (Error 445)"?

?ActiveWorkbook.ActiveSheet.PivotTables.Count
3
?ActiveWorkbook.ActiveSheet.PivotTables(1).Name
PivotTable12
?ActiveWorkbook.ActiveSheet.PivotTables(2).Name
PivotTable3
?ActiveWorkbook.ActiveSheet.PivotTables(3).Name 
PivotTable2

?ActiveSheet.PivotTables(1).Location
?ActiveSheet.PivotTables(2).Location
?ActiveSheet.PivotTables(3).Location
?ActiveSheet.PivotTables("PivotTable12").Location

[Location Def][1]
[Immediate Window][2]
[Error][3]

Upvotes: 2

Views: 124

Answers (1)

ashleedawg
ashleedawg

Reputation: 21619

Some objects have properties or methods which either aren't available in every version, or in every situation. Seemingly most tasks in VBA can be accomplished in multiple ways; this, it's important to be comfortable with looking for "alternative means of accomplishing the same thing."

In this case my next step would be to check out which properties/methods are available for my object in my scenario by Setting it to a variable:

Dim p As PivotTable
Set p = ActiveSheet.PivotTables(1)
Stop

Run that code, and when execution breaks at the Stop, double click the variable p to select it, right-click it, click Add Watch... and click OK.

This will open the Watches. Use the ⊞ to open and explore the tree to see what's available in this context.

Location likely says "Application Defined error..." however perhaps you can find the location with:

?p.DataBodyRange.Cells.Address

or

?p.DataLabelRange.Cells.Address

...see what the Watches window shows as available within the context you're using.

Upvotes: 1

Related Questions