Excel Tip of the Week 382 Keeping your VBA code readable

Hello all and welcome back to the Excel Tip of the Week! This week, we have a Developer post in which we’re going to look at a sort of meta-topic to accompany our ongoing exploration of VBA – specifically, how to keep your VBA code readable.

First of all, it’s worth noting that there is no universal standard for this sort of thing – all I’ll be doing in this post is highlighting some of my own recommendations and practices that I’ve picked up while working on my VBA over the years.

Line breaks

First of all, line breaks are a critical tool for helping a reviewer or other future user follow your code. Having everything all in one stream is hard to break down. I like to have each process or separate step in the code separated by a blank line.

You can also create line breaks within individual lines of code if they are getting too long, by using an underscore:

Indentation

You can also see this in action in the screenshot above, but here’s a more detailed example:

Indents help read down. I use indents to keep all parts of one piece of code lined up – so here for example, you can see the ‘For’ loop that begins at the top of the code, and then scan down that line to see where it ends. Likewise the two internal If-Then-Else statements are clearly separated, so that you can see which steps belong to which.

Use easy to understand names for variables

You can call your variables pretty much anything you like – so do your reviewer (and indeed your future self who is trying to debug your code) a favour by picking simple, self-explanatory names that are easy to distinguish. Abbreviations and vague names can save a few seconds when writing, but will make life incredibly difficult down the line. Of course, you don’t need a lengthy name for every little variable that’s being used for counting and so on, but for the important ones, a good name will go a long way.

Define variables only at the last minute

I like to define variables and their dimensions just before they’re used for the first time. This helps the reader immediately understand what the variables are doing, rather than defining them all up front before the reader has gotten a grip on the code.

And once again – the usefulness of commenting and these other good practices isn’t limited to if your code will be reviewed by somebody else – they will help you read and understand your own code when you come back to it later.

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.