Exception Handling in VBA

This post is about something often ignored by VBA programmers. Many people think that handling exceptions in VBA is simply useless. It is much better, they say, to see the error, open the VBA Editor and go fix it right away.

For those we ask: would you do the same if it was a Java application? What about a C++ application? 

Of course one of the main advantages of VBA is the fact that the programming environment is right there next to the application and ready to receive modifications on the fly. But our purpose here is to create programs that can run as stable as a python application, with no need of any kind of manual intervention on the code whatsoever.

One of the best ways to avoid your program to bizarrely crash is to use exception handling.

We don't want to spend time explaining what exception handling is because there are many other source on the internet that can explain it better than we. But for those who have no idea what exception handling is, let us try to explain it in our on way:

Exception Handling is the programmers response to an unexpected failure event of the system. In a more practical way: it is a part of code that is executed when another part of the code fails by any reason.

VBA has only one way that we know of dealing with exceptions: the On Error clause. Let us give an example of how it is used:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Option Explicit

Public Sub Exception_Handling_Example()

    

    Dim x as Double 

   On Error GoTo label_01

        x = 1000 / 0

        Exit sub

label_01:

   Exit Sub 

End sub 

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

The code below, when compiled, will present no errors. But when executed, it should generate an exception because we are trying to divide a number by zero. But since we've put the "On Error GoTo" clause, instead of halting the program and showing an error message, the program will simply exit.

The process that was developed for this program to end without halting is the Exception Handling. The On Error GoTo label_01 clause tells the program to jump to the label_01 part of the code in the occurrence of an exception.

All the lines of code below the "On Error" clause will be affected by it until another "On Error" clause is used. This means that one "On Error" clause invalidates the previous one so you can use different labels for exceptions  different parts of the code.

Now let us show you our suggestion of what a program with an Exception Handling should be.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Option Explicit

Public Sub MAIN_Exception_Handling_Example2()

    Dim function_name as String

    function_name =  "MAIN.MAIN_Exception_Handling_Example2"

    LOG_MODULE.Log function_name

    

    Dim x, y as Double 

   On Error GoTo error_1000

        x = 1000

   

   On Error Goto error_2000

         

        y = 1000 / 0

       
        LOG_MODULE.Log function_name, 0

        Exit sub

error_1000:

    LOG_MODULE.Log function_name, Err.Description

    LOG_MODULE.Log function_name, "The x caused a problem"

    LOG_MODULE.Log function_name, 1 

    err.Raise(1) 

    Exit Sub 

error_2000:

    LOG_MODULE.Log function_name, Err.Description

    LOG_MODULE.Log function_name, "The y caused a problem"

    LOG_MODULE.Log function_name, 1

    err.Raise(1)

    Exit Sub

End sub 

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

The program above has elements that we use extensively in all of our codes. This is really the basic layout of pretty much all of the functions we created and that are in our modules. Let us try to explain what is going on.

The first thing one must notice is that this program generates log. This means that the LOG_MODULE must be imported (the LOG_MODULE is available for download in this blog).

The first thing the function does is to log its name. To do so we create an variable, "function_name" and give it the name of the function module plus the function name - the function is in the MAIN module. After that we call the "LOG_MODULE.Log" function and pass the name of the function as a parameter. This prints the parameter - the function name - to the log file, which is generated by the LOG_MODULE.Log function. This is the explanation of the 3 lines below.

    Dim function_name as String

    function_name =  "MAIN.MAIN_Exception_Handling_Example2"

    LOG_MODULE.Log function_name

After that, we declare 2 variables, x and y as doubles. After that we start our 1st error handling scope: we use the "On Error GoTo error_1000" clause which means that if any part of the code from that point on generates an exception, the program must jump to the label "error_1000". This is true until the second error handling scope, which starts with the "On Error GoTo error_2000" clause, which means that if any part of the code from that point on generates an exception, the program must jump to the label "error_2000".

This gives our function 2 possible outcomes in the presence of exceptions: the code below the error_1000 label and the code below the error_2000.

One important thing to notice is that this program WILL halt in the event of an exception. This is because we put the "Err.Raise(1)" method in the end of each error handling. This method "raises" the exception so that if the function is being called by another function, the parent function will get an exception. Had we not put this clauses, the function would never halt, it would just log the err.Description message, log the "ERROR" message and exit the function.

This might be a little confusing for those who are not used to handle exception. But think this way: sometimes we can not predict what will happen to our program. For example, we can ask for a database connection and the database management system might be out, so we would get an connection error - which would cause an exception. In that case, we might not want the program to enter debug mode and show an error message, but rather show us, say, a friendly message or even to try to connect again without showing any messages. We would also like to see what happened written in a log file, so we wouldn't have to go into the source code and execute the program step by step to debug it.

That is exactly what this way of handling exception does. It gives the tools for the programmer to decide what to do in the event of a exception. And we strongly recommend the logging, so that the exception messages are stored in he log file so that the programmer can understand what happened.

Keep in mind that all the modules we will publish have this structure. They all generate log and they all handle exceptions. This makes them as robust as, say, Java or Python libraries (of course, giving VBA's limitations) and this strucuture is ideal to create real VBA application that are designed to work like any other real application.