Peter,
Thanks for all that.
It does seem that the issue of graphics and Excel is not consistently or
logically designed, as illustrated by the numerous caveats you outline
below.
I go through this again, but I still feel it will be a hit/miss process when
dealing with these objects.
NickHK
"Peter T" <peter_t@discussions wrote in message
...
Hi Nick,
I've spent a lot of time working with these collections and not sure if
I'm
in any better position now to clarify. Just a few ad-hoc comments -
The Shapes and related ShapeRange collections were introduced in Office97
and added several methods & properties to existing DrawingObjects as well
as
introducing new objects. These appear to exist as different 'levels' of
the
same fundamental object. The Comments is a bit of an anomaly, seems to be
at
DrawingObject level yet not in the DrawingObjects collection.
.Shapes.Count, .DrawingObjects.Count, For Each obj in .DrawingObjects: cnt
=
cnt + 1:Next
All these counts might be different (eg with groups on the sheet).
Which to use, Shapes or DrawingObjects. My preference is the
DrawingObjects
(or sub category) when possible, if whatever property I'm accessing is
available at this level. I find read/write much faster with
DrawingObjects.
There are other advantages, eg
Activesheet.Rectangles.Count 'no need to iterate the Shapes collection
similarly apply properties at this level, fewer potential errors, or even
Activesheet.Rectangles.Delete 'no error if no Rectangles exist
(Rectangles is one of many sub collections at the Drawing level)
It's also very significantly faster to add multiple objects to an array at
the drawingobject level.
Frustratingly some type of objects, although they exist in the
DrawingObjects collection, don't belong to any sub-category (eg certain
AutoShapes).
Variables can be declared as any of the various DrawingObject sub groups.
To
see those available select the Excel library in Object Browser, the
right-click and "Show hidden members", they'll appear grey but available
in
all xl versions to 2003, I assume (?) also in 2007.
Notably ChartObjects and OLEObjects have not been 'hidden', and to work
with
them still need to start effectively at the drawingobject level. So if
declared & set as a shape need to go sideways into its DrawingObject then
continue as if it was correctly declared in the first place in order to
work
with it, eg
Set shp = ActiveSheet.Shapes("Chart 1")
Set shp = ActiveSheet.Shapes("Chart 3")
shp.DrawingObject.Chart.whatever
similarly for OLEObjects
Some properties can be accessed at either Shape or DrawingObject, others
only in one or the other 'level'
Shapes vs ShapeRange
This is relatively well documented in Help but not entirely accurately. Eg
this from "ShapeRange Collection Object"
-A shape range can contain as few as a single shape or as many as all the
shapes on the document
Hmm, to cater for the unknown possibility of a selection of a Single
textbox
or some other single Shape might need to do try both these,
.Shapes(1) or ShapeRange(1), one will work but the other will error
depending on the object.
It's all a bit of a mess!
Regards,
Peter T
"NickHK" wrote in message
...
I do find the collections somewhat confusing; deciding if you should be
working with Shapes, ShapeRange, DrawingObjects, OLEObjects etc
Anyone a link to clarify this ?
NickHK
"Jim Cone" wrote in message
...
DrawingObjects were replaced by Shapes from xl97 on.
DrawingObjects still function but are not covered in the help file.
You would probably have to secure the help file from one of the
older versions (if you can find one) to learn anything about them.
Or you can study up on Shapes.
Personally, I feel MS should have stuck with DrawingObjects.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
"pan65"
wrote in message
Is there a good resource that gives more code examples and further
explanation of using Drawings Objects than the helps menu?