Drawing in VBA - compatibility between Excel 2003 and 2007

[gazzer]

I have “inherited†a number of legacy worksheets that use, I believe, the pre
Excel 97 via VBA drawing object model to plot complex diagrams on a number of
sheets.

These work perfectly in all versions of Excel up and to Excel 2003, but now
give problems when running under Excel 2007 (although the VBA compiles OK) .
In Excel 2007 the positioning and rotation of the arcs (and to some extent
other drawing items).
are totally different in Xl 2007

A simply example of the code I have is show below:

Sub DrawArc()
With ActiveSheet.Arcs.Add(10, 10, 200, 200)
With .Border
.LineStyle = xlContinuous
.Weight = xlThin
End With
End With
End Sub

If I run the above example in Excel 2003, the arc starts in cell A1 and ends
in E14, but in Excel 2007 it starts in cell E1 and ends in I14. Effectively
the drawing routines in the worksheets are now rendered useless under 2007
because of this.

Is this a known compatibility issue? And my question really is, is there a
workaround or am I faced with having to rewrite the code, (which is quite a
major task), to get make the worksheets useable under 2007?

 

[Martin Brown]

I have “inherited†a number of legacy worksheets that use, I believe, the pre
Excel 97 via VBA drawing object model to plot complex diagrams on a number of
sheets.

These work perfectly in all versions of Excel up and to Excel 2003, but now
give problems when running under Excel 2007 (although the VBA compiles OK) .
In Excel 2007 the positioning and rotation of the arcs (and to some extent
other drawing items).
are totally different in Xl 2007

A simply example of the code I have is show below:

Sub DrawArc()
With ActiveSheet.Arcs.Add(10, 10, 200, 200)
With .Border
.LineStyle = xlContinuous
.Weight = xlThin
End With
End With
End Sub

If I run the above example in Excel 2003, the arc starts in cell A1 and ends
in E14, but in Excel 2007 it starts in cell E1 and ends in I14. Effectively
the drawing routines in the worksheets are now rendered useless under 2007
because of this.

Is this a known compatibility issue? And my question really is, is there a
workaround or am I faced with having to rewrite the code, (which is quite a
major task), to get make the worksheets useable under 2007?

Fraid so. You are lucky the code actually compiled in XL2007 without
major errors. They changed the Shape object model gratuitously so as to
break almost everything that depends on exact positioning.

They seem to have completely wrecked .Arcs.Add
It is FUBAR and with no plausible work around that I can see.

Under certain circumstances the X coordinate of the start of the curve
has half the x radius added! And you can't use negative start positions.

You may find the output of the following test piece amusing:

Sub DrawArc()
For r = 10 To 220 Step 10
With ActiveSheet.Arcs.Add(100, 400, r, r) '' fails r>100
End With
With ActiveSheet.Arcs.Add(200, 200, r, 100) '' fails r>200
End With
With ActiveSheet.Arcs.Add(300, 10, 100, r) '' OK since 100<300
End With
Next r
End Sub

I suggest a bug report to MickeySoft. I don't know what they were
smoking when they coded this. It isn't even consistent about the
handling of X and Y and it all goes to pot when Xradius > Xstart

Horribly broken doesn't being to describe it.

Regards,
Martin Brown

 

[Peter T]

In 97-2003 the Arc.Add arguments are X1, Y1, X2, Y2. Point-2 can be to the
left and/or above Point1.

In 2007 the arguments are L, T, W, H, the bottom-right must be bottom-right!
If really need to use Arcs.Add would need to keep this in mind and use
horizontal & vertical flip methods if/as necessary. It does mean catering
differently for 2007 and earlier versions. Better though for consistency, if
nothing else, to use Shapes.AddShape msoShapeArc etc.

You could argue either way as to which is the more logical method (ie which
set of arguments), but I agree it should have simply stayed the same. Don't
hold your breath though in the expectation it will be changed. The entire
DrawingObjects was maintained in 97 only for legacy reasons with earlier
versions. However many operations work very significantly faster at this
'level and fortunately it's still exposed (almost), but by more favour
rather than obligation. I suspect it was not straightforward as the entire
drawing system in 2007 is radically different to previous versions.

Regards,
Peter T

 

[Martin Brown]

In 97-2003 the Arc.Add arguments are X1, Y1, X2, Y2. Point-2 can be to the
left and/or above Point1.

In 2007 the arguments are L, T, W, H, the bottom-right must be bottom-right!
If really need to use Arcs.Add would need to keep this in mind and use
horizontal & vertical flip methods if/as necessary. It does mean catering
differently for 2007 and earlier versions. Better though for consistency, if
nothing else, to use Shapes.AddShape msoShapeArc etc.

Trouble is that is *not* what it delivers.

For L >= W it works OK
But L < W it draws an arc with effective arguments L+W/2, T, W, H

The y coordinates are handled correctly.

This is why his original 10,10 start with radius 200 drifts across the
page. I haven't tried msoShapeArc but I expect it shares this quirk.

You could argue either way as to which is the more logical method (ie which
set of arguments), but I agree it should have simply stayed the same. Don't

Changing it after so long was a disaster and broke a lot of legacy code.
BTW Can you get MS Help to show the argument list for Arc.Add in
XL2007? If so what keywords will get it to display?

hold your breath though in the expectation it will be changed. The entire
DrawingObjects was maintained in 97 only for legacy reasons with earlier
versions. However many operations work very significantly faster at this
'level and fortunately it's still exposed (almost), but by more favour
rather than obligation. I suspect it was not straightforward as the entire
drawing system in 2007 is radically different to previous versions.

And glacially slow to boot. Race conditions exist in the chart and graph
axes drawing steps when used directly from VBA

Regards,
Martin Brown

 

[Peter T]

Trouble is that is *not* what it delivers.

For L >= W it works OK
But L < W it draws an arc with effective arguments L+W/2, T, W, H

Assuming you mean 2007 it works as predicted and as I described for me

Dim a As Arc

Set a = ActiveSheet.Arcs.Add(100, 200, 300, 400)
With a
Debug.Print .Left, .Top, .Width, .Height
End With

 

[Peter T]

Sorry about the accidental post

"Martin Brown" wrote in message

Trouble is that is *not* what it delivers.

For L >= W it works OK
But L < W it draws an arc with effective arguments L+W/2, T, W, H

Hmm your're right, or rather I agree L >=W is OK, but for me
if L < W then it ends up as L=W

The y coordinates are handled correctly.

I don't think it's right to call them Y coordinates (in 2007), but T & H
work as anticipated in all scenarios

This is why his original 10,10 start with radius 200 drifts across the
page. I haven't tried msoShapeArc but I expect it shares this quirk.

Yes right again. In effect you cannot add msoShapeArc closer to the left
that its width, presumably to allow it to be flipped. But not right at all.
I doubt MS will accept the Arcs method is a bug as it is not an officially
supported method, hasn't been for ages. But msoShapeArc I think is a bug.

Changing it after so long was a disaster and broke a lot of legacy code.
BTW Can you get MS Help to show the argument list for Arc.Add in XL2007?
If so what keywords will get it to display?

None of the DrawingObjects methods are listed in help, haven't been for ages
and only barely in 97. You'll get most of them in object browser but not the
Add arg's. However if you have the tools to hand to examine the excel.exe
the arg's are named as
X1, Y1, X2, Y2
and same as in previous versions. But in actuality in 2007 they are as I
mentioned previously L,T,W,H but L must be >= W

And glacially slow to boot. Race conditions exist in the chart and graph
axes drawing steps when used directly from VBA

Yeah that's another issue, particularly in an old system.

Regards,
Peter T

 

[pbart]

Your problem does not appear to rest with VBA. If you simply draw the curves
you want from the user interface it appears that any arc is treated as part
of a complete circle/oval, with 'resizing handles' that frame the entire
figure. If you move the object around (opening the angle out an filling to
form a sector may help) you will find that you cannot move any part of the
oval off the worksheet.

If you really do need an arc so close the edge you may have to replace it by
drawing a freeform starting at X,Y and followed by the curved segment with
nodes/control points at X+W/2, Y; X+W, Y+H/2 and X+W,Y+H. Not what you were
hoping for I guess.