Scatter with Dynamic (Array) Data

  • Thread starter Thread starter Jim S.
  • Start date Start date
J

Jim S.

Hi,

I'm trying to create a scatter plot using Array's instead
of using Cell's in a worksheet but can't seem to get it to
work. Here's what I'd LIKE to be able to do but it keeps
bombing. Any ideas?

JS

=======

Sub simple_array_table()
Dim myXvalues(1#, 2#, 3#) As Double
Dim myYvalues(4, 2, 4) As Double

Charts.Add
ActiveChart.ChartType = xlXYScatter

ActiveChart.SeriesCollection(1).xvalues = myXvalues
ActiveChart.SeriesCollection(1).Values = myYvalues
End Sub
 
I figured out the problem. Or, more correctly stated, I
figured out a way around what I think is an Excel
limitation. Using arrays works just fine SO LONG AS THE
ARRAY SIZE IS KEPT "SMALL", like, on the order of 10 or so
elements. Otherwise, I get:

Run-time error '1004':
Unable to set the XValues property of the Series class

Here's a code fragment that works, so long as MaxArraySize
is small. Crank it up to 100 and I get the above error:

Sub simple_array_table_2()
Const MaxArraySize = 10
Dim theChart As Chart
Dim myXvalues(1 To MaxArraySize) As Variant
Dim myYvalues(1 To MaxArraySize) As Variant
For i = 1 To MaxArraySize
myXvalues(i) = i
myYvalues(i) = i ^ 2
Next i
Set theChart = Charts.Add
theChart.ChartType = xlXYScatter
theChart.SetSourceData _
Source:=Sheets("Sheet1").Range("A1:B1"), _
PlotBy:=xlColumns
theChart.PlotArea.Select
theChart.SeriesCollection(1).xvalues = Array
(myXvalues)
theChart.SeriesCollection(1).Values = Array
(myYvalues)
End Sub

Also, I can't seem to get anything to work without running
the SetSourceData method and passing in a Range object.
Anyone know how to avoid this step, since I really don't
want to plot cell data? If I comment out that line, I get
the following error:

Run-time error '1004':
Select method of PlotArea class failed

Thanks for any help.

JS
 
There is a limit to the number of characters in the SERIES formula that
corresponds to the plotted series.

The code below works. Increasing MaxArraySize by 1 (to 57) will cause
a problem. The code also works without having to create a dummy
series.

A final note: The diagnostic statements will work only on versions of
VBA that support the Join function.

Option Explicit

Sub simple_array_table_2()
Const MaxArraySize = 56
Dim theChart As Chart, i As Integer
Dim myXvalues(1 To MaxArraySize) As Variant
Dim myYvalues(1 To MaxArraySize) As Variant
For i = 1 To MaxArraySize
myXvalues(i) = i
myYvalues(i) = myXvalues(i) ^ 2
Next i
Set theChart = Charts.Add
With theChart
'if some cells were selected at the time this code _
was run, XL will have plotted some default series. _
To be on the safe side, delete all existing series.
For i = 1 To .SeriesCollection.Count
.SeriesCollection(1).Delete
Next i
.ChartType = xlXYScatter
.SeriesCollection.NewSeries
MsgBox Len(Join(myXvalues, ",")) _
& ", " & Len(Join(myYvalues, ","))
.SeriesCollection(1).XValues = Array(myXvalues)
.SeriesCollection(1).Values = Array(myYvalues)
MsgBox Len(Join(.SeriesCollection(1).XValues, ",")) _
& ", " & Len(Join(.SeriesCollection(1).Values, ","))
End With
End Sub

--
Regards,

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

In addition to Tushar's comments, there are a few problems with your code.

Delete the "theChart.PlotArea.Select" line. Without data, the chart has
no PlotArea. Adding SetSourceData only enables an unnecessary step.

I don't understand your use of Array(myXvalues) since myXvalues is
already an array. I made your first scenario work with this short bit of
code:

Sub simple_array_table()
Dim myXvalues
Dim myYvalues

myXvalues = Array(1, 2, 3)
myYvalues = Array(4, 2, 4)

Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SeriesCollection.NewSeries

ActiveChart.SeriesCollection(1).XValues = myXvalues
ActiveChart.SeriesCollection(1).Values = myYvalues
End Sub

This will only work up to the limit detailed by Tushar.

You can get around the character length limitation of the XValues and
YValues arrays by using a worksheet range or a named range to contain
the literal arrays. This macro shows how to use named ranges to expand
the array size allowed in the chart:

Sub ArrayToRangeName()
Dim myXvalues
Dim myYvalues

myXvalues = Array(1, 2, 3)
myYvalues = Array(4, 2, 4)

ActiveWorkbook.Names.Add _
Name:="myXvalues", _
RefersTo:=myXvalues
ActiveWorkbook.Names.Add _
Name:="myYvalues", _
RefersTo:=myYvalues

Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SeriesCollection.NewSeries

ActiveChart.SeriesCollection(1).Values = _
"='" & ActiveWorkbook.Name & "'!myYvalues"
ActiveChart.SeriesCollection(1).XValues = _
"='" & ActiveWorkbook.Name & "'!myXvalues"

End Sub

- Jon
 
Back
Top