Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2003
    Thanked 0 Times in 0 Posts

    Chart macro (2002)

    I want to chart data generated from a macro in Excel. The data is simply loaded into arrays x and y: Dim x(1 to 100) as double, Dim y(1 to 100) as double.
    I can do this by first putting the data out to a sheet, and then charting from the sheet by using a Range object. However, this step seem unnecessary.

  2. #2
    2 Star Lounger
    Join Date
    Feb 2001
    Brussels, Brussel, Belgium
    Thanked 0 Times in 0 Posts

    Re: Chart macro (2002)

    not sure if i understand your question. you cannot chart directly from an array, you need to copy the data to a range on a sheet and refer to that range as the sourcedata of your chart.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Chart macro (2002)

    You can NOT do it with an ARRAY of 100, the formula for the "series" only allows 255 characters max, so your series has to be a range name or less items:

    This is how (in principal) you can do it with a smaller array (X and Y are your arrays of values)

    xArray = "{"
    yArray = "{"
    For i = LBound(x) To UBound(x)
    xArray = xArray & x(i) & ","
    yArray = yArray & y(i) & ","
    xArray = Left(xArray, Len(xArray) - 1) & "}"
    yArray = Left(yArray, Len(yArray) - 1) & "}"

    ActiveSheet.ChartObjects(1).Chart.SeriesCollection (1).Formula = _
    "=SERIES(" & Chr(34) & "Series Name" & Chr(34) & "," & _
    xArray & "," & yArray & ",1)" </pre>

    BUT like I said the length of the "formula" including all the text above (that is with the MUST be <256 characters, which is why in practice it is much easier to use named ranges than to List each and every one of the x and y points.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts