Introduction to VBA Editor in Excel

Writing VBA code in Excel enables you to create powerful macros that can complete repetitive tasks at the push of a button. And the VBA editor is where this all happens.

In this tutorial, we will introduce you to the VBA editor, learn how to customize it to our needs, and see how we can add VBA code. Getting familiar with the Visual Basic editor will prepare you for other tutorials where we learn to write code to create macros.

What is the VBA editor in Excel?

VBA stands for Visual Basic for Applications and is sometimes simply referred to as Visual Basic. The VBA editor (VBE) is the environment in Excel where you view, create, edit, run and debug your Excel macros. The VBE is an integrated development environment (IDE). Everything you need to work on your macros is found in this one place.

How to open the VBA editor in Excel

Before we go any further in this introduction to VBA in Excel series, we need to know how to open VBA in Excel, or more specifically, how to open the VBA editor.

To open the Visual Basic editor, click Developer > Visual Basic or press Alt + F11 as shown in the following image.

If you do not see the Developer tab on the Ribbon in Excel, then click File > Options > Customize Ribbon. Then check the Developer box in the list on the right of the window to show the tab on the Ribbon.

Different elements of the Visual Basic editor window

The VBA editor window opens on top of the Excel window. Both windows are open, and if you were to close the VBE, you would be returned to the Excel workbook.

The following image shows how the VBA editor typically looks when opened for the first time.

The majority of the VBA editor window is taken up by the Code window. This shows the VBA code for an existing macro that is currently open. All code windows look the same (except for the written code), so the Title bar displays the source of this code.

The Menu bar and toolbar are positioned at the top of the window. You will find the functionality to run, debug, and navigate your code, along with many other functions, within the menus and toolbar commands. To the left of the VBE window are the Project Explorer and Properties windows.

The Project Explorer is a very important window. It lists the open files (or projects) and the objects within them (workbooks, sheets, userforms, modules etc). From the Project Explorer, you can navigate to your different code segments and open them in the Code window.

The Properties window lists the properties, or settings, for the active object or control. This window can be used to modify settings such as formatting, caption text, and the size and position of elements.

If any of the aforementioned windows are not visible in your VBA editor, click the View menu and then click the required window. 

In the View menu, you can also show other useful windows, including the Locals and Immediate windows. These are very helpful when stepping through and debugging code.

How to customize the VBA editor

You can customize the options of the VBA editor to fit your needs. Click Tools > Options to open the Options window.

There are four tabs in the window - Editor, Editor Format, General, and Docking.

The first tab is Editor, and these settings focus on the way the editor presents information to you, such as syntax errors, the current value of a variable, or the properties and methods available for the active object. We will not go into detail on the behavior of the different options right now, as they will not make sense until we understand how to write VBA code. Just be aware that there are options to change many aspects of the VBA editor.

The Editor Format tab contains formatting settings for the various code colors you will see when writing VBA code. The VBA editor uses a dark blue color for keywords and red for syntax errors.

You can modify these if you find it difficult to read the text in this style.

Where to add VBA code in Excel

VBA code can be added to any of the objects found in the Project Explorer window - modules, workbooks, sheets, and the different elements of a userform.

Most VBA code can be found within modules. This enables you, as the developer, the ability to organize your code how you want.

More complex macros are broken into smaller chunks and organized efficiently. And chunks of VBA code that are often utilized in macros can be saved in modules and recycled again and again in other projects.

VBA code found in objects such as sheets and workbooks is stored there because they run off different events, such as the opening of a workbook or changing the value in the cell of a sheet. These techniques are covered in our Excel macros and VBA course.

How to add and remove VBA modules

The primary place to store macro code is in modules, though you can store macros (otherwise known as sub-procedures in VBA) within workbooks, worksheets, or form controls. So, it is important to know how to add and remove VBA modules.

1. In the Project Explorer window, click an object in the project you want to insert the module. In this example, the Book1 project has been activated.
2. Click Insert > Module.

If this is the first module in that project, a Modules folder is created to store the module and the module is named Module1. If you were to insert another module, it would also be stored within the same Modules folder.

To rename a module, click on the module in the Project Explorer to select it, and edit the (Name) field in the Properties window.

To remove a VBA module, such as Module1, right-click on the module and click Remove Module1.

A message appears asking if you want to export the code of that module before you remove it. Click Yes to export the code, or click No to simply remove the module.

Ready to master Excel VBA?

Learn how to automate and run repetitive tasks at the push of a button with Excel macros and VBA. Our online Macros and VBA course contains 41 practical tutorials where you can gain the skills to create powerful macros that really make Excel work for you!