This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. The Problem With Labelling the Data Points in an Excel Scatter Chart Setting Up the Example (this blog) Using VBA in Excel to Modify Charts Adding Data Labels to a Chart Using VBA Loops Improving the System

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.

The Problem With Labelling the Data Points in an Excel Scatter Chart
Setting Up the Example (this blog)
Using VBA in Excel to Modify Charts
Adding Data Labels to a Chart Using VBA Loops
Improving the System

Posted by Andrew Gould on 08 June 2011

In this blog

The Starting Point
Writing a Subroutine

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! You'll pick up even more tips, of course, by attending one of our Excel training courses or training courses in VBA ...

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.

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

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!

Parts of this blog
The Problem With Labelling the Data Points in an Excel Scatter Chart Setting Up the Example (this blog) Using VBA in Excel to Modify Charts Adding Data Labels to a Chart Using VBA Loops Improving the System