Community Pick: Many members of our community have endorsed this article.

A Guide to Writing Understandable and Maintainable VBA Code

Martin LissKeep everyone healthy; Get Vaccinated
CERTIFIED EXPERT
Almost 50 years of programming experience. Click '+ More' in my "Full Biography" to see links to some articles I've written.
Published:
Updated:
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!

79
23,058 Views
Martin LissKeep everyone healthy; Get Vaccinated
CERTIFIED EXPERT
Almost 50 years of programming experience. Click '+ More' in my "Full Biography" to see links to some articles I've written.

Comments (14)

M MelsonVBA EXCEL

Commented:
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.
Martin LissKeep everyone healthy; Get Vaccinated
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2023

Author

Commented:
Thanks.
Fabrice LambertConsulting
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Nice article.

I don't agree with all your variables naming conventions tho.
CamelCase is good, but prefix tend to decrease readability, especially with objects:
On one side, it isn't realistic to use the generic prefix obj, when there are so many objects available (and I don't speak about user defined classes).
Plus, let's say, you have a global ADODB connection, how would you name it ? gObjADOCnMyConnection ? geez, better be blind ....
So stick with meaningfull variables name, it is enough.
Martin LissKeep everyone healthy; Get Vaccinated
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2023

Author

Commented:
I would probably name that global gadoMyCn or gadoMyConnection. I (obviously) also disagree that prefixes decrease readability. If you were in Excel and you wanted to define a global range, wouldn't grngCustomers be better than Customers? I say that because if you just saw Customers you might not know what type of variable it was or its scope without looking it up, while with grngCustomers you'd know immediately. BTW, some people would do g_rngCustomers and I'm OK with that.

I'm happy to discuss this more or to hear if you have more suggestions, but if you do then let's do it via EE messages so that this thread doesn't get bloated.
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
Nicely written. But some more thoughts about "Documenting your code":

Before documenting methods or lines of code, you should ask yourself, why do I need an additional comment?

When it is unclear what CountOccur does, then maybe cause we used an abbreviation and we missed the object in our methods name (like in verb-subject-object):

Public Function CountSubstringOccurrences(_
                    ASearchIn As String, _
                    ASearchFor As String, _
                    ACompareMethod As VBA.VbCompareMethod _
                    ) As Long
'Body
End Function

Open in new window


Now the method name transports every thing we need to now about it in its names (Yeah! Semantics!).

This also applies to inline comments.

Why do I need it? Is the following block too large that I can see its purpose at one glance? What does it tell us?
hmm, "Determine if the validation range still has validation"..
Doesn't this sound like a description of a method?

Then why not using a method instead:

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
        If HasRangeValidation(Target) Then
            bUndo = True
            Application.Undo
            MsgBox "Your last operation was canceled." & _
            "It would have deleted data validation rules.", vbCritical
        End If
        Else
        Exit Sub
    End If

End Sub

Public Function HasRangeValidation(ARange As Excel.Range) As Boolean

  On Error Resume Next

  Dim Dummy As Variant

  HasRangeValidation = False
  Dummy = ARange.Validation.Type
  HasRangeValidation = (Err.Number = 0)
  Err.Clear

End Function

Open in new window


Imho this much better expressing what it does in a much cleaner fashion. And additional benefit: We have now a new method for our tool belt.

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.