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

The error occured in this line:

        .SeriesCollection(4).Values = oSheet.Range("A7").Resize(1, 3)

Please check the rest of the code below.
Can somebody explain to me why there's an error?

    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
    On Error GoTo Err_CreateChart
 
    'Start Excel and create a new workbook
    Set oXL = CreateObject("Excel.application")
    Set oBook = oXL.Workbooks.Add
 
    Set oSheet = oBook.Worksheets.Item(1)
 
    oSheet.Range("A1").Resize(7, 3).Value = aTemp
    oSheet.Name = "Chart_Data"
 
    Set oChart = oXL.Charts.Add
    With oChart
        .SetSourceData Source:=oSheet.Range("A1").Resize(7, 4), PlotBy:=xlColumns
        .ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column on 2 Axes"
        .SeriesCollection(1).XValues = oSheet.Range("A1").Resize(1, 3)
        .SeriesCollection(1).Values = oSheet.Range("A4").Resize(1, 3)
        .SeriesCollection(1).Name = "Plan"
        .SeriesCollection(2).Values = oSheet.Range("A6").Resize(1, 3)
        .SeriesCollection(2).Name = "Actual"
        .SeriesCollection(3).Values = oSheet.Range("A5").Resize(1, 3)
        .SeriesCollection(3).Name = "Accu. Plan"
        [COLOR=red].SeriesCollection(4).Values = oSheet.Range("A7").Resize(1, 3)  '<-- ERROR occurs here...what's the reason?[/COLOR]
        .SeriesCollection(4).Name = "Accu. Actual"
        .Location Where:=xlLocationAsNewSheet, Name:="Chart_MD"
        .HasTitle = True
        .Axes(xlCategory, xlSecondary).HasTitle = True
        .Axes(xlCategory, xlSecondary).AxisTitle.Characters.Text = "Accumulated"
        .ChartTitle.Characters.Text = "Char Title"
        .Axes(xlCategory, xlSecondary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "AAA"
        .Axes(xlValue, xlSecondary).HasTitle = True
        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Accumulated AAA"
        .HasLegend = False
        .HasDataTable = True
        .DataTable.ShowLegendKey = True
 
        ' Unselect the ActiveChart
        .Deselect
    End With
 
    oXL.ActiveWindow.Zoom = 85
 
    ' Make Excel Visible:
    oXL.Visible = True
    oXL.UserControl = True
 
Exit_CreateChart:
    'clean-up
    Set oXL = Nothing: Set oChart = Nothing: Set oSheet = Nothing: Set oBook = Nothing
    Exit Sub
Err_CreateChart:
    MsgBox CStr(Err) & " " & Err.Description
    Resume Exit_CreateChart

I'm stuck now.
Help is really, greatly appreciated.

 

[keirnus]

Test Code for the problem

Got no replies since yesterday.

Anyway, I made a test code below:

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 = 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:

[COLOR=red]       .SeriesCollection(4).Values = oSheet.Range("A5").Resize(1, cNumCols)[/COLOR]

If cNumCols = 2, ERROR occurs in this line:

[COLOR=red]       .SeriesCollection(3).Values = oSheet.Range("A5").Resize(1, cNumCols)[/COLOR]

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?

(actually, number of columns lesser than the number of series)

Help will be greatly appreciated.

 

[keirnus]

Finally solved the 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"
        [COLOR=red].ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column" ' <-- this one![/COLOR]
        .Location Where:=xlLocationAsNewSheet, Name:="chartMD"

Eureka!

Intermittent problem gone.

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


keirnus

 

[asd382]

Dear KeirnUS i am in need of some urgent assistance. Getting the same error as above "1004 Unable to set the Values property of the Series class"
If you could kindly help i would be more than grateful..

Basically i'm running a spreadsheet that has a 'plot chart' macro, which then pulls through information from a few of the other tabs. out of the three tabs it pulls data from 2 will always have data but sometimes the third one is blank, and it should ignore that one and plot the remaining two..

I get the error for the line stating: "ActiveChart.SeriesCollection(Counter).Values = ws.Range("b2:b2005")"

I dont understand why, as the graph is plotting when data is available for all the tabs, but when one tab is blank instead of just plotting the existing 2 it throws up the error.

"Sub charting()
Dim ws As Worksheet
Dim Counter As Integer
Dim Msg As String
Sheets("Spectra Chart").Activate
'Reset option to allow for user to press the create chart button many times
'and not create multiple charts
If ActiveSheet.ChartObjects.Count > 0 Then
Msg = MsgBox("The existing spectra chart will be redrawn", vbOKOnly)
ActiveSheet.ChartObjects("Comparison").Delete
End If
Application.ScreenUpdating = False
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.Location Where:=xlLocationAsObject, Name:="Spectra Chart"
ActiveSheet.ChartObjects(1).Name = "Comparison"
Counter = 1
'Loop through each worksheet and select spectra data to create plot
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Summary" Then
GoTo nextws
End If
If ws.Name = "Details" Then
GoTo nextws
End If
If ws.Name = "Spectra Chart" Then
GoTo nextws
End If
Sheets("Spectra Chart").Activate
ActiveSheet.ChartObjects(1).Name = "Comparison"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(Counter).XValues = ws.Range("a2:a2005")
ActiveChart.SeriesCollection(Counter).Values = ws.Range("b2:b2005")
ActiveChart.SeriesCollection(Counter).Name = ws.Name
Counter = Counter + 1
nextws:
Next ws
Sheets("Spectra Chart").Activate
ActiveSheet.ChartObjects(1).Select
'ActiveChart.PlotArea.Select
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Comparison of Crude Spectra"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Wavenumber(cm-1)"
End With
'Set scaling for the chart
With ActiveChart.Axes(xlCategory)
.MinimumScale = 5200
.MaximumScale = 7600
.MinorUnit = 200
.MajorUnit = 400
.Crosses = xlAutomatic
.ReversePlotOrder = True
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
With ActiveChart.Parent
.Left = 8
.Width = 600
End With"


Hope you can help!!
Thank you

 

[keirnus]

Dear KeirnUS i am in need of some urgent assistance. Getting the same error as above "1004 Unable to set the Values property of the Series class"
If you could kindly help i would be more than grateful..

Basically i'm running a spreadsheet that has a 'plot chart' macro, which then pulls through information from a few of the other tabs. out of the three tabs it pulls data from 2 will always have data but sometimes the third one is blank, and it should ignore that one and plot the remaining two..

I get the error for the line stating: "ActiveChart.SeriesCollection(Counter).Values = ws.Range("b2:b2005")"

I dont understand why, as the graph is plotting when data is available for all the tabs, but when one tab is blank instead of just plotting the existing 2 it throws up the error.

"Sub charting()
Dim ws As Worksheet
Dim Counter As Integer
Dim Msg As String
Sheets("Spectra Chart").Activate
'Reset option to allow for user to press the create chart button many times
'and not create multiple charts
If ActiveSheet.ChartObjects.Count > 0 Then
Msg = MsgBox("The existing spectra chart will be redrawn", vbOKOnly)
ActiveSheet.ChartObjects("Comparison").Delete
End If
Application.ScreenUpdating = False
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.Location Where:=xlLocationAsObject, Name:="Spectra Chart"
ActiveSheet.ChartObjects(1).Name = "Comparison"
Counter = 1
'Loop through each worksheet and select spectra data to create plot
For Each ws In ActiveWorkbook.Worksheets
If ws.Name = "Summary" Then
GoTo nextws
End If
If ws.Name = "Details" Then
GoTo nextws
End If
If ws.Name = "Spectra Chart" Then
GoTo nextws
End If
Sheets("Spectra Chart").Activate
ActiveSheet.ChartObjects(1).Name = "Comparison"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(Counter).XValues = ws.Range("a2:a2005")
ActiveChart.SeriesCollection(Counter).Values = ws.Range("b2:b2005")
ActiveChart.SeriesCollection(Counter).Name = ws.Name
Counter = Counter + 1
nextws:
Next ws
Sheets("Spectra Chart").Activate
ActiveSheet.ChartObjects(1).Select
'ActiveChart.PlotArea.Select
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Comparison of Crude Spectra"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Wavenumber(cm-1)"
End With
'Set scaling for the chart
With ActiveChart.Axes(xlCategory)
.MinimumScale = 5200
.MaximumScale = 7600
.MinorUnit = 200
.MajorUnit = 400
.Crosses = xlAutomatic
.ReversePlotOrder = True
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
With ActiveChart.Parent
.Left = 8
.Width = 600
End With"


Hope you can help!!
Thank you

oic...

i will tell you the same thing Mr. Jon Peltier told me.
"Line and XY charts don't like it when a series starts with no data."

...and i can cut the chase and head straight to the point.

i think you are using XY Chart.
so, we'll start in the setting of the Chart Type.

try changing this:

    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers

...into this:

     ActiveChart.ChartType = xlColumnClustered

that's the 1st thing you should do...
the 2nd is this.

set again the Chart Type into its original value after setting all series.

so, it will look like this:

           ActiveChart.SeriesCollection(Counter).Name = ws.Name
           [COLOR=Red]ActiveChart.ChartType = xlXYScatterSmoothNoMarkers ' Set again the Chart Type here[/COLOR]
           Counter = Counter + 1

Just message me again if there are problems.

 

[asd382]

Hi there,
Thank you for your prompt response, really appreciate it.

I tried your suggestions but i still get the same error message and for the same line:

ActiveChart.SeriesCollection(Counter).Values = "=ws.Rangeb2:b2005"

Please let me know of any other amendments i could make?

Thank you

 

[asd382]

sorry line should be as below

ActiveChart.SeriesCollection(Counter).Values = ws.Range("b2:b2005")

still i get the same error.

 

[keirnus]

sorry line should be as below

ActiveChart.SeriesCollection(Counter).Values = ws.Range("b2:b2005")

still i get the same error.

can't seem to replicate your error.
i tried your code and it runs ok.

i'm just curious, what OS Language are you using?
English or non-English?

 

[asd382]

Hi there once again,

I am running windows in english language.
Could it be something to do with the fact that it is referencing blank data? as sometimes the data in 'ws.range("b2:b2005")' is filled and other times that whole column is blank. The same goes for the a2:a2005 range.

Thank you for your help once again.

 

[keirnus]

Hi there once again,

I am running windows in english language.
Could it be something to do with the fact that it is referencing blank data? as sometimes the data in 'ws.range("b2:b2005")' is filled and other times that whole column is blank. The same goes for the a2:a2005 range.

Thank you for your help once again.

Sorry for late reply.
Been busy lately.

I wasn't able to replicate it before.
Maybe because I was using Office 2007.

I tried running it in Office 2003 and replicated the prob.

Changing this:

    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers

...into this

    ActiveChart.ChartType = xlColumnClustered

...is correct.

But my last post on setting the original Chart Type was wrong.
It would still get the error because it was within the loop.
It should be after the loop.

Thus, it should be like this:

    .....
    'Loop through each worksheet and select spectra data to create plot
    For Each ws In ActiveWorkbook.Worksheets
         ..........
nextws:
    Next ws
 
    [COLOR=red]ActiveChart.ChartType = xlXYScatterSmoothNoMarkers ' <<<<[/COLOR]
 
    Sheets("Spectra Chart").Activate
    .....

Sorry for not noticing the loop.
Please message me if it still doesn't work.

 

[asd382]

its working!!!! your a life saver..

thank you ever so much for taking the time out to assist me..
much appreciated! you are the vba guru!!

 

[asd382]

i have one last query i promise its the last!!

i've created a macro button with the following code. Basically it takes information from the worksheet and submits it to an application database installed on my pc. I am having problems with the 'if' statements, i want the button to perform both the 'if' statements but it only does the first one
'DoAssayUpdate'


Private Sub CommandButton2_Click()
Call SynchroniseAll

' Publish crude to database
Dim sAnswer As String
Dim strBaseCrude As String
Dim bUpdate As Boolean
Dim strCrude As String
Dim strDBGUID As String
strBaseCrude = ActiveSheet.Cells(5, 8)
strCrude = ActiveSheet.Cells(5, 3)
strDBGUID = ActiveSheet.Cells(7, 3)

If checkbox1 = True Then
sAnswer = DoAssayUpdate(ActiveSheet.Range("B5", "C6"), ActiveSheet.Range("C10", "X15"), strBaseCrude)
End If
If checkbox1 = True Then
sAnswer = PublishCrude(strCrude, strDBGUID, "Dscription of published crude")
End If
End Sub

 

[chergh]

How do you know it is only doing the first one? Have you confirmed it is only doing the first one by stepping through the code?

 

[asd382]

It does begin submitting but when i check the database it doesnt contain the information i just submitted. Could be a problem on the database side not picking up information.

How do i step through the code? sorry im still a newbie to excel.. alot of this code was originally created by someone else.. im just amending..

 

[chergh]

you use the F8 key to step through the code. I would assume the second 'if' is being performed but something is going wrong PublishCrude function.

 

[keirnus]

i have one last query i promise its the last!!

i've created a macro button with the following code. Basically it takes information from the worksheet and submits it to an application database installed on my pc. I am having problems with the 'if' statements, i want the button to perform both the 'if' statements but it only does the first one
'DoAssayUpdate'


Private Sub CommandButton2_Click()
Call SynchroniseAll

' Publish crude to database
Dim sAnswer As String
Dim strBaseCrude As String
Dim bUpdate As Boolean
Dim strCrude As String
Dim strDBGUID As String
strBaseCrude = ActiveSheet.Cells(5, 8)
strCrude = ActiveSheet.Cells(5, 3)
strDBGUID = ActiveSheet.Cells(7, 3)

If checkbox1 = True Then
sAnswer = DoAssayUpdate(ActiveSheet.Range("B5", "C6"), ActiveSheet.Range("C10", "X15"), strBaseCrude)
End If
If checkbox1 = True Then
sAnswer = PublishCrude(strCrude, strDBGUID, "Dscription of published crude")
End If
End Sub

Hello asd382,

Please put your codes inside the CODE tag.
Example:
[CODE*]
' your code here
[/CODE*]

Just remove the asterisks(*) to make it work.
Or simply select your codes and click the number sign (#)
above of the editor. It says, "Wrap

 tag around selected text." 
This is to view the right position of the codes.

To your prob...i agree with chergh.
Try debugging the PublishCrude() by stepping through it.

Hope you'll find the prob and solve it.
If you need help, just message here.

There are lots of gurus here.
...and i'm not one of them. :o

 

[asd382]

Hi there,

Ok this is my last day at doing this... i think i might know what the problem is..
I feel the 'publishcrude' function is working... but i think the problem lies with the data that it is submitting.

The "strDBGUID = ActiveSheet.Cells(7, 3)" is displaying a unique GUID key that is derived from another cell on the work sheet, 'L22'.
The'L22' cell has a function to determine the value in cells(7,3). If your getting what im saying..

Althought the GUID key is displaying fine in cell (7, 3) when i click on that cell, at the top of the page it displays the actual cell calculation in this case being '=GUID(L22)'.

So i think when the data is being submitted, the actual GUID key is not being submitted, as function is taking the cell contents at face value of '=GUID(L22)'.

please view image here:


Is there any way of taking the actual value of the cell instead of the formula?

Thank you once again for all your help
much appreciated!

 

[chergh]

For the screenshot you attached, assuming the row number is 7, to return the value you would do:

worksheets("SheetName").range("C7").value

 

[asd382]

I have done all the necessary changes but still not publishing the crude, maybe the original 'publishcrude' function needs to be amended, this is held in referenced DLL file.
As shown below

Public Function PublishCrude(strReference As String, strShareID As String, strDescription As String) As Long

' Calls to publish to a database are of the type:

'    <Request Type='CrudeSync' Operation='PublishFluid' >
'        <Update Reference='DLLTEST1' ShareID='{45D5A366-12F5-4326-ADE0-0639B57FF199}' Description='DLL test crude added' />
'    </Request>

    Dim xmldom As New DOMDocument
    Dim eleRequest As IXMLDOMElement
    Dim eleUpdate As IXMLDOMElement
    Dim answer As structAnswer
  
    ' Create the top level <CQL><Request> nodes
    Call xmldom.appendChild(xmldom.createNode(1, "CQL", ""))
    Set eleRequest = xmldom.selectSingleNode("/CQL").appendChild(xmldom.createNode(1, sRequestTag, ""))
    
    ' Define the type of call
    Call eleRequest.setAttribute("Type", "CrudeSync")
    Call eleRequest.setAttribute("Operation", "PublishFluid")
      
    ' Add an "update"
    Set eleUpdate = eleRequest.appendChild(xmldom.createNode(1, "Update", ""))
    Call eleUpdate.setAttribute("Reference", strReference)
    Call eleUpdate.setAttribute("ShareID", strShareID)
    Call eleUpdate.setAttribute("Description", strDescription)
       
    Dim lReturn As Long
    Dim lSize As Long
    
    lSize = Len(answer)
    
    ' Call into the DLL with the CQL that's in the XML dom
    ' xmldom.Save ("c:\windows\temp\temp.xml")
    lReturn = CQLQuery(xmldom.XML, answer, lSize)
    
    PublishCrude = lReturn

End Function

Thank you