Detecting if a range contains a shape

[Nick Hebb]

As the topic title says, I'm trying to determine whether a range of
cells contains a shape. So far, I can see only one method:

Iterate through all the shapes in the worksheet and do boundary
checking using the TopLeftCell and BottomRightCell properties of the
Shape object.

This works but there could be 100's of shapes to iterate through on the
worksheet that I'm working with, so it would be computationally
expensive.

Is there a way to take a Range object and test it directly to see if it
contains any Shapes?

 

[Peter T]

I don't know of any way without looping, but I don't find it
"computationally expensive". Do you mean amount of code or speed. If speed,
I find it's pretty fast to "get" all objects in a range even if 500 exist on
the sheet (about than 0.01 sec in my very old machine), not so much code
either.

FWIW, I don't look at TopLeftCell and BottomRightCell, but compare left,
top, right, bottom of each object with that of the range, and set a flag
against the object index if inside. End up with something like

set getShapes = activesheet.shapes.range(arr)

where arr is an array of indexes of shapes inside the range

If you're not against looping (?) I could extract some code.

Regards,
Peter T

 

[Peter T]

a slight typo!

about than 0.01 sec

less than 0.1 sec

Peter T

 

[Nick Hebb]

FWIW, I don't look at TopLeftCell and BottomRightCell, but compare left,

top, right, bottom of each object with that of the range, and set a flag
against the object index if inside.

That's a good idea. I was using the Intersect (and Offset(-1,-1) for
the BottomRightCell) then testing the result for Is Nothing.

less than 0.1 sec

I'm performing quite a few operations in addition to this and I'm
noticing a slight latency. I haven't measured anything yet, but I'm
just looking through my code to see where i might be able to trim it
up.