Shapes & Drawing Object Bug Excel 2007 Beta 2


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.)



Option Explicit
Sub TestAll()
Debug.Print "--------------------------"
ShapesPrint "Oval 97"
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",, TypeName(s),
End Sub
Sub DObjPrint(name$)

Dim dobj As Object ' DrawingObject "not defined"
Set dobj = ActiveSheet.DrawingObjects(name)
Debug.Print "DObj",, TypeName(dobj) ' --> Oval
End Sub

Sub DrawShapes()
Dim oval As Object

ActiveSheet.Shapes.AddShape(msoShapeRectangle, 219.75, 24.75, 96#,
60.75). _
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 = "Oval 97"
Selection.ShapeRange.Fill.Visible = msoTrue
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 = "Oval 95"
With Selection.Interior
.Pattern = xlSolid
.ColorIndex = 3
End With
End Sub

Peter T

Hi Anthony,

I don't have xl2007 (see below) so can't answer your question how to report
a bug, but I'll speculate about this this line of code you posted -
Selection.Interior.Pattern = xlSolid

At the Shapes level to set a pattern first need to set the FillFormat type
(though can use .Patterned followed by a pattern constant without an =), eg


so with the above wouldn't then need to apply xlSolid, though note as a
shape pattern format -
xlSolid = msoPattern5Percent = 1

I wonder if this is just a special case your drawingobjects method fails in

Re your comment concerning performance with Shapes or Drawing Object -
With a large number of shapes like you I find Shapes slow doing certain
things, but I find Drawingobjects very considerably faster. I posted some
comments a couple of weeks ago in thread subject: "DrawingObjects".

As I mentioned I don't have xl2007 but hope(?) most methods with
DrawingObjects will continue to be supported, though disappointed to see
this in your code -
Dim dobj As Object ' DrawingObject "not defined"

I tried to obtain an early xl2007 beta last October but for some reason MS
'aggressively refused to supply it.

Peter T


Hi Anthony,

This looks fixed in current builds of Excel 2007 - it runs without error and
applies a solid fill to the selected drawing object/shape.

Excel Team

Note: We've only got a couple weeks to get bugs filed and fixed, so if I've
requested additional info via email, it'd be great if you can get that to us
ASAP. Please include any necessary sample files, as well as detailed repro
steps so that we can try to reproduce the problem on our side. Also - if
you're emailing me directly (definitely the most efficient at this point)
you'll want to fixup my email address to remove everything after danbatt and
before the @.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question