<

Go Premium for a chance to win a PS4. Enter to Win

x

A Guide to Writing Understandable and Maintainable VBA Code

Published on
17,306 Points
4,506 Views
68 Endorsements
Last Modified:
Martin Liss
Over 40 years of programming experience. Expand my "Full Biography" to see links to some articles I've written.
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As a person who answers a lot of questions here at Experts-Exchange I often come across code that is hard to follow and/or hard to understand. Even if you don’t look at other people’s code you may well have had the experience of going back to your old code and finding that you had trouble understanding it. If you want to avoid that then the techniques described in this article will help you.

The techniques I’m going to describe are:
  • Documenting your code
  • Coding Standards
  • Layout

Documenting your code


One of the most important things you can do to improve your code is to document it. Good documentation will make good code better and even poorly written code easier to understand. 

How does it make it better? Well for one thing it helps you think. Occasionally I’ve written some code that I thought was fine but the process of documenting it brought more clarity to my thoughts and I was able to see that the code could be improved or was just plain wrong. This saved me the time I would have needed to debug the code. 

The problem with undocumented code is that what’s crystal clear now may be Greek six months from now when you want to change it. Also consider the unfortunate programmer who has to maintain it if he takes over for you. Since he probably has never seen the code before he may have to spend hours figuring out what it does. So do him and yourself a favor and document your code. Here are some guidelines for doing so.


Do it as you code 


You should document the code as you write it because it will fresh in your mind and you won’t have forgotten why you coded it the way you did. Another reason for doing it then is that it’s not as much fun as writing code and if you put it off you may find excuses for not doing it at all.


Every non-trivial procedure should have a header


By a non-trivial procedure I mean one which is longer than two or three lines, and by a header I mean a short description of what the procedure does. Here’s an example.

Public Function CountOccur(sField As String, sFind As String, nMethod As Integer) As Integer
'***************************************************************************
'Purpose: Count the occurrences of sub-strings in a text or memo field
'***************************************************************************

    Dim nPos As Integer
    
    nPos = 1
    Do While nPos > 0
        nPos = InStr(nPos, sField, sFind, nMethod)
        If nPos > 0 Then
            CountOccur = CountOccur + 1
            nPos = nPos + Len(sFind)
        End If
    Loop

End Function

Open in new window


At a minimum that’s what it should look like but you can expand the header to something like this if you like.
 

'***************************************************************************
'Purpose: Count the occurrences of sub-strings in a text or memo field
'Inputs:  sField - The text or memo field to be searched
'         sFind - The sub-string to be searched for
'         nMethod - The type of search to perform. If = vbTextCompare then
'                   the search is not case-sensitive. If = vbBinaryComapre
'                   then the search is case-sensitive.
'Outputs: The number of occurrences found
'***************************************************************************

Open in new window


If you’re thinking that it would be tedious to add headers in a lot of procedures I agree, but one way to decrease the tediousness would be to create an empty template in your code like this
 

'***************************************************************************
'Purpose: 
'Inputs
'Outputs: 
'***************************************************************************

Open in new window


and copy/paste it to your procedures. An even better way is provided by the great MZ-Tools add-in. Among its many features is the ability to create a template like the one above once, and from that point on have it available to be added to your procedure with a single button click. MZ-Tools remembers what you add so the template will be available in any future workbook. 


Documenting individual lines of code – and why you mostly shouldn’t


First let me say that most individual lines of code are understandable so documentation is usually not necessary and in fact can clutter up the code and make it harder to read. 

The two main reasons why you might want to document individual lines of code are to explain how the code works and why the code is there. As for explaining how the code works I suggest as a general rule that you should never do it, because whatever you write will be just like a second version of the code and will probably provide little value, and that is assuming that the explanation is correct. In addition a “how” comment may be correct when you write it but if you change the code and forget to change the comment you may be left with misleading information, and while documentation is good, there’s little worse than incorrect documentation! On the other hand comments that explain “why”, like the one near the middle of the following code does add value because it makes for a quicker and easier understanding of the code. 

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim varTVT As Variant
    If bUndo Then
        bUndo = False
        Exit Sub
    End If
    
    If Not Intersect(ActiveCell, Range(ValidationRange)) Is Nothing Then
        On Error Resume Next
        'Determine if the validation range still has validation
        varTVT = Target.Validation.Type
        On Error GoTo 0
        If Err.Number = 0 Then
            bUndo = True
            Application.Undo
            MsgBox "Your last operation was canceled." & _
            "It would have deleted data validation rules.", vbCritical
        Else
            Exit Sub
        End If
    End If
End Sub

Open in new window


Document with Named Constants instead of using Magic Numbers


“Magic Numbers” are the direct usage of a number in a line of code and you should avoid them whenever possible in favor of the Named Constants provided by the language.



For example you may have seen or been tempted to write something like 

Columns("C").SpecialCells(4).EntireRow.Delete

Open in new window


You can probably figure out that that line of code causes entire rows to be deleted based on what’s in column “C”, but what the condition actually is is probably a mystery. However if you were to write 

Columns("C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Open in new window


the Named Constant xlCellTypeBlanks (which has a value of four) solves the mystery and makes the line self-documenting.


Document Code Changes


Most of us have had the experience of having stable code fail unexpectedly and being at a loss to understand why. It then dawns on us that a couple of weeks ago we made a few minor changes and it was probably one of those changes that caused the error. The bad news is that we did a million other things since then and so we don’t remember everything we changed, so we’re stuck with a tough job of debugging. The way to avoid this is to document all the code changes you make and then it’s a much easier job to isolate the problem. Please see my article on Documenting changes to VB6 or VBA code which describes a relatively easy way to do the documenting.


Coding Standards


consider this short paragraph its written without any capitalization punctuation the apostrophe in its
andsomepartswithoutspacing doing so makes it hard to read

You probably had to read that a couple of times before understanding it, and today no one would write it that way. You may be surprised however to find out that for much of history everything was written that way and people had to struggle to understand the meaning of documents until, gradually, standards of punctuation were developed. (See Origins of Punctuation  if you're interested in the subject.)

Similarly, standards have been developed for coding. Many companies both large and small have such standards and programmers in those companies are required to write code that follows the standards. This makes it much easier for someone who takes over the code to understand and maintain it. Also in some large projects different parts may be written by different people and coding standards are needed to make a cohesive whole out of the parts.

Even if you write code by and/or for yourself you should have standards and they should conform to generally accepted standards. If you are reading this article you have probably asked questions here, and if you have then one good reason to write your code using generally accepted standards is that experts here at EE may give up on answering your question if the code is too hard to follow.

The following are some of the standards that I’ve developed. They are not meant to be complete but rather just a guide you can use to develop your own. 
 

Variable names should be self-documenting


in other words the names should be meaningful. 

Example: rowcount

rowcount is much better than something like “n” because unlike the latter, you know exactly what it is without looking any further.


Variable names should be written using a CamelCase

CamelCase, after the Bactrian camel that has two humps, is the practice of using a combination of upper and lower case letters.

Example: RowCount

RowCount is easier to read than rowcount, but there’s another important reason for using CamelCase and that is that if you type “rowcount”, VB will change it to RowCount, but if you were to mistakenly type something like “rowcont”, it would remain all lower case which is an immediate indication that you’ve made a typo. By the way it's my practice to always type all lower case letters, just for that reason.


Variable names should indicate the type of the variable


Why look for a Dim statement to find out what type a variable is, when the name can tell you?

Example: lngRowCount

In my standards I add a lower case prefix, usually 3 characters in length, that defines the type. in this case the "lng" prefix says that the variable is a Long which gives the reader immediate, valuable, information about the capabilities of the variable without looking any further. Some other prefixes that I use are:

int        Integer
dbl        Double
cur        Currency
str        String
var        Variant
b or bln   Boolean
sng        Single
dte        Date
col        Collection
dic        Dictionary
obj        Object
arr        Array

Open in new window



Variable names should indicate where the variable is defined

This also tells you the scope of the variable. In other words where it can be used.

Example: mlngRowCount
In my standards the “m” in the name indicates that the variable is defined in the declarations section of a form or code module and hence is available anyplace in the form or module. A “g” indicates that the variable is defined in the declarations section of a code module and hence it is available everywhere in the application if it’s defined as Public. If it’s defined as Private it’s only available in the module where it’s defined but the ‘g’ still tells you where it is. The absence of “m” or “g” indicates that the variable is local to the procedure in which it is defined and can only be used there. 


Variables should always be explicitly defined

Example: Private mlngRowCount As Long
Without the "As Long" the variable would be treated by VBA as a Variant. Variants are the largest and slowest of the variable types and so you should try to avoid using them. Earlier in this article where I talked about documenting individual lines of code, I showed some code which used a variable named varTVT. When I wrote that code I didn't know what type that variable should be so I used a Variant because a Variant can hold any type of data. When I defined it I could have just written "Dim varTVT" and left off the "As Variant" and it would default to being a Variant, but being the good programmer that I am I included the "As Variant" for documentation purposes (even though the "var" prefix would have told me what it was).
 

Constants should be all uppercase


Example:  MAXIMUM or MAX_ROWS


Always use Option Explicit


Option Explicit (which is placed in the declarations section) forces you to define your variables. Why bother you say? Well if you ran the following little macro in Excel it might take you a while to figure out why you sometimes got that oh so unspecific "Application-defined or object-defined" error. Debugging would point out that when Sheet2 was active that the function returned zero which might be puzzling until you put on your glasses and noticed that for the Sheet2 case, NewLine was spelt with a "1" rather than an "i" leaving the value of the NewLine function at its initial value of "0". If you had used Option Explicit you would have gotten a "Variable not defined" error highlighting and pointing out "NewL1ne".

Sub MyMacro()
    ActiveSheet.Cells(NewLine(6), "A") = "New Line"
End Sub
Private Function NewLine(CurrentLine As Long) As Long

Dim lngLine As Long

Select Case ActiveSheet.Name
    Case "Sheet1"
        NewLine = CurrentLine + 2
    Case "Sheet2"
        NewL1ne = CurrentLine + 3
End Select

End Function

Open in new window

To have VBA or VB6 automatically add Option Explicit in all forms and modules, go to Tools|Options and select "Require Variable Declaration".

Layout


I have only four things to say on this subject and they are

  • Indent your code
  • Indent your code
  • Indent your code
  • One line of code per line


Indent your code

All too often I've seen code that looks like this and worse (and longer!).

Private Sub Bad()

Dim i As Integr
Dim j As Integer

For i = 1 To 10
For j = 3 To 20
Select Case gstrSomeGlobal
Case "blah"
' do somthing
Case "yada"
If i <> 1 Then
'do this
Else
'do that
End If
End Select
Next
Next
End Sub

Open in new window

I really don't understand why some people do that. Maybe they don't understand that you can indent or maybe they're just too lazy to press the Tab key, but my advice is to always indent. It doesn't matter if it's four spaces (like I do) or three, or two, but indent. If you do that your code will look like this which is so much easier to follow.

Private Sub Bad()

Dim i As Integr
Dim j As Integer

For i = 1 To 10
    For j = 3 To 20
        Select Case gstrSomeGlobal
            Case "blah"
                ' do somthing
            Case "yada"
                If i <> 1 Then
                    'do this
                Else
                    'do that
                End If
        End Select
    Next
Next
End Sub

Open in new window


One line of code per line

Occasionally I see code that looks like this.

x = x + 1: y = x + 5

Open in new window

Again I don't understand why people do that. It saves nothing other than a visual line while on the other hand it's easy to overlook the fact that it's really two separate lines and it's harder to debug.

This article is based on my real-world Excel VBA and Classic Visual Basic experience. These principles can probably be applied to most programming languages, and I hope that they will help you write better code no matter what language you use. I apologize for the length of the article, but in fact much more could be written on these topics. I’ll leave further research to the reader. 
 
If you find that this article has been helpful, please click the “thumb’s up” button below. Doing so lets me know what is valuable for EE members and provides direction for future articles. It also provides me with positive feedback in the form of a few points. Thanks!

68
Comment
Author:Martin Liss
  • 2
  • 2
  • 2
  • +5
11 Comments
 
LVL 66

Expert Comment

by:Jim Horn
Lots of details, applicable to most programming languages, and a joy to read.  Voted Yes.
0
 

Expert Comment

by:isnoend2001
I agree with Jim Horn
Martin Liss has answered many of my vb6 questions i have asked over 800 over the years.
When i ask i always hope he answers.
0
 
LVL 12

Expert Comment

by:Jamie Garroch
I've written a lot of VBA code and follow all of the principles here except the 'g' and 'm' idea, proving that it's never too later to learn something new. Great article and one which will change the way I work. One tiny typo (assuming all code is VBA), the example
x = x + 1; y = x + 5

Open in new window

should be:
x = x + 1: y = x + 5

Open in new window

You could also explain the pros (and I suspect cons) of using an underscore at the end of lines to split single statements across multiple lines. I've seen some really confusing multiple If...Then statements liberally sprinkled with a bunch of _ characters making the code really hard to read!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

Author Comment

by:Martin Liss
Thanks Jamie (and others) typo corrected. I'll consider adding a section about the use of underscores but for now I'll just say I think that if if done right that it's better than having to scroll left and right to read long lines of code. BTW if there are more suggestions for changing/correcting/improving the article I'd prefer it if you use the Ask a question button, but please continue to praise me here:)
0
 

Expert Comment

by:isnoend2001
The part that really hit home for me was procedure headers. i am attempting to to correct my lazy
writing now, but it is so much easier as you code
0
 

Expert Comment

by:morrjay
I never thought about adding a letter (like g or m) to my variables. That is great advice. Also, I was over documenting my code, using procedure headers will save time and look much cleaner. Thanks for writing this article.
0
 
LVL 22

Expert Comment

by:rspahitz
Good article. A few things:

1) "CamelCase, after the Bactrian camel that has two humps, is the practice of using a combination of upper and lower case letters"
Although, that is correct informally, technically that's Title Case  thisIsCamelCase (first letter always lower case)

2) Extending the idea of nicely named variables would be nicely named procedure names, typically stating with a verb like BuildText or GetValue

3) On the topic of indenting, the tradition is to indent every one one tab's worth (typically 4 spaces.)  Blocks in Subs, Functions, If/End If, For/Next, Do/Loop, Select/End Select and a few others.  In your example, you failed to indent the Sub block.  This makes it harder to find the end of a sub and the beginning of the next sub (although there are other ways to manage that.)

4) "One line of code per line"
Related to that, one variable definition per line.  The biggest issue is that different languages have different interpretations of single-line definitions and can make variables defined as variants.
e.g.
Dim a, b, c As Integer
In some languages, this type of syntax would create three integer variables but in VB it gives you 1 integer and 2 variables.  You could use this:
Dim a As Integer, b As Integer, c As Integer
but that just makes things harder to read than putting them on 3 lines.

And one of the things I teach in my VBA classes is not only the naming standards for VB, but also the naming standards for other languages (which have worked their way into VB.Net.) Obviously, if most of a project is written in one standard, you should try to maintain that, but for newer projects, it's good to at least know the industry standards for general coding.  The main reason is that some people who write VBA code may work their way up to VB.Net programmers and if you use the VBA standards, you will look amateur (and they will also probably not be the company standards so you will have  to work extra hard to get used to another standard.)
For example, in most languages the type prefix on variables (int, str, etc) is no longer used.  The reason is that the better tools will show you the data type when you hover over the variable.  Personally, I don't like that because there's still the issue of copying code and pasting into EE, where you don't have the benefit of the tool to help out.

Another very useful coding method is modularization.  A simple implementation of that is to avoid having any procedure exceed about 5-7 lines of code.  How do you do that?  Group related pieces and move them into a well-named sub-routine of function.  Then if you have too many methods (subs/functions) you may want to move them into their own Module or Class.  These ideas are less common in VBA, where most parts are smaller, but in larger projects, they become very useful.

Anyway, thanks for the refreshing article. I was a bit concerned when you started by pushing people to add comments, but I felt better after I read the way you presented it.
0
 
LVL 71

Expert Comment

by:Qlemo
I don't agree to some of the statements made.

A function or procedure may be as long as one or two pages, if it just doesn't make sense to bloat the modules by introducing artificial procedures, just to keep them short. However, if there is code you might want to reuse, or if you use the same bunch of code lines more than twice, your should consider sub'ing.

There is no use of differing between long and short. A single type letter as prefix is enough. f for float, n for integer numeric, s for string etc. However, I would never write nRowCount - don't comment the obvious!
0
 
LVL 22

Expert Comment

by:rspahitz
>A function or procedure may be as long as one or two page
The problem with this is that it makes it that much harder to debug if something goes wrong.  Further, if you have that many lines, you probably have multiple things going on and following the concept of self-documenting code, it's a simple refactor to change a block of 10 or 20 lines into something like UpdateAllRecords() which is WAY better than doing this:

'''25 lines of setup code here
'''
' Update all records or some other comment that can become obsolete and confusing
Do Until some big condition
   ' 10 lines to read the record
   Blah1
   blah2
   blah3
   ...

   ' 10 more lines to update the record
   Blah11
   blah12
   blah13
Loop
'''
''' 25 more lines of wrap-up code here

---
Instead just do this:
' 3 lines that clearly explain the 70+ lines of code
SetUpItems
UpdateAllRecords 
WrapUpItems

Open in new window

...
Sub SetupItems()
...
End Sub

Sub UpdateAllRecords()
...
End Sub

Sub WrapUpItems()
...
End Sub

---
Obviously there are times that creating those subs is more trouble than it's worth (like when you have to pass it 5 parameters and return 3) but often that means that refactoring the code will greatly improve performance and readability anyway.
0
 

Expert Comment

by:M Melson
Very nice article. Thank you for taking the time writing and sharing it.  I can use part with variable names to indicate the type of variable. Good advice.
0
 
LVL 49

Author Comment

by:Martin Liss
Thanks.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Join & Write a Comment

This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month