plot an array

[ntolani]

Just wondering whether it is possible to plot an array into a chart. I have 2 arrays of data which I would like to plot against eachother, but I'm not sure (1) whether this is doable in VBA Excel or (can you only plot something referenced in the sheet itself, ie "K1, K2..."?) and (2) what the correct syntax would be if it is doable.

Writing this in my code:
ActiveChart.SetSourceData Source:=Array_1(), Array_2()
results in a Syntax Error.

Any advice is much appreciated. Thank you.

 

[SkipVought]

Hi,

It appears from VBA help that SetSourceData requires a RANGE OBJECT for its source.

I would suggest that you write your arrays into ranges on a sheet and have at it...

SetSourceData Method
See Also Applies To Example Specifics 
Sets the source data range for the chart.

expression.SetSourceData(Source, PlotBy)

expression   Required. An expression that returns a Chart object.

Source   Required Range. The range that contains the source data.

PlotBy   Optional Variant. Specifies the way the data is to be plotted. Can be either of the following XlRowCol constants: xlColumns or xlRows.

Hope this helps Skip,
SkipAndMary1017@mindspring.com

 

[bryanbayfield]

Not sure you have to use SetDataSource Skip.

Using the macro recorder to create a chart that uses two arrays of user-defined values gives:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 24/06/2002 by Bryan Bayfield
'

'
    Charts.Add
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = "={1,2,3,4,5}"
    ActiveChart.SeriesCollection(1).Values = "={1,2,3,4,5}"
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    With ActiveChart
        .HasTitle = False
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
End Sub

I'd suggest creating a routine that would convert your arrays into the strings seen above in the braces (curly brackets). Those strings can then be used for the assignment of the properties XValues and YValues.

This may seem a bit convoluted, but I think I'd rather do it this way than create ranges of values on the worksheet. But that's just a stylistic preference. SuperBry!

 

[SkipVought]

Nice touch, Bry! Skip,
SkipAndMary1017@mindspring.com

 

[bryanbayfield]

Here's the code to do almost everything. The rest you'll have to do (ie tailor to your requirements, which shouldn't be too hard).

Dim x_arr(4) As Double, y_arr(4) As Double, x_st As String, y_st As String
Sub Example()
' creation of example arrays
For i = 0 To 4
    x_arr(i) = i
    y_arr(i) = i
    Next
' creating strings to use as data series
x_st = convert_arr(x_arr())
y_st = convert_arr(y_arr())
' creates a chart with the strings used as the data sources
Charts.Add
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).XValues = x_st
    ActiveChart.SeriesCollection(1).Values = y_st
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    With ActiveChart
        .HasTitle = False
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
End Sub

Public Function convert_arr(pass_arr() As Double) As String
For i = 0 To UBound(pass_arr())
    Select Case i
            Case 0
                convert_arr = "{" & pass_arr(0) & ","
            Case 1 To UBound(pass_arr()) - 1
                convert_arr = convert_arr & pass_arr(i) & ","
            Case UBound(pass_arr())
                convert_arr = convert_arr & pass_arr(i) & "}"
            End Select
    Next
End Function

SuperBry!

 

[ntolani]

Thank you for your help. Your suggestion seems to work. However, I'm having a little trouble with the X-axis, and I think it has to do with the fact that the string I'm supplying to
ActiveChart.SeriesCollection(1).XValues =
is actually a string of strings (one,two,three), and not a string of numbers (1,2,3).
It works well if I substitute a string of integers here. Is it possible to use strings on the x-axis? Is there another way of doing this?
Converting my array of strings into one long string separated by commas seems to be working okay, so I don't think that's the problem.
Once again, any help here is much appreciated. Thanks for the advice you already provided.
-Neil

 

[ntolani]

I forgot to mention the error that I get:

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

thanks,
Neil

 

[bryanbayfield]

That depends what type of chart you are creating.

If it is a chart that requires a numeric rather than categorical x-axis (eg scatter plot) then you will need a string of numeric values definitely.

It sounds like you will need an additional procedure to convert your string of "named" numbers to the usual numeric type. I think there may be a faq on this. SuperBry!