The result would be incorrect, but the macro would (usually) continue to run to completion.
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.įor example, you may accidentally code your macro to add together the wrong variables in a procedure. However, the 'bug' may cause the macro to perform unexpected actions or return an incorrect result. Logical Errors, otherwise known as 'bugs', occur during the execution of the VBA code, and allow the code to continue to run to completion. If desired, instead of re-trying the file, the Sub procedure could be terminated at this point, by using the Exit Sub command. Once the user does this and clicks OK, the code is resumed and a further attempt is made to open the file. 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. "Please add the workbook to C:\Documents and Settings and click OK" 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. These statements capture a runtime error and divert the macro into a specified section of VBA code, where the error is handled. In order to assist with runtime error trapping, VBA provides us with the On Error and the Resume statements.
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. 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. Some runtime errors may not be caused by faulty code. define i as an integer, then attempt to assign the string "text" to i)įile not found(occurs when attempting to open a file)
Type mismatch(this error arises when you attempt to assign the wrong type of value to a variable - e.g. if you define an array indexed from 1 to 10, then attempt to access entry no. Subscript out of range(this error arises if you attempt to access elements of an array outside of the defined array size - e.g. However, some of the more common VBA error messages are shown in the table below: 5 The different runtime error codes are explained on the Microsoft Support Website. 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→Local s Window). 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. In the above example, the information provided in the message box, and the highlighted line of code, make it very easy to spot the cause of the error.
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. 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.įor 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".ĭepending on the structure of your VBA project, you may be given the option to debug the code, (see below). An error occurred while downloading the file.Runtime errors occur during the execution of your code, and cause the code to stop running.