Reputation: 322
I want to get a collection of all shapes that are in a certain group. Shapes in the group may be a group themselves so I (think I) need a recursive function to get every shape.
This is my code:
Function getAllShapes(shp As Shape) As Collection
Dim shapes As Collection
Dim subshp As Shape
Dim colShp As Shape
Dim col As Collection
If shp.Type = 2 Then 'Check if shp is a group
For Each subshp In shp.shapes
shapes.Add (subshp) 'Add the subshp to the shape collection
If subshp.Type = 2 Then 'Check if subshp is group
Set col = getAllShapes (subshp) 'Get all the shapes from the group
For Each colShp In col 'Error here
shapes.Add (colShp) 'Add all the shapes to the collection
Next colShp
End If
Next subshp
Else: shapes.Add (shp)
End If
Set getAllShapes = shapes
Set shapes = Nothing
End Function
However, I always get a runtime error: Object Required whenever I try to access the collection from the function (for example in the function itself)
Upvotes: 2
Views: 2309
Reputation: 2438
Your problem starts in this line:
shapes.Add (subshp) 'Add the subshp to the shape collection
Which should be (note the removal of the parenthesis):
shapes.Add subshp 'Add the subshp to the shape collection
What is happening here is that the ()
means that the VBA evaluates subshp
and would return whatever the default method returns (e.g. name or ID). So you are not adding a Shape
to your collection but something else (e.g. a String
or Long
).
The space in this line confuses me:
Set col = getAllShapes (subshp) 'Get all the shapes from the group
Your VBE should have written this as:
Set col = getAllShapes(subshp) 'Get all the shapes from the group
So this tells me something else is going on there. However, at this point in the code subshp
is a Shape
object so should not be causing any problems.
So now, when you use the following code, the VBA is looking for a Shape
but your collection contains other things. This causes a mismatch in the line of code - hence your error.
For Each colShp In col 'Error here
shapes.Add (colShp) 'Add all the shapes to the collection
Next colShp
How often do you do this?
shapes.Add (subshp) 'Add the subshp to the shape collection
shapes.Add (colShp) 'Add all the shapes to the collection
Else: shapes.Add (shp)
Also: no need to join two lines together with the ":" - this helps obfuscate your code. They should be:
shapes.Add subshp 'Add the subshp to the shape collection
shapes.Add colShp 'Add all the shapes to the collection
Else
with shapes.Add shp
on the next line before the End If
As a final point, a good idea is not to name variables (e.g. 'shapes') the same as an existing common method or property - this could lead to some confusion as to which parameter you are using.
Upvotes: 3