BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
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! 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:
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:
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!