Adding series to charts from arrays

[Ravalia]

Hi

I'm trying to add series' to line-graphs "on-the-fly" using data retreived from Excel/VBA calls to an XLL.

There are no problems if I have a named range containing an array of the values I would like to plot and using VBA to add a NewSeries to the graph and set the .Values property of the series to my named range.

However, if I try to set the .Values property to the contents of a VBA array (returned by a call to a function in the XLL), I get errors.

Has anyone had any experience with this kind of problem?

Any help/re-direct would be appreciated.

 

[SkipVought]

Ravalia,

It would be nice to see your code. Where is the source data? Which XLL? You need to supply enough info for someone who know NOTHING about what you are doing, can provide some help!

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[Ravalia]

This is a sample:

Public Sub graphIt()
    dim vDates as Variant
    dim vValues as Variant

'This Works
    set vDates = Range("myDateRange")
    Set vValues = Range("myValues")
'But this does not (the xx functions are defined in a library to return arrays of dates/doubles)
    vDates = xxGetTSDates(myArgs)
    vValues = xxGetTSValues(myArgs)

'With this code
    With ActiveSheet.ChartObjects("myGraph").Chart
        'Delete existing series
        For i = .SeriesCollection.Count To 1 Step -1
            .SeriesCollection(i).Delete
        Next i
        With .SeriesCollection.NewSeries
            .Name = "myNewSeries"
            .XValues = vDates
            .Values = vValues
            .Border.Weight = xlMedium
        End With
        .ChartTitle.Caption = "My TimeSeries"
    End With
End Sub

The problem is not in the XLL as I can see vDates and vValues populated correctly as Arrays of Dates & Doubles, but rather with the lines

.XValues = vDates
.Values = vValues

These lines work fine when vDates & vValues either refer to ranges (of any size) or to Arrays of very small size. However, if the Arrays returned by the 'xx' functions are reasonably sized, Excel displays an error message.

I don't know if I have exlpained the problem properly but any help/advice would be appreciated.

 

[SkipVought]

How many elements are there in each array

UBound(vdates, 1)
UBound(vvalues, 1)


Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[Ravalia]

Skip,

When I try with small arrays (say UBound = 5), there are no problems - the series reads in the array and is plotted appropriately.

When I try with something like UBound = 100, I get problems.

I believe the problem is that the array is implicitly converted into a formula

"=SERIES(1.23,2.34,3.45,...)"

which is then applied to the series object within the graph. Furthermore, there appears to be an error whenever this formula reaches approx 200 characters.

As I mentioned, I don't have any problems if I read the array into a range and assign the range to the .Values property of the series object. The only problem with this is that I have to maintain the extra worksheet - which gets trickier and trickier when I have several series, several graphs and the functionality to change graphs "on-the-fly".