Common VBA Code Bugs: Debugging and Solutions

Compile Errors

First we will discuss about the Compile 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 highlight 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 compile error is an incorrectly formatted line of VBA code, the VBA editor will immediately detect and highlight this(by 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 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

A type of VBA error that occurs during the execution of your code or macro. This type of error cause the program to crash (stop abruptly). Again just like a Compile error, Runtime error 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 thus 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 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 the logical error. Logical error just like Runtime error occurs during the execution of your code but instead of crashing they either give “abnormal” or incorrect result or cause the macro to preform unexpected actions. These errors are the most difficult to find and fix off the 3 errors like it does with Compile and Runtime errors. The VBA compiler cannot find and point to these errors. These error are caused by the incorrect “logic” that the user has written in 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.