Excel: Bubble chart labels - use 4th dimension?

Question

I've got a dataset that has 4 dimensions - lets say they're the following:

+------------------------------+
| Type  | Amnt |Price| Quality |
+-------|------|-----|---------+
|  A    |  15  | 4.0 |   3     |
|  B    |  32  | 4.5 |   4     |
|  C    |  35  | 3.8 |   7     |
+------------------------------+

What I'm looking to do is draw a bubble chart of this data in Excel. I can happily draw a chart showing Price against Quality, and I can then size the bubble according to Amount. However, I can't seem to find a way of being able to label the bubbles using the Type dimension. The default labels seem to just be one of the existing three dimensions used on the graph, which isn't that useful to me.

I know I can add data labels and then manually rename each one, but is there a way of using a 4th dimension for the labels automagically?

Answer 1

Once you've setup the chart with each bubble set to being series named by the Type column (which is a big pain by itself), just run this code, and it will label all of them based on that series name:

Sub BubbleLabel_Click()

Dim BC As ChartObject
Dim i As Integer

Set BC = ActiveSheet.ChartObjects(1)

For i = 1 To BC.Chart.SeriesCollection.Count

  With BC.Chart.SeriesCollection(i)
    .ApplyDataLabels
    .DataLabels.ShowSeriesName = True
    .DataLabels.ShowValue = False
  End With

Next i

End Sub

OR use this code based on a named range "MakeMeAChart" (change as you'd like) on all the data, but not the headers. It will create the chart, with each row a series, and then label them all the first column:

Sub BubbleLabel_Click()

Dim i As Integer

With ActiveSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
  For i = 1 To Range("MakeMeAChart").Rows.Count
    .Chart.SeriesCollection.NewSeries
    .Chart.SeriesCollection(i).Name = Range("MakeMeAChart").Rows(i).Cells(1, 1)
    .Chart.SeriesCollection(i).XValues = Range("MakeMeAChart").Rows(i).Cells(1, 3)
    .Chart.SeriesCollection(i).Values = Range("MakeMeAChart").Rows(i).Cells(1, 4)
    If i = 1 Then .Chart.ChartType = xlBubble3DEffect
    .Chart.SeriesCollection(i).BubbleSizes = "="& Range("MakeMeAChart").Parent.Name _
                & "!" & Range("MakeMeAChart").Cells(1, 2).Address(1, 1, xlR1C1)
  Next i

  For i = 1 To .Chart.SeriesCollection.Count
    With .Chart.SeriesCollection(i)
      .ApplyDataLabels
      .DataLabels.ShowSeriesName = True
      .DataLabels.ShowValue = False
    End With
  Next i
  .Chart.Legend.Delete
End With

End Sub

Note: There are a lot of bugs and tricks to doing bubble charts in VBA.

Answer 2

@Lance's code works well. I just had a problem with setting the ChartType, and there's a missing .Rows(i) when setting the BubbleSize

This is what I came up with:

Sub BubbleLabel_Click()

Dim r As Range

With ActiveSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225).Chart
  'Sacrifical rows that will be lost
  .SeriesCollection.NewSeries
  .SeriesCollection.NewSeries
  .ChartType = xlBubble3DEffect
  .Legend.Delete

  For Each r In [MakeMeAChart].Rows
    With .SeriesCollection.NewSeries
      .Name = r.Cells(1, 1)
      .XValues = r.Cells(1, 3)
      .Values = r.Cells(1, 4)
      .BubbleSizes = "=" & [MakeMeAChart].Parent.Name & "!" & r.Cells(1, 2).Address(1, 1, xlR1C1)
      .ApplyDataLabels
      .DataLabels.ShowSeriesName = True
      .DataLabels.ShowValue = False
    End With
  Next
End With

End Sub