Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shapes & Drawing Object Bug Excel 2007 Beta 2
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(msoTextOrientationHo rizontal, 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shapes & Drawing Object Bug Excel 2007 Beta 2
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 ActiveSheet.Shapes(1).Fill.Solid 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 xl2007. 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. Regards, Peter T "Anthony Berglas" wrote in message oups.com... 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(msoTextOrientationHo rizontal, 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shapes & Drawing Object Bug Excel 2007 Beta 2
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. Cheers, Dan 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 @. "Anthony Berglas" wrote in message oups.com... 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(msoTextOrientationHo rizontal, 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When drawing shapes in excel the shapes keep disappearing | Excel Discussion (Misc queries) | |||
Drawing shapes in a chart | Charts and Charting in Excel | |||
Excel 2007 Beta 2 - Macro Run-time error '424' Object required | Excel Programming | |||
Trouble Drawing shapes with macros in Excel | Excel Programming | |||
regarding: Trouble Drawing shapes with macros in Excel | Excel Programming |