Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting if a range contains a shape
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting if a range contains a shape
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 "Nick Hebb" wrote in message oups.com... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting if a range contains a shape
a slight typo!
about than 0.01 sec less than 0.1 sec Peter T |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detecting if a range contains a shape
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shape Range | Charts and Charting in Excel | |||
detecting range of spreadsheet | New Users to Excel | |||
my curser changed from arrow shape to a cross shape???? | New Users to Excel | |||
align a picture/shape in a Range! | Excel Programming | |||
detecting shape clicked on | Excel Programming |