Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
x-axis scaling | Excel Discussion (Misc queries) | |||
x-axis scaling | Charts and Charting in Excel | |||
Scaling axis | Charts and Charting in Excel | |||
axis scaling | Charts and Charting in Excel | |||
Auto Scaling of Y-axis | Charts and Charting in Excel |