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.
Microsoft Excel

Avatar of undefined
Last Comment
Arundo

8/22/2022 - Mon
gowflow

I guess this question has same objective as the other one posted earlier on the last column ?
gowflow
gowflow

Could you post some sample data it would facilitate decoding
gowflow
Patrick Matthews

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Arundo

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

wrong question
gowflow

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
Arundo

ASKER
This is only because i wanted to include a nota bene not to offer dynamical named ranges as a solution.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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
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
Patrick Matthews

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
byundt

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

Patrick Matthews

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))
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
byundt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Arundo

ASKER
Brad,

Works great.

Thanks,
Stan
Arundo

ASKER
Excellent!