VBA Error Types

There are three types of VBA error that you may encounter when executing an Excel macro. These are:

·         Compile Errors;

·         Runtime Errors;

·         Logical Errors ('bugs').

These three VBA error types are discussed in turn, below.

Compile Errors

Auto Syntax Check Option

The Auto Syntax Check option causes a message box to pop up whenever a syntax error is typed into the visual basic editor.

If this option is switched off, the visual basic editor still highlights syntax errors in red.

The Auto Syntax Check option is accessed by selecting Tools→Options from the menu at the top of the visual basic editor.

Compile Errors are recognized by the VBA compiler as being illegal and therefore, are highlighted as errors before your macro even starts to run.

If you type in a syntax error, the VBA editor will immediately highlight this, either by popping up a message box or by highlighting the error in red, depending on the setting of the Auto Syntax Check option (see right).

Alternatively, a compile error may be detected when the code is compiled, just before being executed.

A compile error is generally easy to fix, as the VBA compiler pops up a message box, which provides information on the nature of the error.

For example, if you get the message "Compile error: Variable not defined"when you attempt to run your code, this indicates that you are referencing a variable that has not been declared in the current scope. (This error is only generated when you are using Option Explicit).

Runtime Errors

Runtime errors occur during the execution of your code, and cause the code to stop running. This type of VBA error is also usually relatively easy to fix, as you will be given details of the nature of the error, and shown the location where the code has stopped running.

For example, if your code attempts to divide by zero, you will be presented with a message box, which states "Run-time error '11': Division by zero".

Depending on the structure of your VBA project, you may be given the option to debug the code, (see below). In this case, clicking on the Debug button on the debug message box, causes the line of code that generated the VBA error to be highlighted in your vba editor.

Due to the message box content and the highlighted line of code in the above example, it is very easy to spot the error in this code.

If your code is more complex, you can gain further information on the reason for the VBA error by looking at the values of the variables in use. This can be done in the VBA editor by simply hovering your mouse cursor over the variable name, or by opening the local variables window (by selecting View→Locals Window).

The different runtime error codes are explained on the Microsoft Support Website. However, some of the more common VBA error messages are shown in the table below:

5	-	Invalid procedure call
7	-	Out of memory
9	-	Subscript out of range (this error arises if you attempt to access elements of an array outside of the defined array size - e.g. if you define an array indexed from 1 to 10, then attempt to access entry no. 11)
11	-	Division by zero
13	-	Type mismatch (this error arises when you attempt to assign the wrong type of value to a variable - e.g. define i as an integer, then attempt to assign the string "text" to i)
53	-	File not found (occurs when attempting to open a file)

Trapping Runtime Errors

Some runtime errors may not be caused by faulty code. For example, if you need to open a file, that contains essential data for your macro, you can't avoid the generation of a VBA error, if the file does not exist. In these types of cases, it is far more professional to 'trap' the error, and write VBA code to handle it, so that your macro exits gracefully, rather than having your macro crashing.

In order to assist with runtime error trapping, VBA provides us with the On Error and the Resume statements. These statements capture a runtime error and divert the macro into a specified section of VBA code, where the error is handled. After the error handling code has run, the programmer can request that the VBA code resumes from the point of the error, or alternatively, the macro can be terminated cleanly. This is shown in the example code below.

' Sub procedure to set the supplied values, Val1 and Val2 to the values ' in cells A1 and B1 of the Workbook "Data.xls" in the C:\ directory Sub Set_Values(Val1 As Double, Val2 As Double) Dim DataWorkbook As Workbook On Error GoTo ErrorHandling ' Open the Data Workbook Set DataWorkbook = Workbooks.Open("C:\Documents and Settings\Data") ' Set the variables Val1 and Val2 from the data in DataWorkbook Val1 = Sheets("Sheet1").Cells(1, 1) Val2 = Sheets("Sheet1").Cells(1, 2) DataWorkbook.Close Exit Sub ErrorHandling: ' If the file is not found, ask the user to place it into ' the correct directory and then resume MsgBox "Data Workbook not found;" & _        "Please add the workbook to C:\Documents and Settings and click OK" Resume End Sub

In the above example, the code attempts to open the Excel File 'Data' and if it fails to find the specified file, prompts the user to place the data file into the correct folder. Once the user does this and clicks OK, the code is resumed and a further attempt is made to open the file. If desired, instead of re-trying the file, the Sub procedure could be terminated at this point, by using the Exit Sub command.

Logical Errors

Logical Errors, otherwise known as 'bugs', occur during the execution of the VBA code, and allow the code to continue to run to completion. However, the 'bug' may cause the macro to perform unexpected actions or return an incorrect result. These errors are the most difficult to detect and fix, as there is no way that the VBA compiler can identify and 'point to' the error, in the way that it does for compile and runtime errors.

For example, you may accidentally code your macro to add together the wrong variables in a procedure. The result would be incorrect, but the macro would (usually) continue to run to completion.

The Excel VBA editor provides a number of debugging tools to assist you in finding logical errors in your VBA code, so that they can be fixed. These tools are currently beyond the scope of this page. However, an overview of the Excel VBA debugging tools is provided on the Microsoft Help & Support Website.