Scaling Axis via VBA?
 

I'm creating a spreadsheet with many charts.

Each chart may have a different range of values - "Deal Count", for instance,
may vary from 1 to 10 on one chart, but from 1 to 145 on another chart.

I want to set each chart's Axis.MajorUnitScale to an appropriate amount.

I'm figuring something like:
---------------------------------
Count < 10 = 1
Count between 11 and 100 = 10
Count between 101 and 1000 = 100
---------------------------------

And so-on....

Do I have to go to each chart's data range, figure out which column has "Deal
Count" in it, figure out how many rows there are, and then do a Max()?

Or is there some simpler way?

The exact MajorUnitScale isn't a religious issue - I just want it so that the
tick mark labels aren't all scrunched together.
--
PeteCresswell

Scaling Axis via VBA?
 

You don't need to go as far as the data source range. Iterate through the
series and find the min and max values:

Dim srs As Series
Dim xMin As Double
Dim xMax As Double
Dim yMin As Double
Dim yMax As Double

With ActiveChart.SeriesCollection(1)
xMin = WorksheetFunction.Minimum(.XValues)
xMax = WorksheetFunction.Maximum(.XValues)
yMin = WorksheetFunction.Minimum(.Values)
yMax = WorksheetFunction.Maximum(.Values)
End With
For Each srs In ActiveChart.SeriesCollection
With ActiveChart.SeriesCollection(1)
If xMin WorksheetFunction.Minimum(.XValues) Then xMin =
WorksheetFunction.Minimum(.XValues)
If xMax < WorksheetFunction.Maximum(.XValues) Then xMax =
WorksheetFunction.Maximum(.XValues)
If yMin WorksheetFunction.Minimum(.Values) Then yMin =
WorksheetFunction.Minimum(.Values)
If yMax < WorksheetFunction.Maximum(.Values) Then yMax =
WorksheetFunction.Maximum(.Values)
End With
Next

Given these min and max data values, you can use Stephen Bullen's technique
to calculate decent min, max, and major chart scale units:

http://groups.google.com/group/micro...13d5257af6a8c7

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"(PeteCresswell)" wrote in message
...
I'm creating a spreadsheet with many charts.

Each chart may have a different range of values - "Deal Count", for
instance,
may vary from 1 to 10 on one chart, but from 1 to 145 on another chart.

I want to set each chart's Axis.MajorUnitScale to an appropriate amount.

I'm figuring something like:
---------------------------------
Count < 10 = 1
Count between 11 and 100 = 10
Count between 101 and 1000 = 100
---------------------------------

And so-on....

Do I have to go to each chart's data range, figure out which column has
"Deal
Count" in it, figure out how many rows there are, and then do a Max()?

Or is there some simpler way?

The exact MajorUnitScale isn't a religious issue - I just want it so that
the
tick mark labels aren't all scrunched together.
--
PeteCresswell

Scaling Axis via VBA?
 

Per Jon Peltier:
You don't need to go as far as the data source range. Iterate through the
series and find the min and max values:

Thanks.

At the risk of turning into a tarbaby on you...

I'm in MS Access VBA.

To get to ExcelApplication.WorkSheetFunction.Maximum, I need to convert that
series into an array of double.

But I can't figure out how to determine the size of a series.

To wit:
----------------------------------------------------
set mySeries = theSS.ActiveChart.SeriesCollection(2)
?mySeries.Name
Count
?mySeries.Values(1)
3
?mySeries.size
("object doesn't support this property or method)
?mySeries.count
("object doesn't support this property or method)
?uBound(mySeries)
("Expected array")
----------------------------------------------------

Do I have to iterate through the series until the code
traps out with a "Subscript out of range"? ... or is
there a "right" way to do it?
--
PeteCresswell

Scaling Axis via VBA?
 

Pete -

To get the number of points:

?mySeries.Points.Count

or

?UBound(mySeries.Values)

mySeries.Values is already the array of values you need.

But to find the min and max, use what I provided:

xMin = WorksheetFunction.Minimum(mySeries.XValues)
xMax = WorksheetFunction.Maximum(mySeries.XValues)
yMin = WorksheetFunction.Minimum(mySeries.Values)
yMax = WorksheetFunction.Maximum(mySeries.Values)

This will not be appropriate for nonnumeric X axis labels.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"(PeteCresswell)" wrote in message
...
Per Jon Peltier:
You don't need to go as far as the data source range. Iterate through the
series and find the min and max values:

Thanks.

At the risk of turning into a tarbaby on you...

I'm in MS Access VBA.

To get to ExcelApplication.WorkSheetFunction.Maximum, I need to convert
that
series into an array of double.

But I can't figure out how to determine the size of a series.

To wit:
----------------------------------------------------
set mySeries = theSS.ActiveChart.SeriesCollection(2)
?mySeries.Name
Count
?mySeries.Values(1)
3
?mySeries.size
("object doesn't support this property or method)
?mySeries.count
("object doesn't support this property or method)
?uBound(mySeries)
("Expected array")
----------------------------------------------------

Do I have to iterate through the series until the code
traps out with a "Subscript out of range"? ... or is
there a "right" way to do it?
--
PeteCresswell

Scaling Axis via VBA?
 

Per Jon Peltier:
To get the number of points:

?mySeries.Points.Count

Thanks again!

That, plus Stephen Bullen's code got me out of the woods on this one.
--
PeteCresswell