How to Use the VBA Editor in Excel: Quick Guide (2023)
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 VBA 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!
Table of Contents
What is the VBA editor?
The Visual Basic editor, also called the VBA editor, VB editor, or VBE, is an interface for creating scripts.
VBA (Visual Basic for Applications) is the coding language that’s used to create these scripts.
Visual Basic is a full-featured programming language, but Microsoft Office’s very own VBA programming language is easier to get the hang of, so you can get started with developing applications much more quicker.
If you’ve done any programming in an integrated development environment (IDE), the VBA editor in Excel 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 the Visual Basic editor and do a few basic things.
How to use the VBA editor in Excel
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:
If you don’t see the Developer tab, go to File > Options > Customize Ribbon and make sure that the developer tab is checked in the right pane. If you want a more thorough explanation of how to add the developer tab in Excel, read it here.
You can also open the VBA editor with the shortcut key Alt + F11.
As you can see, the VBA editor is packed full of buttons, menu bars, and options. Don’t worry—we’ll go through the important ones in this guide.
In this guide, we’ll focus on the most basic parts of the Visual Basic editor.
The project view, in the left, vertical, menu bar in the VBA editor, has a folder called Modules.
This folder holds Excel VBA modules, which are like containers for VBA code. When you record macros, they’re included in a module.
Modules also contain the code window where you’ll be writing code (if you’re not recording it).
To add a new, empty module, click the Insert menu button and select 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 Excel 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].
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 Visual Basic Editor window.
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:
You can also press the shortcut key F5 on your keyboard to run the macro from the VBA editor.
PRO TIP: Change the name of a module
If you’re developing big spreadsheets with lots of VBA, all the macro codes won’t be able to fit in one single module. You’ll need more. You can easily add those from the menu bar, but as you add more, it becomes increasingly more difficult to figure out what macros are in what modules.
Luckily, you can easily change the name of a module in the Properties window.
Add the Properties code window from the Insert button on the menu bar.
If the Project window is missing
If it looks like this when you open the Visual Basic editor:
The code window is missing, there’s no left vertical menu bar. Nothing is visible except the horizontal menu bar on top 🤷
You need to click on the ‘View’ tab on the menu bar, and then click to show the ‘Project Explorer’ window.
That’s it – Now what?
This was a simple Excel tutorial on getting started with the Visual Basic editor in Excel and should get you on the right track to write code (or record it).
Mastering the Excel VBA editor is important for both beginners and advanced Excel users.
When you write more VBA code, you’ll see that the Excel VBA editor becomes a better help for you in your work.
For instance, it helps you autocomplete your VBA coding with IntelliSense, helps you find syntax errors with auto syntax check, debug with the immediate window, uses the object code window, and much more.
For now, play around with the VBA editor to get a feel for where the buttons and menus are, and start getting used to the structure of VBA.
If you want to dive deeper into VBA programming, check out my free 30-minute VBA course here.
The VBA editor is just a tiny portion of what macros are all about. You definitely need to check out my big VBA guide here.