Excel VBA drawing Arcs programmatically - not lining up!
Hi,
I'm trying to create a dial-type control for an Excel-based report. The shape I'm after is basically the top half of a pie chart, and I'll add numbers, needles etc. I'm using msoShapeArc to create specific zones for the dial e.g. green-amber-red, which will be a 0-100% scale. It's almost working, but using the .Adjustment(1) and (2) settings to set the angles is shifting the arcs out of line, and I can't figure out why! The code is below - any help greatly appreciated. To see the problem exaggerated, repeat the line ".Adjustments(1) = 144" in the second segment and watch the arc shift right each time. For some reason it's not all of the arcs, just the some (the 2nd in this axample). Thanks, Pat. Sub DrawDial() Dim myArc As Shape For Each myArc In ActiveSheet.Shapes myArc.Delete Next myArc 'draw first segment Set myArc = ActiveSheet.Shapes.AddShape(msoShapeArc, 200, 100, 100, 100) With myArc .Adjustments(1) = 180 .Adjustments(2) = 144 .Fill.Visible = msoTrue .Fill.Transparency = 0.3 .Fill.ForeColor.SchemeColor = 11 End With 'draw second segment Set myArc = ActiveSheet.Shapes.AddShape(msoShapeArc, 200, 100, 100, 100) With myArc 'THIS NEXT LNE SHIFTS THE ARC RIGHT BY 1 POINT! .Adjustments(1) = 144 .Adjustments(2) = 70 .Fill.Visible = msoTrue .Fill.Transparency = 0.3 .Fill.ForeColor.SchemeColor = 53 End With 'draw third segment Set myArc = ActiveSheet.Shapes.AddShape(msoShapeArc, 200, 100, 100, 100) With myArc .Adjustments(1) = 70 .Adjustments(2) = 0 .Fill.Visible = msoTrue .Fill.Transparency = 0.3 .Fill.ForeColor.SchemeColor = 10 End With End Sub |
Excel VBA drawing Arcs programmatically - not lining up!
I see the problem but don't know what causes it. If it happens consistently,
you could correct for it using the IncrementLeft method. You can add code like this after the End With line for any of the segments: myArc.Select Selection.ShapeRange.IncrementLeft -0.5 -0.5 means to move half a point to the left. Positive numbers will move it to the right. I got the best results with your example by moving the first segment to the right 0.5, the second segment to the left 0.5, and the third segment to the right 0.2 points. Hopefully one of the MVPs can give us both a real explanation and solution, not just my clumsy workaround. Hope this helps, Hutch "Pat Russell" wrote: Hi, I'm trying to create a dial-type control for an Excel-based report. The shape I'm after is basically the top half of a pie chart, and I'll add numbers, needles etc. I'm using msoShapeArc to create specific zones for the dial e.g. green-amber-red, which will be a 0-100% scale. It's almost working, but using the .Adjustment(1) and (2) settings to set the angles is shifting the arcs out of line, and I can't figure out why! The code is below - any help greatly appreciated. To see the problem exaggerated, repeat the line ".Adjustments(1) = 144" in the second segment and watch the arc shift right each time. For some reason it's not all of the arcs, just the some (the 2nd in this axample). Thanks, Pat. Sub DrawDial() Dim myArc As Shape For Each myArc In ActiveSheet.Shapes myArc.Delete Next myArc 'draw first segment Set myArc = ActiveSheet.Shapes.AddShape(msoShapeArc, 200, 100, 100, 100) With myArc .Adjustments(1) = 180 .Adjustments(2) = 144 .Fill.Visible = msoTrue .Fill.Transparency = 0.3 .Fill.ForeColor.SchemeColor = 11 End With 'draw second segment Set myArc = ActiveSheet.Shapes.AddShape(msoShapeArc, 200, 100, 100, 100) With myArc 'THIS NEXT LNE SHIFTS THE ARC RIGHT BY 1 POINT! .Adjustments(1) = 144 .Adjustments(2) = 70 .Fill.Visible = msoTrue .Fill.Transparency = 0.3 .Fill.ForeColor.SchemeColor = 53 End With 'draw third segment Set myArc = ActiveSheet.Shapes.AddShape(msoShapeArc, 200, 100, 100, 100) With myArc .Adjustments(1) = 70 .Adjustments(2) = 0 .Fill.Visible = msoTrue .Fill.Transparency = 0.3 .Fill.ForeColor.SchemeColor = 10 End With End Sub |
Excel VBA drawing Arcs programmatically - not lining up!
Hi Tom,
Thanks for the reply. I did try using IncrementLeft but the code listing I used was just an example - the actual code allows the user to add any number of arcs into the dial, so I don't know in advance how much adjustment to make to the arc, and with say, 10 arcs in the dial, it might be the 4th and 8th that are out. Because of where the handles are on arcs, I can't reliably use "pin the Left of the next arc on the Left+Width of the previous arc" either. I've also tried making sure it's not some kind of number-rounding problem too. Because all the arcs have the same control points and radius etc. they should all centre at the same point, and the fact that adjusting the start and end angles of the arcs causes movement in the x,y location makes me think it has to be a bug of some kind, because the two things should be unrelated (I think). Especially since just repeating the same line continually moves it to the right. It's frustrating because it's almost not noticeable.... only it is ;-) "Tom Hutchins" wrote: I see the problem but don't know what causes it. If it happens consistently, you could correct for it using the IncrementLeft method. You can add code like this after the End With line for any of the segments: myArc.Select Selection.ShapeRange.IncrementLeft -0.5 -0.5 means to move half a point to the left. Positive numbers will move it to the right. I got the best results with your example by moving the first segment to the right 0.5, the second segment to the left 0.5, and the third segment to the right 0.2 points. Hopefully one of the MVPs can give us both a real explanation and solution, not just my clumsy workaround. Hope this helps, Hutch "Pat Russell" wrote: Hi, I'm trying to create a dial-type control for an Excel-based report. The shape I'm after is basically the top half of a pie chart, and I'll add numbers, needles etc. I'm using msoShapeArc to create specific zones for the dial e.g. green-amber-red, which will be a 0-100% scale. It's almost working, but using the .Adjustment(1) and (2) settings to set the angles is shifting the arcs out of line, and I can't figure out why! The code is below - any help greatly appreciated. To see the problem exaggerated, repeat the line ".Adjustments(1) = 144" in the second segment and watch the arc shift right each time. For some reason it's not all of the arcs, just the some (the 2nd in this axample). Thanks, Pat. Sub DrawDial() Dim myArc As Shape For Each myArc In ActiveSheet.Shapes myArc.Delete Next myArc 'draw first segment Set myArc = ActiveSheet.Shapes.AddShape(msoShapeArc, 200, 100, 100, 100) With myArc .Adjustments(1) = 180 .Adjustments(2) = 144 .Fill.Visible = msoTrue .Fill.Transparency = 0.3 .Fill.ForeColor.SchemeColor = 11 End With 'draw second segment Set myArc = ActiveSheet.Shapes.AddShape(msoShapeArc, 200, 100, 100, 100) With myArc 'THIS NEXT LNE SHIFTS THE ARC RIGHT BY 1 POINT! .Adjustments(1) = 144 .Adjustments(2) = 70 .Fill.Visible = msoTrue .Fill.Transparency = 0.3 .Fill.ForeColor.SchemeColor = 53 End With 'draw third segment Set myArc = ActiveSheet.Shapes.AddShape(msoShapeArc, 200, 100, 100, 100) With myArc .Adjustments(1) = 70 .Adjustments(2) = 0 .Fill.Visible = msoTrue .Fill.Transparency = 0.3 .Fill.ForeColor.SchemeColor = 10 End With End Sub |
Excel VBA drawing Arcs programmatically - not lining up!
I see what you mean. I suspect the reason relates to the precision of any
drawing is only to within (typically) 0.75 points. When the arc is redrawn to accommodate the "adjustments" all sorts of rounding occurs, ending up with the best fit and position of a rectangle that surrounds the arc. With different sets of coordinates I found arcs might move pt in any direction, not only to the right. The total 'error' may be compounded if they move in different directions. I don't see an easy solution. With some trig' though it should be possible to predict what the outer rectangular sizes and positions will end up as, rounded to the nearest 0.75. Then reposition the shapes where they ought to be. Actually that should be doable - good luck! Regards, Peter T "Pat Russell" wrote in message ... Hi, I'm trying to create a dial-type control for an Excel-based report. The shape I'm after is basically the top half of a pie chart, and I'll add numbers, needles etc. I'm using msoShapeArc to create specific zones for the dial e.g. green-amber-red, which will be a 0-100% scale. It's almost working, but using the .Adjustment(1) and (2) settings to set the angles is shifting the arcs out of line, and I can't figure out why! The code is below - any help greatly appreciated. To see the problem exaggerated, repeat the line ".Adjustments(1) = 144" in the second segment and watch the arc shift right each time. For some reason it's not all of the arcs, just the some (the 2nd in this axample). Thanks, Pat. Sub DrawDial() Dim myArc As Shape For Each myArc In ActiveSheet.Shapes myArc.Delete Next myArc 'draw first segment Set myArc = ActiveSheet.Shapes.AddShape(msoShapeArc, 200, 100, 100, 100) With myArc .Adjustments(1) = 180 .Adjustments(2) = 144 .Fill.Visible = msoTrue .Fill.Transparency = 0.3 .Fill.ForeColor.SchemeColor = 11 End With 'draw second segment Set myArc = ActiveSheet.Shapes.AddShape(msoShapeArc, 200, 100, 100, 100) With myArc 'THIS NEXT LNE SHIFTS THE ARC RIGHT BY 1 POINT! .Adjustments(1) = 144 .Adjustments(2) = 70 .Fill.Visible = msoTrue .Fill.Transparency = 0.3 .Fill.ForeColor.SchemeColor = 53 End With 'draw third segment Set myArc = ActiveSheet.Shapes.AddShape(msoShapeArc, 200, 100, 100, 100) With myArc .Adjustments(1) = 70 .Adjustments(2) = 0 .Fill.Visible = msoTrue .Fill.Transparency = 0.3 .Fill.ForeColor.SchemeColor = 10 End With End Sub |
Excel VBA drawing Arcs programmatically - not lining up!
There are other approaches to creating a dial/speedometer chart. Here are
links to a couple of step-by-step tutorials on creating them: http://www.brainbell.com/tutorials/m...eter_Chart.htm http://www.andypope.info/charts.htm Also, here is a link to charting guru Jon Peltier's site, where he explains why he thinks this kind of chart is ineffective: http://peltiertech.com/Excel/Charts/SpeedometerXP.html I hope these give you a workable alternative solution. Hutch "Pat Russell" wrote: Hi Tom, Thanks for the reply. I did try using IncrementLeft but the code listing I used was just an example - the actual code allows the user to add any number of arcs into the dial, so I don't know in advance how much adjustment to make to the arc, and with say, 10 arcs in the dial, it might be the 4th and 8th that are out. Because of where the handles are on arcs, I can't reliably use "pin the Left of the next arc on the Left+Width of the previous arc" either. I've also tried making sure it's not some kind of number-rounding problem too. Because all the arcs have the same control points and radius etc. they should all centre at the same point, and the fact that adjusting the start and end angles of the arcs causes movement in the x,y location makes me think it has to be a bug of some kind, because the two things should be unrelated (I think). Especially since just repeating the same line continually moves it to the right. It's frustrating because it's almost not noticeable.... only it is ;-) "Tom Hutchins" wrote: I see the problem but don't know what causes it. If it happens consistently, you could correct for it using the IncrementLeft method. You can add code like this after the End With line for any of the segments: myArc.Select Selection.ShapeRange.IncrementLeft -0.5 -0.5 means to move half a point to the left. Positive numbers will move it to the right. I got the best results with your example by moving the first segment to the right 0.5, the second segment to the left 0.5, and the third segment to the right 0.2 points. Hopefully one of the MVPs can give us both a real explanation and solution, not just my clumsy workaround. Hope this helps, Hutch "Pat Russell" wrote: Hi, I'm trying to create a dial-type control for an Excel-based report. The shape I'm after is basically the top half of a pie chart, and I'll add numbers, needles etc. I'm using msoShapeArc to create specific zones for the dial e.g. green-amber-red, which will be a 0-100% scale. It's almost working, but using the .Adjustment(1) and (2) settings to set the angles is shifting the arcs out of line, and I can't figure out why! The code is below - any help greatly appreciated. To see the problem exaggerated, repeat the line ".Adjustments(1) = 144" in the second segment and watch the arc shift right each time. For some reason it's not all of the arcs, just the some (the 2nd in this axample). Thanks, Pat. Sub DrawDial() Dim myArc As Shape For Each myArc In ActiveSheet.Shapes myArc.Delete Next myArc 'draw first segment Set myArc = ActiveSheet.Shapes.AddShape(msoShapeArc, 200, 100, 100, 100) With myArc .Adjustments(1) = 180 .Adjustments(2) = 144 .Fill.Visible = msoTrue .Fill.Transparency = 0.3 .Fill.ForeColor.SchemeColor = 11 End With 'draw second segment Set myArc = ActiveSheet.Shapes.AddShape(msoShapeArc, 200, 100, 100, 100) With myArc 'THIS NEXT LNE SHIFTS THE ARC RIGHT BY 1 POINT! .Adjustments(1) = 144 .Adjustments(2) = 70 .Fill.Visible = msoTrue .Fill.Transparency = 0.3 .Fill.ForeColor.SchemeColor = 53 End With 'draw third segment Set myArc = ActiveSheet.Shapes.AddShape(msoShapeArc, 200, 100, 100, 100) With myArc .Adjustments(1) = 70 .Adjustments(2) = 0 .Fill.Visible = msoTrue .Fill.Transparency = 0.3 .Fill.ForeColor.SchemeColor = 10 End With End Sub |
Excel VBA drawing Arcs programmatically - not lining up!
Thanks for these links - really interesting, almost certainly useful.
In the end I settled on adding some specific functions to adjust the positions of the arcs based on whether they crossed boundaries for the dial shape as a whole. It's not perfect because an arc can be out of line but not overlap anything of the 'outline', but it works well enough for me to create something usable. Interesting argument about whether these dials are useful. I undertand the psychological disapproval, but the fact is, if it gets people looking at important numbers (the more senior they are, the worse they are!), then it works, regardless of whether it should or not. Theory: 0, Real World: 1 ;-) Thanks again, Pat. |
Excel VBA drawing Arcs programmatically - not lining up!
Hi guys,
I am working on a visual display (speedo dial) in Excel similar to what you have been talking about in this thread. I can assist with the issue that has plagued me (moving arcs). It is an artefact of the drawing of the arc that causes it to move... as has been discussed earlier in the thread. So I figured if it is drawing an arc then why not try to pin it in the one place that should stay in one spot - the centre. This can be found and referred back to the .top and .left placement points using .width and .height. This should work for all quadrants of the arc. I have only tried it successfully on the dials in my project but feel free to feed back to me and I can see if I can help if you can't get it going. NeoDan *** Sent via Developersdex http://www.developersdex.com *** |
All times are GMT +1. The time now is 12:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com