The first step to work using Visual Basic in Excel involves become familiar using VBA’s Visual Basic Editor (also called the VBA Editor or VB Editor).
In this video I will go over everything you need to learn about the VBA Editor as well as some helpful choices to consider for coding within Excel VBA.
This Tutorial Covers:
- What is Visual Basic Editor in Excel?
- Opening the VB Editor
- Keyboard Shortcut to Open the Visual Basic Editor
- Using the Developer Tab
- Using the Worksheet Tab
- Anatomy of the Visual Basic Editor in Excel
- Menu Bar
- Tool Bar
- Project Explorer
- Properties Window
- Code Window
- Immediate Window
- Where to Add Code in the VB Editor
- Module Code Window Vs Object Code Window
- Adding VBA Code in Module
- Adding Code to the Object Code Window
- Customizing the VB Editor
- Editor Tab
- Auto Indent
- Editor Format Tab
- General Tab
What is Visual Basic Editor in Excel?
Visual Basic Editor is a distinct application that is component of Excel and is available when the user opens any Excel workbook. It is, by default, not accessible and, to use it, you have to enable it.
VB Editor will be the location in which you store your VB code.
There are many ways to obtain the code from the VB Editor.
- If you create macros that is created, it makes a brand new module using the VB Editor and inserts the code within the new module.
- You can type manually VB code inside the VB editor.
- You can copy the code from another workbook, or even from websites and then paste the code into the VB Editor.
Opening the VB Editor
There are many ways to start Visual Basic Editor in Excel. Visual Basic Editor in Excel:
- Utilizing a keyboard shortcut (easiest and most efficient)
- Using the Developer Tab.
- Using the Worksheet Tabs.
Let’s look at each of these in a short time.
Keyboard Shortcut to Open the Visual Basic Editor
The easiest method to open Microsoft’s Visual Basic editor is to make use of the keyboard shortcut Alt + F11 (hold the alt key while pressing on the key F11).
When you click this the program will open in another window to display Visual Basic. Visual Basic editor.
This shortcut functions as a toggle. So whenever you try it again the shortcut will bring users back into the Excel application (without shutting it). VB Editor).
For the Mac version is Opt + F11 or Fn + Opt + F11
Using the Developer Tab
To launch Visual Basic Editor, open the Visual Basic Editor from the ribbon:
- Click on the developer tab (if you do not see a Developer tab go through this about how to access it).
- In the Code section In the Code group, click to open Visual Basic.
Using the Worksheet Tab
This is a less popular method of opening the Editor in the Vb Editor.
Select one tabs on the worksheet then right-click and choose „View Code‘.
This method won’t only launch it to the VB Editor, it will also open the code window for the worksheet object.
This is very useful for when you need to write software that only works for specific worksheets. This is often the case for worksheet events..
Anatomy of the Visual Basic Editor in Excel
When you launch the VB Editor for the first time it might appear a little intimidating.
There are a variety of possibilities and sections that might appear to be completely new initially.
It also features an old Excel appearance from 97 days ago. Although Excel has seen a huge improvement in terms of design and usability throughout the time however, The VB Editor has not seen any changes in how it appears.
Within this article, I’ll walk you through the various components that comprise this application. Visual Basic Editor application.
Note: When I first started using VBA a few years ago, I was completely overwhelmed by all the new options and windows. As you get familiar using VBA you’ll get comfortable with the majority of them. In most cases you won’t be required to make use of all the options, but the majority.
Below is a picture of the various components in this program. Below is an image of the various components in VB Editor. The components are explained in more detail in the next parts of the tutorial.
Let’s take a quick look at the various components to be aware of what they do:
Menu Bar
Here you will find all the options you can access within this editor. VB Editor. It’s like the Excel ribbon which has different tabs and options on each tab.
You can look through the various options by clicking on the respective menu elements.
It is evident that the majority of the options available in VB Editor have keyboard shortcuts that are listed in the next section. Once you are familiar with some keyboard shortcuts using the VB Editor becomes really easy.
Tool Bar
There is an instrument bar that is available in VB Editor which has some useful options are likely to be used often. It’s similar to it’s Quick Access Toolbar in Excel. It allows easy access to many of the most useful options.
It is possible to customize it by removing or adding additional options (by clicking the small downward-pointing arrow towards the bottom of the bar).
Most of the time the default toolbar will be the only toolbar you require when working using The VB Editor.
The toolbar can be moved over that menu bar simply by pressing those three dots (at the top on the right side of the menu bar) and then drag it over that menu bar.
Notice The four toolbars that are available in the VB Editor – Standard, Edit, Debug, and the User form. The toolbar you see in the picture below (which can also be the standard) is the normal toolbar. It is possible to access additional toolbars by clicking the View option and hovering your cursor over the Toolbars option. There is the option to include one or several of the toolbars in your VB Editor if you want.
Project Explorer
Project Explorer is a window to the left of the screen that displays all open objects in Excel.
When working in Excel each workbook or add-in you open is considered a project. Each one of these projects could contain a an array of objects within it.
For instance, in the image below it shows you can see that the Project Explorer shows the two workbooks open (Book1 as well as Book2) and the contents of the workbooks (worksheets, ThisWorkbook, and Module in Book1).
There’s a plus symbol on the left side of objects, which you can click to either collapse the list of objects, or to expand it and view the entire set of items.
The following objects are part of Project Explorer: Project Explorer:
- Every open Workbook – inside each workbook (which is also referred to as“project“) there are some of the following items:
- worksheet object for every worksheet in the workbook
- ThisWorkbook object that represents the workbook in itself
- The chartsheet is the item for every chart sheet (these are not as widespread as worksheets)
- Modules – This is where the code generated by the macro recorder. You could also write or copy-paste VBA scripts here.
- All open Add-ins
Think of your Project Explorer as a place where you can see all the objects within Excel at the moment.
The keyboard shortcut that opens Project Explorer is Control + R. Project Explorer is Control + R (hold the control key, then hit R). For closing it just click the close button at the top right corner of the Project Explorer window.
Note: For each object that you find in Project Explorer, there is an option to code the object in which you can type your code (or duplicate it and copy from elsewhere). The code window opens when you double-click the object.
Properties Window
The Properties window allows you to view the properties of the selected object. If you do not already have the Properties window in place you can access it by pressing keystroke F4 (or navigate to the View tab, then click Properties Window).
The Properties Window is floating one that you can dock into the VB Editor. In the example below I docked it right beneath that of the Project Explorer.
The Properties window lets us modify the properties of an object. For example, if would like to make a worksheet invisible (or extremely obscure) it is possible to do this by altering the visible Property of the worksheet object.
related: Hiding a worksheet within Excel (that cannot be removed easily)
Code Window
The code windows for every object that appears within the Project Explorer. It is possible to open the code window of the object you want to open by clicking it within the Project Explorer area.
The Code window lets you write your code , or copy and paste a code you’ve found elsewhere.
If you create a macro the code that you recorded is put in the Code Window of the module. Excel will automatically add modules to put the code when recording the macro.
Related: How to Run a Macro (VBA Code) in Excel.
Immediate Window
Its Immediate Window is typically used for trying to debug code. One method I have used to use this window to use the Print.Debug statement in the code, and then running the code.
It allows me to troubleshoot the code and figure out the areas in which my code becomes stuck. If I see the results of Print.Debug in the window immediately I can tell that the code was working at least up to that point.
If you’re brand new to VBA programming, it could take some time to master the instant window to debug.
By default the window that is immediately visible by default not accessible in VB Editor. It is accessible pressing Keyboard shortcut Control + G (or navigate to the View tab and select „Immediate Window“).
Where to Add Code in the VB Editor
I’m sure you’ve got the basics of the basics of what VB Editor is and what each of its components are.
In this part of the tutorial, I’ll guide you on how to insert an VBA code to Visual Basic Editor. Visual Basic Editor.
There are two locations where you can include the VBA code to Excel:
- The code window of an object. They could include a worksheet, workbook or User Form.
- The code window for an application.
Module Code Window Vs Object Code Window
Let me quickly clarify the distinction between adding code to a module and inserting a code into an object-code window.
If you add code to any object, it’s dependent on an event which will trigger the code. For instance, if, for example, you want to remove all worksheets of the workbook once you open the workbook and then, the code will be placed in The ThisWorkbook object (which is the one that represents that workbook).
The trigger in this instance is opening the workbook.
In the same way, if you would like to secure the worksheet when an additional worksheet is active and the worksheet code will be placed into the worksheet code window.
They are referred to as events. You can also associate an event with a specific code that will be executed whenever events occur.
related: Learn more about events within VBA.
In contrast this code module must be manually executed (or it can be invoked from subroutines in other ways as well).
Once you have recorded a macro Excel instantly creates an new module and adds the macro you recorded into it. If you want to run the program, you must manually run the macro.
Adding VBA Code in Module
When a macro is recorded, it automatically creates a new module and adds the code inside There are some limitations that come with an application to record macros. For instance, it will not make use of loops or If Then Then conditions.
In these cases it’s best to cut and paste it manually, or create your own code.
A module can be utilized to store the following kinds of VBA codes:
- Declarations You are able to declare variables within an element. Declaring variables lets you define the type of data it can contain. You can declare a variables to be used only for a sub-routine, as well for the entire sub-routine of the module (or the entire module)
- Subroutines (Procedures): This is the code that contains the steps you need VBA to complete.
- Function Procedures: This is a function procedure that gives a single value, and can be used to develop custom functions (also known as UDFs, also known as User Defined Functions or UDFs using VBA)
A module is not included in the Workbook. It is necessary to add the module first before you can use it.
Adding a Module in the VB Editor
Here are the steps needed to include an element:
- Right-click on any item of the workbook (in which you would like to add to install the module to be).
- Place the cursor in to the Insert option.
- Click on Module.
This will instantly create a folder named Module and add an object named Module 1. If there is already an existing module using the above procedure, you can create a new module.
Once the module is installed after which you can double-click on the module object within the Project Explorer and it will bring up the code window.
You can now copy-paste the code, or create it yourself.
Removing the Module
Below are the steps you can take to get rid of a module within Excel VBA:
- Right-click on the component you wish to eliminate.
- Click on the Remove Module Option.
- The dialog box that appears Click on the button that says.
Note that you can export a module, before you remove it. It is saved as an .bas file that you can use it to import into any other project. For exporting a module click the module’s right-click and select ‚Export file‘.
Adding Code to the Object Code Window
In order to open the Code windows for an object, just double-click it.
After it has opened you can type in the code manually , or paste the code into other modules or the internet.
Be aware that certain objects let you choose the type of event you’d like to write the code.
For example, if are writing a program to trigger an event when changes are made within your worksheet. You will need first choose the worksheets from the drop-down on the left-hand side on the screen for the code, and then choose the event to change from the drop-down menu on the right.
Be aware that these events are unique for the particular object. If you start the code window for an office book, you’ll observe the events associated with the worksheet object. If you open the code window to access the worksheet, you’ll observe the events associated with workbook object.