How to Open VBA in Excel - MS Excel

Visual Basic Analysis (VBA) is an MS Excel tool that helps automate repetitive and tedious tasks. It aims to simplify tasks that involve the use of complex applications. For example, VBA helps to create macros that can automate data-processing functions and create custom graphs and reports.    

You're at the right place if you want to learn how to get started with Excel VBA. Even if you fall under 27% of the advanced Excel user's category, our blog will help you further enhance your Excel skills. Once you’re familiar with the steps  on How to Open  VBA in Excel, you’ll be able to write VBA code or reassess recorded macros. In this blog, you will learn about VBA in Excel and how to open visual basic Editor and how we use it with basic Steps. Read more! 
In this blog, you will learn about VBA in Excel and how to open visual basic Editor and how we use it with basic Steps. Read more!   

Table of Contents

1) What is VBA?

2) How do I get access to VBA in Excel?

3) How to open VBA in Excel

4) Excel's Visual Basic Editor's structure 

 5) Vital VBA terms 

 6) Conclusion  

What is VBA?

Office suite programs run on common programming languages. The VBA code can be integrated into each suite to improve its performance. However, VBA is considered the best fit for MS Excel compared to other MS Office suite programs. It’s because Excel performs tasks like data organisation and analytics, and its spreadsheets are repetitive.   

To drive activities, VBA uses variables, projects, properties, logical operators, modules, and objects in Excel.  So, you can use VBA to command the computer to perform a set of actions. To get started, you will have to create custom macros. You can develop custom macros by entering commands in an editing module. VBA allows users to modify Graphical User Interface (GUI) features. 

Graphical User Interface (GUI) features comprises menus, dialogue boxes, forms, and toolbars. Besides, you can implement VBA to automate processes, develop Windows Application Programming Interface (API), user-defined functions, etc. Users find it helpful in automating computer calculations and processes.
   

How do I get access to VBA in Excel?

If you are unable to find the Developer tab in Microsoft Excel. You can start using it by following these steps.

Go to File > Options > Customize Ribbon and confirm if the ‘Developer’ option is checked in the right pane. You can also open the VBA editor option using the Alt +F11 shortcut on your keyboard



The ‘Developer’ tab will have all the options mentioned in the image below.



You can use the shortcut Alt+F11 or click on Visual Basic option on the ribbon for the Microsoft VBA window to open.



Following the above steps, you can quickly start using VBA in MS Excel spreadsheets. As we have understood how to open VBA in Excel spreadsheets, now let us move on to comprehend how to insert Loop action in Excel spreadsheets using VBA

Learn how to use advanced formulas in MS Excel - Sign up for Microsoft Excel Expert!

How to open VBA in Excel?  

You can open VBA in Excel by following a few basic steps. These steps are as follows:  

  a) Use a Keyboard Shortcut   

  b) Use the Developer Tab  

Use a Keyboard Shortcut 

You can press the ALT key + F11 keys on keyboard to open the VBA quickly. This will open the window displaying the Visual Basic Application on the Excel worksheet separately.   

Use the Developer Tab 

You can access the VBA from the Excel ribbon.   

The Developer tab on the Excel ribbon includes buttons required to open VBA. Besides, the tab helps to generate Form/ActiveX Controls such as checkboxes, buttons, etc. Press on File from the menu bar as it shows the Developer tab. Now, click on the ‘options’ button on the drop-down menu.   

Once the Excel Options window is displayed, you can choose the ‘Customise Ribbon’ option on the left. Now, browse through the Main Tabs and select the Developer checkbox. Next, press on the OK option at the page's end.  

Click on the Developer tab on the toolbar at the top of your screen. Now, go to the code group and select the Visual Basic option.    

Learn the top 10 Excel shortcuts with our Microsoft Excel Masterclass, sign up now!

Excel's Visual Basic Editor's structure

The image above shows the different components of the VB Editor, which are described in detail in the following section. Let us now understand, what each of them does

Menu Bar

This is where you have all the options which you can use in the VB Editor, which is similar to the Excel ribbon where there are tabs and options with each tab; By clicking on each of the menu elements, you can explore the in-hand options. One thing you will notice is that most of the options in VB Editor have Kkeyboard shortcuts mentioned next to them. Once you are familiar with a few keyboard shortcuts, working with the VB Editor becomes really simple.

Tool Bar

This toolbar in the VB Editor, by default, has some useful options that are likely to be needed most often. It is similar to the Quick Access Toolbar in Excel, which gives you quick access to some of the useful options. You can do a bit of customisation by adding or removing options to it (click on the small downward pointing arrow at the end of the toolbar). It is mostly in the cases where the default toolbar is needed while working with the VB Editor.


 

VB Editor, there are four toolbars: Standard, Debug, Editor, and User form. You can also access other toolbars by choosing the View option and hovering the cursor on the Toolbars option. Further, if you want to, then you can also add one or more toolbars to the VB Editor.

Project Explorer

There is a window on the left, known as the Project Explorer, that shows all the objects that are currently open in Excel. When working with Excel, every workbook or add-in that is open resembles a project, and each of these projects has a collection of objects in it. For instance, in the below image, the Project Explorer shows that there is one workbook (Book1) that is open, and there are objects in this workbook (worksheets, ThisWorkbook, and Module in Book1).


 

1) All open Workbooks – Within each workbook, the following objects exist. there exist the following objects. They are

 a) Worksheet object depicting each worksheet in the workbook.

 b) ThisWorkBook object that implies the workbook itself.

 c) Chartsheet object representing for each chart sheet, but they are not as common as worksheets.

 d) Modules is where the code gets generated when a macro recorder goes. You can also write or copy and paste your VBA code here.

2) All Open Add-ins

The Project Explorer is considered as a place that outlines all the objects that are open in Excel at a given time. To open Project Explorer, the keyboard shortcut used is ‘Control + R’; to close it, click on the close icon at the top right of this window.

Properties Window

In Properties Window, you can get to see the properties of the object selected. If you do not have the Properties window before, you can get it by pressing the Kkeyboard shortcut F4 (alternatively, go to the View tab and click on ‘Properties Window’). It is a floating window that you can dock in the VB Editor. It also allows you to change the properties of the selected object.



For instance, if you want to make any worksheet hidden, that can be done by editing the visible property of the selected worksheet object.


 

Code Window

For each object, there is a code window that is listed in Project Explorer. By double-clicking, you can open the code window for an object in the Project Explorer area. It is also a window where you can write your code or copy and paste a code from somewhere else. When you start recording a macro, the code for this macro goes into the code window of the module. As a result, when recording it, Excel automatically puts a module to place the code in it.

Immediate Window

When debugging code, the Immediate Window is mostly used. One way to use the Immediate Window is by using a ‘Print.Debug’ statement within the code and further run the code. If the result of Print.Debug is seen in the Immediate Window; it indicates that the code is working till that line. The Immediate Window, by default, is not visible in the VB Editor, but you can get it by typing the keyboard shortcut Control + G (alternatively, go to the View tab and click on ‘Immediate Window’).


 

Vital VBA terms   

Some of the vital VBA terms that you must know when working with VBA on Excel:  

Module:

A Module in Excel is used for storing VBA codes. Data related to the modules in a spreadsheet are stored in Project Explorer. The Visual Basic Application includes a section known as the Project Explorer. The modules folder saves all modules. These modules are also commonly called standard modules.    

Objects:

The codes in VBA help to handle or control objects in a skilled manner. Objects include cells, fonts, cell ranges, worksheets, and workbooks. These objects form a code’s parts used during VBA coding. For example, the “Active Cell” is used in VBA code to arrange the objects in the spreadsheet. To do this, you’ll have to select the object per your requirements.   

Procedures:

The procedure is a feature of the computer program. It helps in performing specific tasks. It's the code’s block that includes a declaration in the beginning. Besides, it ends with a final declaration.    

VBA includes two kinds of procedures, namely, the function procedures and the sub-procedures. Here, the function procedures can handle necessary calculations and estimate a value. At the same time, the sub-procedures act in MS Excel. The sub procedures start with the code “Sub”.    

Statement:

A statement is a set of instructions that can be categorised into two types. Firstly, declaration statements are used to highlight something significant. For example, it defines and highlights a variable or a constant value. Secondly, executable statements include codes that prompt the user to take action.    

Build your career as a Data Analyst - sign up for Data Analysis Training using MS Excel!  

Conclusion   

This blog is a quick guide to help you on How to Open VBA in Excel. It is used in MS Office applications to summarise tons of data. Additionally, it also helps to create and manage complex financial models. So, when MS Office users know to use VBA programming, they can access specific and normal functions. 

Learn more about VBA by registering for Microsoft Excel VBA and Macro Training!