ICAEW.com works better with JavaScript enabled.
Exclusive

Excel Tip of the Week

Excel Tip of the Week #382 - Keeping you VBA code readable

Author: David Lyford Smith

Published: 23 Feb 2021

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

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.

excel Screenshot

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

excel Screenshot

Indentation

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

 

excel Screenshot

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.

excel Screenshot

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.

excel Screenshot

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.

excel Screenshot

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.

Archive and Knowledge Base

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.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250