Question: Extract a Category Label for a Point
I came across a question in the Excel Reddit, Is there a way to select a chart series point and have the label name of that point be copied into a cell?
Answer 1: Chart with Simple Axis
The short answer is the following function: you get .XValues property of the series, which is an array of category labels, and find the element of the array for the particular point:
Function GetCategoryLabel(cht As Chart, iSrsNum As Long, iPtNum As Long) As String
Dim srs As Series, vCats As Variant
Set srs = cht.SeriesCollection(iSrsNum)
vCats = srs.XValues
GetCategoryLabel = vCats(iPtNum)
End FunctionYou call it like this, passing in the chart, series number, and point number:
Sub TEST_GetCategoryLabel()
Dim s As String
Dim cht As Chart, iSrs As Long, iPt As Long
Set cht = ActiveSheet.ChartObjects(1).Chart
iSrs = 1
iPt = 3
s = GetCategoryLabel(cht, iSrs, iPt)
s = "Category Label for Series " & iSrs & " - Point " & iPt & ":" & vbNewLine & s
MsgBox s
End SubThis doesn’t stick the label into a cell, like the question asked, but displays the label with a message box. As shown below, Series 1 – Point 3 has the label “C”:
Answer 2: Chart with Multi-Tier Category Labels
It turns out that the chart was a pivot chart, based on a pivot table with several fields in the rows area. Each field contributes a tier of labels to the category axis. The screenshot below shows a Table of data, a Pivot Table based on this Table, and above both a Pivot Chart, with a two-tiered category axis.
If you use the simple approach above, the .XValues don’t distinguish among the tiers, and the labels from different tiers are put together, separated by spaces. In the chart below, Series 1 – Point 3 has the compound label “Alpha C”, according to the simple algorithm.
We need a much more detailed VBA approach. We have to parse the series formula, get the address of the range containing the category labels, then get the corresponding range, figure out how many columns of labels there are, then find the label in each column corresponding to the point. This is complicated by the empty cells in the pivot fields, so we have to keep looking upward until we find a cell with a label.
Here is the more intricate function that extracts an array of category labels:
Function GetCategoryLabels(cht As Chart, iSrsNum As Long, iPtNum As Long) As Variant
Dim srs As Series
Dim sFmla As String, vFmla As Variant
Dim rCats As Range, vCats As Variant, vOutput As Variant
Dim iRow As Long, iCol As Long, nRows As Long, nCols As Long
Set srs = cht.SeriesCollection(iSrsNum)
sFmla = srs.Formula
sFmla = Mid$(Left$(sFmla, Len(sFmla) - 1), InStr(sFmla, "(") + 1)
vFmla = Split(sFmla, ",")
Set rCats = Range(vFmla(LBound(vFmla) + 1))
vCats = rCats.Value2
ReDim vOutput(1 To UBound(vCats, 2))
For iCol = 1 To UBound(vCats, 2)
For iRow = iPtNum To 1 Step -1
If Len(vCats(iRow, iCol)) > 0 Then
vOutput(iCol) = vCats(iRow, iCol)
Exit For
End If
Next
Next
GetCategoryLabels = vOutput
End FunctionIt’s called in a similar way:
Sub TEST_GetCategoryLabels()
Dim v As Variant, i As Long, s As String
Dim cht As Chart, iSrs As Long, iPt As Long
Set cht = ActiveSheet.ChartObjects(1).Chart
iSrs = 1
iPt = 3
v = GetCategoryLabels(cht, iSrs, iPt)
s = v(LBound(v))
For i = LBound(v) + 1 To UBound(v)
s = s & ", " & v(i)
Next
s = "Category Labels for Series " & iSrs & " - Point " & iPt & ":" & vbNewLine & s
MsgBox s
End SubWhen we put all the category fields into the rows area of the pivot table, we get three labels for Series 1 – Point 3, “Alpha”, “A”, and “III”:
When we put the fields “Greek” and “Latin” into the rows area of the pivot table and move “Roman” to the columns area, we get two labels for Series 1 – Point 3, “Alpha” and “C”:
When we leave the field “Greek” in the rows area of the pivot table and put “Latin” and “Roman” to the columns area, we get a single label for Series 1 – Point 3, “Gamma”:
