How To Make Your VBA Code BulletProof

VBA BulletProof

“I am indeed amazed when I consider how weak my mind is and how prone to error.” – Rene Descartes



This post shows a simple technique that will vastly reduce the number of errors in your VBA code. I have used this technique thousands of times on countless projects. It will save you considerable time creating applications and it will make your application practically bulletproof.

If you adopt this technique then I guarantee you will see results almost immediately. Reading about it is not enough – you have to actually try it out for yourself. Once you see how useful it is you will want to keep using it.

The simple technique I’m referring to is the Assertion statement. It  is simple to use and implement and will provide dramatic results. However don’t be fooled by the simplicity of an Assertion. Used correctly it is an incredibly powerful way of detecting errors in your code.

Before we look at Assertions lets have a quick look at a startling fact about errors


A Startling Fact About Errors in Code

According to Steve McConnell in ‘Code Complete: A Practical Handbook of Software Construction’: “there are about 15 – 50 errors per 1000 lines of delivered code”.

Just think about this for a moment. If you average the time on each bug to 30 minutes then you are talking 8 to 25 hours spent on errors for every 1000 lines of code.

30 Minutes is an optimistic average. When a bug is found in delivered code many of the following steps are required

  • Reproduce the bug
  • Emails about details of the issue
  • Find the bug
  • Fix the bug
  • Update the Bug Report
  • Create new version of the code
  • Test the new version
  • and so on

If you find a bug in development

  • Find the problem and fix it

This leads us to the following rule:

VBA Debug.Assert

Therefore finding bugs early and close to the cause is a priority. Let’s have a look at your first line of defence when it comes to errors. Then we will look at Assertions


The Front Line in the War on Errors

In VBA there are some basic tools for finding errors. These are very important for catching errors before you run your code. So it’s vital that you use them in your development.  I’m going to briefly mention them here.

The Interpreter : The interpreter in VBA checks each line of code as you write it. When you press return it will check for errors in the line and display the error if it finds one. These type of errors are referred to as syntax errors.

The Compiler : Before you run your code you should always compile it to find any existing errors. To compile select Debug->Compile VBAProject  from the menu.

The type of errors found by the compiler relate to more than one line. So for example, if you have an If statement without a corresponding End If statement. Other types are calling a Sub that does not exist, For without Next, Do without Loop, Select with End Select and so on.

Note: If you Run your code(Run->Run Macro from menu) it will find some of the same errors. However it will only check in the code that is being run.

Review Source Code from Mz-Tools: MZ-Tools is great tool(previously free now with 30 day free trial) that provides extra functionality to the VBA Editor that includes reviewing the source code. This review looks for items such as unused variables, parameters and other useful items.

To use this tool select Other Utilities->Review Source Code from the MZ-Tools toolbar


VBA Bugs

Using the MZ-Tool to Review Code

Now that we have covered the basic error finding methods lets look at using Assertions.


Why Use Assertions

We have just looked at ways of detecting errors in your code. However certain errors will not appear until you run it. This is where Assertions come into play.  

A great description of Assertions can be found in the book Debugging Windows Programs(2000 Developmentor Series)

“You can add information to your code to have the program itself automatically detect many types of run-time errors”

The key phrase here is “automatically detect”. Once you add Assertions to your code they will automatically check for errors each time you run your code. This makes it very difficult for many types of errors to exist. So running your code with Assertions is a fantastic way of smoking out errors.


What are Assertions

Assertions are used in development to check your code as it runs. An Assertion is a statement that evaluates to true or false. If it evaluates to false then the code stops at that line. This is useful as it stops you close to the cause of the error.

Let’s explain using an analogy. Imagine the different paths through your code were the streets of a city and variables were vans that drove the streets. Assertions would then be checkpoints that ensure the vans(variables) contain valid goods(values) before they are allowed to pass.


VBA Assertion

© Marcogarrincha |

If the city was full of checkpoints it would be very difficult for the Van to travel far with invalid goods. It’s the same with code. The more Assertions there are the harder for the code to run for long with errors.

In the city solution the checkpoints would affect performance. They would slow the city traffic considerably. With Assertions there are no performance issues. They are turned off when you deliver your software to the user. This means you can add as many Assertions as you like and it will have no affect on how your code runs.


How to Create A VBA Assertion

It is simple to create an assertion in VBA.  You use the function Debug.Assert followed by a Condition.

    Debug.Assert Worksheets.Count > 0
    Debug.Assert Text <>  ""

Here are some more examples

    ' This will fail if readRow is not 1 or greater
    Dim readRow As Long
    Debug.Assert readRow > 0

    ' This will fail if month is not between 1 and 12
    Dim month As Long
    Debug.Assert month >= 1 And month <= 12

    ' This will fail if firstname is blank
    Dim FirstName As String
    Debug.Assert FirstName <> ""

    ' This will fail if the workbook is not assigned to anything
    Dim wk As Workbook
    Debug.Assert Not wk Is Nothing

When the code meets a Debug.Assert line it evaluates the condition. If the condition evaluates to false then the code stops on this line. If it evaluates to true then the code simply continues on.


When to Use Assertions

The best way to use Assertions is to test the following items:

  1. The input values of a Sub/Function
  2. A value before it is returned from a function
  3. A value that is received from a function
  4. A global variable before it is used

The following functions uses Assertions to test

  • the values of the input parameters (Precondition)
  • the value that is being returned(Postcondition)


Function GetType(price As Long, shipType As String) As Double

    ' TEST THE INPUTS(Preconditions)
    Debug.Assert price > 0 And price < 100
    Debug.Assert Len(shipType) = 1
    ' Do some calculations
    Dim newVal As Double
    If shipType = "A" Then
        newVal = price + 3.99
    ElseIf shipType = "B" Then
        newVal = price + 5.99
        newVal = price + 0
    End If
    ' TEST THE RETURN VALUE(Postconditions)
    Debug.Assert newVal > 0

    ' Return the value
    GetType = newVal

End Function

In the next example, we use an Assertion to test the return value from a function

Sub RunReport()

    Dim total As Long
    total = CalculateTotal()
    ' check that total is in the expected range(1 to 999)
    Debug.Assert total>0 And total <1000

End Sub


Assertions Versus Error Handling

Error handling is used in code to anticipate error conditions and deal with them.

For example, sometimes a workbook you are trying to open my have been moved, deleted or renamed. In this case the code should report the error and return to the state before it tried to use this file.

Let’s look at a second example. Imagine you use a software application to play music files. If you try to play an invalid file the application should inform you the file is incompatible. The application should then return to it’s previous state – ready for you to select a file to play as if nothing happened. The application not stop working or begin working incorrectly.

The following code shows a simple example of error handling. We use the Dir function to check the file exists. If not then we inform the user there is a problem. We only attempt to open the file when we know it actually exists.

Sub ReadData(ByVal filename As String)

    ' Use Dir to check the file exists
    If Dir(filename) = "" Then
        ' Tell the user the file does not exist
        MsgBox "Could not find the file " +  filename
        ' Open workbook
        Workbooks.Open filename
    End If

End Sub

The difference between Assertions and Error Handling is that

  • Assertions deal with values coming from an internal source
  • Assertions are used to inform the programmer of errors and not the user
  • Error Handling deals with errors coming from an external source e.g. opening files, user input, spreadsheet data etc.

Let’s update the example above so it uses both Assertions and Error Handling.

Sub ReadData(ByVal filename As String)

    Debug.Assert filename <> ""

    ' Use Dir to check the file exists
    If Dir(filename) = "" Then
        ' Inform the user
        MsgBox "Could not find the workbook: " + filename
        ' Open workbook
        Workbooks.Open filename
    End If

End Sub

If these seems confusing just remember you mostly use assertions on arguments and return values.


How to Turn Assertions On or Off

Assertions are used to check your code during development or maintenance. When you give your application to a user then you turn the Assertions off. This provides us with two really great advantages:

  1. You can add as many Assertions as you like as it will not have any impact on the code you give the user
  2. If in doubt about adding an Assertion then add it anyway. You can easily remove it and it won’t affect the user.

Let’s look at how to turn Assertions On and Off.

To turn Assertions On/Off we use a special type of If statement to surround our code. This is the #If statement – note the #character before the If . First of all we need to create a “Conditional Argument” that we can use in this if statement

Select Tool->VBAProject Properties from the VBA menu.

VBA Conditional Argument

You can see in the screen shot that we have written Debugging = 1 in the cryptically named textbox “Conditional Compilation Arguments”. Debugging can be any name we like and =1  means it is currently true.

We use this argument to decide if we are going to use certain code when our application runs. The following code shows how to use the #If statement with the compilation argument i.e. in this case Debugging

Function GetType(price As Long, shipType As String) As Double

#If Debugging Then
    ' Test inputs(Preconditions)
    Debug.Assert price > 0 And price < 100
    Debug.Assert Len(shipType) = 1
#End If

End Function

In the above example the assertions are only used when Debugging is turned on(set to a value other than 0). When we turn debugging off this code will not be used. 

When we want to turn off the Assertions we simply change the Conditional Argument in the dialog to Debugging = 0.

This means all the code inside the #If Debugging statements will not be used.


What to Avoid

Never put executable code inside a #If statement. Only use code that will help you when debugging. In VBA this is essentially the two statement types Debug.Assert and Debug.Print.

The second statement writes to the Immediate Window(Ctrl G or View->Immediate Window from Menu) and it useful for testing code.

The following example is something you should avoid doing

#If Debugging Then
    ' Don't use executable inside #If statements
    Debug.Assert WriteData() = True
#End If

End Sub

Function WriteData() As Boolean
        WriteData = False
End Function

The correct way to write this code is shown in the following example

Sub TestAssert()

    Dim success As Boolean
    success = WriteData()

#If Debugging Then
    Debug.Assert success = True
#End If

End Sub

Function WriteData() As Boolean

        WriteData = False

End Function

In the second example, there is no executable code in the #If statement. This is important as it means the code will run exactly the same when you turn debugging on or off.


Using Assertions With Collections and Objects

When you are using a Collection you should check two things:

  1. If the Collection has been created
  2. If the collection has elements

Lets look at how to create a collection. We can declare in one line and create in a second line like this code shows

Sub CreateCollection1()

    ' Declare a collection variable
    Dim coll As Collection
    ' Assign coll to a new empty collection
    Set coll = New Collection

End Sub

We can also declare and create in one single line as the next example shows.

Sub CreateCollection2()

    ' Declare and create collection in one line
    Dim coll As New Collection

End Sub

In the first example the variable Coll is set to Nothing until we use the Set command to create a new Collection for it.

So when using Collections(or any object) we need first to check they are not empty(Set to Nothing).

The following example shows how to check an object is set to something

Sub TestCollection(coll As Collection)

    Debug.Assert Not coll Is Nothing

End Sub

This code may seem strange as it has two negatives – Not and Nothing. However all objects are tested this way so you can use it even if you don’t understand it at first.

The next example shows Assertions that test a Workbook and Worksheet object to ensure they have been assigned to something.

Sub WriteData(wk As Workbook, sh As Worksheet)

    Debug.Assert Not wk Is Nothing
    Debug.Assert Not sh Is Nothing

End Sub

When using Collections it is also a good idea to check that it contains some elements

Sub TestCollection(coll As Collection)

    Debug.Assert Not coll Is Nothing
    Debug.Assert coll.Count >= 1

End Sub



The following is a summary of the main points of this post

  • Finding errors early is vital
  • It will save time and improve quality
  • Assertions find errors during run time
  • Assertions are created in VBA using Debug.Assert
  • They are used during development and mantainence
  • Assertions are not a substitute for error handling code
  • Assertions provide information to the programmer not the user
  • If in doubt add the Assertion anyway. You can easily remove it from the code.
  • Assertions are turned off when the code is released. It is simple to do this in VBA.


Major Advantages of Assertions

  • It is simple to add them to your code
  • They automatically detect errors when your code is running
  • They have no performance issues as you turn them off upon release.


What’s Next?

Free VBA Tutorial If you are new to VBA or you want to sharpen your existing VBA skills then why not try out the The Ultimate VBA Tutorial.

Related Training: Get full access to the Excel VBA training webinars .

(NOTE: Planning to build or manage a VBA Application? Learn how to build 10 Excel VBA applications from scratch.)


Get the Free eBook

How To Ace the 21 Most Common Questions in VBA

Please feel free to subscribe to my newsletter and get exclusive VBA content that you cannot find here on the blog, as well as free access to my eBook, How to Ace the 21 Most Common Questions in VBA which is full of examples you can use in your own code.

Free VBA eBook



  1. Wow! What a fantastic website. I have been writing VBA code for a few years but I taught myself gradually and have a lot of bad habits. There was also a lot of stuff that I was not comfortable using. Thanks to this website I can put all of that right. Thanks for all of the work you have put in.

  2. Pretty good information here! I have a question on your section “What to Avoid”. When you’re saying at the end “the code will run exactly the same when you turn debugging on or off” should I consider that when we run the code in mode “debugging on” that the code shouldn’t stop? Because I tried it and the code stop.

    1. Hi Dany,

      The code should stop when debugging is on. When debugging is on the assertions work, when debugging is off they do not work.

      “the code will run exactly the same when you turn debugging on or off” – the word code refers to the main code of your application.
      Debugging on = Main code + Assertions
      Debugging off = Main code only

      Hope this makes sense

  3. This is a very informative article. I’ve used Assert statements when building automated testing in other languages, but wasn’t aware that this could be done in VBA. Thanks for sharing this!

  4. Dear Paul,
    I just stumbled opun your website when looking for an Error Handling solution in some VBA work I did a few years ago.
    Probably, this is the best site on VBA code I have ever seen.


    Jeroem from Belgium

  5. Hey Paul,
    I have been using your site for a while and I cannot find a site that is better at explaining topics than yours.
    Thanks so much for the effort you put into helping us!

  6. Indeed great article. These techniques can be used in small or large applications to eradicate potential errors before passing the application to end users..
    Thank you very much for your valuable content.

    I would highly recommend your content to colleagues, friends, students and corporates.

  7. Hi Paul, just subscribed yesterday. I like what I have seen so far. Self-taught, (from the VBA help files no-less), with lots of bad habits…. The code generally works well, but as it gains wider distribution I want to make sure that it is bullet-proofed and also, easier to debug. So this article was very helpful. Thank you. I would like to learn more about events, is there a complete listing anywhere, and do you have a tutorial on the subject? Tim

    1. Hi Tim,

      Glad you enjoy the content. I don’t currently have any content on events.

      Here is some basic info to get you started: Worksheet_Change and Workbook_Open are by far the two most commonly used.

      Set Application.EnableEvents to false at the beginning of an event and to true at the end so it doesn’t kick off other events.

      For Worksheet_Change, you normally check first if the cell matches the one you want. It is also common to check if more than one cell is selected.

      To see the worksheet events click on any worksheet in the project window. Then select worksheet from the dropdown above the main window. In the right dropdown you will see the list of events.

      To see the workbook events click on ThisWorkbook in the project window. Then select workbook from the dropdown above the main window. In the right dropdown you will see the list of events.

      Hope this helps to get you started.

  8. Dear Paul,
    Your website is fantastic !
    Is there a program that reads a source code and report the variables declared, used and not used in VBA (cross-reference)?

  9. Hi Paul this is great content again.

    In my case I have a small group of people do testing before the project is released to the whole office. The issue is that the project is password protected (intellectual property as I am a contractor) so if a Debug.Assert is hit, the debug.assert is skipped over and code execution continues for users who do not have the project password. To remedy this, I use a custom CAssert class with optional arguments that pops up a dialog box. I think eventually I will set it up so there is a link to send me an email.

    Here is example usage (note some of the variables are not relevant and are declared elsewhere – this is cut and paste production code):
    ‘ top of class using CAssert
    Private Const CLASS_NAME As String = “CBarRectRegion” ‘ name of current class
    #If DEBUGGING Then
    Private Assert As CAssert
    #End If

    ‘ in the class at bottom
    Private Sub Class_Initialize()
    #If DEBUGGING Then
    Set Assert = New CAssert
    #End If
    End Sub

    ‘ and for the actual usage
    ‘ in a function/sub/get/let in the module or code
    #If DEBUGGING Then
    ‘ all information is sent to the user with class name and other info such as the assert and the function
    Assert.Chk mInputIsInitialized, “mInputIsInitialized”, CLASS_NAME & “:Property Get XStartOffset”

    ‘uses optional arguments to get same syntax as debug.assert
    Assert.Chk mRegionType RectRegionType.NoBars
    #End If

    ‘elsewhere in the project is the following Class Module “CAssert”
    Option Explicit
    Public Sub Chk(Condition As Boolean, Optional Msg As String = “”, Optional ClassName As String = “”)

    If Not Condition Then
    Dim FullMsg As String
    FullMsg = “A debug assertion failed: “”” & Msg & “””” & vbCrLf & vbCrLf
    FullMsg = FullMsg & “Class or Module Name: ” & ClassName & vbCrLf & vbCrLf
    FullMsg = FullMsg & “Please report this assertion message.” & vbCrLf & vbCrLf & “Execution will terminate.”
    MsgBox FullMsg, vbCritical, “Assertion Failed”
    End If

    End Sub

  10. If you want to evaluate 2 conditions to false using debug.assert, then you must use OR not AND. If you look at an AND truth table, True AND False = False, this would not be the desired behavior of debug.assert because you want both of your conditions when false to evaluate to False. For that reason you should use OR instead of AND. The OR truth table will only evaluate to False if both the expressions (ie inputs) are false (False OR False = False), the OR truth table will return true for all other cases.

Leave a Reply

Your email address will not be published. Required fields are marked *