Types of errors in VBA

When running Excel macros, errors can occur, which in VBA are divided into three categories:

• Compilation errors
• Runtime errors
• Logic errors (bugs)

Next, we will talk about each of the three types of VBA errors in detail.

Compilation errors

The VBA compiler treats compilation errors as invalid and highlights them in the code before it even gets to run the macro.

If a syntax error is made while writing the code, the VBA editor signals this immediately: either using a message box or highlighting the error in red, depending on the status of the mode. Auto Syntax Check.

Note: When enabled Auto Syntax Check each time a syntax error appears in the Visual Basic editor in the entered code, a corresponding message will be displayed. If this mode is turned off, then the VBA editor will continue to report syntax errors, simply highlighting them in red. option Auto Syntax Check can be turned on/off in the menu tools > Options Visual Basic editor.

In some cases, a compilation error may be detected when compiling the code, just before the macro is executed. Usually, a compilation error is easy to detect and fix because the VBA compiler provides information about the nature and cause of the error.

For example, the message “Compile error: Variable not defined‘ when trying to start executing VBA code, indicates that an attempt is being made to use or refer to a variable that has not been declared for the current scope (this error can only occur if Option Explicit).

Runtime errors

Runtime errors occur during code execution and cause the program to stop running. This type of VBA error is also usually not difficult to detect and fix, as it provides information about the nature of the error and the location in the code where it stopped.

An example of such an error would be an attempt to perform a division by zero. As a result, the message “Run-time error ’11’: Division by zero«.

Depending on the structure of the VBA project, you may be prompted to debug the code (as shown in the figure below). In this case, when you click on the button Debug (in the debug message box) will highlight the line of code that caused the VBA error.

Having received such a message and seeing the highlighted line of code, as in the example above, it will not be difficult to find the cause of the error.

If the code is more complicated than in our example, then to get more information about the cause of the VBA error, you can check the values ​​of the variables used. In the VBA editor, it is enough to hover the mouse pointer over the variable name, or you can open the local variable tracking window (in the editor menu View > Locals Window).

Various runtime error codes are deciphered on the Microsoft Support site (in English). The most common VBA errors are listed in this table:

Catching Runtime Errors

Not all runtime errors are caused by flaws in the code. For example, a VBA error cannot be avoided if a macro needs to open a data file and the file does not exist. In such cases, it is a sign of professionalism to catch errors and write VBA code that will be executed when they occur. Thus, instead of unpleasant failures, a graceful termination of the macro will occur.

To help deal with errors that occur, VBA provides the developer with statements On Error и DESCRIPTION. These statements track errors and direct macro execution to a special section of VBA code where the error is handled. After executing the error handling code, the program can be continued from the place where the error occurred, or the macro can be stopped completely. This is shown below with an example.

'The Sub procedure assigns to the variables Val1 and Val2 the values ​​'stored in cells A1 and B1 of the workbook Data.xlsx located in the directory C:Documents and Settings Sub Set_Values(Val1 As Double, Val2 As Double) Dim DataWorkbook As Workbook On Error GoTo ErrorHandling ' Open a workbook with data Set DataWorkbook = Workbooks.Open("C:Documents and SettingsData") 'Assign data from the workbook to variables Val1 and Val2 DataWorkbook Val1 = Sheets("Sheet1").Cells(1, 1) Val2 = Sheets( "Sheet1").Cells(1, 2) DataWorkbook.Close Exit Sub ErrorHandling: 'If the file is not found, prompt the user to place it in 'the correct location and continue MsgBox "Workbook not found!" & _ "Please add the Data book .xlsx to the C:Documents and Settings directory and click OK." Resume End Sub

This code attempts to open an Excel file named Data. If the file is not found, then the user will be prompted to place this file in the correct folder. After the user does this and clicks OK, code execution will continue and the attempt to open this file will be repeated. Optionally, instead of trying to open the desired file, executing the procedure Sub can be interrupted at this point with the command Exit Sub.

Logic errors

Logical errors (or bugs) occur during the execution of VBA code, but allow it to run until completion. True, the result may not be the actions that were expected, and an incorrect result may be obtained. These errors are the hardest to spot and fix because the VBA compiler doesn’t recognize them and can’t point to them in the same way that compilation and runtime errors do.

For example, when creating a macro in a procedure, variables that were not supposed to be summed were accidentally summed. The result will be erroneous, but the macro will continue to run until completion.

The Excel VBA editor provides a set of debugging tools to help you find and fix logical errors in your VBA code. In this article, we will not consider these tools in detail. The inquisitive user can find an overview of the VBA debugging tools on the Microsoft Help & Support site (in English).