Attaching Labels to Excel Chart

[macutan]

Hi guys,

I have been trying to figure out (unsuccessfully) how to modify the VBA code that microsoft suggested on their site http://support.microsoft.com/kb/213750

Ideally, what i am trying to do is to avoid having the user select the range of labels ahead of running the code as the text I would like on the labels is in a dynamic defined range (let's call it xDateLabel).

Here is what the code I have so far. this works when i select the cells that contain the Date that I would like the points to have, but I am not savvy with VBA syntax so I dont' know how to just point it to the range name xDateLabel.

Any guidance will be greatly appreciated.

Additionally, I have another series within the same chart that also needs to be labeled (using the same method), I guess I need to create two different strings, or can i use the same and redim it?


Sub AttachLabelsToPoints()
'Dimension variables.
Dim Counter As Integer, ChartName As String, xVals As String
' Disable screen updating while the subroutine is run.
Application.ScreenUpdating = False
ActiveSheet.ChartObjects("Chart 1").Activate
'Store the formula for the first series in "xVals".

xVals = ActiveChart.SeriesCollection(6).Formula
'Set xVals = ActiveSheet.Range("xDateLabel")

'Extract the range for the data from xVals.
xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))
xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)
Do While Left(xVals, 1) = ","
xVals = Mid(xVals, 2)
Loop
'Attach a label to each data point in the chart.
For Counter = 1 To Range(xVals).Cells.Count
ActiveChart.SeriesCollection(6).Points(Counter).HasDataLabel = True
ActiveChart.SeriesCollection(6).Points(Counter).DataLabel.Text = Format(Range(xVals).Cells(Counter, 1).Offset(0, -1).Value, "mmm-dd")
' ActiveChart.SeriesCollection(6).Points(Counter).DataLabel.Text = Range(xVals).Cells(Counter, 1).Offset(0, -1).Value

Next Counter

End Sub

 

[Jon Peltier]

It may be easier to parse the series formula using Split(), which returns an array of strings within a variant. The array has elements 0, 1, 2, 3 corresponding to series name, X values, Y values, and plot order.

Sub LabelMyPoints()
  Dim srs As Series
  Dim sFmla As String
  Dim vFmla As Variant
  Dim rLabels As Range
  Dim iPt As Long

  Set srs = ActiveChart.SeriesCollection(6)

  sFmla = srs.Formula
  sFmla = Mid$(sFmla, Len("=SERIES(") + 1)
  sFmla = Left$(sFmla, Len(sFmla) - 1)
  vFmla = Split(sFmla, ",")

  Set rLabels = Range(vFmla(1)).Offset(, -1)

  For iPt = 1 To srs.Points.Count
    srs.Points(iPt).HasDataLabel = True
    srs.Points(iPt).DataLabel.Text = Format(rLabels.Cells(iPt).Value, "mmm-dd")
  Next

End Sub

To loop over a couple series with arbitrary index, try this:

Sub LabelMyPoints()
  Dim srs As Series
  Dim sFmla As String
  Dim vFmla As Variant
  Dim rLabels As Range
  Dim iPt As Long
  Dim iSrs As Long

  For iSrs = 1 to 2

    If iSrs = 1 Then
      Set srs = ActiveChart.SeriesCollection(6)
    Else
      Set srs = ActiveChart.SeriesCollection(12)
    End If

    sFmla = srs.Formula
    sFmla = Mid$(sFmla, Len("=SERIES(") + 1)
    sFmla = Left$(sFmla, Len(sFmla) - 1)
    vFmla = Split(sFmla, ",")

    Set rLabels = Range(vFmla(1)).Offset(, -1)

    For iPt = 1 To srs.Points.Count
      srs.Points(iPt).HasDataLabel = True
      srs.Points(iPt).DataLabel.Text = Format(rLabels.Cells(iPt).Value, "mmm-dd")
    Next

  Next

End Sub

 

[macutan]

Hi Jon, thanks for your reply,... I ended up using the below, couldn't figure out how to implement your split()... The below works too. Thanks for getting back.
Paco

Public Sub AttachLabelsToPointsTEST()
'Dimension variables.
Dim Counter As Integer, ChartName As String
' Disable screen updating while the subroutine is run.
Application.ScreenUpdating = False

ActiveSheet.ChartObjects("Char 1").Activate
'Attach a label to each data point in the chart.
For Counter = 1 To Range("xDateLabel").Cells.Count
ActiveChart.SeriesCollection(6).Points(Counter).HasDataLabel = True
ActiveChart.SeriesCollection(6).Points(Counter).DataLabel.Text = Format(Range("xDateLabel").Value2(Counter, 1), "mmm-dd")
Next Counter

For Counter = 1 To Range("yAmpText").Cells.Count
ActiveChart.SeriesCollection(7).Points(Counter).HasDataLabel = True
ActiveChart.SeriesCollection(7).Points(Counter).DataLabel.Text = Format(Range("yAmpText").Value2(Counter, 1), "0 bps")
Next Counter
End Sub