Adding data labels to a chart using VBA
Part two 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 (this blog)
  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

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.

Setting Up the Example

In the first part of this blog series we described the problem with labelling data points in a scatter chart.  In the following parts we're going to show you how to use some VBA code to solve the problem.  Wise Owl recently used a simlar technique to create an analysis tool for the marketing department of a large IT organisation.  If you're fairly new to VBA (maybe you've recorded a couple of macros and edited them?), you might find some of the ideas in these articles a little complicated, but don't let that put you off!  Try to follow along anyway; you'll pick up some good techniques for VBA in general and maybe even awaken your inner geek!

If you don't like typing, feel free to copy and paste the code from this page directly into your module - it's not considered cheating!

The Starting Point

The starting point for this example is an Excel file containing a simple table of data and with a scatter chart embedded on the same worksheet.  You can use your own spreadsheet if you have one available, or download our simple list of films by clicking here.

The basic spreadsheet

This is the starting point for our example - a simple table of data, with a chart on the same worksheet.

Once you have your Excel file open, our next job is to get into the Visual Basic Editor application.  Whichever version of Excel you're working in, you can hold down the ALT key and press F11 to open the VB Editor.

Before you can start writing your code, you need to insert a module.  To do this, from the menu select: Insert -> Module

The VB Editor

This is what you should see having followed the above steps. If you don't see the phrase Option Explicit at the top of the page, feel free to type it in.


Writing a Subroutine

The next job is to create a program, or Subroutine, that we can later run to label our chart.  To do this, start typing the following into the module you created earlier:

Option Explicit

Sub CreateDataLabels

The name of your subroutine isn't that important, but it's nice if you can think of a descriptive name for it.  Avoid using spaces and punctuation characters in your subroutine name.  If you press Enter at the end of the line shown above, you should get the following:

Option Explicit

Sub CreateDataLabels()

End Sub

The VB Editor fills in the required extra bits of code for you automatically.  Helpful or what?  So far, so good, but things get a bit more complicated from here on in.  Read on if you're still feeling brave!

This blog has 0 threads Add post