Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Chart Min, Max and other scaling
David,
Thanks for your replay. Setting the values is the easier part. I was having a hard time extracting the source information to then know what the min and max are currently within the series. I was able to get this, now all I have to do is figure out what the buffer should be between the series max/min and the chart max/min so that the charts look clean. Thanks again. jbl "JbL" wrote: Hi, I am trying to write a code that will programitically alter a charts scaling (right now assuming only 1 data series per chart) for the min, max, major unit, etc that is based on the values in the chart. I have been able to change these specs based on values I provide by looking at the chart, but I am hoping to create a code that I will basically be able to make the min scale X% below the lowest value in the chart series, the max X% above the highest value and the units some portion of the range inbetween. I'm using a dynamic chart with a named range. Do I need to have the code extract that name from the chart series and then look though those cells in data series? Or is there a simple way to pull that info from the chart itself? Thank you for any assistance. Regards, jbl |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Chart Min, Max and other scaling
JBL,
Here's a small extract from a similar routine. There a lot of other calls involved before my routine gets to this point so I'm not going to reproduce the whole thing, but I would think that you can use the logic to figure out how to implement it in your code. 'dMaxVal is the highest value for any series on the axis 'dMinVal is the lowest value for any series on the axis dSpread = Abs(dMaxVal - dMinVal) 'get number format for axis strOrigFormat = .Axes(xlValue, nAxisCounter).TickLabels.NumberFormat If InStr(strOrigFormat, "%") 0 Then Select Case dSpread Case Is <= 0.0003 strNumFormat = "#,##0.000%" Case Is 0.0003, Is <= 0.005 strNumFormat = "#,##0.00%" Case Is 0.005, Is <= 0.06 strNumFormat = "#,##0.0%" Case Is 0.06 strNumFormat = "#,##0%" End Select Else Select Case dSpread Case Is < 0.03 strNumFormat = "#,##0.000" Case 0.03 To 0.5 strNumFormat = "#,##0.00" Case 0.5000001 To 6 strNumFormat = "#,##0.0" Case Is 6 strNumFormat = "#,##0" End Select End If If InStr(strOrigFormat, "$") 0 Then _ strNumFormat = "$" & strNumFormat dMinVal = dMinVal - (dSpread / 10) 'set min val for axis If dMinVal < 0 Then dMinVal = Application.RoundUp( _ dMinVal, 2 - Len(Application.RoundDown(dMinVal, 0))) Else dMinVal = Application.RoundDown( _ dMinVal, 2 - Len(Application.RoundDown(dMinVal, 0))) End If .Axes(xlValue, nAxisCounter).MinimumScale = dMinVal .Axes(xlValue, nAxisCounter).TickLabels.NumberFormat = strNumFormat HTH, Robin Hammond www.enhanceddatasystems.com "JbL" wrote in message ... David, Thanks for your replay. Setting the values is the easier part. I was having a hard time extracting the source information to then know what the min and max are currently within the series. I was able to get this, now all I have to do is figure out what the buffer should be between the series max/min and the chart max/min so that the charts look clean. Thanks again. jbl "JbL" wrote: Hi, I am trying to write a code that will programitically alter a charts scaling (right now assuming only 1 data series per chart) for the min, max, major unit, etc that is based on the values in the chart. I have been able to change these specs based on values I provide by looking at the chart, but I am hoping to create a code that I will basically be able to make the min scale X% below the lowest value in the chart series, the max X% above the highest value and the units some portion of the range inbetween. I'm using a dynamic chart with a named range. Do I need to have the code extract that name from the chart series and then look though those cells in data series? Or is there a simple way to pull that info from the chart itself? Thank you for any assistance. Regards, jbl |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Chart Min, Max and other scaling
Robin,
Thanks for the code. I'll be trying to wrap this up today and this looks very helpful. Much appreciated. jbl "Robin Hammond" wrote: JBL, Here's a small extract from a similar routine. There a lot of other calls involved before my routine gets to this point so I'm not going to reproduce the whole thing, but I would think that you can use the logic to figure out how to implement it in your code. 'dMaxVal is the highest value for any series on the axis 'dMinVal is the lowest value for any series on the axis dSpread = Abs(dMaxVal - dMinVal) 'get number format for axis strOrigFormat = .Axes(xlValue, nAxisCounter).TickLabels.NumberFormat If InStr(strOrigFormat, "%") 0 Then Select Case dSpread Case Is <= 0.0003 strNumFormat = "#,##0.000%" Case Is 0.0003, Is <= 0.005 strNumFormat = "#,##0.00%" Case Is 0.005, Is <= 0.06 strNumFormat = "#,##0.0%" Case Is 0.06 strNumFormat = "#,##0%" End Select Else Select Case dSpread Case Is < 0.03 strNumFormat = "#,##0.000" Case 0.03 To 0.5 strNumFormat = "#,##0.00" Case 0.5000001 To 6 strNumFormat = "#,##0.0" Case Is 6 strNumFormat = "#,##0" End Select End If If InStr(strOrigFormat, "$") 0 Then _ strNumFormat = "$" & strNumFormat dMinVal = dMinVal - (dSpread / 10) 'set min val for axis If dMinVal < 0 Then dMinVal = Application.RoundUp( _ dMinVal, 2 - Len(Application.RoundDown(dMinVal, 0))) Else dMinVal = Application.RoundDown( _ dMinVal, 2 - Len(Application.RoundDown(dMinVal, 0))) End If .Axes(xlValue, nAxisCounter).MinimumScale = dMinVal .Axes(xlValue, nAxisCounter).TickLabels.NumberFormat = strNumFormat HTH, Robin Hammond www.enhanceddatasystems.com "JbL" wrote in message ... David, Thanks for your replay. Setting the values is the easier part. I was having a hard time extracting the source information to then know what the min and max are currently within the series. I was able to get this, now all I have to do is figure out what the buffer should be between the series max/min and the chart max/min so that the charts look clean. Thanks again. jbl "JbL" wrote: Hi, I am trying to write a code that will programitically alter a charts scaling (right now assuming only 1 data series per chart) for the min, max, major unit, etc that is based on the values in the chart. I have been able to change these specs based on values I provide by looking at the chart, but I am hoping to create a code that I will basically be able to make the min scale X% below the lowest value in the chart series, the max X% above the highest value and the units some portion of the range inbetween. I'm using a dynamic chart with a named range. Do I need to have the code extract that name from the chart series and then look though those cells in data series? Or is there a simple way to pull that info from the chart itself? Thank you for any assistance. Regards, jbl |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Chart Scaling | Charts and Charting in Excel | |||
Scaling within a chart | Charts and Charting in Excel | |||
2 Axis chart and scaling | Charts and Charting in Excel | |||
value of chart scaling | Excel Programming | |||
Chart Scaling | Excel Programming |