+ Reply to Thread
Results 1 to 4 of 4

Problem with plotting a chart when using arrays as Values and Xvalues

  1. #1

    Problem with plotting a chart when using arrays as Values and Xvalues

    Hi

    I am trying to generate a chart from VBA arrays, but I am finding that
    once the arrays become too large I get the following runtime error:

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

    A simple piece of code which reproduces the error:

    Option Base 1
    Sub test()
    Const nPts As Long = 81 'if increase this to >= 82, doesn't work!
    Dim x(nPts) As Double, y(nPts) As Double
    Dim i As Long

    For i = 1 To nPts
    x(i) = i
    y(i) = i
    Next i

    'Create graph
    Set Graph = ActiveSheet.ChartObjects.Add _
    (Left:=100, Width:=375, Top:=75, Height:=225)

    Graph.Activate

    'Plot Data
    With ActiveChart.SeriesCollection.NewSeries
    .Name = "Data"
    .XValues = x
    .Values = y
    .ChartType = xlXYScatter
    End With
    End Sub

    This is with Excel 2003.
    Any help with be fantastic!
    Jonathan


  2. #2
    Tushar Mehta
    Guest

    Re: Problem with plotting a chart when using arrays as Values and Xvalues

    XL has a limit on how many characters it will accept in the string for
    either the x- or the y-values. I don't remember exactly what it is but
    it is either around 250 characters or 450 characters. You could always
    put the data in a worksheet range and specify the range as the chart
    source.

    --
    Regards,

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

    In article <[email protected]>,
    [email protected] says...
    > Hi
    >
    > I am trying to generate a chart from VBA arrays, but I am finding that
    > once the arrays become too large I get the following runtime error:
    >
    > Run-time error '1004':
    > Unable to set the XValues property of the Series class
    >
    > A simple piece of code which reproduces the error:
    >
    > Option Base 1
    > Sub test()
    > Const nPts As Long = 81 'if increase this to >= 82, doesn't work!
    > Dim x(nPts) As Double, y(nPts) As Double
    > Dim i As Long
    >
    > For i = 1 To nPts
    > x(i) = i
    > y(i) = i
    > Next i
    >
    > 'Create graph
    > Set Graph = ActiveSheet.ChartObjects.Add _
    > (Left:=100, Width:=375, Top:=75, Height:=225)
    >
    > Graph.Activate
    >
    > 'Plot Data
    > With ActiveChart.SeriesCollection.NewSeries
    > .Name = "Data"
    > .XValues = x
    > .Values = y
    > .ChartType = xlXYScatter
    > End With
    > End Sub
    >
    > This is with Excel 2003.
    > Any help with be fantastic!
    > Jonathan
    >
    >


  3. #3
    Jon Peltier
    Guest

    Re: Problem with plotting a chart when using arrays as Values andXvalues

    The limit's about 250. I w ould have thought 256, but apparently it
    reserves a few for = and {} around the array.

    More information:

    http://peltiertech.com/Excel/ChartsH...ChartData.html

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______


    Tushar Mehta wrote:

    > XL has a limit on how many characters it will accept in the string for
    > either the x- or the y-values. I don't remember exactly what it is but
    > it is either around 250 characters or 450 characters. You could always
    > put the data in a worksheet range and specify the range as the chart
    > source.
    >


  4. #4
    Registered User
    Join Date
    11-01-2004
    Posts
    11

    Similar Problem

    Read this thread when I was trying to solve the same problem and got discouraged. But, for future reference, here's the way around it without cluttering your spreadsheet (instead of populating a range, you use a name)

    ActiveWorkbook.Names.Add Name:="Cht1Srs1X", RefersTo:=myArrayX
    ActiveWorkbook.Names.Add Name:="Cht1Srs1Y", RefersTo:=myArrayY
    Xstring = "='" & WorkSheetName & "'!Cht1Srs1X"
    Ystring = "='" & WorkSheetName & "'!Cht1Srs1Y"
    mySeries.XValues = Xstring
    mySeries.Values = Ystring

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1