VBA Editor

What Is Excel VBA Editor?

VBA Editor is a platform in Excel to write and edit the Visual Basic for Applications (VBA) code. It is a window where we can see all the VBA codes, be it in MS Excel, MS Word, or MS PowerPoint.

By default, in Excel, it is under the Developer tab. If you are using it for the first time, you may not see the Developer tab in your Excel workbook.

By default, you don’t see the Developer tab.

We have to enable this manually to access the VBA editor window. Follow the steps listed below to enable the Developer tab.

Step 1: Open an Excel workbook from your computer.

Step 2: Click on the “File” tab at the ribbon.

Step 3: Click on More and then click on “Options”.

Step 4: This will open the “Excel Options” window. In this window, click on “Customize Ribbon.”

Step 5: On the right-hand side, under Main Tabs, check the “Developer” tab check box.

Step 6: Click on “OK.” It should enable the Developer tab on the ribbon, and we can see the tab now.

How to Open Visual Basic Editor?

Basically, we can open the Visual Basic Editor window using the following three methods.

• Using Developer Tab
• Using Worksheet Tab
• Using Shortcut Key

Let us show you in detail how to open the Visual Basic Editor window in each method now.

#1 – Using Developer Tab

After enabling the Developer tab, click on it from the ribbon.

Under the Developer tab, click on the “Visual Basic” option.

As soon as we click on the “Visual Basic” option, it will open the following Visual Basic editor window. You can also open the Excel VBA editor dark mode through the Tools tab.

#2 – Using Worksheet Tab

Within the worksheet, we can open the VB editor window. For that, right-click on any of the worksheets in the Excel workbook and then click on “View Code.”

#3 – Using Shortcut Key

The fastest way to open the Visual Basic Editor window is by using the shortcut key. The shortcut key to open the Excel VBA editor shortcut is ALT + F11.

Hold the ALT key and then press F11 to open the Visual Basic editor window.

Visual Basic Editor Window

The Visual Basic Editor window looks basic with the icons used to showcase the ribbon. The following is the look of the Visual Basic editor window.

1. Menu Bar: Like Excel, the Visual Basic Editor window also has a menu bar. Here, we can insert modules from the Insert menu, format codes, debug, run, and do many other things.
2. Tool Bar: In the toolbar section, we have all the shortcuts to access some of the features like save, run, pause, copy, cut, paste, etc.
3. Project Window: In this section, we can see all the workbooks that are opened on our computer, along with the worksheets in that workbook.

From this section, we can launch the properties window by pressing the F4 key. For example, select any of the worksheets from the project window section.

Now press F4 key. It will open the following properties window of the selected worksheet.

In the properties section, we can play around with the properties of the worksheet, like changing the name of the worksheet, visibility, which can be set to true or false, and many other things.

How to Use VBA Editor?

The best way to start using the VBA Editor is by recording a macro.

Record macro is a feature available to record all the actions that we do in an Excel workbook and store them in VBA language.

We can see the code that the macro recorder recorded while the recording was going on.

1. Head of a Macro: All the procedures start with the keyword “Sub,” followed by its unique name. Remember, while starting the recording, the name “Test” was given, and it has been recorded here as well.
2. The Code: Whatever action is performed after we start recording is recorded as code here. The first action was selecting the cell A1; hence, it has recorded this action as “Range(“A1”).Select”.
   Then, we entered the value “Testing Recording” and it has recorded this action as below.
3. ActiveCell.FormulaR1C1 = “Testing recording”

After entering the value, we hit enter, and subsequently, it selected cell A2. This action is recorded below.

“Range(“A2”).Select”.

In this way, we can make use of the Visual Basic Editor window to play around with the recorded codes, and we can start creating our sub-procedures from scratch here.

Examples of Excel VBA Editor

Example #1 – Adding Modules on Editor

When we record a macro, Excel automatically adds a module to store the code for the actions that we perform in the Excel Workbook. The code area is a code window where we write the VBA code.

However, when you open the VB editor for the first time without using the record macro option, we end up seeing the window below.

Now, we must add the module manually to start writing the code from scratch without the help of the macro recorder. Let’s follow the steps below.

Step 1: Go to the Insert tab.

Step 2: Under the Insert tab, click on “Module.”

Now, it inserts the module for us.

Double-click on Module 1, and it will open the code window to the right side.

Another way to insert the module is by right-clicking on the workbook or worksheet objects. You will see an “Insert” option.

Hover on the “Insert” option, and you will see the “Module” option.

Click on the “Module” option, and it will insert a new module.

Thus, we can insert multiple modules. In the code window, we write the code. For example, enter the keyword “Sub.”

After the keyword “Sub,” enter a macro name.

After the macro name, hit the enter key, and it will create the tail of the macro automatically.

Inside the sub-procedure, we can write a code. For example, to insert a value 123 in cell A1, we can write a code as shown in the image below.

Similarly, we can create multiple modules and write separate codes.

Example #2 – Working with Properties Window

Working with Properties takes you a long way in VBA coding. Let’s explore this in detail in this example. The Properties tab is available for objects of VBA, i.e., the worksheets in the workbook.

Select any of the worksheet objects and press the F4 key to access the properties window.

Name Property: To change the name of the worksheet, we use the “(Name)” property. Let’s give a different name to the worksheet “Sheet2.”

Let’s go back to the worksheet and see if the name is changed there as well.

Even though we have changed the name, it remains the same in the worksheet. The reason for this, though, is that the property “(Name)” is used to change the name of the worksheet at the VBA level and not the worksheet level.

Hence, to change the name at the worksheet level, we must use the “Name” property instead of the “(Name)” property at the top of the list.

Now, the name of the worksheet will be changed to “New Report.”

Visible Property: Visible is nothing but the hide and unhide in worksheet level. By default, the visible property is set to -1 – xlSheetVisible.

Let’s change the property to 0 – xlSheetHidden.

Now, we cannot see this worksheet.

Thus, we can work with the properties of the worksheets to manipulate them.

Important Things to Note

1. By default, the “Developer” tab is not enabled in Excel. It has to be enabled manually. 
2. Once there is a VBA code in the Visual Basic Editor window, we must save the file in “Excel Macro-Enabled Workbook (*.xlsm).”
3. F4 is the shortcut to launch the properties window in the Visual Basic editor window.

Frequently Asked Questions (FAQs)

Download Template

This article must help us understand the VBA Editor formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to VBA Editor in Excel & its meaning. Here we explain how to use the VBA Editor with its properties, adding module, examples & downloadable excel template. You can learn more from the following articles –

• VBA Web Scraping
• VBA String Comparison
• VBA On Error Resume Next