VBA HOME PAGE

Menu

Recording macros

Looking at the code

Ways of running macros

Where macros are stored

Reasons to write macros

Writing macros

Procedure types

Visual Basic editor (VBE)

Rules & conventions

Excel objects

Range/Selection objects

Object hierarchy

Object browser

Chart objects

Pivot Table objects

Formulas

Visual Basic Functions

Creating Add-Ins

Variables & constants

Object variables

Arrays

Collections

Message Box

VBA Input Box

Excel Input Box

Making decisions (If)

Making decisions (Case)

Looping (Do...Loop)

Looping (For...Loop)

With...End With blocks

User defined functions

Event handling

Error handling

Debugging

Creating User Forms

DAO/ADO Objects

Input/Output Files

 

Other links

Example code snippets

Userform input example

 

 

 

 

 


Error Handling

No matter how thorough you are when writing code, errors can and will happen.

There are steps that developers can take to help reduce unwanted errors and this is considered just as important as the actual process of the procedure.

Before understanding and applying error-handling routines, planning to avoid errors should be undertaken.

  • Design the procedure’s process electronically or on paper – flow chart and paper test.

  • Creating smaller portions of code – snippets to be called and re-used

  • Using the Option Explicit statement – declaring your variables officially.

  • Syntax checking – user defined commands and functions.

  • Comments – remarking your code at various points.

  • Testing application – functional and usability.

Note: Some of the above points are methodologies which are outside the scope of this reference guide.

 

There are three different types of errors:

1.   Design Time Errors

2.   Run Time Errors

3.   Logical Errors

 

The order of the above progressively is harder to find leaving the last item the most challenging!

 Design Time Errors

The simplest form of error and often caused by typing (typo's) mistakes.

When typing a known keyword or statement, VBA will turn the text to red (default colour) and if the option is enabled, provide a prompt:

To switch off the above prompt, go to Tools select Options… and deselect Auto Syntax Check option.

The routine will instantly cause a run time error if not corrected at the design time and must but resolved before macros can run.

 Run Time Errors

When executing code, no matter how thorough the debugging process has been, code may encounter errors while running. 

There is only one way of doing this - On Error GoTo instruction. It is not a very sophisticated function, but it allows the flow of the code to continue and also where applicable, prevent infinite loops (when the computer keeps on calculating never coming to an end).

Three variations are available:

  1. On Error GoTo LabelName
  2. On Error Resume Next
  3. On Error GoTo 0

 

On Error GoTo LabelName branches to the portion of the code with the label LabelName (‘LabelName’ must be a text string and not a value).

These commands are usually placed at the beginning of the procedure and when the error occurs, the macro will branch to another part of the procedure and continue executing code or end, depending on the instruction given.

'Simple Error handler with Err Object
Sub
ErrorTestOne()
    On Error GoTo myHandler
    
    Dim
intDay As Integer
    intDay = "Monday"
    MsgBox intDay
    Exit Sub

myHandler:
    MsgBox "Error Number: " & Err.Number & vbNewLine _
                              & "Description: " & Err.Description
End Sub

The above procedure will cause an error when executed and users will see:

myHandler is a user defined label (must not use known keywords) which listens for any errors that may occur. When an error is detected, the procedure jumps to a bookmark of the same label with a colon ( : )  (myHandler:) and executes from that point forward.

Using the Err object, developers can return two common properties ‘Number and ‘Description.  The example message box concatenates these two properties into a user-friendly message (see above).

It is important to include the Exit Sub statement prior to the bookmark label otherwise the procedure will execute to the very end of the sub routine and should only be executed when an error has genuinely occurred.

The error above was due to a type mismatch. In other words I declared a variable intDay as an integer and assigned a string value to it.

 

Another example:

'Error to handle incorrect InpuBox value.
Sub
ErrorTestTwo()
    On Error GoTo myHandler
   
    Dim
intInput As Integer
    Dim strResponse As String
    Dim blnErr As Boolean
    intInput = CInt(InputBox("Enter your age:"))
    blnErr = False
    If Not blnErr Then
        If intInput > 64 Then
            strResponse = "You are at the retirement age!"
        Else
            strResponse = "You have " & (65 - intInput) & _
                " year(s) remaining until retirement."
        End If
    Else
        strResponse = "Unknown error entered!"
    End If
    MsgBox strResponse
    Exit Sub

myHandler:
    intInput = 0
    blnErr = True
    Resume Next
End Sub

The above example illustrates how to gracefully handle incorrect (type mismatched) values and then resume the next line of execution using Resume Next statement.

The variable blnErr is flagged as true if an error occurs which is then tested with an If statement.

If the Resume Next is replaced with just the Resume statement, you will find the input box will loop itself until the correct data is entered. Be careful before testing this out due to infinite loops that may occur (if you edit the wrong part of the procedure).

 

The statement On Error GoTo 0 (zero) simply disables the error command during the procedure.

Should users wish to switch off this feature? To switch it back on, just introduce a new statement line of either:

 

  1. On Error Goto myLabel
  2. On Error Resume
  3. On Error Resume Next

 

Any code can be written to handle errors gracefully which can include If and Case statements. It is common to have a Case statement to test which error was fired and deal with it in a separate calling procedure (branch out another procedure).

 Logical Errors

This type of error is the most difficult to trace as its syntax is correct and it runs without any run time errors.

A logical error is one that does not give users any indication that an error has occurred due to the fact that a logical error is the process of logic and not the code itself.

Performing a calculation in a spreadsheet using a function will return an answer but is it the correct answer?

 

Example:

'Logical Error Test Example
Sub
LogicalErrorTest()
    Dim lngQty As Long
    Dim dblNet As Double
    Dim sngDiscount As Single
   
    lngQty = 10
    dblUPrice = 250
    sngDiscount = 0.15
   
   
'Calculate gross (inc VAT @ 17.5%)
   
'Logically INCORRECT!
    MsgBox Round(lngQty * dblUPrice * 1 - sngDiscount * 1.175, 2)

   
'Logically CORRECT!
    MsgBox Round(((lngQty * dblUPrice) * (1 - sngDiscount)))

End Sub

The above procedure showed a quantity (intQty) of goods, with a unit price (dblUPrice), a discount (sngDiscount) at a fixed vat rate of 17.5%.

To calculate the correct gross value, there is an order of which operands are calculated (see Formulas) first and without the care of using brackets, the system follows the rules of mathematics and looks at the operator’s precedence automatically.


The first message box shows:

WRONG!

Followed by the second message box:

CORRECT!

Both calculations worked but the first was illogical to the objective of the process (workflow).

 

How we find such errors? Debugging tools!

 

 


Next Topic: Debugging

 

Want to teach yourself Access? Free online guide at About Access Databases

Tip: Save you work before running error examples that contain loops (which try again).

VBA Keywords: On Error GoTo, MsgBox, InputBox, CInt, Dim, Resume, Resume Next, Round, If...Then...Else.

 
Home | Terms of Use | Privacy Policy | Contact

© copyright 2010 TP Development & Consultancy Ltd, All Rights Reserved.

All trademarks are copyrighted by their respective owners. Please read our terms of use and privacy policy.