Graph based on array with dates

[RB Smissaert]

Using Excel 2000. Trying to make a graph based on different arrays. The
y-values work fine, but I am having problems with XValues. The XValues are
based on an array filled with dates. I find that when there are more than 19
dates there occors an error:
1004, unable to get the XValues of the series class. The problem doesn't
occur when the same array is filled with integer numbers rather than dates.
This is the relevant bit of code:

With .ChartObjects(1).Chart
With .SeriesCollection(1)
.Values = GraphHome
.XValues = GraphDates

Thanks for any advice.

RBS

[Peltier]

Hello RBS,

The problem with using arrays is that they are listed in the series
definition in all their glory. That means every digit is entered. But
the series definition (in the formula bar) is limited to something like
255 characters. I expanded on your code in the silly little macro
below. When I allow GraphHome to be Double (many digits long), I can
only get 14 values before the series definition is too long:

=SERIES(,{36874,36873,36872,36871,36870,36869,36868,36867,36866,36865,36864,36863,36862,36861,36860,36859,36858,36857,36856,36855,36854,36853,36852,36851,36850,36849,36848,36847,36846,36845,36844,36843,36842,36841,36840,36839,36838,36837,36836},{6,18,13,13,9,2,11,14,18,17,0,11,18,9,14,10,10,9,7,8,5,1,5,20,1,8,7,10,3,9,5,13,11,3,19,13,10,8,2},1)

When I dim GraphHome as integer, I can get 39 values, like this:

=SERIES(,{36874,36873,36872,36871,36870,36869,36868,36867,36866,36865,36864,36863,36862,36861,36860,36859,36858,36857,36856,36855,36854,36853,36852,36851,36850,36849,36848,36847,36846,36845,36844,36843,36842,36841,36840,36839,36838,36837,36836},{6,18,13,13,9,2,11,14,18,17,0,11,18,9,14,10,10,9,7,8,5,1,5,20,1,8,7,10,3,9,5,13,11,3,19,13,10,8,2},1)

Note that my dates are stored here as longs, but in the chart axis,
these are treated like dates. I had trouble setting GraphDates as date
values. They were put into the array within quotes (as if they were a
string), and that messed up the plot.

I usually just put the arrays into a worksheet range, and construct my
chart using these. You can hide the sheet if you want.

- Jon

'----------------------------------------------
Sub MakeAChart()
Dim GraphHome() As Double, GraphDates() As Long
Dim iCounter As Integer

For iCounter = 1 To 10

ReDim Preserve GraphHome(1 To iCounter)
ReDim Preserve GraphDates(1 To iCounter)
GraphHome(iCounter) = Rnd * 20
GraphDates(iCounter) = CLng(Now) - iCounter
Debug.Print GraphHome(iCounter), GraphDates(iCounter)
Next
'
With ActiveSheet.ChartObjects(1).Chart

With .SeriesCollection(1)
.Values = GraphHome
.XValues = GraphDates

End With
End With
'
Erase GraphDates
Erase GraphHome
End Sub
'----------------------------------------------

[RB Smissaert]

Hi Jon,

Thanks for the explanation. So it is the limited capacity of the formulabar
that is the problem.
Tried one other way to "delink" the graph from range data, by using:
.Values = .Values
.XValues = .XValues
The trouble here is that the dates (I am particularly interested in the
chart tips) get displayed as numbers. Haven't found a good way round this
yet.

RBS

"Peltier" <pel...@home.com> wrote in message
news:3A3981CB...@home.com...

[Peltier]

Yes, as I said below, the dates get turned into longs, although the
chart axis scaling works fine. Chart tips show the long value. After
about ten minutes, I gave up. When I did have what looked like dates,
they were really strings, in quotes, and messed up the chart axis scale.

I thought that delinking with .Values = .Values (or by clicking in the
series formula definition in the formula bar, then clicking F9, then
enter) also runs into the string length limit of the series definition
formula, but the click+F9+Enter lets a large series be delinked.

FWIW, I figured a way to get larger arrays into a series, without
putting the arrays into the sheet. Define the arrays in VBA, then
create workbook names, and assign the arrays to the RefersTo of these
names, and use the names as the values and xvalues of the series. I put
it in the macro below.

- Jon

'----------------------------------------------
Sub GraphByName()
Dim GraphHome() As Double, GraphDates() As Date
Dim iCounter As Integer

For iCounter = 1 To 20

ReDim Preserve GraphHome(1 To iCounter)
ReDim Preserve GraphDates(1 To iCounter)
GraphHome(iCounter) = Rnd * 20

GraphDates(iCounter) = CDate(CLng(Now) - iCounter)
Debug.Print GraphHome(iCounter), GraphDates(iCounter)
Next

With ActiveSheet
ActiveWorkbook.Names.Add Name:="GraphDates", RefersTo:=GraphDates
ActiveWorkbook.Names.Add Name:="GraphHome", RefersTo:=GraphHome
With .ChartObjects(1).Chart
With .SeriesCollection(1)
.XValues = "='" & ThisWorkbook.Name & "'!GraphDates"
.Values = "='" & ThisWorkbook.Name & "'!GraphHome"
End With