PC Review


Reply
Thread Tools Rating: Thread Rating: 2 votes, 5.00 average.

Error: unable to set the value property of the series class

 
 
=?Utf-8?B?RnJhbmNvaXM=?=
Guest
Posts: n/a
 
      20th Oct 2004
Hi

I have a serious problem with a chart that I made using VBA.

I get the error: Runtime error 1004:
"Unable to set the values property of the series class"
and sometimes the same error but just for the named property of the series.

The chart is a standard lines chart.

I hope somebody can help me

Below is the section of code that is flagged:

Sub ChartOptions()
' this sets up the QC Graph for the main lab with Chart title,
' axis title and scale of Y-axis according to each parameter

Sheets("Calcs").Select
ActiveCell = Cells(3, 2)
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=Ave+1*(STDEV)" ' UWL
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=Ave-1*(STDEV)" 'LWL
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=Ave+3*(STDEV)" 'UCL
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=Ave-3*(STDEV)" 'LCL

Range("B3:B7").Select
Selection.Copy
Range("C3:AZ7").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

a = MsgBox("Limits data created. Click Ok to view Graph", vbOKOnly,
"Limits Data")

If a = vbOK Then
Sheets("QC Graph").Select
With Charts("QC Graph") 'this sets up the chart title, axis
title and legend
.HasTitle = True
.ChartTitle.Text = "QC Graph for:" & " " & QCType & " "
& "at the" & " " & QCSection
.SeriesCollection(1).Name = QCType
**** .SeriesCollection(1).Values = QCData *****
End With
With Charts("QC Graph").Axes(xlValue)
.HasTitle = True
.AxisTitle.Text = QCType
End With
End If
'this sets up the various QCtypes' Y-axis scales
If QCSection = "Clean Lab" Then
Select Case SectionNumber
Case 1 To 4 'Cl,SO4,Fe,Cu
Sheets("QC Graph").Select
With Charts("QC Graph").Axes(xlValue)
.MinimumScale = 5
.MaximumScale = 15
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End Select
ElseIf QCSection = "Boiler Bench" Then
Select Case SectionNumber
Case 1 'Na
Sheets("QC Graph").Select
With Charts("QC Graph").Axes(xlValue)
.MinimumScale = 5
.MaximumScale = 15
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
Case 2 'SiO2
Sheets("QC Graph").Select
With Charts("QC Graph").Axes(xlValue)
.MinimumScale = 15
.MaximumScale = 25
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ScaleType = xlLinear
End With
End Select
ElseIf QCSection = "Water Bench" Then
Select Case SectionNumber
Case 1 To 2 'M-Alk,CaH
Sheets("QC Graph").Select
With Charts("QC Graph").Axes(xlValue)
.MinimumScale = 85
.MaximumScale = 125
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ScaleType = xlLinear
End With
Case 3 'COD
Sheets("QC Graph").Select
With Charts("QC Graph").Axes(xlValue)
.MinimumScale = 35
.MaximumScale = 65
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ScaleType = xlLinear
End With
End Select
ElseIf QCSection = "Micro Lab" Then
Select Case SectionNumber
Case 1 To 3 'HPC,TC,FC
With Charts("QC Graph").Axes(xlValue)
.MinimumScale = Auto
.MaximumScale = Auto
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ScaleType = xlLinear
End With
End Select
End If

End Sub


 
Reply With Quote
 
 
 
 
Tushar Mehta
Guest
Posts: n/a
 
      20th Oct 2004
Maybe because QCData is both undeclared and uninitialized?

It is a very good idea to declare the kind of variable you should be
using. Put a 'Option Explicit' statement at the top of each module.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <(E-Mail Removed)>,
http://www.pcreview.co.uk/forums/(E-Mail Removed) says...
> Hi
>
> I have a serious problem with a chart that I made using VBA.
>
> I get the error: Runtime error 1004:
> "Unable to set the values property of the series class"
> and sometimes the same error but just for the named property of the series.
>
> The chart is a standard lines chart.
>
> I hope somebody can help me
>
> Below is the section of code that is flagged:
>
> Sub ChartOptions()
> ' this sets up the QC Graph for the main lab with Chart title,
> ' axis title and scale of Y-axis according to each parameter
>
> Sheets("Calcs").Select
> ActiveCell = Cells(3, 2)
> ActiveCell.Offset(1, 0).Select
> ActiveCell.FormulaR1C1 = "=Ave+1*(STDEV)" ' UWL
> ActiveCell.Offset(1, 0).Select
> ActiveCell.FormulaR1C1 = "=Ave-1*(STDEV)" 'LWL
> ActiveCell.Offset(1, 0).Select
> ActiveCell.FormulaR1C1 = "=Ave+3*(STDEV)" 'UCL
> ActiveCell.Offset(1, 0).Select
> ActiveCell.FormulaR1C1 = "=Ave-3*(STDEV)" 'LCL
>
> Range("B3:B7").Select
> Selection.Copy
> Range("C3:AZ7").Select
> Selection.PasteSpecial Paste:=xlPasteValues
> Application.CutCopyMode = False
>
> a = MsgBox("Limits data created. Click Ok to view Graph", vbOKOnly,
> "Limits Data")
>
> If a = vbOK Then
> Sheets("QC Graph").Select
> With Charts("QC Graph") 'this sets up the chart title, axis
> title and legend
> .HasTitle = True
> .ChartTitle.Text = "QC Graph for:" & " " & QCType & " "
> & "at the" & " " & QCSection
> .SeriesCollection(1).Name = QCType
> **** .SeriesCollection(1).Values = QCData *****
> End With

{snip}
 
Reply With Quote
 
 
 
 
Jon Peltier
Guest
Posts: n/a
 
      22nd Oct 2004

Tushar Mehta wrote:

> Maybe because QCData is both undeclared and uninitialized?


No. See below.

> It is a very good idea to declare the kind of variable you should be
> using. Put a 'Option Explicit' statement at the top of each module.


Very true, despite what I'm going to say.

If you assign a null to the series values at this point, and the series already
contains a valid set of values, Excel will assign a single zero to the values. The
null variable QCData is converted the best Excel knows how. (I tested this twice to
make sure, because at first I thought Tushar nailed it.)

The problem is that the series probably does not contain a valid set of values.
Perhaps it points to a range of blank cells, or errors (like #N/A). If the data is
not sufficient to produce a visible series, you cannot access any of the components
of the series formula. Even if you are trying to set it, not read what it is, you
will get an error.

This accounts for the failure to set the series values. It does not account for the
ability to set its name in the previous line.

For a quick workaround, you can set the chart type temporarily to an Area type:

activechart.SeriesCollection(1).charttype = xlarea

Do what you need to with the series formula and its components, then change the
chart type back.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Unable to set the xvalues (or values) property of the series class Kate Microsoft Excel Programming 1 5th May 2005 02:13 PM
Unable to set the XValues property of the Series class David Mullins via OfficeKB.com Microsoft Excel Programming 0 26th Jan 2005 03:54 PM
error - unable to set the XValues property of the series class vjp Microsoft Excel Charting 1 22nd Oct 2004 03:56 AM
Unable to set the Name Property of the Series Class : Error '1004' =?Utf-8?B?RnJhbmNvaXM=?= Microsoft Excel Charting 1 21st May 2004 02:35 AM
Unable to set the Values Property of the Series Class =?Utf-8?B?TWlrZSBDYXB1dG8=?= Microsoft Excel Charting 0 25th Feb 2004 04:16 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 10:33 AM.