Returning array from a function OR use data from a function (not cells) to plot line graph

Question

Hello to you kind people out there.

I would like to plot some data from a function I've written. My function calls in an array and several cells. It then performs some calculations. I'd like to plot dates along the x-axis, and my data along the y-axis. Both will be 'As Double'.

I cannot find information about SetSourceData Source:= NOT BEING FROM A RANGE OF CELLS, but from calculations performed within the function.

I'd like to feed in two arrays into my line chart.

To work around this, I thought that I could return two column arrays into Excel (not ideal, but if it works I'm happy). Here is my attempt at returning the dates (in years). In my spreadsheet I am highlighting cells A1:A1001 and typing "=Arr()" and pressing shift+ctrl+enter. It fills in cells A1:A1001 with 1946 (the first year) repeatedly.

Thanks to you all again. I'll keep my fingers crossed :o)

Function Arr() As Variant
    Dim q() As Double
    ReDim q(1 To 1001)
    q(1) = 0

    Dim dq As Double
    dq = 0.001
    For G = 2 To 1001
        q(G) = q(G - 1) + dq
    Next G

    Dim DTE() As Variant
    ReDim DTE(1 To 1, 1 To 1001)

    For k = 1 To 1001
        DTE(1, k) = (q(k) * 64) + 1946
    Next k

    Arr = DTE

End Function

chris neilsen chris neilsen · Accepted Answer · 2012-05-17T06:21:15

This is not an answer to your direct question, but rather to your desire to chart data other than a range.

You can assign an array to the Chart.SeriesCollection(#).Values property

To demonstrate, put a chart on the active shet called Chart 1 and run this code

Sub zx()
    Dim ch As Chart

    Set ch = ActiveSheet.ChartObjects("Chart 1").Chart
    ch.SeriesCollection(1).Values = Array(1, 3, 5, 7, 11, 13, 17, 19)
    ch.SeriesCollection(1).XValues = Array(5#, 6.3, 12.6, 28, 50)

End Sub

Returning array from a function OR use data from a function (not cells) to plot line graph

1 Answers