CODEWITHSUNDEEP
vbaexcelcharts
Dibstar
Dibstar

Reputation: 2364

Excel VBA only show labels for each series' max value

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.

Upvotes: 2

Views: 5038

Answers (2)

Jon Peltier
Jon Peltier

Reputation: 6063

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.

Upvotes: 1

chuff
chuff

Reputation: 5866

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

Upvotes: 1

Related Questions