How to Compile VBA Code

Creating an Execuatble File
In all VBA programs (Access, Word, Excel, PowerPoint, …) compiling does not translate into creating a standalone executable file (exe). Compiling is a process which performs a minimal validation on the code to ensure it is “problem free” (I use that term very loosely!).

An extremely common question or subject brought up by many answerers on countless forum thread always comes back to compiling your VBA code.

Why Compile VBA?

The easiest answer is simply that it reviews your code to ensure it ‘sanitary/clean’ code.  This means it checks that all the required input variables are present, it validates that all required variable declaration are present (assuming you use Option Explicit, which you should!), …

By Compiling your VBA code, you minimize runtime errors and crashes.

 

How To Compile VBA?

The following is an example of compiling VBA code within MS Access, but the principle is the same across all Office applications: MS Word, MS Excel, MS PowerPoint, …

Open your MS Access database
Access Blank GUI

Press CTRL+G (or ATL+F11) to enter the VBE (Visual Basic Editor)

On the Standard Toolbar, Goto Debug -> Compile YourDatabaseName

Access VBA Compilation

Below is an example or one type of error that can be flagged (missing variable declaration within an Option Explicit module)

Access VBA Compilation Error Example

So once you successfully address all the flagged errors, the compilation should complete with reporting any more errors, thus your VBA code is clean and you can continue to distribute your App.

Normally, you will learn to compile your code regularly while you code, thus saving you excessive hassle at the end.

In the case of MS Access specifically, you will be unable to create a compiled version of your database (mde or accde) until your code can be compiled without error!  Once again illustrating the importance of regular compilation.

 

Whether it be MS Access or any other MS Office Application (Word, Excel, PowerPoint, …) with some VBA in it, I highly recommend ensure it compiles before distributing it or releasing it for official use.

11 responses on “How to Compile VBA Code”

  2. John smith

    This function is more like error-checking instead of giving a stand alone executable file.

    1. Daniel Pineault Post author

      Yes, compiling (in VBA) is a form a error checking to ensure your code is “error free” to try and avoid any real-time errors being thrown.

      As for distributing a single .exe, you should review my article Convert Your Access Database Into An Exe some of the proposed alternatives enable you to create a package that will install Access, if required, then proceed to install your db. So it is indeed possible to create a single exe for distribution. Then again, I have never seen any problem with creating a simple folder structure containing multiple file and a setup.vbs to do the same, but that’s just me!

  3. Rashmi

    Hi Daniel ,

    I am new to MS access and want to know is there some way to compile all VBA code within MS Access. I have one application in MS access . User is interacting with GUI ( Access application ) to enter data in Asscess data base. I have some requirement coming to change code in application . Do I need to compile all the code after making changes or just need to compile the code I have made required changes . In both the cases how I can achieve this . Is there some way to compile all code together ? code is written in VB . Also I am not able to open .mbd file in design mode. When I am selecting this mode .mdb is getting closed . Previously I was able to open the file in this mode.

    1. Daniel Pineault Post author

      You need to compile the code whenever you make change to code to ensure it is free of errors, etc. Typically, you debug frequently during development so you don’t end up having to deal with all sorts of error at the end. It is easier to compile a function while developing than trying to compile it a few week later. So do it frequently to avoid any headaches.

      The Debug -> Compile … will compile all the code modules within the database.

      So compile frequently, compile prior to distributing to your users, compile before creating any mde/accde.

  4. Rashmi

    Daniel ,

    Could you please also confirm do we need visio to compile / run MS access project ? While looking for help over the net ,somewhere I found reference of Visio with MS Access . I lost that link and it left me with confusion . Therefore please help me in understanding the requirement for compiling and making executable file ( from .mdb to .mde ) . I have checked in net but did not found any one suggesting this . In your article too it is not mentioned anywhere .

    Making my question precise ” Why and where we need visio in MS access project ( written in VB script ) ?

    Regards
    Rashmi

    1. Daniel Pineault Post author

      You don’t need Visio for an Access database, unless you are interacting with Visio. But I don’t have Visio and my databases work just fine. All you need for an Access database is Access (full version or free Runtime version).

      You cannot make an executable version of an Access database! You can only make a compiled version (mde/accde), but the users still need to have Access installed to work with the database.

  5. Keith

    Good article! Thank you for the information. I read a tip from David Fenton where he suggested added a button to your VBE toolbar to compile your code quickly.

    To do this: go to View > Toolbars > Customize. Go to the “Commands” tab, select “Debug” from the categories frame, and then click and drag “Compile Project” to your toolbar.

    I’ve placed mine right beside the Save button to remind me to compile as often as I save (which I’ve learned to do after nearly every change that is made). Thank you again for the article, and God Bless!

  6. azer

    when we found one error , is there anyway to go to the next one, to fix what we can and get back later to the error not yet adressed ?