Fundamental Excel VBA Classes & Objects

Question

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]

Personally I've never been able to get the .Location property of PivotTables to work. But instead you can use the .TableRange2 property like so: ActiveSheet.PivotTables(1).TableRange2.Address for the whole range of the pivottable, or ActiveSheet.PivotTables(1).TableRange2.Cells(1).Address for just the top left cell. — tigeravatar
– tigeravatar, Commented May 31, 2018 at 19:42
Seriously? All the undocumented nuances make this too painful to learn. Thank you for your information! Maybe some will have an explanation for understanding. — mopwop
– mopwop, Commented May 31, 2018 at 19:51
You can use Location to set, but even then it makes 100% no sense whatsoever. I think this .Location property was added and tested during happy hour and the documentation was written while they were passed out soon afterwards. — JNevill
– JNevill, Commented May 31, 2018 at 20:10
These comments and the answer below are great help. Thank you to tigeravatar, JNevill, and ashleedawg Also were my images no good is that why they no longer show or are available in the question? — mopwop
– mopwop, Commented Jun 8, 2018 at 15:20

ashleedawg · Accepted Answer · 2018-05-31 20:10:43Z

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.

Collectives™ on Stack Overflow

Fundamental Excel VBA Classes & Objects

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related