By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,985 Members | 1,830 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,985 IT Pros & Developers. It's quick & easy.

1004 Unable to set the Values property of the Series class

P: 48
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub TestGraph3()
  2.  
  3.     Dim oXL As Excel.Application    ' Excel application
  4.     Dim oBook As Excel.Workbook     ' Excel workbook
  5.     Dim oSheet As Excel.Worksheet   ' Excel Worksheet
  6.     Dim oChart As Excel.Chart       ' Excel Chart
  7.  
  8.     Dim myRange As String
  9.     Dim xlSourceRange As Excel.Range
  10.  
  11.     Dim iRow As Integer      ' Index variable for the current Row
  12.     Dim iCol As Integer      ' Index variable for the current Row
  13.  
  14.     Const cNumCols = 4       ' Number of points in each Series
  15.     Const cNumRows = 5       ' Number of Series
  16.  
  17.     ReDim aTemp(1 To (cNumRows + 1), 1 To cNumCols)
  18.  
  19.     ' Start Excel and create a new workbook
  20.     Set oXL = CreateObject("Excel.application")
  21.     Set oBook = oXL.Workbooks.Add
  22.  
  23.     Set oSheet = oBook.Worksheets.Item(1)
  24.  
  25.     ' Insert data into Cells for the two Series:
  26.     For iCol = 1 To cNumCols
  27.        aTemp(1, iCol) = "A" & iCol
  28.        aTemp(2, iCol) = 1
  29.        aTemp(3, iCol) = 1 + 2
  30.        aTemp(4, iCol) = iCol
  31.  
  32.        If (iCol * 2) <= cNumCols Then
  33.         aTemp(5, iCol) = iCol * 2
  34.        Else
  35.         aTemp(5, iCol) = cNumCols
  36.        End If
  37.     Next iCol
  38.  
  39.     oSheet.Range("A1").Resize(5, cNumCols).Value = aTemp
  40.  
  41.     oSheet.Name = "MyChart_MD"
  42.  
  43.     myRange = "A1:D" & (cNumRows)
  44.     Set xlSourceRange = oBook.Worksheets(1).Range(myRange)
  45.  
  46.     Set oChart = oXL.Charts.Add
  47.  
  48.     With oChart
  49.         .SetSourceData Source:=xlSourceRange, PlotBy:=xlColumns
  50.         .ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"
  51.  
  52.         .SeriesCollection(1).XValues = oSheet.Range("A1").Resize(1, cNumCols)
  53.         .SeriesCollection(1).Values = oSheet.Range("A2").Resize(1, cNumCols)
  54.         .SeriesCollection(1).Name = "Plan"
  55.         .SeriesCollection(2).Values = oSheet.Range("A3").Resize(1, cNumCols)
  56.         .SeriesCollection(2).Name = "Actual"
  57.         .SeriesCollection(3).Values = oSheet.Range("A4").Resize(1, cNumCols)
  58.         .SeriesCollection(3).Name = "Accu. Plan"
  59.         .SeriesCollection(4).Values = oSheet.Range("A5").Resize(1, cNumCols)
  60.         .SeriesCollection(4).Name = "Accu. Actual"
  61.         .Location Where:=xlLocationAsNewSheet, Name:="chartMD"
  62.  
  63.         .HasTitle = True
  64.         .ChartTitle.Characters.Text = "Chart : MD"
  65.         .Axes(xlCategory, xlPrimary).HasTitle = False
  66.         .Axes(xlValue, xlPrimary).HasTitle = True
  67.         .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Accu."
  68.         .Axes(xlCategory, xlSecondary).HasTitle = False
  69.         .Axes(xlValue, xlSecondary).HasTitle = False
  70.  
  71.         .HasLegend = False
  72.         .HasDataTable = True
  73.         .DataTable.ShowLegendKey = True
  74.     End With
  75.  
  76.     ' Make Excel Visible:
  77.     oXL.Visible = True
  78.     oXL.UserControl = True
  79.     ' Unselect the ActiveChart
  80.     oChart.Deselect
  81.     oXL.ActiveWindow.Zoom = 85
  82.  
  83.     ' Protect whole Worksheet
  84.     oSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
  85.  
  86.     ' Clean-up
  87.     Set oXL = Nothing: Set oChart = Nothing: Set oSheet = Nothing: Set oBook = Nothing
  88.  
  89. End Sub
  90.  
If cNumCols = 3, ERROR occurs in this line:
Expand|Select|Wrap|Line Numbers
  1.         .SeriesCollection(4).Values = oSheet.Range("A5").Resize(1, cNumCols)
  2.  
If cNumCols = 2, ERROR occurs in this line:
Expand|Select|Wrap|Line Numbers
  1.         .SeriesCollection(3).Values = oSheet.Range("A5").Resize(1, cNumCols)
  2.  

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.
Sep 11 '08 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,347
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.
Sep 11 '08 #2

P: 48
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.
Sep 12 '08 #3

P: 48
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 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:

Expand|Select|Wrap|Line Numbers
  1.         ....
  2.         .SetSourceData Source:=xlSourceRange, PlotBy:=xlColumns
  3.         .ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"
  4.         ....
  5.  
...into this:
Expand|Select|Wrap|Line Numbers
  1.         ....
  2.         .ChartType = xlColumnClustered
  3.         .SetSourceData Source:=xlSourceRange
  4.         ....
  5.  
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:
Expand|Select|Wrap|Line Numbers
  1.         ....
  2.         .SeriesCollection(4).Name = "Accu. Actual"
  3.         .ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column" ' <-- this one!
  4.         .Location Where:=xlLocationAsNewSheet, Name:="chartMD"
  5.         ....
  6.  
Eureka!

Intermittent problem gone.

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


keirnus
Sep 12 '08 #4

NeoPa
Expert Mod 15k+
P: 31,347
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 :)
Sep 12 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.