Excel VBA only show labels for each series' max value

Question

I am trying to create a macro which loops through series in a chart and only shows the maximum / minimum label dependent on what the max / min value is.

Some of the series will only have negative values and in these cases I wish to only show the minimum datapoint label, and vice versa for series with 0 or greater values.

The code I have so far is:

Sheets("Curve").ChartObjects("Chart 14").Activate
For Each serie In ActiveChart.SeriesCollection
        Dim pointCount As Integer
        Dim pointValues As Variant
        pointCount = serie.Points.Count
        pointValues = serie.Values
        For pointIndex = 1 To pointCount
          If pointValues(pointIndex) < 1000 Then
            serie.Points(pointIndex).HasDataLabel = True
          End If
        Next pointIndex
      Next serie
End Sub

Which works fine when I manually enter the threshold, but I want to replace the '1000' with Max(series) value instead, so that each series in the chart has only one label visible.

One way would be to execute the For..Next loop twice, once to calculate the max point value in the PointValue array, once to set the label if the point equals the max point value. — chuff
– chuff, Commented May 24, 2013 at 12:06

chuff · Accepted Answer · 2013-05-28 15:17:20Z

1

The following modified routine includes MaxPoint, MaxPointIndex, MinPoint, and MinPointIndex variables which are calculated in the For loop on each serie's points. It then sets the label for the maximum point if the series has only positive value and minimum point otherwise.

  Option Explicit

  Sub chart()
  Dim serie As Variant
  Dim Pointindex As Long
  Dim MaxPoint As Long
  Dim MaxPointIndex As Long
  Dim MinPoint As Long
  Dim MinPointIndex As Long
  Sheets("Curve").ChartObjects("Chart 14").Activate
  For Each serie In ActiveChart.SeriesCollection
      Dim pointCount As Integer
      Dim pointValues As Variant
      pointCount = serie.Points.Count
      pointValues = serie.Values
      MinPoint = 10000              'set to value greater than any point in any serie
      MaxPoint = 0
      For Pointindex = 1 To pointCount
          If pointValues(Pointindex) > MaxPoint Then
              MaxPoint = pointValues(Pointindex)
              MaxPointIndex = Pointindex
          ElseIf pointValues(Pointindex) < MinPoint Then
              MinPoint = pointValues(Pointindex)
              MinPointIndex = Pointindex
          End If
      Next Pointindex
      If MinPoint >= 0 Then
          serie.Points(MaxPointIndex).HasDataLabel = True
      Else
          serie.Points(MinPointIndex).HasDataLabel = True
      End If
  Next serie
  End Sub

edited May 28, 2013 at 15:17

answered May 24, 2013 at 12:16

chuff

5,9061 gold badge23 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dibstar Over a year ago

Thanks for this, but I can't seem to get this to apply the datalabels to my chart: I get an error message at "serie.Points(MaxPointIndex).HasDataLabel = True" saying runtime error 1004, inmvalid parameter.

chuff Over a year ago

Hmm, I'm not getting the error. It's difficult to debug this kind of thing from a distance. If you are comfortable with it, could you upload your file to www.wikisend.com and share the link here? In any event, what version of Excel are you using and what style of chart is it? (Also, I've modified the code slightly to put minimum value label if series has negative value label and maximum value label otherwise.)

Jon Peltier · Accepted Answer · 2020-04-24 02:40:46Z

I have an alternative approach, which does not require VBA. It adds an extra series with an invisible data point at the max and this point has a data label. It also changes dynamically if the data changes and a different point is maximum, without having to rerun the VBA procedure.

For each series in the chart you'll need to use a range the same size as the Y values of the series.

Assume the original Y values for a given series is in D2:D10, and we'll use G2:G10 for our extra data. In G2 enter =IF($D2=MAX($D$2:$D$10),$D2,NA()) and fill this down to G10. Modify this formula for the case where you might instead look for the minimum value if everything is negative.

Copy G2:G10 and hold CTRL while you select the X values for this series. Copy. Select the chart, use Paste Special, and select Add data as new Series, in Columns, Categories in First Column.

Select the added series, which is one point (unless there are two points at the maximum), format to have no lines and no markers. Add your data labels to this series.

Repeat for the other series in the chart.

Collectives™ on Stack Overflow

Excel VBA only show labels for each series' max value

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related