CODEWITHSUNDEEP
excelvbagraph
Eric Aguado
Eric Aguado

Reputation: 1

Excel-VBA How do I Add a Static Line onto a Graph?

I'm pretty new to this stuff so it's been rough. I'm trying to create a script that plots values collected from testing and then 2 static values (a requirement spec for QC and for Production) for comparison. I have been able to create a chart that displays the testing values, but I can't seem to get a straight line for the QC and Production specifications unless I type in each Array value like this:

.Values = Array(19, 19, 19, 19)

I want the line to be somewhat dynamic in length to ensure that it stretches across the graph no matter how many rows/columns there are.

Any help in what direction I should take or a better way to do this would be very much appreciated!

    Dim myChtObj As ChartObject
    Dim rngChtData As Range
    Dim rngChtXVal As Range
    Dim iColumn As Long
    Dim iRow As Long

    ' make sure a range is selected
    If TypeName(Selection) <> "Range" Then Exit Sub

    ' define chart data
    Set rngChtData = Selection

    ' define chart's X values
    With rngChtData
        Set rngChtXVal = .Columns(1).Offset(1).Resize(.Rows.Count - 1)
    End With

    ' add the chart
    Set myChtObj = ActiveSheet.ChartObjects.Add _
        (Left:=250, Width:=375, Top:=75, Height:=225)

    With myChtObj.Chart

        ' make an XY chart
        .ChartType = xlXYScatterLines

        ' remove extra series
        Do Until .SeriesCollection.Count = 0
            .SeriesCollection(1).Delete
        Loop

        ' add series from selected range, column by column
        For iColumn = 3 To rngChtData.Columns.Count
            With .SeriesCollection.NewSeries
                .Values = rngChtXVal.Offset(, iColumn - 1)
                .XValues = rngChtXVal
                .Name = rngChtData(1, iColumn)
            End With
        Next

      Set ser = .SeriesCollection.NewSeries
      ser.Values = Array(19, 19, 19, 19)
      ser.XValues = rngChtXVal
      ser.Name = "QC Retraction"

    End With
End Sub

Current Output

Current Output What I would like to Output

What I would like to Output

Upvotes: 0

Views: 803

Answers (1)

Tim Williams
Tim Williams

Reputation: 166560

You only need to add a series with two points - one at the minimum x axis value, and one at the maximum value (with the same y value). Then format that line as required.

For example:

  Set ser = .SeriesCollection.NewSeries
  .Legend.LegendEntries(.SeriesCollection.Count).Delete 'remove from legend
  With ser
    .Values = Array(19, 19)
    .XValues = Array(myChtObj.Chart.Axes(xlCategory).MinimumScale, _
                     myChtObj.Chart.Axes(xlCategory).MaximumScale)
    .Name = ""
    .MarkerStyle = -4142  'no markers
    .Format.Line.ForeColor.RGB = vbBlack
    .Points(2).ApplyDataLabels
    .Points(2).DataLabel.Format.TextFrame2.TextRange.Characters.Text = "QC Retraction"
  End With

Edit - adding the line may itself change the x-axis limits, so you might want to set those directly before adding the series.

Upvotes: 2

Related Questions