VBA Compile Error

This tutorial will explain what a VBA Compile Error means and how it occurs.

Before running your code, the VBA Editor compiles the code. This basically means that VBA examines your code to make sure that all the requirements are there to run it correctly – it will check that all the variables are declared (if you use Option Explicit which you should!), check that all the procedures are declared, check the loops and if statements etc. By compiling the code, VBA helps to minimize any runtime errors occurring.

(See our Error Handling Guide for more information about VBA Errors)

Undeclared Variables

If you do not declare variables, but your Option Explicit is switched on at the top of your module, and then you run the macro, a compile error will occur.

If you click OK,  the relevant procedure will go into debug mode.

Alternatively, before you run your code, you can force a compilation of the code.

In the Menu, select Debug > Compile Project.

The compiler will find any compile errors and highlight the first one it finds accordingly.

Undeclared Procedures

If you code refers to a procedure that does not exist, you will also get a compile error.

For example:

Sub CallProcedure()
'some code here then 
  Call NextProcedure
End Sub

However, if the procedure – NextProcedure does not exist, then a compile error will occur.

Incorrect Coding – Expected End of Statement

If you create a loop using For..Each..Next or With..End With and forget to and the Next or the End With… you will also get a compile error.

Sub CompileError()
 Dim wb As Workbook
 Dim ws As Worksheet
 For Each ws In wb
   MsgBox ws.Name
End Sub

The same will happen with an If statement if the End If is omitted!

Missing References

If you are using an Object Library that is not part of Excel, but you are using the objects from the library in your variable declaration, you will also receive a compile error.

 

This can be solved by either Late Binding – declaring the variables are Objects; or by adding the relevant Object Library to the Project.

In the Menu, select Tools > References and add the relevant object library to your project.