Articles on all things data...

Creating Excel Macros (part 2)

 April 12

by Andrew Richards

In the first part of this tutorial series on writing Excel macros, we created a very simple macro and looked at how to run it from your workbook.

In this part, we'll dig a little more into the anatomy of a macro - looking at what your options are as you create a new macro, at interesting ways to make your macro run, at different types of procedure you can create, and at some basic tips for making your macros more useful.

First, let's back up a little, and refresh the key points that we covered last time.

Recap of the essentials

  • To create our first macro, we created a new Excel workbook.
  • Then we pressed ALT and F11 to get to the code editor
  • Then we clicked Insert --> Module from the menu bar
  • Then we typed our code into the window.
  • Finally, when we saved the file, we made sure to save it as a macro-enabled workbook, with a .xlsm extension

Let's take a look at what we actually were doing in each of these steps.

A guided tour of the VBA editor

When you pressed ALT and F11 together, you were taken to the Visual Basic Integrated Development Environment, or VBA IDE (or just "macro window" or "code window") for short.

This IDE is a separate thing to Excel - it's a tool in its own right, which is supplied with Excel - and with Word, PowerPoint, Access, Outlook and other applications - specifically to allow you to write and edit code. In each of these applications, you can use that same shortcut - ALT and F11 - to open this code window.

And just as Excel can be customised by turning on various windows and toolbars to suit your needs, so that's true of the VBA window too. Most commonly, you'll have a window or two on the left when you open the VBA window - these are the Project Explorer and the Properties window, as shown here:

The Project Explorer shows you all the various places where your VBA code might live (we'll cover very shortly exactly which of these places you should use for each type of code).

The Properties Window shows you, well, some of the properties of the object you've got selected in your Project Explorer.  For example, if you've got a worksheet selected in the Project Explorer window, the Properties window will show you properties such as the name of the sheet, and whether it's currently visible or hidden.

In my screenshot above, there's another window visible, called the Immediate Window. This is a really useful tool which helps you test your code and try things out "immediately" - ie without having to run a whole macro. Again, we'll come on to this later.

It's likely that you won't have the Immediate Window showing, but you can turn it - and both of the other windows - on and off by going to the View menu and selecting it from the list. Alternatively, each has a shortcut:

  • Press CTRL - G to turn the Immediate Window on
  • Press CTRL - R to turn the Project Explorer on
  • Press F4 to turn the Properties Window on

The other window - the biggest one - is the actual place where you write your code. In here you'll put your various procedures, one below the other. You can see in the screenshot above the procedure called "ShowTheDate" which we wrote in part one of this guide.

Where to write your macro code

In the Project Explorer, you will see each file that you've got currently open, and below each file, one or two folder icons grouping objects by type. 

Firstly, you'll see "Microsoft Excel Objects", within which is each worksheet, plus something called "ThisWorkbook".

And secondly, you may see "Modules" - although if you've not yet written or recorded a macro, this probably won't be visible.

Each of these things - the worksheets, the workbook and any modules - is, actually a place where you can write your VBA code. It may also be somewhat confusing to hear that each of these is actually a module - even though only some of the objects appear in the "modules" folder. Here's how I'd define what is actually meant by "a module":

A module is any place where you can write and store your VBA code.

So, you can store your code within the module called, say, Sheet1. Or within the module called ThisWorkbook. Or within a module called Module1, as in my screenshot above. 

Does it matter in which of these places you store your code? Yes, absolutely it does.

Class modules in Excel

If you want your macro to respond every time the user does something specific - opens a file, changes the contents of cell A5, prints a worksheet or whatever - then you need to write the code in the module for the object in question.

So, if you want to write a macro which writes a log entry every time the user changes any value in Column B of Sheet 1, you need to write that code in the module for Sheet1.

If you want to display a message to the user every time they switch to Sheet 4, you need to write the code in the module called Sheet4.

And if you want to display a warning to the user whenever they open a workbook, you need to write that code in the module called ThisWorkbook for the file in question. 

Each of these is an example of what's called a "Class Module" - that is, code attached to a real object such as a worksheet or workbook.

Standard modules

But it's probably more common to want to do something through code which isn't attached to any particular worksheet. For example, you may want to create a new function which works a bit like the SUM function, only instead of adding the selected cells, it multiplies them. But you want that to work for any sheet, not just one. 

Or, you may want to create a macro which copies the current selection and pastes it into a new Word document, then formats it in a particular way. Again, that "current selection" could be on any worksheet, or even in any file.

In these cases, you must make sure that your code is not attached to any one object, but rather is generic. And you do that by putting it in what's called a "Standard module" - that is, one of those modules that appears under the "Modules" folder. As you saw in part one of this guide, if you don't yet see any Modules folder or any standard modules, you can create one by clicking on the Insert menu, and selecting "Module".

If you're writing your code to respond to something the user does on Sheet 2, then you MUST put the code in the class module called Sheet2. Similarly, if you want to respond to the user doing something on Sheet 1, then the code MUST go into the class module called Sheet1.

But if your code is designed to apply to any object, or do anything more generic, then it must go into a Standard Module. Any standard module. It doesn't matter - in theory - whether you write all your generic code into one large standard module, or write each procedure in its own standard module. So, given that, how should you organise your "generic" code? 

Think of each Standard module as a drawer in a filing cabinet (and, to stretch the analogy, the "Modules" folder at the top could be thought of as the whole filing cabinet). It doesn't matter where you store stuff - you can bung everything into one drawer, or you can have lots of folders, with one document in each. What actually matters is that you can actually find what you've filed away. So if you've got a lot of code that you've created over a period of time, it would probably be sensible to divide it up into different modules. 

Perhaps have one module which deals with interactions with Word, and another that deals with interactions with PowerPoint. A third might contain functions like the Multiply function that I suggested above. And then, you can name each module to something sensible to help you find the code you're looking for.

Of course, it may very well be that your workbook only contains a handful of procedures, and in that case it probably makes sense to just have a single module and put the code in there.

The anatomy of a procedure in VBA

When you write a procedure, the structure of that procedure always stays the same, even if the specific content changes. Let's take a look at that structure.

Here's an example of a procedure:

An example of an Excel macro

Here's a relatively simple Excel macro. What's important here isn't the code, but the structure of the macro.

Let's break this down bit by bit to see how each piece fits together.

The first line of code is this:

Public Sub AddDetailsToCell(Optional AddToCell As Range)

which is matched at the end of the code with this:

End Sub

These two lines mark the beginning and end of the block of code. The last line is pretty straightforward, simply marking the end of the code, but the first line needs a bit more explanation.

The first word - Public - means that this is a procedure which can be used from anywhere within this file - or indeed any other file, as long as this one (containing the code) is open.

Sometimes you'll see the word Private used instead, which means that this procedure can only be used by other procedures elsewhere in this module, but can't be used by other modules, or by other Excel files. And often, you'll find that there's neither Private nor Public specified - in which case the default behaviour applies, which is Public.

Note:

Technically, the words Private and Public are known as "Access modifiers" as they control which other procedures can "access" this one. But don't get too worried about the terminology at this point - unless you're preparing for the world's weirdest quiz, in which case you need to get out more.

Subs vs Functions

After the word Public in our sample is the type of procedure - in this case Sub

Although there are other types, the two most common types of procedure you're likely to come across are Subs and Functions, and each serves a different purpose.

A sub is designed to perform some task. That task may be very simple - perhaps asking the user for their full name, and adding this name to the footer of a worksheet. Or, it may be more complex - connecting to some external system, extracting data which meets the user's requirements based on the entries on a particular worksheet, then creating a chart which displays a visual analysis of this data, highlighting any values of special interest and then putting the kettle on to make a coffee.

I may have exaggerated the ability of a macro to make coffee.

But the point is that - whether it's simple or complex - the job of a Sub is to perform tasks.

The job of a Function is to calculate something, and return an answer (often returning the answer to the cell where you type that function). You're no doubt already familiar with Functions - you use them everywhere in Excel - SUM, MAX, MIN, AVERAGE, COUNT, TODAY and UNIQUE are all examples of Functions.

One bit of terminology that you're likely to hear is that this idea that you type the name of a function in a cell, and get the answer back, such as typing =SUM(A1:E1) and being given the answer 471 (or whatever), is called "calling a function" in VBA-speak.

So, in our case, this is a procedure designed to do some actions, to perform a task. 

Following that keyword - Sub or Function - which specifies the type of procedure comes the name of the procedure. The name is not usually important - you could call it Bob, or Pineapple, or WobblyJelly, but they would be odd names - it's more usual to name a procedure in a way that describes what it does.

Naming rules for Excel Macros

There are one or two rules governing what you are - and aren't - allowed to call your procedures:

  • Names must begin with a letter
  • Names must be unique within any module
  • Names cannot contain spaces or any of the following:   . , @ & $ # ( ) !
  • Names must not exceed 255 characters

In addition, the following conventions are frequently observed

  • Names should be written in “camel case” – lower case, but with a capital letter to mark the start of each word, eg. HighlightValues, or RemoveOldData
  • Avoid all symbols in naming macros – including the underscore

The list of Parameters

Finally, after the name of the procedure comes a set of parentheses - round brackets. Inside these brackets is a list of any information needed for this procedure to do its job. For example, thinking again of the functions you've no doubt put into your worksheets, the SUM function needs to be given a list of numbers or cell references to be added up. The IF function needs to have a test, a true part and a false part. 

Just occasionally, you may come across a function - the TODAY function is probably the most common example - which doesn't need any information in order to return its value. In such a case, the parentheses will be empty - but the brackets must still be there, even if they're empty.

In our procedure, the brackets contain the following information:

Public Sub AddDetailsToCell(Optional AddToCell As Range)

The word Optional indicates that the following parameter is, well, optional (later in the code, we test whether the parameter was actually provided, and if not, we set a default value).

The actual name of the parameter is "AddToCell". Again you can name parameters whatever you like (as with the names of procedures, you're not allowed spaces or special characters in the names of parameters) but picking something intelligent and meaningful is probably a good idea. 

After the name of the parameter comes the word AS, followed by the type of information this parameter should contain. In our case, we're saying that "AddToCell" should be a "Range" - meaning one or more cells. The purpose of this procedure is to populate the specified cell with information about this file - its name, its path, the name of the author and the current date - and it's this parameter which allows us to tell the code where the "specified" cell actually is, or where we actually want to put this information. 

So, when we come to "call" this procedure (meaning that we tell it to execute) we'll say something like this (in this case, we're calling it from the immediate window, meaning that we can type the name of the procedure, providing the necessary parameters, and then just hit Enter to run the code):

Here, we've run the procedure by typing "Call" followed by the name of the procedure "AddDetailsToCell", then a pair of brackets, and in the brackets we've specified to put the information in cell A10 - which is what we said we needed to know when we wrote the procedure above. 

To dive into one more detail for a moment, when we wrote the procedure, we said we'd need to be given something called "AddToCell", which would be a range... and here, we've used one of Excel VBA's built in functions - RANGE - to specify that range.

The contents of the code

I don't propose to go into detail here about how the code does what it does, and the meaning of each line of the code - that will have to be for a later tutorial as there's quite a lot of content there, and it would take quite a long time to go through it all. However, I want to break down a couple of points of interest which are common to many procedures.

A sample Excel macro with syntax highlights

A sample Excel macro with syntax highlights

Variables

When you create code, you often need to store information. For example, you might want to ask the user for their name, and then use that information later. A variable is the term for a piece of storage where you put that sort of information (the name comes from you storing information which can change, or vary, as your code runs). 

It's quite common - and good practice - to list all the storage you're going to need at the start of the code, which is what's happening here with the line

Dim Output As String

Note:

Again, we're getting a little ahead of ourselves here, but setting up (or "declaring" to use the correct term) our variable "As String" we're telling Excel that we need a bit of storage space that we can store a string of letters, numbers and symbols in. If we didn't do that, Excel might have set up a bit of storage only big enough to hold a number, not text, which would cause problems later....

Comments

When you write code, it's a good idea to note what you're doing. Perhaps you'll need to come back to your code later and adjust it, and you'll need to remind yourself of what you were doing. Or perhaps you'll need to make notes so that someone else can take a look and work with your macro later. 

The way we do this is to write a "Comment" - which is simply a piece of text that Excel just ignores - it's for your use only. 

To do this, you just type an apostrophe - and everything on that line after the apostrophe is ignored. To make it clear that this is a comment and will be ignored, the text goes green (by default - you can change the colour scheme if you want to). 

In our sample above, you can see that there are a couple of comments - one near the top, which runs to two lines, each of which has to have an apostrophe prefix, and one further down, at the end of a line of code.

Line continuation characters in VBA

When you write code, the code can end up being quite complex. Excel doesn't care if your line ends up long, and you have to scroll left-to-right in order to read it... but you might. It's not easy to read if you have to scroll not just down but to the right as well. But, you can't just hit Enter - that tells Excel that you've come to the end of that line of code... and your code may not have finished yet! 

So, to tell Excel "This line continues below, treat the next line as though it was part of this one" you type two things - a space, then an underscore, as we've done in our code.

What's next?

That's it for this post. I know it's possibly a bit frustrating to get through all that content, and still not know how to actually write anything that DOES anything! 

But think of this as your health and safety briefing before you start scuba-diving, or parachute-jumping or whatever. We all know it's not what you're here for, but we also all know that it's something that just has to be done....

Next time, we're going to take a look at using code to work with our spreadsheet - changing what's in cells, the formatting of those cells and more.

share this

Related Posts

Sign up now to be the first to hear about new articles!