Using an array variable to define chart values

[Jennifer]

I am in the process of programmatically charting some
data. I am also trying to add another series to the chart
where the points are derived from averaging data in the
following manner. We have continuous data over a period
of several months, and we get a reference value for each
hour of a standard week (e.g. all readings that were taken
on Sundays at 12:00 midnight are averaged to get one
reference value). We then compare these averages to the
real data as a first crack at flagging regions where the
data might be suspect.

I use the weekday and time when the actual data was taken
as a basis for looking up these reference values. So far
so good. However, the length of time showing on the chart
is longer than 7 days, so simply using cell references was
not working--the series always starts with the value for
Sunday 12:00 AM, as that is the first one listed, and I
want it to instead line up with the real data on the chart
(the chart start and stop date/times are chosen by the
user).

Therefore, it appears that I need to use an array of y
values to make the "artificial" data series start and end
in the right place. I could also do this by having the
VBA paste the values onto the sheet as it looked them up,
but these date ranges can be long and we want to avoid
excess numbers on the worksheet that won't mean anything
to the user.

The problem is that I can't figure out how to use an array
variable for the y-values of a chart series. The code I'm
using is below. I did check the Locals Window and the
array is populated correctly. It would usually have 200+
values. Also note that if I replace the reference to the
array with something like "Array(1,2,3)" then it works.
But when I try to use my array variable I get "Cannot set
the Values property of the Series class."

*******************************************************
Dim series3yarray() As Double, series3yrange As Variant
'I also get the value of a parameter called numHours and
then Redim the array to that length

For j = 1 To numHours
LookupValue = Worksheets(SheetName).Range
("$B$2").Value & Weekday(dt) _
& Hour(dt) & Placeholder
With Worksheets("GreenLine Data").Range("$B$2:$B$7561")
Set ylookup = .Find(LookupValue, LookIn:=xlValues,
lookat:=xlWhole).Offset(0, 5)
End With
series3yarray(j) = ylookup
dt = dt + 1 / 24
Next j

series3yrange = series3yarray

Sheets(SheetName).Select
ActiveSheet.ChartObjects(1).Select
ActiveChart.SeriesCollection.Add Source:=Range
("$A$1:$A$10")
ActiveChart.SeriesCollection(3).Select
Selection.Values = series3yrange
With ActiveChart.SeriesCollection(3)
.XValues = series3xrange
.Values = series3yarray
.Border.ColorIndex = 4
.PlotOrder = 1
End With
***********************************************************


Thanks in advance for your assistance.

Jennifer

 

[Bill Manville]

The problem is that I can't figure out how to use an array
variable for the y-values of a chart series. The code I'm
using is below. I did check the Locals Window and the
array is populated correctly. It would usually have 200+
values.

I think the number of values is probably the problem.
Excel creates a formula for the Series which will have the array
embedded in it
=SERIES(....{0.12345, 0.23456, ....}...)
and this formula is too long for Excel.


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup