1004 Unable to set the Values property of the Series class

[keirnus]

Hello,


I have an intermittent problem here.
It sometimes goes well, sometimes not.

I really don't know why the following error occurs:

1004 Unable to set the Values property of the Series class

Here's my Test code:

Private Sub TestGraph()
 
    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 = 1       ' 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.Name = "MyChart_MD"
    oSheet.Range("A1").Resize(5, cNumCols).Value = aTemp
         
    myRange = "A1:A" & (cNumRows)
    Set xlSourceRange = oBook.Worksheets(1).Range(myRange)
 
    Set oChart = oXL.Charts.Add
 
    With oChart
        .SetSourceData Source:=xlSourceRange
        .ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"
        
        .Activate
        .Select
        
        .SeriesCollection(1).XValues = "='MyChart_MD'!R1C1:R1C" & cNumCols
        .SeriesCollection(1).Values = "='MyChart_MD'!R2C1:R2C" & cNumCols
        .SeriesCollection(1).Name = "Plan"
        .SeriesCollection(2).Values = "='MyChart_MD'!R3C1:R3C" & cNumCols
        .SeriesCollection(2).Name = "Actual"
        .SeriesCollection(3).Values = "='MyChart_MD'!R4C1:R4C" & cNumCols
        .SeriesCollection(3).Name = "Accu. Plan"
        .SeriesCollection(4).Values = "='MyChart_MD'!R5C1:R5C" & 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

Since I'm setting the column value to 1, the error always occur in this line:

        .SeriesCollection(2).Values = "='MyChart_MD'!R3C1:R3C" & cNumCols

It's the second index of the Series Collection.
But the range is "A1:A5" so it should have 5.
Dunno why it stops in the 2nd.

Why does this error occur? (intermittent)
How to fix this intermittent problem?

Help is greatly appreciated.


already frustrated,
keirnus

 

[keirnus]

Finally solved the intermittent problem

Finally solved the intermittent problem!
(It seems like I'm the only one on this thread..as usual..hee!)

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 xlColumnClustered 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