Having lost hours in a similar problem, I found a couple of concepts related to linking shapes in excel, but none of them satisfy me 100%. To access the form you have 4 clean methods:
Shape.Name : FAST, but NOT RELIABLE. The form name can be used to get a link to the shape, but provided that you do not have duplicate names. Code: ActiveSheet.Shapes("Shape1")
Shape.ZOrderPosition : very FAST, but NOT RELIABLE. You can use the ZOrder of a form to get a link to a shape, because it is the same as the shape index in the shape collection. But if you do not have a group of figures that violates the previous rule (see fooobar.com/questions/955144 / ... ). Code: ActiveSheet.Shapes(ZOrderFromOneShape)
Set shpRef = Shape : FAST, RELIABLE, but NOT CONSTANT. I try to use it always, I can, especially when I create a new form. Moreover, if I have to repeat new forms later, I try to keep a reference to an object inside the collection. However, not Persistent, this means that if you run VBA code again and again to lose all links and collections. Code: Set shp = NewShape , or you can add it to the collection: coll.add NewShape for the loop later.
Shape.ID : RELIABLE, SIDE, but not directly supported! The form identifier is very reliable (do not change and cannot duplicate identifiers in the sheet). However, there is no direct VBA function to return a form knowing its identifier. The only way is to completely process all the shapes until the identifier matches the ID you were looking for, but it can be very SLOW! .
code:
Function FindShapeByID(ws as excel.worksheet, ID as long) as Excel.Shape dim i as long set FindShapeByID = nothing 'Not found... for i = 1 to ws.shapes.count if ws.shapes(i).ID = ID then set FindShapeByID = ws.shapes(i) 'Return the shape object exit function end if next i End Function
Note 1: If you want to access this function several times, you can improve it using the cache of form identifiers. This way you will loop only once.
Note 2: If you move a shape from one sheet to another, the shape identifier will change!
Mixing and using the above knowledge, I concluded two main approaches:
FIRST APPROACH
- FAST, BUT VOLATILE: (same as item number 3). Try to keep the link in the object as long as possible. When I have to repeat a bunch of shapes later, I keep the links inside the collection, and I avoid using other secondary links, such as name, ZOrder or ID.
For instance:
dim col as new Collection dim shp as Excel.Shape '' <- Insert the code here, where you create your shape or chart col.add shp1 '' <- Make other stuffs for each shp in col '' <- make something with the shape in this loop! next shp
Of course, the problem is that collection and reference are not permanent. You will lose them when you stop and restart the vba code!
SECOND APPROACH
- PERSISTENT: My solution is to keep the name and ID of the form for later reference. What for? Having a name, I can access the form very quickly most of the time. Just in case, when I found a duplicate name, I do a slow ID search loop. How to find out if there is a duplicate name? Very simple, just check the identifier of the first name search, and if they do not match, you should assume that this is duplicated.
Here is the code:
Function findShapeByNameAndID(ws As Excel.Worksheet, name As String, ID As Long) As Shape Dim sh As Excel.Shape Set findShapeByNameAndID = Nothing 'Means not found On Error GoTo fastexit Set sh = ws.Shapes(name) 'Now check if the ID matches If sh.ID = ID Then 'Found! This should be the usual case! Set findShapeByNameAndID = sh Else 'Ups, not the right shape. We ha to make a loop! Dim i As Long For i = 1 To ws.Shapes.Count If ws.Shapes(i).ID = ID Then 'Found! This should be the usual case! Set findShapeByNameAndID = ws.Shapes(i) End If Next i End If fastexit: Set sh = Nothing End Function
Hope this helps you!
Note 1: if you want to search for shapes that can be inside groups, then the function is more complicated.
Note 2: ZOrder looks good, but cannot find it useful. When I tried to take advantage of this, there was always a missing part ...