Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
547 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
|
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! And we offer training in Excel and also courses in VBA should you need back-up ...
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).
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.
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:
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
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.