In Excel 2003, open a new workbook and add this code.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
Sub TestShapes() Dim i As Long Dim sType As String Dim shp As Shape Dim s1 As String, s2 As String For Each shp In Sheet1.Shapes shp.Delete Next shp s1 = Sheet1.ChartObjects.Add(1, 1, 1, 1).Name s2 = Sheet1.Rectangles.Add(20, 20, 20, 20).Name Sheet1.Shapes.Range(Array(s1, s2)).Select For i = 1 To Selection.Count Debug.Print Selection(i).Name Next i Debug.Print Sheet1.Shapes.Range(Array(s2, s1)).Select For i = 1 To Selection.Count Debug.Print Selection(i).Name Next i Debug.Print Sheet1.Shapes.Range(Array(s2, s1)).Select sType = TypeName(Selection(1)) For i = 1 To Selection.Count Debug.Print Selection(i).Name Next i End Sub |
It adds a chart object and a rectangle, selects them in different orders, then reads the selection. I get this
If I select the chart first, it returns both shapes as expected. If I select the rectangle first, it returns the rectangle twice. If I select the rectangle first, but check the TypeName of Selection(1), it slaps Excel in the head so that it realizes what’s selected.
Am I missing something here?
It looks like that the first time you do a print.debut it is reporting the based on the order of the array and after that it reports based on the order the shapes were created.
So if we had three shapes:
s1 = Sheet1.ChartObjects.Add(1, 1, 1, 1).Name
s2 = Sheet1.Rectangles.Add(20, 20, 20, 20).Name
s3 = Sheet1.Ovals.Add(20, 20, 20, 20).Name
Sheet1.Shapes.Range(Array(s1, s2, s3)).Select
Debug.Print
For i = 1 To Selection.Count
Debug.Print Selection(i).Name
Next i
It looks like that the first time you do a debug.print it is reporting based on the order of the array and after that it reports based on the order the shapes were created.
So if we had three shapes:
s1 = Sheet1.ChartObjects.Add(1, 1, 1, 1).Name
s2 = Sheet1.Rectangles.Add(20, 20, 20, 20).Name
s3 = Sheet1.Ovals.Add(20, 20, 20, 20).Name
Sheet1.Shapes.Range(Array(s2, s3, s1)).Select
Debug.Print Selection(2).Name
Debug.Print
For i = 1 To Selection.Count
Debug.Print Selection(i).Name
Next i
You would get “Oval” first (because that is in position 2 of the array and then “Chart, Rect, Oval” because they were created in that order.
This also means the first debug.print loop looks correct but is in fact “incorrect”. It’s only because the array is selected in the same order the shapes were created that it gives a correct result.