One Graph: Two Dynamic Ranges: Two Axis: Two chart types 1

[Welshbird]

I'm stuck on this one. I have a chart which has always been working rather well, thank you very much.

It is based on a dynamic named range as the series number can vary from 1 to 3. I have this located in a sheet and this is always a column chart.

I now have a need to add an extra series (whos values may change) which is a named range but need it to be a line chart on the secondary axis.

I previously just refreshed the graph with the following:

ActiveSheet.ChartObjects("crtHeliRadValue").Chart.SetSourceData Source:=Sheets("HelicopterData").Range("Heli_Rad_Value")

But if I extend the range for the extra series I randomly get series in either lines or columns and on either axis.

Google has not been my friend so far in this so I'd really appreciate some assistance.

Ta!

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[N1GHTEYES]

Fee, can you clarify something? Are you CREATING the chart in code, and, if so, do you have to? Or are you just changing the source data?

If the latter, are you changing it from one named range to another, or is it always the same range, but the range itself is dynamic?

I just did a quick test where I created two dynamic ranges, set one of them to a column series and the other to a line series on the secondary axis, and then changed the size of the series dynamically. Both series behaved themselves perfectly.

All of the above was without any need to resort to code - it was done purely in the spreadsheet.

Tony

 

[N1GHTEYES]

Here is a macro I recorded when doing what I described above.

On the worksheet I have:
B1 = a number indicating the desired size of the X series
B2 = a number indicating the desired size of the Y series

A6:A20 = data for the x series (dependent on size selected by B1)
B6:B20 = data for the x series (dependent on size selected by B1)

That's it. Do not start with any names or charts created.

I then did the following as recoded by this macro:

Sub feestuff()
'
' feestuff Macro
' Macro recorded 02/09/2011 

'
    Range("A5:A20").Select
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A5:A20"), PlotBy _
        :=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    Windows("Book1").Activate
    ActiveWorkbook.Names.Add Name:="x", RefersToR1C1:= _
        "=OFFSET(Sheet1!R6C1,0,0,Sheet1!R1C2,1)"
    ActiveWorkbook.Names.Add Name:="y", RefersToR1C1:= _
        "=OFFSET(Sheet1!R6C2,0,0,Sheet1!R2C2,1)"
    ActiveWorkbook.Names.Add Name:="y", RefersToR1C1:= _
        "=OFFSET(Sheet1!R6C2,0,0,Sheet1!R2C2,1)"
    Range("B5:B20").Select
    Selection.Copy
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns, SeriesLabels:=True, _
        CategoryLabels:=False, Replace:=False, NewSeries:=True
    Application.CutCopyMode = False
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).AxisGroup = 2
    ActiveChart.SeriesCollection(2).ChartType = xlLineMarkers
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Formula = "=SERIES(Sheet1!R5C1,,Book1!x,1)"
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).Formula = "=SERIES(Sheet1!R5C2,,Book1!y,2)"
End Sub

Does that help?

Tony

 

[Welshbird]

It might well... I tried recording and have this so far:

    With ActiveSheet.ChartObjects("crtHeliRadValue").Chart
        .SetSourceData Source:=Sheets("HelicopterData").Range("Heli_Rad_Value")
        .ChartType = xlColumnClustered
        .SeriesCollection.NewSeries
        .SeriesCollection(2).Name = "=""Effort"""
        .SeriesCollection(2).XValues = "=HelicopterData!$D$194:$D$228"
        .SeriesCollection.Values = "=HelicopterData!$I$194:$I$228"
        .SeriesCollection.ChartType = xlLine
        .SeriesCollection.Select
        .SeriesCollection.AxisGroup = 2
    End With

But this way in my legend I have a spurious "Series 3" that ought to have a name from the original dynamic named range (Heli_Rad_Value in this case).

Cup-o-tea I think before I try looking again. Thanks Tony - will try your method too..

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

Tony - the chart is already created - The issue I had was not that the size of the series changed, but that the number of series changed - it could be just one (product) or three (products).

I guerss the end aim is simply to add the new series to the graph as a specific chart type and on a secondary axis. Which is much easier sounding than my original post!

Right, off to step through again!

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

Ra! I wasn't counting the number of series, so how could I name an unkinwn numbered series!

    With ActiveSheet.ChartObjects("crtHeliRadValue").Chart
        .SetSourceData Source:=Sheets("HelicopterData").Range("Heli_Rad_Value")
        .ChartType = xlColumnClustered
        .SeriesCollection.NewSeries
        Col = .SeriesCollection.Count
        .SeriesCollection(Col).Name = "=HelicopterData!$B$193"
        .SeriesCollection("Effort").Values = "=HelicopterData!$I$194:$I$228"
        .SeriesCollection("Effort").XValues = "=HelicopterData!$D$194:$D$228"
        .SeriesCollection("Effort").AxisGroup = 2
        .SeriesCollection("Effort").ChartType = xlLine
    End With

Jsut in case it helps someone else.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[N1GHTEYES]

OK, I added to the sheet:

B3 = number defining required size of Z series

C6:C20 = Z data

Then the following code works:

Sub addzmacro()
Dim label As Range, data As Range
label = ActiveSheet.Range("C5")
data = ActiveSheet.Range(z)
Set chrt = ActiveChart
addnewseries chrt, label, data
End Sub

Sub addnewseries(chrt As ChartObject, label As Range, data As Range)
Dim pos As Long
source.Copy
With chrt
    .SeriesCollection.Add source:=data, Rowcol:=xlColumns, SeriesLabels:=True, _
        CategoryLabels:=False, Replace:=False, NewSeries:=True
    pos = .SeriesCollection.Count
    .SeriesCollection(pos).Formula = "=SERIES(" & label.Address & ",," & data.Worksheet.name & "!" & data.name & "," & Str(pos) & ")"
End With
End Sub

The first sub just grabs the required data from the sheet (set up as described) and then calls the second which more generally adds the series as specified by the input params.

When I used this, I got a nice neat chart with a dynamic z series, correctly labelled.

Is that any better?

If this does not do what you need soon, Skip will be here and show us how it should be done properly!


Tony

 

[N1GHTEYES]

Aaaarrgghh - brain completely in neutral.

I posted the wrong code.

This, however, does work:

Sub addzmacro()
Dim label As Range, data As Range
Set label = ActiveSheet.Range("C5")
Set data = ActiveSheet.Range("z")
Set chrt = ActiveChart
addnewseries chrt, label, data, "z"
End Sub


Sub addnewseries(chrt As Variant, label As Range, data As Range, dataname As String)
Dim pos As Long
data.Copy
With chrt
    .SeriesCollection.Paste Rowcol:=xlColumns, SeriesLabels:=True, _
        CategoryLabels:=False, Replace:=False, NewSeries:=True
    pos = .SeriesCollection.Count
    Dim serform As String
    serform = "=SERIES("
    serform = serform & label.Worksheet.name & "!" & label.Address & ",,"
    serform = serform & ThisWorkbook.name & "!"
    serform = serform & dataname & "," & Trim(Str(pos)) & ")"
    .SeriesCollection(pos).Formula = serform
End With
End Sub

Sorry,

Tony

 

[N1GHTEYES]

Minor mod:

When I ran the above, I had not saved the workbook and it worked fine.

Having saved it as "Example for Fee.xls" I ran it again and it fell over.

It turns out that unsaved, the series name is Book1 whereas afterwards it is 'Example for Fee.xls'

- note the extra '' s.

So finally:

Sub addzmacro()
Dim label As Range, data As Range
Set label = ActiveSheet.Range("C5")
Set data = ActiveSheet.Range("z")
Set chrt = ActiveChart
addnewseries chrt, label, data, "z"
End Sub


Sub addnewseries(chrt As Variant, label As Range, data As Range, dataname As String)
Dim pos As Long
data.Copy
With chrt
    .SeriesCollection.Paste Rowcol:=xlColumns, SeriesLabels:=True, _
        CategoryLabels:=False, Replace:=False, NewSeries:=True
    pos = .SeriesCollection.Count
    Dim serform As String
    serform = "=SERIES("
    serform = serform & label.Worksheet.name & "!" & label.Address & ",,"
    serform = serform & "'" & ThisWorkbook.name & "'" & "!"
    serform = serform & dataname & "," & Trim(Str(pos)) & ")"
    .SeriesCollection(pos).Formula = serform
End With
End Sub

BTW I didn't say it was pretty...

Tony

 

[Welshbird]

Well, I have it working and am very happy about this!

One extra annoyance though, is in appearance:

Link to Chart
So, I want the zero on the second axis to cross at the same point as the zero from the first axis.

How on earth???

I tried using this bit of thang here, but that makes both minimum's Zero, and clearly that doesn't help me.

In these charts the columns are growth in percentage, so can be negative, but the line is 'Sales Effort' which can't be.

But, I think its offputting and difficult to interpret as it implies negative effort.

Ta chaps,

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[Welshbird]

Tony - Apologies - I almost forgot a twinkly for all of your help thus far!

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[N1GHTEYES]

So what code worked in the end?

As far as the axes goes, if you want to change the Y axes, you can use:

ActiveChart.Axes(xlValue, xlSecondary).MinimumScale and .MaximumScale to set the 2nd axis and

ActiveChart.Axes(xlValue).MinimumScale, and .MaximumScale for the main axis.

To get the zeros to lie at the same point, you will have to manually ensure that the proportion of the distance from the min to the zero position is the same for both axes. In other words, if the main goes from -100 to +200 then the 2nd axis needs to go from -1 to +2, or -2000 to +4000 or whatever.

I find Excel's idea of scaling is not very good anyway. For example, If I have x going from 2000 to 2460 and y from 530 to 760, Excel by default sets both the axes to start at 0. This looks pretty dumb and wastes most of the chart.


To get a sensible scale which uses the range more fully, I use the following formulae in the spreadsheet to derive better limits then use the worksheet_change event to redraw the charts using these limits:

y range = ymax-ymin
y unit nom =10^(INT(LOG(y_range)))
y unit actual =IF(y_range/y_unit_nom>5,y_unit_nom,IF(y_range/y_unit_nom>2,y_unit_nom/2,y_unit_nom/5))
ystart =INT(ymin/y_unit_actual)*y_unit_actual
yend =CEILING(ymax,y_unit_actual)

where ystart and yend should be set as the min and max values on the y axis.

You could put these formulae in the sheet (as I've done) or incorporate them directly in code when you redraw your chart.

Is any of that any use?

Tony

 

[Welshbird]

Tony - that sounds just the ticket!

I'm going to go and try that right away. I'll probably use the formula in the worksheet, as I try and do as much of the calculations as possible there, and then just use code to populate cell ranges.

I really appreciate your help here - I'll be back to let you kwow how I get on.

Oh, the code I ended up with is as follows:

    Set mycharts = ActiveSheet.ChartObjects
    For Each mychart In mycharts
    With mychart.Chart
            .SeriesCollection.NewSeries
      col = .SeriesCollection.Count
            .SeriesCollection(col).Name = "=GeogData!D19"
            .SeriesCollection("Effort").Values = "=GeogData!$F$20:$K$20"
            .SeriesCollection("Effort").XValues = "=GeogData!$F$19:$K$19"
            .SeriesCollection("Effort").AxisGroup = 2
            .SeriesCollection("Effort").ChartType = xlLine
    End With
    Next

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[N1GHTEYES]

While you're playing with charts, you might be interested in this thread:

http://www.tek-tips.com/viewthread.cfm?qid=1649209&page=1

It might be handy if you have lots of charts scattered around.

Or this one:

http://www.tek-tips.com/viewthread.cfm?qid=1650411&page=1

If you want to get at the data represented by the chart by clicking on the chart itself.

Tony

 

[Welshbird]

Hi Tony,

I've calculated this far more simply here:

= (2ndMax / 1stMax) / 1stMin.

And all seems very tidy indeed.

Thanks so much for this. I'd give you a second twinkly but I can't!



Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[N1GHTEYES]

I'm always in the market for simpler ways of doing things, but what have you calculated using "= (2ndMax / 1stMax) / 1stMin"?

Is that your formula for the 2nd min to ensure the zeros are coincident? In that case, shouldn't it be "2ndMin= (2ndMax / 1stMax) * 1stMin?

If so, then I think that is what I meant by saying "the proportion of the distance from the min to the zero position is the same for both axes". In the example I gave, I said if your main axis goes from -100 to +200 then you 2nd axis should (for example) go from -1 to +2. Using the above formula:
1st max = 200
1st min = -100
2nd max = 2

then 2nd min = (2/200)*(-100)=-1, which is the value I suggested.

The other formulas I posted were not for doing that. They were for deciding good values to set the main axis limits to to sensibly use the available chart space, as opposed to using Excel's default min and max limits. I probably did not make that very clear though.

Tony

 

[Welshbird]

Oh- It IS * 31stMin.

And yes - I just want to calculate the second minimum so that the line for effort does not go below the zero from the other axis.

And it works. Tidy.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[N1GHTEYES]

To quote a Sirius Cybernetics lift "Glad to be of service"

Now I'm off for a well-deserved break in the "sunny" Lake District!

Tony

 

[Welshbird]

Ooo have a fab time, and eat much Kendall Mint Cake!

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[SkipVought]

here's an example of balancing axes when you have 2 value axes...

Sub AdjustAxes()
    Dim oPri As Axis, oSec As Axis
'[b]this assumes ONE chart on YourSheetObject[/b]    
    With YourSheetObject.ChartObjects(1).Chart
        Set oPri = .Axes(xlValue, xlPrimary)
        Set oSec = .Axes(xlValue, xlSecondary)
'[b]first set all to auto scale[/b]        
        With oPri
            .MaximumScaleIsAuto = True
            .MinimumScaleIsAuto = True
        End With
    
        With oSec
            .MaximumScaleIsAuto = True
            .MinimumScaleIsAuto = True
        End With
'[b]then balance the max & min scale values[/b]        
        Select Case oPri.MaximumScale - oSec.MaximumScale
            Case Is < 0: oPri.MaximumScale = oSec.MaximumScale
            Case Is > 0: oSec.MaximumScale = oPri.MaximumScale
        End Select
'[b]and remove ALL auto scaling[/b]        
        oPri.MaximumScaleIsAuto = False
        oSec.MaximumScaleIsAuto = False
        
        Select Case oPri.MinimumScale - oSec.MinimumScale
            Case Is < 0: oSec.MinimumScale = oPri.MinimumScale
            Case Is > 0: oPri.MinimumScale = oSec.MinimumScale
        End Select
        
        oPri.MinimumScaleIsAuto = False
        oSec.MinimumScaleIsAuto = False
        
    End With
    Set oSec = Nothing
    Set oPri = Nothing
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]