none
I want to use VBA to change the number of points plotted in a chart RRS feed

  • Question

  • I am using Office 2016 with Windows 10 Pro.  I have an Excel spreadsheet with a sheet named "Symbol_Analysis" with a chart named "Basic_Symbol_Analysis".  it plots data from another sheet (in the same workbook) named "Graph_Data".  The chart works perfectly.  I use VBA to copy data from other parts of the spreadsheet into the appropriate ranges of Chart_Data and it appears correctly plotted in the Basic_Symbol_Analysis chart.

    I want to change the number of points that are plotted dynamically and adjust the chart with VBA to accommodate number of points.

    I have had great difficulty in specifying the object that holds the formula for the series in the chart.  I have used several examples from various sources that did not work.  I finally thought I had broken the code with the following two statements (that should be equivalent).

        MsgBox (ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Formula) 
        MsgBox (ActiveSheet.ChartObjects("Basic_Symbol_Chart").Chart.SeriesCollection(1).Formula)

    Both of these statements work, meaning that they return the Series statement that shows up in the formula bar when I select (with the mouse) the first data series in the chart.  (In both cases above, ActiveSheet is the sheet Symbol_Analysis.

    So, I figured that I could now set a new formula for the series with either of the following statements:

        ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Formula = "=SERIES(Graph_Data!$D$12," & "Graph_Data!" & XCells & ", Graph_Data!" & YCells & ",1)"  

        ActiveSheet.ChartObjects("Basic_Symbol_Chart").Chart.SeriesCollection(1).Formula =  "=SERIES(Graph_Data!$D$12," & "Graph_Data!" & XCells & ", Graph_Data!" & YCells & ",1)"  

    Neither of these work.  The first one returns an error that says there is an  "Application-defined or Object-defined error".

    The second one says it can't find the named chart (????)

    The code  "=SERIES(Graph_Data!$D$12," & "Graph_Data!" & XCells & ", Graph_Data!" & YCells & ",1)"  returns an exact replica of the Series statement that I get by selecting (with the mouse) the series in the chart.  I have tried copying that series statement from the spreadsheet and inserting in the VBA code above.  The results are the same.

    I have tried a gazillion things including using the macro recorder to see the resulting code from changing the series lengths with the Excel user interface.  That code refers to a "FullSeriesCollection" instead of "SeriesCollection".  I  looked this up, but the documentation is sparse and does not illuminate anything about the difference between the two. Furthermore the macro recorder code used a ".Points()". method that had no reference to a data range. I tried these things to no avail.

    So, I need help.  Any relevant information will be appreciated.

    Thanks

    Thursday, April 7, 2016 10:45 PM

Answers

All replies

  • >>>So, I figured that I could now set a new formula for the series with either of the following statements:

    ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1).Formula = "=SERIES(Graph_Data!$D$12," & "Graph_Data!" & XCells & ", Graph_Data!" & YCells & ",1)"  

    ActiveSheet.ChartObjects("Basic_Symbol_Chart").Chart.SeriesCollection(1).Formula =  "=SERIES(Graph_Data!$D$12," & "Graph_Data!" & XCells & ", Graph_Data!" & YCells & ",1)"  

    Neither of these work.  The first one returns an error that says there is an  "Application-defined or Object-defined error".

    The second one says it can't find the named chart (????)<<<

    According to your description, I have made a sample to try to reproduce your issue, unfortunately, I can't. Based on these errors, I suggest that you could active "Symbol_Analysis" worksheet firstly to check this issue can be reproduced.
    ActiveWorkbook.Sheets("Symbol_Analysis").Select
    In addition could you unload your Excel file on OneDrive? that will help us reproduced and resolve your issue.

    Thanks for your understanding.
    Friday, April 8, 2016 1:53 AM
  • Thank you for your reply.  After reading your comments, I realized, to my chagrin, that I had not tried to create an all new (simplified) workbook to examine this problem.  I created an all new workbook, copied the Chart_Data sheet to it, and used Macro Record to create a new chart.

    The VBA code produced looked like this:

        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.FullSeriesCollection(1).Select
        ActiveChart.FullSeriesCollection(1).Values = "=Graph_Data!$D$52:$D$221"
        ActiveChart.FullSeriesCollection(1).XValues = "=Graph_Data!$B$52:$C$221"

    I modified this code for my full spreadsheet, and it now works.  So, thank you very much.

    Two more things, though.

    1. You suggested that I explicitly set ActiveSheet before trying to set the series data.  Since I did explicitly set ActiveSheet at the beginning of the macro, I initially did not think I needed to do it again.  However, when I first tried the code above it did not work.  The code between my setting of ActiveSheet and trying to set the Series data addressed other sheets, so the ActiveSheet must have been changed in those operation.  I suppose it is even possible that the original code I sent you would have worked, too, if I had set ActiveSheet again.

    2. The code that was produced using Record Macro uses the "FullSeriesCollection" object.  The documentation on this object in msdn is sparse and confusing, to say the least, and it does not show properties or methods that would lead me to the code above.  I respectfully suggest that it bears improving.

    Thank you again.  I was going quite batty over this.

    • Proposed as answer by David_JunFeng Friday, April 15, 2016 2:21 PM
    Sunday, April 10, 2016 7:58 PM
  • Hi, StillActive

    Congratulation, thanks for sharing your solution.

    In addition if you have any feedbacks for Excel, please feel free to submit them to User Voice:

    https://excel.uservoice.com/

    Thanks for your understanding.
    • Marked as answer by StillActive Friday, April 15, 2016 5:22 PM
    Monday, April 11, 2016 1:30 AM