If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
automatic scale a chart
Hey,
I automatically want the range of a chart to be adjusted to the max and min values of Series1 (only one series, not all of them). I adapted the ms template (http://support.microsoft.com/?kbid=213644) to work for both x and y axis, but it still draws the max and min from ALL series. VBA doesn't like For Each X In .SeriesCollection(1) And if I specify: With ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1) I'm running into problems in the For Each X In ... line Thanks for your help, Claus Here's the code: Private Sub SetScale_Click() 'Dim Xs Dim ValuesArray(), SeriesValues As Variant Dim Ctr As Integer, TotCtr As Integer 'Dim Ys Dim ValuesArrayY(), SeriesValuesY As Variant Dim CtrY As Integer, TotCtrY As Integer ' Uses the first chart on the active worksheet. With ActiveSheet.ChartObjects(1).Chart ' Note: Instead of the preceding line, you could use this line: ' With ActiveChart ' if you wanted to be able to run this macro on a chart sheet. ' Loops through all of the Series and retrieves the values ' and places them into an array named ValuesArray. For Each X In .SeriesCollection SeriesValues = X.Values ReDim Preserve ValuesArray(1 To TotCtr + Ubound(SeriesValues)) For Ctr = 1 To UBound(SeriesValues) ValuesArray(Ctr + TotCtr) = SeriesValues(Ctr) Next TotCtr = TotCtr + UBound(SeriesValues) Next For Each Y In .SeriesCollection SeriesValuesY = Y.XValues ReDim Preserve ValuesArrayY(1 To TotCtrY + UBound(SeriesValuesY)) For CtrY = 1 To UBound(SeriesValuesY) ValuesArrayY(CtrY + TotCtrY) = SeriesValuesY(CtrY) Next TotCtrY = TotCtrY + UBound(SeriesValuesY) Next ' Reset the minimum and maximum scale to the minimum and ' maximum values in the ValuesArray. .Axes(xlValue).MinimumScaleIsAuto = True .Axes(xlValue).MaximumScaleIsAuto = True .Axes(xlValue).MinimumScale = Application.Min(ValuesArray) .Axes(xlValue).MaximumScale = Application.Max(ValuesArray) .Axes(xlCategory).MinimumScaleIsAuto = True .Axes(xlCategory).MaximumScaleIsAuto = True .Axes(xlCategory).MinimumScale = Application.Min(ValuesArrayY) .Axes(xlCategory).MaximumScale = Application.Max(ValuesArrayY) End With End Sub |
#2
|
|||
|
|||
automatic scale a chart
No offense, but that's some ugly code. I mean, who uses a variable called
SeriesValuesY to hold X values?? SeriesValuesY = Y.XValues This works on the first chart, based on the first series: Private Sub SetScale_Click() Dim Srs As Series Dim SeriesValues As Variant Dim SeriesXValues As Variant ' Uses the first chart on the active worksheet. With ActiveSheet.ChartObjects(1).Chart Set Srs =.SeriesCollection(1) SeriesValues = Srs.Values SeriesXValues = Srs.XValues .Axes(xlValue).MinimumScale = Application.Min(SeriesValues) .Axes(xlValue).MaximumScale = Application.Max(SeriesValues) .Axes(xlCategory).MinimumScale = Application.Min(SeriesXValues) .Axes(xlCategory).MaximumScale = Application.Max(SeriesXValues) End With End Sub To work on the active chart (a selected chart in a worksheet, not just a chart sheet as the code comment helpfully but inaccurately says), replace this line: With ActiveSheet.ChartObjects(1).Chart With this line: With ActiveChart - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Custom Solutions - http://PeltierTech.com/ 2006 Excel User Conference, 19-21 April, Atlantic City, NJ http://peltiertech.com/Excel/ExcelUserConf06.html _______ "Claus Haslauer" wrote in message ... Hey, I automatically want the range of a chart to be adjusted to the max and min values of Series1 (only one series, not all of them). I adapted the ms template (http://support.microsoft.com/?kbid=213644) to work for both x and y axis, but it still draws the max and min from ALL series. VBA doesn't like For Each X In .SeriesCollection(1) And if I specify: With ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1) I'm running into problems in the For Each X In ... line Thanks for your help, Claus Here's the code: Private Sub SetScale_Click() 'Dim Xs Dim ValuesArray(), SeriesValues As Variant Dim Ctr As Integer, TotCtr As Integer 'Dim Ys Dim ValuesArrayY(), SeriesValuesY As Variant Dim CtrY As Integer, TotCtrY As Integer ' Uses the first chart on the active worksheet. With ActiveSheet.ChartObjects(1).Chart ' Note: Instead of the preceding line, you could use this line: ' With ActiveChart ' if you wanted to be able to run this macro on a chart sheet. ' Loops through all of the Series and retrieves the values ' and places them into an array named ValuesArray. For Each X In .SeriesCollection SeriesValues = X.Values ReDim Preserve ValuesArray(1 To TotCtr + Ubound(SeriesValues)) For Ctr = 1 To UBound(SeriesValues) ValuesArray(Ctr + TotCtr) = SeriesValues(Ctr) Next TotCtr = TotCtr + UBound(SeriesValues) Next For Each Y In .SeriesCollection SeriesValuesY = Y.XValues ReDim Preserve ValuesArrayY(1 To TotCtrY + UBound(SeriesValuesY)) For CtrY = 1 To UBound(SeriesValuesY) ValuesArrayY(CtrY + TotCtrY) = SeriesValuesY(CtrY) Next TotCtrY = TotCtrY + UBound(SeriesValuesY) Next ' Reset the minimum and maximum scale to the minimum and ' maximum values in the ValuesArray. .Axes(xlValue).MinimumScaleIsAuto = True .Axes(xlValue).MaximumScaleIsAuto = True .Axes(xlValue).MinimumScale = Application.Min(ValuesArray) .Axes(xlValue).MaximumScale = Application.Max(ValuesArray) .Axes(xlCategory).MinimumScaleIsAuto = True .Axes(xlCategory).MaximumScaleIsAuto = True .Axes(xlCategory).MinimumScale = Application.Min(ValuesArrayY) .Axes(xlCategory).MaximumScale = Application.Max(ValuesArrayY) End With End Sub |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
mix chart types | Ken Murray | Powerpoint | 7 | April 28th, 2006 02:50 AM |
adjusting chart axis scale | neeraj | General Discussion | 0 | December 22nd, 2005 08:01 PM |
pasting chart shows chart area, not the chart | Pirjo | Powerpoint | 3 | October 13th, 2005 04:31 AM |
can you create a break in the chart scale | duncanw | Charts and Charting | 1 | September 1st, 2005 10:51 AM |
Multiple filter query of form | truepantera | Using Forms | 6 | August 4th, 2005 08:26 AM |