Handling Error while VBA Scripting

Error handling is a technique to execute the code smoothly and pass proper messages to end users when code is executing any task.

Importance of Error Handling: 

Error handling is a best practice to be followed and helps to programmer as well as end user. However technical errors are handled and can be manipulated by programmer and not end user. And so the errors that occurs at user end should pass the error information in general language so that user tries to sort at their end.

Always use the error handling method in each and every module, you can also create sub procedure and call in your main procedure where ever required.

Typically Error types can be categorize broader in three ways:

1. Syntax/Compile Errors
2. Runtime Errors
3. Logical Errors

Syntax errors or Compile Errors are encountered when programmer or developer misinterprets the coding or follows incorrect methods, objects, incomplete array defining, incomplete parenthesis.

Why and when can failure happen in your procedure.

There are chances to occur errors when code executes or run

Incorrect Syntax and keywords - If you are going to write a code of summing two variables, it is always required that you mention proper data type of variable which will store your values.

Limited Testing - Finalizing your code without debugging or testing at your end before handling VBA project to end user.

Technical Issues - Issues related to applications, referenced software, Server Issues, Application Versions

For example let me write the piece of code of addition. Here the code is trying to execute the multiplications of declared values passed through variable a, b & d

Out of a, b and c are integer data types because these variable will store numeric values and d as string because it will store the alphabets.

Sub CalculateNum()
        Dim a, b, c As Integer

        Dim d As String

                a = 0

                b = 10

                d = "abc"

                        c = Application.Worksheets.Sum(a * b)

                MsgBox c

End Sub

In this example we used Sum function to multiply two values stored in a and b. However the syntax is incorrect and that is why it is throwing syntax or compile error when code is getting executed. And so your code is not compiled.

Now let us see the correct syntax.

In VBA SUM function is member of class WorksheetFunction as it is an inbuilt function, so we have to write the correct syntax as below:

                   c = Application.WorksheetFunction.Sum(a * b)

Another example -

Sub WelcomeMessage()

           Dim x, y

           x = Environ("username")

           y = Now(

       Msgbox "Hello, " & x & " Its now " & y

End Sub

 In this example we made silly mistakes while writing the code. List Not defining variables explicitly and incomplete syntax of function now(

The Correct procedure would be as follows

Sub WelcomeMessage()

    Dim x As String
    Dim y As Date

        x = Environ("username")
        y = CDate(Now())

            MsgBox "Hello, " & x & " Its now " & y
End Sub

See the result

We learn with this example that we should always respect the coding syntax pass proper variables in syntax to get the result.

Now let us learn Runtime Errors

Runtime errors always occurs when you or end user executes the code, that means occurs when code is running. These types of error also called as exceptions.

These errors occurs though your code is correctly written, however the references, linked data format, data types in source data is incorrect and not matching to the data types mentioned in your code.

Let us take an example of calculating two values.

Sub CalculateNum()

        Dim a, b, c As Integer

        

        a = Sheet1.Range("B4").Value 'Assuming Range("B4") has value 25

        b = 10

             c = Application.WorksheetFunction.Sum(a * b)

       MsgBox c

End Sub

Looking at this example your code will successfully execute and return Value as 0 in message box. However we are expecting that the result should be 250, but though you have referenced Range("B4") as value of a and there is no value in a. This would result you are not getting expected result.

Now here comes your skill to have a certain check point in between which will verify for value in cell "B4"

Sub CalculateNum()

        Dim a, b, c As Integer

        

        a = Sheet1.Range("B4").Value 'Assuming Range("B4") has value 25

        b = 10

'Check if value available in referenced cell, if no value throw an message box in general language which end user would be able to interpret the exception and follow the method.

      If IsEmpty(a) Then

           Msgbox "No Value in cell to calculate"

           Exit Sub

      End if

             c = Application.WorksheetFunction.Sum(a * b)

       MsgBox c

End Sub

Now let us learn

Logical Errors - This is bit critical to trace, however these types of errors is something though you write the code and executed successfully and you are not getting correct result. These types of errors can be overcome at developer end only and developer should always apply proper logic while writing the code step by step.

Error Handling - 

It becomes important to every developer to deal with possible errors from user end prospective. Developer has to inject the Error handling code where ever necessary in every procedure. Any runtime error is fatal unless you use On Error Statement in procedure.

Syntax:

           On Error {goto {line | 0 | -1 | Resume Next

Error Object - 

Err.Clear

Err.Description

Err.Number

Err.Raise

Using objects, it helps developer to track the error type occurred and to capture error information, you need to use Error Objects.

You can write the code for error objects

MsgBox "Error Number " & CStr(Err.Number) & " " & Err.Description

Err.Clear

This will help you debug the code faster and easily.

Handling errors can be used in 4 ways.

1. On Error Resume Next 

    Tells the compiler that If error occurs while executing, then skip that fatal statement and execute the next command. In this scenario, though code has a flaw in execution, you are telling compiler to execute the rest of code by ignoring the error occurred if any

2. On Error Goto 0

    Your are informing the compiler that if any error encountered while executing the code, just reset the error number to 0 that means release error and sets to nothing. However in this statement you would not be able to capture the actual error description and becomes difficult to handle error effectively.

3. On Error Goto -1

    Here you telling that if any error irrespective of error description reset the error number to nothing. This is almost same like On Error Goto 0

4. On Error Goto to Label (Here Label is nothing but name of your error code. This can anything. However it is recommended that you should label the Error handling code properly.

Let us take an example of On Error Goto Labelname

Sub OnErrorGotoLabelName()

On Error GoTo ErrHdlr

Dim x, y, z As Integer

x = 50
y = 0

z = x / y

ErrHdlr:

         Select Case Err.Number

            Case 13 'Run-time error

            MsgBox "You are trying to divide the value by zero."

         Case Else

            MsgBox "Unknown error.. " & Err.Number & ":" & Err.Description

End Select

            Resume Next

End Sub

Try these examples and learn how to handle errors in VBA

Subscribe to my blog for Tested coding, regular and unique articles.