Link to home
Create AccountLog in
Avatar of Arundo
Arundo

asked on

Change chart range by VBA

Hi,

I am struggled with a way to update my charts. This is how I would like it is working.
Chart data is located in a row, so evry day new column with data is added.  I have multiple charts each of them has several series. I would like a macro to extract formula of series collection, which is, for example SERIES(Sheet1!$A$11:$C$11,Sheet1!$E$1:$EN$1,Sheet1!$E$11:$EN$11,1) and replace column EN (can be also a different column) by last column in the row with a help of End(xlToRight)). So that formula becomes (Sheet1!$A$11:$C$11,Sheet1!$E$1:$EZ$1,Sheet1!$E$11:$EZ$11,1) .

Sub Charts()
Dim m As ChartObject
Dim n As ChartObject
For m =1 to 6
For n = 1 To Sheet1.ChartObjects(m).Chart.SeriesCollection.Count
Sheet1.ChartObjects(m).Chart.SeriesCollection(n).Formula = ....
Next n
Next m
End Sub

!!! Please do not offer dynamical named ranges based on offset function, because I have several sheets with multiple graphs each consisting of various series. Time killing to put in all named series.
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

I guess this question has same objective as the other one posted earlier on the last column ?
gowflow
Could you post some sample data it would facilitate decoding
gowflow
Arundo,

!!! Please do not offer dynamical named ranges based on offset function, because I have several sheets with multiple graphs each consisting of various series. Time killing to put in all named series.


Why not?  I see nothing in your question that would prevent such an approach from working.  (FWIW, I prefer using INDEX to OFFSET, but that does not change the major point.)  Indeed, such an approach may be preferable to VBA, as that way you need not worry about whether the user has enabled macros.

In any event, please post a sample file that illustrates what you need to do.

Patrick
Avatar of Arundo
Arundo

ASKER

I've requested that this question be deleted for the following reason:

wrong question
You have a weired way of getting Expert's help. You post a question then we give you comments and you proceed to delete the question, I feel this is somehow not very ethical.
I noticed this behavior in a previous similar question of you.
gowflow
Avatar of Arundo

ASKER

This is only because i wanted to include a nota bene not to offer dynamical named ranges as a solution.
Avatar of Arundo

ASKER

i think sample file is not needed. you can just put some datestamps in cells A1:AZ1 and data in cells A2:AZ2. Then create a graph from these. Then put some additional datestamps and data so that last column to have them is BZ, then a macro could automatically adjust SeriesCollection xvalues and values to A1:BZ1 and A2:BZ2. that's it
Avatar of Arundo

ASKER

And note despite I have included a nota bene in this question not to offer dynamical named ranges, I still get them as possible solution.

I am the end user, I do not need to worry whether I have enabled macros or not
Arundo,

In my opinion, the best practice here would be to use dynamic ranges for this.  I understand that that will require some time for you to set up.  However, it would take me time to write and test a VBA procedure to do this, and with respect, I value my time more than I value yours.  (Not trying to be nasty or snarky here, just being honest.)

So, for each chart series:

1) Set up a Name for the labels, notionally LabelRng, referring to:

='Sheet name'!$E$1:INDEX('Sheet name'!$1:$1,1,MATCH("ZZZZZZ",'Sheet name'!$1:$1))

2) Set up another Name for the values, notionally ValueRng, referring to:

=INDEX('Sheet name'!$E:$E,MATCH(10^200,'Sheet name'!$E:$E),1):INDEX('Sheet name'!$1:$1048576,MATCH(10^200,'Sheet name'!$E:$E),MATCH("ZZZZZ",'Sheet name'!$1:$1))

For Excel 2003, you would use this instead:

=INDEX('Sheet name'!$E:$E,MATCH(10^200,'Sheet name'!$E:$E),1):INDEX('Sheet name'!$1:$65536,MATCH(10^200,'Sheet name'!$E:$E),MATCH("ZZZZZ",'Sheet name'!$1:$1))

3) Update each chart to use those defined Names.  When you do that, you will need to prefix the name with a sheet or workbook name:

='Sheet name'!LabelRng

etc

Patrick
If the source data for your charts is in a Table (requires Excel 2007 or later), they will respond automatically as you add new columns.

Here is a macro that will extend your charts as you add data in new columns:
Sub ChartExtender()
'Extends the range for catategories and values for all series and charts on a worksheet. _
    Assumes that data is added with new columns.
Dim m As Long, n As Long
Dim frmla As String, sht1 As String, sht2 As String
Dim rg1 As Range, rg2 As Range
Dim v As Variant, vv As Variant
Dim ser As Series
With ActiveSheet
    For m = 1 To .ChartObjects.Count
        For n = 1 To .ChartObjects(m).Chart.SeriesCollection.Count
            frmla = .ChartObjects(m).Chart.SeriesCollection(n).Formula
            Set v = Nothing
            Set vv = Nothing
            v = Split(frmla, ",")
            
            If n = 1 Then
                vv = Split(v(1), "!")
                sht1 = vv(0)
                If Left(sht1, 1) = "'" Then sht1 = Mid(sht1, 2, Len(sht1) - 2)
                With ActiveWorkbook.Worksheets(Replace(sht1, "''", "'"))
                    Set rg1 = .Range(vv(1))
                    Set rg1 = .Range(rg1.Cells(1, 1), .Cells(rg1.Row, .Columns.Count).End(xlToLeft))
                    .ChartObjects(m).Chart.SeriesCollection(n).XValues = "='" & sht1 & "'!" & rg1.Address
                End With
            End If
        
            Set vv = Nothing
            vv = Split(v(2), "!")
            sht2 = vv(0)
            If Left(sht2, 1) = "'" Then sht2 = Mid(sht2, 2, Len(sht2) - 2)
            With ActiveWorkbook.Worksheets(Replace(sht2, "''", "'"))
                Set rg2 = .Range(vv(1))
                Set rg2 = .Range(rg2.Cells(1, 1), .Cells(rg2.Row, .Columns.Count).End(xlToLeft))
            End With
            .ChartObjects(m).Chart.SeriesCollection(n).Values = "='" & sht2 & "'!" & rg2.Address
        Next n
    Next m
End With
End Sub

Open in new window

The formula for ValueRng in my comment http:#a39040855 above can be simplified, if the data will always be on Row 11.  (My original formula assumed that both the row and the last column had to be dynamic.)

If it will always be on Row 11, then use this instead:

='Sheet name'!$E$11:INDEX('Sheet name'!$11:$11,1,MATCH("ZZZZZZ",'Sheet name'!$1:$1))
Avatar of Arundo

ASKER

Hi byundt,

Thank you for the code. This is awesome and exactly what I was looking for. It is working if chart is on the same sheet where input data is, therefore in my case I have charts on a separate sheet and this is not working, when debugged it highlights this line

.ChartObjects(m).Chart.SeriesCollection(n).XValues = "='" & sht1 & "'!" & rg1.Address
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Arundo

ASKER

Brad,

Works great.

Thanks,
Stan
Avatar of Arundo

ASKER

Excellent!