VBA Errors

There are 3 types of VBA error that you may encounter when executing your macro. These are Compile Errors,Runtime Errors and Logical Errors (or 'bugs'). These VBA error types are discussed in turn, below, although more space is allocated to the Runtime Errors, and the way in which you can cater for these in your VBA code.

Compile Errors

Compile Errors are recognised by the VBA compiler as being illegal and therefore, are highlighted as errors before your macro even starts to run. If a compile error is an incorrectly formatted line of VBA code, the VBA editor will immediately detect and highlight this, as soon as you attempt to move your cursor away from the specific line of code. Alternatively, a compile error may be detected at the time you attempt to run your macro (but before execution has started).

A compile error is generally easy to fix, as the VBA compiler will give you information on the nature of this type of VBA 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 attempting to use, or access, 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 generally 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 subroutine.

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).

A list of the runtime error codes is supplied on the Microsoft Help & 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 - eg. 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 - eg. 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

Although many runtime errors can be eliminated by repairing faulty code, in many cases, the errors cannot be foreseen. 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. Following the error handling, 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.

' Subroutine 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 code above, the subroutine attempts to open the Excel File 'Data' and if it fails to find the 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 subroutine could be terminated at this point, by using the Exit Subcommand.

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 site. However, an overview of the Excel VBA debugging tools is provided on the Microsoft Help & Support Website.