Excel Tip of the Week #382 - Keeping you 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.

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.

You may also like

• Previous post - Treemap and sunburst charts
• TOTW index
• Next time – Revisiting ranking functions