Visual Basic for Applications, or VBA, is a programming language that you can use to code within Word, Excel and other Microsoft Office applications.

You can do some pretty useful stuff with it, like automated report generation or creating user interfaces for complex financial models.

Today, though, we’re going to solve a really simple problem: making custom shortcuts for actions we do every day, often repetitively, but which aren’t already built into Excel like the ones we covered in Speed Excel 2. If you’ve never programmed before, we’ll give you a taste of what VBA can do by showing how common Excel tasks are represented in code.

Cheating Our Way Through

Good news: we’re going to cheat and use the Macro Recorder to generate our code for us.

First, though, we have to enable it. Open a new sheet in Excel and hit the Office button at the top left of your window, then select Excel Options. Under Popular, ensure ‘Show developer tab in the Ribbon’ is ticked, then hit OK at the bottom.

You’ll find the ‘Excel Options’ button down the bottom of the Office menu.

Click this checkbox, and we’ll show you how deep the rabbit hole goes.
This will make an extra tab appear at the end of your ribbon bar. Click it.

Recording a Macro

OK! So this is the Developer tab. There’s lots of interesting stuff here involving making user interfaces (the ‘Controls’ section) and dealing with XML (don’t ask), but right now we want one simple button: ‘Record Macro’, under the Code section. Click it. A menu should come up; for now, just give your macro a distinctive name and click OK. Don’t do anything else yet: Excel’s recording your actions.

Let’s make a simple formatting change that would normally involve a trip to the Ribbon bar, either with the mouse or with the keyboard using the Alt key (as I showed you last tutorial). Cell A1 should already be selected. Do the following:

• Type ‘TEST’.
• Hit Enter to stop typing into the cell, then press the up arrow key to select your test cell again.
• Click on the Font Colour button and change the colour of the text in the test cell to red.
• Go back to the Developer menu and click on ‘Stop Recording’, where the Record Macro button used to be.

Pulling Back the Curtain

Here’s where things get interesting. Almost every action you can do in Excel has an equivalent in VBA code. When you record a macro, Excel takes your actions and converts them into their VBA code equivalents. It means that, with enough macro recording, Googling and trial and error, you can turn pretty much anything you’d normally do in Excel into a macro.

Let’s have a look at the code that Excel generated for the macro we just recorded. In the Developers tab, under the Code section, click on the ‘Macros’ button. Your macro should appear in the list that appears. Ensure it’s selected, then click the ‘Step Into’ button…

This might look like jibberish to you. It’s OK. I’ll explain.

• Each line is an instruction for the computer to do something. The instructions are completed one at a time, from top to bottom.
• The green text is comments, notes you’ve made in the code. The computer ignores them. You can make a comment by starting the line with a single quotation mark.
• Similarly, the indents don’t mean anything to the computer, but they make the code more readable for us by visually indicating relationships between blocks of code.

Understanding the Code

Now, let’s look at each ‘meaningful’ line:

• ActiveCell.FormulaR1C1 = “TEST” makes the cell at the top-left of the selection equal to the string of characters “TEST” (i.e. it puts TEST into the cell in replacement of anything in there before). Here, our selection was just the cell A1.
• Range(“A1”).Select selects the cell with cell reference A1; this was generated when we pressed the up arrow key. (It’s called Range, not Cell, because we can use it to select ranges containing more than one cell, too.)
• The With statement is basically saying “With Selection.Font, do the things I specify below; I’ll tell you when I stop listing things to do by saying End With. Here, it basically makes the code easier to read for humans; you’d get the same results if you removed theWith and End With and just inserted Selection.Font at the start of the instructions indented within the With lines.
• The instructions indented between the With lines are, again, assignments, like the “TEST” assignment we saw before, but this time we’re assigning numbers to properties of the selected cell’s font. (By the way, there are more meaningful ways of defining colours than the 8-digit number that Excel has produced for us here – search for “RGB VBA” in Google.)
• Sub just means ‘Subroutine’, and delineates a VBA program, i.e. our macro. (I named the macro ‘lol’.)

Rewriting the Code

If you try and run this macro again (e.g. by using the Macro menu under the Developer ribbon tab) with a different cell selected, you’ll find it doesn’t work well: it’ll always put “TEST” in the cell initially selected when we run the macro, and the cell that has its font colour changed to red is always the cell A1.

We want to get rid of the first two instructions – just delete them or comment them out. Our macro now looks something like this:

Assigning a Shortcut Key

This is really easy – you can do it when you start recording your macro in the window, or you can add a key to an existing macro by clicking on the Macro button under the Developer tab in the ribbon, selecting your macro and clicking the Options button.

Making Your Macro Always Available

This was an option when we first recorded our macro. The default is to store the code within (a module of) the workbook you’re currently using, but you can also store it within a Personal Macro Workbook. It’s a file called PERSONAL.XLSB, and it loads each time you run Excel. It contains all the macros you want always available to you.

Because the macros are stored in that file, you can actually copy it (Google its name to find its location on your PC; you might need to show hidden files to access it) and back it up or to send it to friends. (Trading useful macros is like trading Pokémon! Only much less cool.)

If you want to add an existing macro into your Personal Macro Workbook, it’s basically a cut and paste job: open the Microsoft Visual Basic window (the ‘Visual Basic’ button under the Developer tab in the ribbon) and ensure the Project Explorer pane is open (Ctrl-R). Voila: you can see all your currently open workbooks as well as PERSONAL.XLSB; select one by double-clicking.

You can put macro code into workbooks and worksheets, but it’s more common to put it into a VBA module. The macro we wrote should be within ‘Module1’ of the workbook you created at the start of the tutorial.

Once you’ve moved the code to your Personal Macro Workbook, you’ll need to reset the shortcut key using the steps outlined above.

Next Steps

So we’ve gone through how to define a custom shortcut using VBA, and how to make it load for any workbook at Excel’s startup. Congratulations: you now know some VBA basics!

Now go out there and use your newfound knowledge. This semester, for example, I’m going to use these techniques to create shortcuts in Word for starting a list of bullet points, and for turning selected text into a heading. (Word actually already has this shortcut — Ctrl-Alt-# — but it only works for heading levels 1-3.)

Your most useful resource will be Google, but you might also be interested in these:

• Smitty: Automating Tasks with the Macro Recorder – First Steps
• Mr Byte: How to Unhide PERSONAL.XLSB and Edit it in Excel 2007
• FunctionX: VBA Tutorial (comprehensive)
• geekgirl: Writing Solid VBA Code

Next time you find yourself reaching for the mouse to do a common, repetitive task, have a go at making your own keyboard shortcut. You won’t regret it.