Using the VBA Editor
Here I explain what you need to know about using the VBA Editor, how to open it and how to use it.
- How to open the VBA Editor
- The Code Window
- Menus: built-in and Code VBA extension
- Project Explorer
- VBAProject (FUNCRES.XSLM) is left out. This project is available purely for technical reasons, but you will never use it. It clutters the interface forwach reason the Code Explorer leaves it out.
- The Code Explorer also shows the procedures in the project whereas the Project Explorer only shows modules. Having the procedures makes for easy navigation when your project gets larger.
- The Code Explorer has many useful extras (click this link for details), such as commenting out a whole procedure in one go.
How to open the VBA Editor
Using the Alt-F11 shortcut key is the fastest way to open the Visual Basic Editor (VBE). Note that you can only open the VBA Editor if you have a document open.
Alternatively, you can select the Developer tab and click on the Visual Basic button at the start of the tab in the Code group.
The above screenshot is from Excel, it will be slightly different in the other Office apps.
If the Developer tab is missing, you will have to add it to the ribbon first ...
To do this, right-click anywhere on the ribbon using Customize and check 'Developer' in the Main Tab area on the right.
Note that you may also decide to add the Visual Basic button to the Quick Access Toolbar on the Options dialog (left pane - one down).
The Code Window
The Code Window is where you write any code associated with your VBA project. An example of such code is the
At the top of the Code Window are two dropdown boxes. Together they inform you about the procedure ('Procedures/Events box') and optionally class ('Object box') the cursor is currently in. These boxes can be used to navigate to procedures and events in the module or class. If you select an event, the event's procedure code is automatically inserted. You can now add statements specifying what you want to happen.
More info on the Code Window can be found here.
Menus: built-in and Code VBA extension
The above shows two menus: on top the VBE built-in menu which provides access to all features for editing, running and saving code.
To get started, the most important menus below the VBE built-in Menu are Insert and Run. Insert lets you either insert a procedure, or a standard or class module. To be able to insert a procedure, there must be an active (open) module available to insert it in.
Run Sub or F5 will be your most used action, to see if you procedure works as intended. Debug then is the next most used, as it will help you find out why it doesn't work as required.
Below the VBE built-in menu is the Code VBA menu which is our extension to the editor to help you to be knowledgeable and productive with the VBA and Office extensive language. For details, visit the above linked page.
The Project Explorer
The Project Explorer presents a hierarchical listing ('tree') of the objects the current project - and possibly other available (referenced) projects. In this example, there are four Excel objects which represent each sheet and workbook in your Excel file - Sheet1 and ThisWorkbook.
If you compare the Project Explorer image on the right with the left pane in the VB Editor screenshot at the top of this page, you will notice that the former is a bit different from what you have without the Code VBA add-ininstalled. The latter includes a replacement called the Code Explorer which has the following benefits: