Adding data labels to a chart using VBA
Part five of a five-part series of blogs

Adding basic data labels to a chart can be done without using any programming code. This blog, however, shows you how to fine-tune Excel chart labels using VBA (Visual Basic for Applications) macros.

  1. The Problem With Labelling the Data Points in an Excel Scatter Chart
  2. Setting Up the example for labelling data points in a chart
  3. Using VBA in Excel to Modify Charts
  4. Adding Data Labels to a Chart Using VBA Loops
  5. Improving the VBA system for chart data labels (this blog)

Posted by Andrew Gould on 08 June 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Improving the System

This final article in this series describes a few ways of improving our program, including a much better way to run it.

Creating a Button to Run Our Program

Rather than having to go into the VB Editor to run our code we can create a clickable button on the spreadsheet itself.  To do this we're first going to draw a shape on the spreadsheet.  The menu option you need to use is different depending on which version of Excel you use.  For Excel 2003 you need to find or show the Drawing toolbar (choose View -> Toolbars -> Drawing if it isn't already visible).  In Excel 2007 or 2010 you need to click on the Insert tab of the Ribbon.

Excel 2003 shapes Excel 2007 shapes
Excel 2003 Excel 2007

Once you've drawn your shape in an appropriate position on the spreadsheet, the next step is to assign a macro to it.  To do this, right-click on the shape:

The right-click menu option

Click the Assign Macro... option to choose which program you want your button to run.  You can also do this with Clip Art or other images that you've inserted into the worksheet.


Now you need to choose which macro you want to attach to your button.  For us it's a fairly easy choice, as we only have one program in our system.

Choosing a macro

You can either double-click on the name of the macro, or click on it once and then click OK


You might want to add some text to the button while it's still selected (just start typing to do this).  When you've added a sensible name you can click away from the button and it will then be ready for use!

Adding text to a button The final clickable button
You can add a sensible name to the button. Click away from the button to activate it.

Making the System Work With Different Sized Lists

The way we've created our system means that if we added new movies to the list we'd have to go in to our code and change the cell references that we typed in earlier.  We can do far better than that!  We're going to edit our code so that no matter how long our list is, our system will always pick up all of the cells in it.

To start with, go back to the VB Editor - you can press ALT + F11 to do this.  Now identify the line that sets our FilmList variable and change it to match the code shown below:

Set FilmList = Range("A2", Range("A1").End(xlDown))

All we've done here is remove the reference to cell "A11", and replaced it with a bit of code that tells Excel to find the cell at the bottom of the list.  You should find that if you can now label any number of data points without having to change the code at all.

Making the System Work With Multiple Charts

What if your worksheet contains multiple charts all based on the same list of films but comparing different ranges of values?

A bigger range of numbers

With more columns of data we could create more scatter charts to compare them.

At the moment our system only changes the labels of the first chart in the worksheet, but it's not too much effort to make our code work for every chart in the worksheet, no matter how many there are.

A multitude of charts

We can get our code to label all of the charts in a worksheet.

The technique we use to look at all of the charts is the same as the one we used to look at all of the cells in the list of films: we loop over the collection of chartobjects, doing something to each on in turn.  To begin with, declare a new variable at the top of the subroutine that can hold a reference to a chartobject:

Dim SingleChart As ChartObject

We then need to tell the subroutine to start looping over the charts.  Find the line that sets the FilmList variable and then add the following line just beneath it:

For Each SingleChart In ActiveSheet.ChartObjects

Now we need to move where we set our FilmCounter variable so that it gets reset to 1 each time we look at a different chart.  Cut and paste the line that sets the FilmCounter variable so that it appears underneath the line you've just added:

For Each SingleChart In ActiveSheet.ChartObjects

FilmCounter = 1

Now look at the line that sets the FilmDataSeries variable.  We need to change this so that it doesn't always point to the first chart that it finds in the worksheet.  Change the line so that it looks like this:

Set FilmDataSeries = SingleChart.Chart.SeriesCollection(1)

Finally, we need to add the line that tells Excel to move on to look at the next worksheet.  Find the line that tells Excel to look at the next SingleCell and add the following line underneath it:

'move on to the next chart in the worksheet

Next SingleChart

And that's it!  All we've done is added three new lines of code and tweaked a couple of others and we now have a system that will work for as many charts and as many lines of data as you have in the worksheet!

If you missed any of the steps above, you can download a complete example by clicking here, and you can see the finished subroutine below:

Sub CreateDataLabels()

Dim FilmDataSeries As Series

Dim SingleCell As Range

Dim FilmList As Range

Dim FilmCounter As Integer

Dim SingleChart As ChartObject

Set FilmList = Range("A2", Range("A2").End(xlDown))

For Each SingleChart In ActiveSheet.ChartObjects

FilmCounter = 1

Set FilmDataSeries = SingleChart.Chart.SeriesCollection(1)

FilmDataSeries.HasDataLabels = True

For Each SingleCell In FilmList

FilmDataSeries.Points(FilmCounter).DataLabel.Text = SingleCell.Value

FilmCounter = FilmCounter + 1

Next SingleCell

Next SingleChart

End Sub

This blog has 0 threads Add post