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

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.

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

Posted by Andrew Gould on 08 June 2011

In this blog

Creating a Button to Run Our Program
Making the System Work With Different Sized Lists
Making the System Work With Multiple Charts

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. You can see how to add clickable buttons to an Excel worksheet here.

When you've created your button, choose to assign it to the button you've created.

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!


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?

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.

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

If you like what you've seen here, maybe it's time to teach yourself VBA or even brush up your Excel skills?

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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Improving the System

Creating a Button to Run Our Program

Making the System Work With Different Sized Lists

Making the System Work With Multiple Charts

Head office

London

Manchester