1004 Unable to set the Values property of the Series class

[keirnus]

Hello,

I've been coding in Access VBA to create an Excel Graph and it was good.

Until I got this error:

1004 Unable to set the Values property of the Series class

Please check the code below:

Private Sub TestGraph3()
 
    Dim oXL As Excel.Application    ' Excel application
    Dim oBook As Excel.Workbook     ' Excel workbook
    Dim oSheet As Excel.Worksheet   ' Excel Worksheet
    Dim oChart As Excel.Chart       ' Excel Chart
 
    Dim myRange As String
    Dim xlSourceRange As Excel.Range
 
    Dim iRow As Integer      ' Index variable for the current Row
    Dim iCol As Integer      ' Index variable for the current Row
 
    Const cNumCols = 4       ' Number of points in each Series
    Const cNumRows = 5       ' Number of Series
 
    ReDim aTemp(1 To (cNumRows + 1), 1 To cNumCols)
 
    ' Start Excel and create a new workbook
    Set oXL = CreateObject("Excel.application")
    Set oBook = oXL.Workbooks.Add
 
    Set oSheet = oBook.Worksheets.Item(1)
 
    ' Insert data into Cells for the two Series:
    For iCol = 1 To cNumCols
       aTemp(1, iCol) = "A" & iCol
       aTemp(2, iCol) = 1
       aTemp(3, iCol) = 1 + 2
       aTemp(4, iCol) = iCol
 
       If (iCol * 2) <= cNumCols Then
        aTemp(5, iCol) = iCol * 2
       Else
        aTemp(5, iCol) = cNumCols
       End If
    Next iCol
 
    oSheet.Range("A1").Resize(5, cNumCols).Value = aTemp
 
    oSheet.Name = "MyChart_MD"
 
    myRange = "A1:D" & (cNumRows)
    Set xlSourceRange = oBook.Worksheets(1).Range(myRange)
 
    Set oChart = oXL.Charts.Add
 
    With oChart
        .SetSourceData Source:=xlSourceRange, PlotBy:=xlColumns
        .ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"
 
        .SeriesCollection(1).XValues = oSheet.Range("A1").Resize(1, cNumCols)
        .SeriesCollection(1).Values = oSheet.Range("A2").Resize(1, cNumCols)
        .SeriesCollection(1).Name = "Plan"
        .SeriesCollection(2).Values = oSheet.Range("A3").Resize(1, cNumCols)
        .SeriesCollection(2).Name = "Actual"
        .SeriesCollection(3).Values = oSheet.Range("A4").Resize(1, cNumCols)
        .SeriesCollection(3).Name = "Accu. Plan"
        .SeriesCollection(4).Values = oSheet.Range("A5").Resize(1, cNumCols)
        .SeriesCollection(4).Name = "Accu. Actual"
        .Location Where:=xlLocationAsNewSheet, Name:="chartMD"
 
        .HasTitle = True
        .ChartTitle.Characters.Text = "Chart : MD"
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Accu."
        .Axes(xlCategory, xlSecondary).HasTitle = False
        .Axes(xlValue, xlSecondary).HasTitle = False
 
        .HasLegend = False
        .HasDataTable = True
        .DataTable.ShowLegendKey = True
    End With
 
    ' Make Excel Visible:
    oXL.Visible = True
    oXL.UserControl = True
    ' Unselect the ActiveChart
    oChart.Deselect
    oXL.ActiveWindow.Zoom = 85
 
    ' Protect whole Worksheet
    oSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
 
    ' Clean-up
    Set oXL = Nothing: Set oChart = Nothing: Set oSheet = Nothing: Set oBook = Nothing
 
End Sub
 

If cNumCols = 3, ERROR occurs in this line:

        .SeriesCollection(4).Values = oSheet.Range("A5").Resize(1, cNumCols)
 

If cNumCols = 2, ERROR occurs in this line:

        .SeriesCollection(3).Values = oSheet.Range("A5").Resize(1, cNumCols)
 

cNumCols is supposed to be the number of points in each series.
Not the number of series.
cNumRows is the number of series.

In a situation where there are 4 Series (Actual, Plan, Accu. Actual, Accu. Plan)
how can I display a graph/chart with 3 columns only?

Help will be greatly appreciated.

[NeoPa]

I can't help with this I'm afraid, but I have a couple of suggestions as to how you can make your question more attractive to potential responders.

1. When you have a large amount of code to post, please do the legwork first to remove anything which is not relevant to the problem. Produce the smallest set of code that you can which still exhibits the problems you're suffering from.
   Simply dumping your native code in for someone else to go through won't win you many friends.
2. The [ CODE ] tags you've used add line numbers when they display. When referring to lines in your code it is most easy to do this by the line number.

I would just add that, while point number one is about courtesy, it also has the added benefit of giving you a better understanding of your own code as you process through deciding what to strip. In fact it's fairly common during this process to discover for yourself the problem you're posting to find.

Good luck anyway with your question.

[keirnus]

I can't help with this I'm afraid, but I have a couple of suggestions as to how you can make your question more attractive to potential responders.

1. When you have a large amount of code to post, please do the legwork first to remove anything which is not relevant to the problem. Produce the smallest set of code that you can which still exhibits the problems you're suffering from.
   Simply dumping your native code in for someone else to go through won't win you many friends.
2. The [ CODE ] tags you've used add line numbers when they display. When referring to lines in your code it is most easy to do this by the line number.

I would just add that, while point number one is about courtesy, it also has the added benefit of giving you a better understanding of your own code as you process through deciding what to strip. In fact it's fairly common during this process to discover for yourself the problem you're posting to find.

Good luck anyway with your question.

Thank you NeoPa for the advice.

I posted them all so others could easily test.
I understand it is not their job to test but to give advice.
I will keep that in mind.


...and I'm still stuck in this weirdness of this Chart's Series Class.

[keirnus]

Finally solved the intermittent problem!

Acknowledgment goes to Mr. Jon Peltier.
He's very helpful and yet an expert in this field.

Line and XY charts don't like it when a series starts with no data.

He's advice is to set Chart Type to xlColumnCluster ed which will
set blank data into valid ones. Then, re-insert the original line
after setting all the data.

Let's base from the above code.

The following lines must be changed from this:

        ....
        .SetSourceData Source:=xlSourceRange, PlotBy:=xlColumns
        .ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"
        ....
 

...into this:

        ....
        .ChartType = xlColumnClustered
        .SetSourceData Source:=xlSourceRange
        ....
 

Not only the Chart Type and the sequence of the lines have changed
but the SetSourceData as well.

Note:
Not changing the sequence of lines seems not to fix the problem.
It only takes longer time before the error occurs again.
Don't have explanation on this yet.

Now, after modifying as stated above, let's re-insert the original line:

        ....
        .SeriesCollection(4).Name = "Accu. Actual"
        .ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column" ' <-- this one!
        .Location Where:=xlLocationAsNewSheet, Name:="chartMD"
        ....
 

Eureka!

Intermittent problem gone.

Hope this helps you the way it helped much to me!


keirnus

[NeoPa]

Thank you for the answer Keirnus. That's much appreciated.

My earler point, about stripping down the code, was about setting up a test rig rather than simply cutting out what you post from your code. To be honest, although we'd appreciate that method, we very rarely come across it, so don't worry too much (although I would still maintain it often proves a very real benefit to the poster).

Anyway, your posted solution (so clearly explained too) should prove very helpful, so full marks for that :)