What are the most common bugs in VBA code?
While using VBA, you may have encountered many different errors (or bugs). These VBA errors fall into 3 categories:
- Compile error
- Runtime error
- Logical error.
These VBA errors are discussed in below.
Compile Errors
First, we will discuss the compilation error. Compile errors are one of the types of VBA errors that occur during the compilation of your code.
While compiling your code, the Visual Basic Editor goes through each line of your code and since compile errors are recognized as being illegal, if the compiler runs into any compile error, it immediately highlights that line of code (or codes) as errors by the VBA compiler before your macro starts to execute.
Of course, you have to fix the compile errors before you can execute your macro. If a compilation error is an incorrectly formatted line of VBA code, the VBA editor will immediately detect and highlight this (with a yellow line) as soon as you attempt to move your cursor away from the specific line of code (or codes).
Alternatively, a compile error may be detected at the time you attempt to run your macro (but before execution has started).
Here is an example of a compilation error.
As you can see from the above picture, the VBA compiler tells you the nature of this type of error. In fact, the VBA compiler also tells you how to solve a compile error by telling you what exactly you did wrong.
In the above picture, you get the compile error “Block if and without End if”.
This error only comes when you didn’t terminate the if condition by simply writing End If before the End Sub statement you can fix this error. Compile errors are generally easier to fix than logical and Runtime error.
Another example of a compilation error:
Runtime Errors
This is a type of VBA error that occurs during the execution of your code or macro. This type of error causes the program to crash (stop abruptly). Again, just like a compilation error, runtime errors are also generally easier to solve.
The VBA compiler tells you the nature of this type of error as well as the location where your code has stopped working (this is highlighted by a yellow line).
This is an example of a runtime error. In this example, you have written a code that tries to divide by the number zero, so you get a message box stating “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, if you click on the Debug button on the debug message box, it causes the line of code that generated the VBA error to be highlighted (in yellow) in your VBA editor.
Thanks to the message box content and the highlighting of code, it is very easy to spot this error in the sub routine.
However, If your code is much more complex, then you can get more 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 placing your mouse cursor over the variable name, or by opening the local variables window (by selecting View > Locals Window).
Logical Errors
The last type of VBA error is a logical error. Logical errors, just like runtime errors, occur during the execution of your code, but instead of crashing, they either give “abnormal” or incorrect result, or cause the macro to perform unexpected actions.
These errors are the most difficult to find and fix of the three types, unlike Compile and Runtime errors. The VBA compiler cannot find and point to these errors. These errors are caused by the incorrect “logic” that the user has written in the code (incorrect line of code).
For example, suppose you have 3 variables: A, B and C. A has value 3(A=3), b has value 4(B=4) and c has value 2(C=2). Now, you may code your macro to add variable A and C while you actually needed to add variable A and B.
The result will be incorrect, but the macro will still run and not crash.
Leave a Reply