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.

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. To do this we're first going
to draw a shape on the spreadsheet. The menu option you need to use is
different depending on which version of Excel you use. For Excel 2003 you
need to find or show the Drawing toolbar (choose View
-> Toolbars -> Drawing if it isn't already visible). In Excel
2007 or 2010 you need to click on the Insert tab of the Ribbon.

Excel 2003

Excel 2007

Once you've drawn your shape in an appropriate position on the spreadsheet,
the next step is to assign a macro to it. To do this, right-click on the
shape:

Click the Assign Macro... option to choose which program you want your button to run.
You can also do this with Clip Art or other
images that you've inserted into the worksheet.

Now you need to choose which macro you want to attach to your button. For
us it's a fairly easy choice, as we only have one program in our system.

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:

ForEach 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:

ForEach 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: