What is a VBA Project? (And How to Edit)

This tutorial will explain what a VBA Project is and how to edit a VBA Project in Excel.

When we record a macro in Excel using the Macro Recorder, a VBA project file is created in the file that we are working in or in a file called the Personal Macro Workbook. The Project File stores any modules or forms that are created either by using the record macro function in Excel, or by writing the VBA code within a module directly.  To view the VBA Project, we need to access the  Visual Basic Editor (VBE).

Accessing the VBE Editor

There are 2 ways to access the VBE Editor in Excel.  You can either record a macro and then edit the macro, or open the Editor directly from the Excel Developer Ribbon.

Recording a Macro

Excel gives us the ability to record macros, and then to edit the macros in the Visual Basic Editor (VBE) should we need to do so.

In the Ribbon,  select View > Macros > Record Macro.

OR

Developer > Visual Basic > Record Macro

Note: If you don’t see the Developer Ribbon, you’ll need to enable it.  You can learn how to do that here.

1) Type in the name for your macro, and then 2) select where to store the macro. This can be in the Personal Macro workbook, the workbook you are currently editing or a new workbook entirely.

Once you have clicked OK, you can follow the steps that you want in your macro (for example bolding a cell, changing the color of the text, etc.), and then click the stop button at the bottom of the screen to stop recording the macro.

To run or view your macro, in the Ribbon, select View > Macros > View Macros.

OR

Developer > Visual Basic >Macros

Click on the Macro in the Macro name list, and then click on Edit.

This will open the VBE and jump you into the VBA Code.

A VBA Project has now automatically been created for your workbook, and within this project, a module (Module 1) has also been created.  The macro is contained within this new module on the right hand side.

Opening the VBE Editor directly from Excel

To open the VBE Editor from Excel directly, in the Ribbon, select Developer > Visual Basic.

The VBA Project

The VBA Project is where the modules and forms are stored when you write VBA code in Excel.   As we recorded the above macro, a Module was automatically created for us within the VBA Project for the workbook we selected (in this case we selected This Workbook).  Had we selected the Personal Macro Workbook to store the macro in, a module would have been created in the Perseonal.xlsb file.

NOTE: if you choose to save macros in the Personal Macro workbook, when you exit Excel you will get a prompt asking you to save this workbook.  The Personal Macro workbook is a hidden workbook not visible in the Excel screen but visible in the  VBE window.It is used to store modules and forms that can then be used across a multitude of Excel files.    Modules and Forms that are stored in each individual workbook can only be used within that workbook.

If we had opened the VBE Editor directly from Excel without recording a macro, our workbook would have a blank VBA Project file that does not contain any modules or forms.

Each Excel file can only contain 1 VBA project, but can have multiple modules and forms.  It is good practice when programming in VBA to group your procedures logically into relevant modules and often you will end up with a multitude of modules and forms.

In the Book1 VBA Project in the graphic above, we have 2 forms (frmInvoice and frmCustomers), 3 Modules (modCustomerRoutines, modGlobalVariables and modInvoiceRoutines)  and 1 class module (clsCustomers).

Inserting a Module or Form into the VBA Project

To insert a new module into your code, click on the Insert option on the menu bar, and click Module.

Or, click on the Insert Module button which you will find on the standard ribbon.

To insert a new user form into your code, select the UserForm option.

A new UserForm will appear in the Project Explorer and will be shown in the Code Window on the right.

You can also insert a Class Module

A class module is used to insert objects into your VBA project.

Adding a Reference to a VBA Project

You VBA project can also contain references to other VBA projects, or to other applications such as Word or Access.   To view the references that are referred to in your VBA Project, click on the Tools menu, and then click on References.

Should we need to add a new reference, we can select the object library in the References dialog box, or if the object library is not in the list, we can click the Browse button to find the relevant object library file on our PC.

VBA Project Properties

Each VBA Project has a number of properties associated with it. These can be customized to suit your needs.

In the Menu, select Tools and then select VBAProject Properties.

Renaming a Project

In the General Tab, change the name of the project to something more relevant than VBAProject , and add a description to describe your project.  If you click the OK button, you will notice that the name of the VBA Project will change to reflect the new project name in the VBE Project Explorer.

Adding Project Security

We can also password protect our VBA Project.  This will prevent other users from being able to see any of your code and is most useful to protect intellectual property rights!

In the Protection tab, 1) make sure the check is on in the ‘Lock project for viewing’ and then 2) type in your password and confirm the password.

Now if you were to save your workbook, when you reopen the workbook and try to access the VBAProject, it will ask you for a password.

NOTE: when you save an Excel file that contains a VBA Project, it has to be saved as a macro enabled workbook (xlsm) to keep the VBA project.  It cannot just be saved as an ordinary xlsx file.