A
Anthony Berglas
The old Excel 95 Drawing Object was replaced by Shapes in Excel 97, but
the Drawing Object interface was retained. Very handy for keeping
compatibility. But there appears to be a critical bug introduced for
interiors. The code bellow dies at
With Selection.Interior
.Pattern = xlSolid
What is the best way to submit a bug report?
(The move in Excel 97 to shapes brought a *huge* performance penalty if
there was a large number of shapes due to a linear search each time one
was referenced (ie. the VBA pointer is dereferenced). That remains
which ever interface (Shapes or Drawing Object) that one uses to access
them. It also remains through the versions, at least until Excel 2003.
Putting in a proper hash table would also be wonderful.)
Anthony
----------------------------
Option Explicit
Sub TestAll()
Debug.Print "--------------------------"
Sheets.Add
DrawShapes
ShapesPrint "Oval 97"
DrawObj95
DObjPrint "Oval 95"
ShapesPrint "Oval 95"
DObjPrint "Oval 97"
End Sub
Sub ShapesPrint(name$)
Dim s As Shape
Set s = ActiveSheet.Shapes(name)
Debug.Print "Shape Name", s.name, TypeName(s),
s.Fill.ForeColor.SchemeColor
End Sub
Sub DObjPrint(name$)
Dim dobj As Object ' DrawingObject "not defined"
Set dobj = ActiveSheet.DrawingObjects(name)
Debug.Print "DObj", dobj.name, TypeName(dobj) ' --> Oval
End Sub
Sub DrawShapes()
Dim oval As Object
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 219.75, 24.75, 96#,
60.75). _
Select
Set oval = ActiveSheet.Shapes.AddShape(msoShapeOval, 219#, 123#,
103.5, 50.25)
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 220.5,
229.5, _
103.5, 69#).Select
Selection.Characters.Text = "XL97 style"
oval.Select
Selection.name = "Oval 97"
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 14
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
End Sub
Sub DrawObj95()
Dim oval As Object
ActiveSheet.Rectangles.Add(36, 28.5, 73.5, 41.25).Select
Selection.Interior.ColorIndex = xlNone
Set oval = ActiveSheet.Ovals.Add(36.75, 96.75, 69.75, 37.5)
ActiveSheet.TextBoxes.Add(39, 169.5, 75, 52.5).Select
Selection.Interior.ColorIndex = 2
Selection.Characters.Text = "Hello XL95"
oval.Select
Selection.name = "Oval 95"
With Selection.Interior
.Pattern = xlSolid
.ColorIndex = 3
End With
End Sub
the Drawing Object interface was retained. Very handy for keeping
compatibility. But there appears to be a critical bug introduced for
interiors. The code bellow dies at
With Selection.Interior
.Pattern = xlSolid
What is the best way to submit a bug report?
(The move in Excel 97 to shapes brought a *huge* performance penalty if
there was a large number of shapes due to a linear search each time one
was referenced (ie. the VBA pointer is dereferenced). That remains
which ever interface (Shapes or Drawing Object) that one uses to access
them. It also remains through the versions, at least until Excel 2003.
Putting in a proper hash table would also be wonderful.)
Anthony
----------------------------
Option Explicit
Sub TestAll()
Debug.Print "--------------------------"
Sheets.Add
DrawShapes
ShapesPrint "Oval 97"
DrawObj95
DObjPrint "Oval 95"
ShapesPrint "Oval 95"
DObjPrint "Oval 97"
End Sub
Sub ShapesPrint(name$)
Dim s As Shape
Set s = ActiveSheet.Shapes(name)
Debug.Print "Shape Name", s.name, TypeName(s),
s.Fill.ForeColor.SchemeColor
End Sub
Sub DObjPrint(name$)
Dim dobj As Object ' DrawingObject "not defined"
Set dobj = ActiveSheet.DrawingObjects(name)
Debug.Print "DObj", dobj.name, TypeName(dobj) ' --> Oval
End Sub
Sub DrawShapes()
Dim oval As Object
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 219.75, 24.75, 96#,
60.75). _
Select
Set oval = ActiveSheet.Shapes.AddShape(msoShapeOval, 219#, 123#,
103.5, 50.25)
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 220.5,
229.5, _
103.5, 69#).Select
Selection.Characters.Text = "XL97 style"
oval.Select
Selection.name = "Oval 97"
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 14
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
End Sub
Sub DrawObj95()
Dim oval As Object
ActiveSheet.Rectangles.Add(36, 28.5, 73.5, 41.25).Select
Selection.Interior.ColorIndex = xlNone
Set oval = ActiveSheet.Ovals.Add(36.75, 96.75, 69.75, 37.5)
ActiveSheet.TextBoxes.Add(39, 169.5, 75, 52.5).Select
Selection.Interior.ColorIndex = 2
Selection.Characters.Text = "Hello XL95"
oval.Select
Selection.name = "Oval 95"
With Selection.Interior
.Pattern = xlSolid
.ColorIndex = 3
End With
End Sub