Return to VBA Code Examples

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.

VBACompileError VarNotDeclared

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

VBACompileError Debug

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

In the Menu, select Debug > Compile Project.

VBACompileError Menu

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.

VBACompileError NoProcedure

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

VBACompileError NoNext

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

VBACompileError NoEndIf

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.

VBACompileError MissingRef

 

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.

VBACompileError RefBox

 

 

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as


Learn More!