Setting Chart Min, Max and other scaling

[JbL]

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

[David]

Hi,
Look at the data and try something like this:

With ActiveChart.Axes(xlValue)
.MinimumScale = (RoundedUpMin - 1)
.MaximumScale = (RoundedDownMax + 1)
End With
Hope this helps you.

"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

[JbL]

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

[Robin Hammond]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[JbL]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>