Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Chart MajorUnit for Clean lookng Chart
Hi,
I'm hoping a chart guru can help me solve this pesky problem. I have load of charts that are dynamic (mostly single series) and I'm trying to finish my code that will adjust the Y-axis and major unit so that the data will maximize the chart area as best as possible and have clean gridlines of approx. 5 per chart (including the zero line if both + & - values) I have been able to read/write the chart's max/min values as well as set the scaling, but my problem lies in the fact that I can't seem to adjust these values and then set a MajorUnit so that the gridlines all appear nice and neat , especially at the Max Scale and around 0 (if both pos and neg). For example, my code will round Max/Min values up by the nearest 5,10,20 etc depending on the max level in the chart, so in my example a series with a max value of 29.1, the max scale is set at 40 (nearest 10, plus 10). THe series min value is -42.3 and the Min scale gets set to -50. In order to have around 5 gridlines the majorunit was set at 20. My current non-working method has been to take the following: MajUnit = (Abs(Newmax) + Abs(Newmin)) / 5 which I have tried to round to a more suitable number. However, the chart in this example shows major units at -50, -30, -10, 10, 30 and then there is space above the y-axis above the last gridline. A more suitable scaling would really be min = -50, max = 50 and major unit = 25. Not totally maxing out the space, but close enough. There are so many charts that fine tuning this by hand is a nightmare. I just can't get the code to set a max/min scale that will have the clean grids I'm looking for. I know this is too long, but if anyone out there has any guidance I would greatly appreciate it. Regards, jbl |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Chart MajorUnit for Clean lookng Chart
Good morning jbl,
to adjust the y-axis within a chart you can use the following code: With Sheets("xyz").ChartObjects("Chart 1").Axes(xlValue) .MinimumScale = minvalue .MaximumScale = maxvalue .MinorUnit = minunit .MajorUnit = majunit .Crosses = xlCustom .CrossesAt = crosspoint End With all variables used above need to be defined before... to get the integer part of a multiplication or division use Int((Newmax + Newmin) / 5) Hope that helps a bit. Best Markus -----Original Message----- Hi, I'm hoping a chart guru can help me solve this pesky problem. I have load of charts that are dynamic (mostly single series) and I'm trying to finish my code that will adjust the Y-axis and major unit so that the data will maximize the chart area as best as possible and have clean gridlines of approx. 5 per chart (including the zero line if both + & - values) I have been able to read/write the chart's max/min values as well as set the scaling, but my problem lies in the fact that I can't seem to adjust these values and then set a MajorUnit so that the gridlines all appear nice and neat , especially at the Max Scale and around 0 (if both pos and neg). For example, my code will round Max/Min values up by the nearest 5,10,20 etc depending on the max level in the chart, so in my example a series with a max value of 29.1, the max scale is set at 40 (nearest 10, plus 10). THe series min value is -42.3 and the Min scale gets set to -50. In order to have around 5 gridlines the majorunit was set at 20. My current non-working method has been to take the following: MajUnit = (Abs(Newmax) + Abs(Newmin)) / 5 which I have tried to round to a more suitable number. However, the chart in this example shows major units at -50, -30, -10, 10, 30 and then there is space above the y-axis above the last gridline. A more suitable scaling would really be min = -50, max = 50 and major unit = 25. Not totally maxing out the space, but close enough. There are so many charts that fine tuning this by hand is a nightmare. I just can't get the code to set a max/min scale that will have the clean grids I'm looking for. I know this is too long, but if anyone out there has any guidance I would greatly appreciate it. Regards, jbl . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart setting scale | Charts and Charting in Excel | |||
Setting a Goal in a Chart | Charts and Charting in Excel | |||
setting the radius of a pie chart | Charts and Charting in Excel | |||
Setting the range in a chart using VB | Excel Programming | |||
vc++ automation: opening chart as chart window and setting scale | Excel Programming |