Adding data labels to a chart using VBA
Part three 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 (this blog)
  4. Adding Data Labels to a Chart Using VBA Loops
  5. Improving the VBA system for chart data labels

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.

Using VBA in Excel to Modify Charts

If you've been following this series of articles up to this point, you should now be in a position to start writing VBA code that will apply data labels to our scatter chart.  If you're new to this programming lark, don't worry!  If there are bits that you don't understand straight away, just go with the flow for the time being.  You can always come back to look at things in detail later on, and remember: copying and pasting isn't cheating!

Declaring Some Variables

The first things we're going to add to the subroutine that we created in the previous article are some variables.  Variables are handy storage spaces to keep track of the important objects and values in our program.  Not all of the variables we're going to use are essential, but they will make our code easier to write and understand.  Ready?  Then type the following code into your subroutine:

Sub CreateDataLabels()

'holds the entire film data series in the chart

Dim FilmDataSeries As Series

'holds one cell at a time

Dim SingleCell As Range

'holds the full list of cells containing film names

Dim FilmList As Range

'keeps track of which datapoint we're labelling

Dim FilmCounter As Integer

End Sub

The lines shown in green are comments in our code - you can add your own comments by typing in an apostrophe followed by whatever you want your comment to say.  Comments are optional, but it's a good idea to remind yourself what you're doing when you come to look at your code later.  You can see more on the art of commenting code in another Wise Owl blog.

The variable declarations are the lines that begin with the word Dim.  You can give your variables different names if you want to, but don't use spaces and try to avoid using punctuation characters.

After the variable name we say what type of information or object we're going to store in it.  We've created two variables that can hold a reference to a Range of cells, one variable that can hold a reference to a Series of data in a chart, and one variable that can hold an Integer value (a whole number in other words).

Setting the Values of Data Variables

Now that we've declared a few variables we can use them to hold values or references to objects.  The first thing that we'll do is set our FilmCounter variable to the number 1.  Add the following line of code underneath your variable declarations, but above the line that says End Sub:

'set the counter to start at 1

FilmCounter = 1

Variables that can hold a single piece of data, such as a number or a bit of text, are fairly easy to deal with.  However, our remaining variables are object variables, which are a little bit more tricky.

Setting References to Objects Using Variables

We're now going to use our FilmList variable to hold the list of cells containing the film names.  In Excel VBA any cell, or block of cells, is referred to as a range.  Add the following line to your subroutine:

'set a reference to the cells containing the list of films

Set FilmList = Range("A2", "A11")

With object variables we have to use the word Set when we're trying to store a reference to something.  In our example our film names are in cells A2 to A11.  You can feel free to change the cell references in the above code if your worksheet is arranged differently.

The next variable we're going to set is going to hold a reference to the series of data stored in our chart.  Add the following line to your code:

'set a reference to the chart data series

Set FilmDataSeries = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)

Referring to the exact part of the chart we want is quite complicated, but here's a breakdown of what the above line means:

  • ActiveSheet - Because our chart sits inside a worksheet, we need to start by saying which worksheet the chart is in.  The ActiveSheet refers to whichever sheet we are in when we run our code.
  • ChartObjects(1) - A worksheet might contain many chart objects.  This part of the code says to look at the first chart object that exists in that sheet.  As we only have one chart in our worksheet, we can be pretty confident that it's number 1!
  • Chart - Somewhat strangely, a ChartObject is only a container that a chart sits inside.  This part of the code says to look at the actual chart.
  • SeriesCollection(1) - A chart can have many series of data.  This part of the code says to look the first series of data in the chart.  As we only have one data series it's fairly easy to work out which one we need!

Referring to a chart is actually much easier if it sits on its own separate chart sheet tab.  If you had a chart sheet tab called Chart1, you could change the line above so that it reads:

Set FilmDataSeries = Charts("Chart1").SeriesCollection(1)

Much easier!

If you've been keeping up so far you should be at the stage where we're ready to start adding the data labels to the chart.  This is the clever bit and deserves a separate article, but first, here's a recap on what you should have so far:

Option Explicit

Sub CreateDataLabels()

Dim FilmDataSeries As Series

Dim SingleCell As Range

Dim FilmList As Range

Dim FilmCounter As Integer

FilmCounter = 1

Set FilmList = Range("A2", "A11")

Set FilmDataSeries = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)

End Sub

This blog has 0 threads Add post