Articles on all things data...

Creating Excel Macros (part 1)

 April 4

by Andrew Richards

If you understand how to create macros in Excel (or any other application), you can add huge power to not your spreadsheets, but also your documents, databases and even your Outlook inbox. What's more, if you really understand how to create macros, you can make yourself a much more attractive proposition to a new employer! 

This is the first in a series of articles about building macros, which will give you the essentials, and some pointers to where to build your knowledge further.

But before we start, we should really get a basic question out of the way.

What exactly is a macro?

A macro is simply a piece of programming code - a set of instructions if you like - which runs whenever you ask it to. That set of instructions gets your current application - Excel, Word, Access, Outlook, PowerPoint or whatever, to do something useful.

If you're using Excel or Word, there's an easy way to create a macro - you can "record" it, which is essentially your way of telling the application "Watch what I'm doing and write it down so that you can do this whenever I ask you to."

The trouble with this method is that it only works in Excel and Word. You can't record a macro in any of the other applications I mentioned above.

Furthermore, creating a macro like this is a bit limiting - there are lots of things that you can't record. For example, if you record a macro in Excel, it can only record what you do in Excel. You can't, say, start recording, then copy data from Excel and paste it into a new Word document. The minute you switch to Word, the Excel macro recorder can no longer see what you're doing, so ignores it.

So, the better way is to learn to write these instructions yourself, rather than rely on the macro recorder. Yes, it takes work, and time, to learn this, but it's a really powerful tool. Or, to put it another way, when you start to learn how to write macros from scratch, it's a slippery slope.... you'll see endless uses for them, and will want to create more and more!

So, let's just get started with something really basic and trivial, just to show you how you can create a macro.

How to write your first macro

Start by opening Excel (although macros exist in all sorts of applications as mentioned above, Excel is definitely a good place to start) then follow these instructions:

  1. Create a new blank workbook.
  2. To get to the place where you write code (the "VBA Editor") press ALT and F11. You'll be taken to a screen that looks a bit like this (although probably with fewer buttons on the toolbars):
The VBA editor, before any code is added

This is what the VBA editor looks like before you add any code (or even a place where you could add code)

  1. Next, click on the Insert menu, and choose "Module". You'll be presented with a blank window. There's a possibility that this blank window will say "Option Explicit" at the top - if so, you can safely delete that.
  2. In your new blank window, type the following EXACTLY (you may want to copy and paste it to be certain of having no errors). 

Sub GetTheDate()
MsgBox "Today's date is " & Format(Date, "dddd dd mmmm yyyy")
End Sub

Important note:

If you copied and pasted, that should all be fine. But if you typed the lines yourself, then when you type the first one (Sub GetTheDate) and pressed Enter, the VBA editor will automatically add the last one (End Sub) for free.... meaning that you just need to type the middle one between the "Sub" and the "End Sub".

Once you've entered your code, your window should look like this:

The VBA editor, showing a simple macro

The VBA editor, with a simple macro

The good news is - that's it! You've written your first macro!

You can now safely close this VBA editor by clicking the X at the top right corner, which will take you back to your main Excel screen.

But what does your macro do? And how do you get it to do that thing that it does?!

How to run a macro in Excel

To answer the second question first, to run this macro, you've got a couple of choices. You could do it the dull way, which is to go to the View tab on the ribbon, then click the Macros button, select "GetTheDate" from the list, and click Run, as per this screenshot:

The Macros dialog box

The Macros dialog box, showing a list of the currently available macros

And that should answer the first question - your macro tells you what the current date is!

But if you want a more fun way to run your macro, try this:

  1. Click on the Insert tab on the ribbon
  2. Click Icons and select an Icon to insert (or if you prefer, click pictures, and then select a picture from your device or from the web)
  3. Move your icon / picture to wherever you want it on your spreadsheet, then right-click on the icon and select Assign macro:
Assigning a macro to an image in Excel

Assigning a macro to an image in Excel

You will be presented with a list of macros... or more accurately a list of one macro!

  1. Select GetTheDate from the list, and click OK
  2. Click away from your picture / icon to de-select it.

Now, every time you want to run your macro, you just need to click the image!

Admittedly, this was quite a trivial macro. If you really wanted to know the current date, you'd no doubt look at the clock in the Windows task-bar, or just check your phone.

But the steps you took - go to the code window, create a module, add some code, find a way to trigger the code - are the starting point to a whole new world of power.

Today "What's the date".

Tomorrow "Take this spreadsheet, select the appropriate range, copy it into a PowerPoint presentation, save the presentation, email it to my boss and set a diary reminder to discuss it with her later this week." 

Or whatever else you want your macro to do.

There's one last point to note, and it's an important one...

How to save your macro

The first thing to say is that you don't need to save your macro as such - you just save the Excel file, which contains the macro. That's why I said earlier in the article that you could just close the VBA window, rather than telling you to hit the Save button first.

But here's the thing - you can't just save an Excel file "as normal" if it contains a macro. 

Since Excel 2007 came out, Excel has supported two different categories of file - those which contain may possibly contain macros, and those which cannot.

By default, when you save a file, it's going to have a .xlsx file extension - and this is a file type which does NOT support macros. If you press CTRL and S to save, or hit the Save button on a file which contains a macro, you may (depending on which version of Excel you're using) get a warning that your file does not support macros. Excel then offers two choices - continuing to save as a .xlsx file, which will remove your macros, or changing to a .xlsm file, which supports macros.

Important note:

The default option is to continue to save as a .xlsx file, which will remove all your macros without further warning!!! If you want to save your file with macros, you'll need to change the file type to a macro enabled workbook (.xlsm) file. 

More recent versions of Excel have, I note with great happiness, changed this behaviour, and if they detect a macro will automatically save with the .xlsm extension so that macros are not removed.

So now you know how to create a macro, and how to save it.

As yet, your macro doesn't do anything that will change the world (or even your world) but in our next tutorial in the series, we'll look at the things we'll need to get in place to get our macro to do more.

share this

Related Posts

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