How to Use the VBA Editor in Excel

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Excel’s Visual Basic for Applications (VBA) editor is a very powerful tool.

It lets you write and edit custom scripts that automate actions in Excel.

In fact, when you record a macro it is stored in VBA code in the VBA editor.

But writing a macro from the VBA editor directly gives you more flexibility than recording a macro in the traditional manner.

You can create better code and complete more complicated tasks by working directly with Visual Basic for Applications.

In this tutorial, I show you the basics of how to use Excel’s VBA editor. Let’s get into it!

Kasper Langmann, Co-founder of Spreadsheeto

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

What is the VBA editor?

The Visual Basic for Applications editor, also called the VBA editor, VB editor, or VBE, is an interface for creating scripts.

VBA is the programming language that’s used to create these scripts.

excel-vba-editor

Visual Basic is a full-featured programming language, but you’ll only need to learn some of the basics to get the hang of VBA scripts in Excel.

Kasper Langmann, Co-founder of Spreadsheeto

If you’ve done any programming in an integrated development environment (IDE), the VBA editor will look familiar. It lets you create, manage, and run VBA code on your Excel spreadsheet.

Let’s take a look at how to open it and do a few basic things.

How to use the VBA editor

Before you start coding, you’ll need to open the VBA editor. To do this, head to the Developer tab and click the Visual Basic button:

excel-visual-basic-button

If you don’t see the Developer tab, go to File > Options > Customize Ribbon and make sure that “Developer” is checked in the right pane.

You can also open the VBA editor with the keyboard shortcut Alt + F11.

As you can see, the VBA editor is packed full of buttons, menus, and options. Don’t worry—we’ll go through the important ones in this guide.

Kasper Langmann, Co-founder of Spreadsheeto

In this guide, we’ll focus on the most basic parts of the VBA editor.

The project view, in the left pane, has a folder called Modules.

vba-editor-modules

This folder holds VBA modules, which are like containers for code. When you record macros, they’re included in a module.

If you decide to write your own code, it will be stored in a module.

To add a new, empty module, click into VBAProject (Book1), and go to Insert > Module.

vba-editor-insert-module

If there was no Modules folder in VBAProject, the folder will be created and there will be a new module inside of it. This is where you’ll put your VBA code when you’re ready to write it.

To delete a module, right-click it in the left pane and select Remove [module name].

vba-editor-remove-module

Excel will ask you to confirm the removal. You may export the module if you’d like to save it.

Finally, let’s look at running a macro from the VBA editor. After you’ve created a macro, either by coding it directly or recording it from the standard Excel interface, you can run it from this view.

To run a macro, just click the Run Macro button in the menu bar:

vba-editor-run-macro

You can also press F5 on your keyboard.

Moving on to VBA

This has been a very basic introduction to the VBA editor.

When you write more VBA code, you’ll see that the VBA editor becomes a better help for you in your work.

For now, play around with the editor to get a feel for where the buttons and menus are, and start getting used to the structure of VBA.

2019-09-02T13:03:17+00:00