Using VBA in Excel to Modify Charts
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 ...
Declaring Some Variables
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()
Dim FilmDataSeries As Series
Dim SingleCell As Range
Dim FilmList As Range
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).
Setting the Values of Data Variables
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:
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.
Setting References
to Objects Using Variables
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 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 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:
- ActiveSheet - Because our chart sits inside
a worksheet, we need to start by saying which worksheet the
chart is in. The ActiveSheet refers to whichever sheet we
are in when we run our code.
- ChartObjects(1) - A worksheet might contain
many chart objects. This part of the code says to look at
the first chart object that exists in that sheet. As we
only have one chart in our worksheet, we can be pretty confident
that it's number 1!
- Chart - Somewhat strangely, a
ChartObject is only a container that a chart sits
inside. This part of the code says to look at the actual
chart.
- SeriesCollection(1) - A chart can have many
series of data. This part of the code says to look the
first series of data in the chart. As we only have one
data series it's fairly easy to work out which one we need!
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
