A Beginner’s Guide To Programming Excel Macros In VBA

Excel, Microsoft’s longstanding spreadsheet tool, has become ubiquitous in workplaces all around the world, largely because a) It’s Microsoft, which never loses the opportunity to drive a fairly friendly straight line between users and what they need to do in any given workday, and b) it’s not overly complicated, can be learned quickly, and gets newcomers up to some degree of speed in a timescale that doesn’t unduly impact the workflow of any business.

But as with most Microsoft products, the more you know about Excel, the more you can do with it.

Macros in Excel

Macros, the “magic spells” of Excel, are simple recorded functions or groups of functions that can help cut the repetition out of any Excel-rich workday. Once they’re recorded, they can be applied with either keyboard shortcuts or bespoke buttons, and they can increase consistency across your worksheets, while saving you time and tedium, and letting you get on with your day. If you’re brand new to Excel, you can quickly learn how to record and deploy your own macros here.

But more knowledge equals more freedom, more flexibility, and a greater ability to use Excel to the maximum of its effectiveness for your particular business and your particular workday. The good thing about that is that Excel lets you program your own macros within its system, in something called VBA – Visual Basic for Applications.

Don’t Panic

If you’re using Excel in your work environment, the chances are high that while you’re great at what you do, you’re not necessarily a computer programmer or coding expert, so the mention of programming macros may have just made you jump off your chair, shut down your computer, and huddle in a corner, quivering.

Don’t panic. Programming macros in VBA within Excel is, above all, a user-friendly process. It has to be – Microsoft’s whole brand is based on making things which would otherwise be complicated as friendly and accessible as possible. Come out of the corner and we’ll walk you through it. Breathe. Hydrate. Open up Excel. You can absolutely do this.

Step #1 – Enable Developer Mode

In Excel, Click “File,” select “Options,” and then click “Customize the Ribbon.” You should see a drop-down menu. Scroll down until you see a checkbox next to “Developer.” Put your check in the checkbox and hit “OK.”

You should now see new options in your ribbon, as Developer Mode has been opened up, including an option called “Visual Basic.” Click on that and you’ve entered the world of VBA macro programming.

Step #2 – Starting To Program

Once you’ve clicked on “Visual Basic,” you’ll find yourself with some options. Click “Insert” and select any button you like. Remember, macros are shortcuts to some impressive functionality, so you need a button to click that will run them.

Choose your button, then right-click on it, then choose “Properties.” You’ll see a table of qualities relating to the button. Firstly, edit the name and the caption of your new button, so you can find it again later, when you may have a lot more than the one.

Having named it, double-click on your button, and you’ll open up the sub-procedure outline for your button.

This is where reality kicks in. VBA is a kind of language that tells Excel what you want your button to do. But languages have vocabulary, and to make the most out of Excel macros, you need to have some VBA vocabulary at your disposal.

VBA Vocabulary

Generally speaking, VBA vocabulary consists of shortened English forms of commands. Verbs, in other words. Verb-commands in VBA can be loosely grouped by function. You can have:

• Lookup commands (such as CHOOSE)
• String/Text commands (like FORMAT STRINGS)
• Date/Time commands (like DATE)
• Math or Trig commands (like SQR, which returns the square root of a number)
• Logic commands (like both AND and OR – the first returning a result if all the conditions are true, the second if some of the conditions are true)
• Informational commands (like ISNULL – which, it won’t surprise you to hear, checks for null values)
• Financial commands (like PMT, which returns the payment amount on a loan, for instance)
• File/Directory commands (like MKDIR – make directory – to create a new file or directory)
• And data type conversion commands (like CCUR to convert a standard value into a currency value).

Having a good grasp of the VBA verb-commands under your belt will help speed up your macro programming, and will help you automate potentially huge swathes of your Excel workday. The internet is full of useful ready-reference lists of these commands, and “cheat sheets” to help you  until you have the verbs you most regularly use at your mental command.

Executing Commands

Back to the sub-procedure outline. Make sure you’re in Design mode while you do this – it’s a simple toggle on the ribbon.

You’ll see two lines of text. The first will say:

Private Sub The_Name_You_Gave_Your_Button_Click ()

The second will say:

End Sub

These are the capital letter and the period of your command sentence. In between them, you can put your verb command.

For instance, if you want to program your macro to give you a currency conversion, you’d end up with a sub-procedure that looked like this:

Private Sub The_Name_You_Gave_Your_Button_Click ()

CCUR

End Sub

At that point, if that were all you intended your macro to do, you would press the “OK” button, and have yourself a currency value conversion macro.

Of course, verb commands can be extra useful with additional context, so Excel lets you go much further than these simple verb commands.

Terminology and Meanings

Modules

Modules are the areas in which you write your VBA commands. When you start a new Workbook, it won’t have any modules in it. To insert a new module, navigate to “Insert” on the ribbon, and select “Module.” Simple, no?

Procedures/Sub-procedures

Procedures are the areas within the module where you write your VBA commands. There are two main types of procedures – sub-procedures, which we’ve discussed, and function procedures. If sub-procedures are equivalent to verbs in ordinary English, then function procedures are the equivalent of grammar. They’re re-usable pieces of code that can be deployed throughout any program, time and time again – like an exclamation point or a question-mark.

Using function procedures alongside sub-procedures allows you to build up some fairly complex command structures in your macros, and so lets you create significantly more sophisticated macros than you could manage with Excel’s simple record and deploy function.

Explore For Yourself

The more you explore the various commands, the more you can build, but using Modules, sub-procedures, function procedures, and the verb commands of VBA in Excel should be more than enough to get you started with your exploration of programmable Excel macros.