Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Scatter graph labels in VBA (excel xp)

    I need to know if it's possible to draw the 3 lines i have on my graph using code. The x and y coordinates will be given to me and i need to plot them on the graph. I will have 50 of these chart sheets so i want to see if there is a way i can write a macro to draw them.

    Also, I only have 2 series on the scatter graph. There will probably be 20-30 and i'm afraid that the labels will overlap and i will have to manually move them about. Is there a way to control the data labels from not touching each other? Thank you for the help.
    Attached Files Attached Files

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Scatter graph labels in VBA (excel xp)

    > draw the 3 lines
    Just add a new series for each line (without points, just lines)

    > Is there a way to control the data labels from not touching each other
    Excel just places them on the right (but you can change this, look at the properties of DataLabels) and makes no effort to keep them from touching. You can specify the top and left of each DataLabel, but it would be a large effort to figure out how to keep them from touching and still make them readable. Just figure out the point density and turn off the DataLabels when it gets too high. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Scatter graph labels in VBA (excel xp)

    Another thought regarding datalabels. If I have a few "important" data points on my chart that I really want to label, then I will create another series with just these points, turn-off markers & lines, and turn-on data labels.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Scatter graph labels in VBA (excel xp)

    I know how to add a new series but not "without points, just lines". Can you help me with that. thanks

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Scatter graph labels in VBA (excel xp)

    I'm working on an example. Meantime, record a macro that adds a new series and then formats that series setting Markers to None and Lines to Automatic. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Scatter graph labels in VBA (excel xp)

    ok, thanks

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Scatter graph labels in VBA (excel xp)

    Here's some sample code. See attachment to watch it plot and view the entire macro, but here is the plotting stuff:
    <pre> With Charts.Add
    .ChartType = xlXYScatter
    ' Create main scatter plot with just markers
    .SetSourceData Worksheets("Sheet1").Range("A2:B21")
    With .Axes(xlCategory)
    .HasMajorGridlines = False
    .HasMinorGridlines = False
    End With
    With .Axes(xlValue)
    .HasMajorGridlines = False
    .HasMinorGridlines = False
    End With
    .HasLegend = False
    .PlotArea.ClearFormats
    With .Axes(xlValue)
    .MinimumScale = yMin
    .MaximumScale = yMax
    .Crosses = xlCustom
    .CrossesAt = yMin
    .MinorTickMark = xlOutside
    End With
    With .Axes(xlCategory)
    .MinimumScale = xMin
    .MaximumScale = xMax
    .Crosses = xlCustom
    .CrossesAt = xMin
    .MinorTickMark = xlOutside
    End With
    ' Put value datalabels on min & max point
    With .SeriesCollection.NewSeries
    .XValues = Array(xLine1(1), xLine2(1))
    .Values = Array(yLine1(1), yLine2(1))
    .Border.LineStyle = xlNone
    .MarkerStyle = xlNone
    .ApplyDataLabels xlDataLabelsShowValue
    .DataLabels.NumberFormat = "0.0"
    End With
    ' Plot regression line & a vertical line & a horizontal line
    For i = 2 To 4
    With .SeriesCollection.NewSeries
    .XValues = Array(xLine1(i), xLine2(i))
    .Values = Array(yLine1(i), yLine2(i))
    .Border.LineStyle = xlContinuous
    .Border.ColorIndex = 1
    .MarkerStyle = xlNone
    End With
    Next i
    .Deselect
    End With
    </pre>

    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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