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?

gazzer wrote:
> 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

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" <|||newspam|||@nezumi.demon.co.uk> wrote in message
news:8Zlqn.265256$(E-Mail Removed)...
> gazzer wrote:
>> 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 wrote:
> 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
>
> Regards,
> Peter T
>
>
>
>
>
> "Martin Brown" <|||newspam|||@nezumi.demon.co.uk> wrote in message
> news:8Zlqn.265256$(E-Mail Removed)...
>> gazzer wrote:
>>> 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
>
>

"Martin Brown" <|||newspam|||@nezumi.demon.co.uk> wrote in message
newsZpqn.59995$(E-Mail Removed)...
> Peter T wrote:
>> 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

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



>
> 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
>>
>> Regards,
>> Peter T
>>
>>
>>
>>
>>
>> "Martin Brown" <|||newspam|||@nezumi.demon.co.uk> wrote in message
>> news:8Zlqn.265256$(E-Mail Removed)...
>>> gazzer wrote:
>>>> 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
>>

Sorry about the accidental post

"Martin Brown" wrote in message
> Peter T wrote:
>> 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

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.

>> 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?

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

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

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

Regards,
Peter T

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.

"gazzer" wrote:

> 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?
>