- 1 Introduction
- 2 A Startling Fact About Errors in Code
- 3 VBA’s Built-In Error Detection Tools
- 4 Why Use the VBA Debug.Assert Statement?
- 5 What are Assertions?
- 6 You Tube Video on Debug.Assert
- 7 Using VBA Debug.Assert to create an Assertion
- 9 How to Use the VBA Debug.Assert Statement
- 10 VBA Assert Versus Error Handling
- 11 How to Turn VBA Debug.Assert On or Off
- 12 What to Avoid with Debug.Assert
- 13 Using Assertions With Collections and Objects
- 14 Summary
- 15 Major Advantages of Assertions
- 16 What’s Next?
This post provides a complete guide to the VBA Assert statement(i.e. Debug.Assert). The VBA Assert statement is a very powerful statement that simply evaluates a condition to be true or false.
But don’t let the simplicity fool you. When used correctly, the VBA assert statement 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 the VBA Assert statement 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 VBA Assert statement allows us to create assertions in our code. Assertions are available in almost all programming languages. Before we look at the VBA Assert statement and creating assertions lets have a quick look at a startling fact about errors.
A Startling Fact About Errors in Code
“I am indeed amazed when I consider how weak my mind is and how prone to error.” – Rene Descartes
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:
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 the Debug.Assert statement.
VBA’s Built-In Error Detection Tools
VBA has built-in 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.
VBA checks each line of code as you write it for syntax errors. When you press Return it will check the line for errors and display an error message if it finds one.
Syntax errors are when you write code that doesn’t follow the rules of Visual Basic syntax. Most of these errors are caused by omitting expected syntax.
Some examples of syntax errors are:
' Error: The keyword then is missing. ' Error message: "Expected: Then or GoTo" If a > b ' Error: The equals sign is missing after i. ' Error message: "Expected: =" For i 2 To 7 ' Error: The right parenthesis is missing ' Error message: "Expected: List separator or )" b = left("ABCD",1 ' Error: goto is not required ' Error message: "Expected: end of statement" i = x goto
The compiler finds errors that involve more than one line of code. It checks the entire project for errors. To compile select Debug->Compile VBAProject from the menu.
Examples of compiler errors are as follows:
- An If statement without a corresponding “End If” statement.
- A Select statement without a corresponding “End Select” statement.
- A With statement without a corresponding “End With” statement.
- Calling a sub or function that doesn’t exist – normally because it’s renamed or deleted.
- Calling a sub or function and not providing the expected parameters.
Note: When you run the code it will also find compile errors. However, it will only check the code in the subs/functions that it reaches. Therefore you should always use Debug->Compile to find and remove errors before you run your code.
The Runtime Error Handler
When an error occurs, while our code is running, VBA will use display the error and pause the code if you are not using any error handling methods like “On Error Goto”. If you want to know more about error handling I have written an entire post about it here.
The problem with these errors is that they may not occur where the actual problem is. So we have to debug the code(i.e. step through the code line by line) to find the origin of the error. This can be very time consuming trying to recreate the error and trace the origin.
We can use Assertions to help us to detect the error as soon as it occurs. Assertions can also help us to detect potential errors that we are unaware of.
Why Use the VBA Debug.Assert Statement?
When we are writing our code there we can spend a lot of time trying to find and fix errors. When we are constantly adding code are introducing errors that we may not be aware of.
What we need is an early detection system for errors.
A great description of Assertions is as follows:
“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 errors to remain undetected. Therefore, running your code while using Assertions is a fantastic way of smoking out errors.
What are Assertions?
Assertions are used when writing code 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 very 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 around the city streets. Assertions would be checkpoints at each intersection that ensure the vans(variables) contain valid goods(values) before they are allowed to pass.
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 it is for errors to remain undetected.
In the city example, the checkpoints would affect performance. They would slow the city traffic considerably. The beauty of the VBA Debug.Assert statement is that there are no performance issues. This is because 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 effect on how your code runs for the user.
You Tube Video on Debug.Assert
Watch the explanation of Debug.Assert in this video:
You can get the demo workbook and code for the video here.
Using VBA Debug.Assert to create an Assertion
It is simple to create an assertion in VBA. You use the function Debug.Assert followed by a Condition like this:
' true if x is greater than zero Debug.Assert x > 0 ' true if the text variable is not empty Debug.Assert text <> "" ' true if the month is in the range 1 to 12 Debug.Assert month >= 1 And month <= 12
When the code meets a Debug.Assert line it evaluates the condition to be either true or false. The following happens based on the condition:
- True – the code continues as normal.
- False – the code pauses on the line.
When the code pauses we can then check the variables and see what the problem is.
Here is an example of how that works:
' A simple example of using Debug.Assert ' ExcelMacroMastery.com Sub DebugAssertExample() Dim month As Long month = 12 Debug.Assert month >= 1 And month <= 12 ' TRUE - The code will continue month = 6 Debug.Assert month >= 1 And month <= 12 ' TRUE - The code will continue month = 15 Debug.Assert month >= 1 And month <= 12 ' FALSE - The code will pause here End Sub
How to Use the VBA Debug.Assert Statement
The best way to use the VBA Assert statement is to test the following items:
- The input values of a Sub/Function
- A value before it is returned from a function
- A value that is received from a function
- A global variable before it is used
The following functions use the VBA Debug.Assert statement to test:
- the values of the input parameters (called preconditions)
- the value that is being returned(called postconditions)
' https://excelmacromastery.com/ 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 Else 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:
' https://excelmacromastery.com/ 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
VBA Assert 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 may 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 its previous state – ready for you to select a file to play as if nothing happened. The application should not stop working or crash.
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.
' https://excelmacromastery.com/ 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 Else ' Open workbook Workbooks.Open filename End If End Sub
The difference between Assertions and Error Handling is that:
- Assertions are used when you are writing and testing the code. They should be turned off when you give the application to the end-user.
- Error Handling is primarily used to handle errors that happen when the application is being used by the end-user. If you are writing an application for yourself, it is also very useful to have some error handling but less is required.
- Assertions are used to inform the person writing the code when errors occur.
- Error Handling is used to provide the user with a friendly message when an error occurs.
- Assertions will stop in the code where the errors occur.
- Error Handling will keep the application running.
- Assertions deal with errors coming from an internal source – within your code.
- Error Handling deals with errors coming from an external source e.g. opening files, user input, spreadsheet data etc. It can also deal with internal errors but we hope to have these removed by the time we give the application to the end-user(see unexpected errors).
Let’s update the example above so it uses both Assertions and Error Handling:
' https://excelmacromastery.com/ Function ReadMonth(ByVal filename As String) As Long ' USE VBA ERROR HANDLING MECHANISM FOR UNEXPECTED ERRORS On Error Goto eh ' USE Debug.Assert TO CHECK VALUES FROM INSIDE APPLICATION Debug.Assert filename <> "" ' USE ERROR HANDLING FOR EXPECTED ERRORS ' Use Dir to check the file exists Dim wk As Workbook If Dir(filename) = "" Then ' Inform the user MsgBox "Could not find the workbook: " + filename Goto done Else ' Open workbook Set wk = Workbooks.Open(filename) End If ' Return the month ReadMonth = wk.Worksheets(1).Range("A1").Value ' USE Debug.Assert TO CHECK THE MONTH IS CORRECT Debug.Assert ReadMonth >= 1 And ReadMonth <= 12 wk.Close done: Exit Function eh: ' Handle unexpected errors here MsgBox "The following error has occured: " & Err.Description End Function
If these seem confusing just remember you mostly use assertions on arguments and return values.
How to Turn VBA Debug.Assert On or Off
Assertions are used to check your code during development(writing the code) or maintenance(adding updates to the code or fixing errors). When you give your application to a user you then you turn the Assertions off. This provides us with two really great advantages:
- You can add as many Assertions as you like as it will not have any impact on the code you give the user.
- If you are in doubt, about adding an Assertion, just add it anyway. You can easily remove it and it won’t affect the user.
Let’s look at how to turn Debug.Assert 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 keyword . First of all we need to create a “Conditional Compilation Argument” that we can use in the #If statement. Ignore the cryptic sounding name as it very straightforward to use.
Select Tool->VBAProject Properties from the VBA menu:
You can see in the screenshot that we have written Debugging = 1 in the “Compilation Arguments” textbox. We can use any name we like. I have us Debugging here but we can call it anything. The =1 means it is currently true.
We use this argument to decide if we are going to use certain VBA code when our application runs. The following code shows how to use the #If statement with the compilation argument i.e. in this example Debugging is the compilation argument:
' https://excelmacromastery.com/ Function GetType(price As Long, shipType As String) As Double #If Debugging Then ' Test inputs 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(i.e. set to a value other than 0). When we turn Debugging off(set to zero) 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 or remove it altogether. Both of these situations mean that all the code inside the #If Debugging statements will not be used.
Making The Code Neater
If we have #If statements everywhere in our code it can look really untidy. We definitely don’t want code like this:
Sub UnTidyCode() #If Debugging = 1 Then Debug.Assert coll.Count > 10 #End If #If Debugging = 1 Then Debug.Assert Text <> "" #End If #If Debugging = 1 Then Debug.Assert amount < 1000 #End If End Sub
We can create a much nicer version of this code by creating our own simple DebugAssert sub:
Public Sub DebugAssert(ByVal condition As Boolean) #If Debugging = 1 Then Debug.Assert condition #End If End Sub
We can use it as follows:
Sub TidyCode() DebugAssert coll.Count > 10 DebugAssert Text <> "" DebugAssert amount < 1000 End Sub
You can see that this makes our code much neater and much more readable.
What to Avoid with Debug.Assert
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:
' https://excelmacromastery.com/ 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:
- If the Collection has been created
- If the collection has elements
Let’s look at how to create a collection. We can declare in one line and create in a second line like this code shows
' https://excelmacromastery.com/ 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 a Collection in one single line as the next example shows.
' https://excelmacromastery.com/ 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
' https://excelmacromastery.com/ 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.
' https://excelmacromastery.com/ 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:
' https://excelmacromastery.com/ 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.
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.)
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.
Thanks Dave, Glad you like it.
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.
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
Thanks for this good article, good in debugging!
Thanks Fei. Glad you like it.
Very good information here!
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!
Glad you like it:)
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
Thanks Jeroem. Glad you like it.
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!
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.
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
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.
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)?
MZ Tools finds variables that have been declared but not used.
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
‘ in the class at bottom
Private Sub Class_Initialize()
#If DEBUGGING Then
Set Assert = New CAssert
‘ 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
‘elsewhere in the project is the following Class Module “CAssert”
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”
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.
What example are you referring to?
Great knowledge you’re sharing. You really explains things thoroughly and in a way that even I understand it 😉
I have to agree with a lot of the statements above … I’m self-raught and a wee bit proud of that but that comes with a lot of looking up topics on sites. I wish I had found your site sooner – it’s by far the best one I’ve come across. Thanks for making a smart site that doesn’t assume too little or too much.
Self-raught. That’s great. I’m SO proud of myself. Sigh.
You’re welcome Joseph
Thanks, Paul. Nice to see Debug.Assert well-explained, at last.
One question: In the example where you have defined the #If block as a single procedure which then gets called from anywhere, I’m wondering whether the compiler is smart enough to ignore those procedure calls when the #If statements are disabled. I’m imagining that even when #if is disabled, the compiler will still generate the calls, and that the calls will just return nothing and the code will keep going smoothly. But would the do-nothing dead-end calls still eat up some clock cycles? Or is the compiler smart enough to determine that the calls don’t do anything and so pass over them without even following them?
Admittedly, in most cases a dead-end call wouldn’t make a measurable difference; but if you had such a call in the middle of a large loop, I’m imagining a potential performance hit. Probably immeasurably small in most cases, though.
The compiler will make the procedural call but in most cases, it shouldn’t add much of an overhead. If the debug.assert must be in a loop then it’s better to use the if statement here rather than the procedural call.
Such a great post!! I am relatively new with VBA, and has been looking for information on debugging. Thanks a lot for sharing your great knowledge!!!
Thanks. Glad you found it useful.