Understanding Macro Securities and Using Visual Basic Editor

HomeTop 15 Excel VBA Tutorials – Best Way to Learn VBA in Excel (Guide)How to use Visual Basic Editor in Excel


The world of VBA starts with the Visual Basic Editor (VBE).

It’s the place where you write and manage all the macro codes and if you ask me about VBE, I’d say if you are serious about learning VBA you need to understand all the components of VBE and you need to master its options.

That’s why this is the first chapter of our VBA tutorials and in this guide, we have covered every single aspect of Visual Basic Editor to make you understand it’s functionality.

Note: I’ll be using different words (VBA editor, VB editor, or VBE) in this guide for referring to the Visual Basic Editor.

...so let’s get started.

What is the Visual Basic Editor?

Visual Basic Editor is an application (a separate) in which you can write and save all the VBA codes. In simple words, it’s a code editor for Excel in which you can write all the macros and store them. Even though it’s a separate application (VB Editor) you can only use it with Excel.

Yes, that’s right. You can't run VBE separately; there must be an Excel workbook open for using VBE.

Visual Basic Editor os the only way to write a VBA code in Excel. In fact, all Microsoft applications that host VBA use the Visual Basic Editor for script writing (writing a code).

Below is the VBE window:

window-excel-visual-basic-editor

As you can see Visual Basic Editor is fully loaded with options and tools which you can use to write, save, and manage your VBA codes easily.

And according to Richard Mansfield in his book Mastering VBA for Microsoft® Office 2016, he mentioned that VB editor is highly effective and a culmination of more than 20 years of modifications and improvements.

Components of Visual Basic Editor

components visual basic editor

Even when you record a macro where you don’t write anything. Excel stores all the code in the VBE.

VBE is a separate application which opens in a different window, but, it's already installed in your Microsoft Office pack, so there is no need to install it.

But make sure to open your Office app in which you want to use it.

The easiest way to open Visual Basic Editor is to use the keyboard shortcut key Alt + F11. When you press the shortcut key you'll instantly get the VBE window.

To understand how VBE works you need to understand its components and how you can use them.

Just like any other application, it has its menu bar where all the options are listed under different sections.

menu bar in visual basic editor

Each section has a drop down with all the options. If you notice closely, for most of the options there are also shortcuts which you can use to open them.

shortcut keys with all the menu options in vbe

For example, if you want to find something the keyboard shortcut is control + F (it’s the same keyboard shortcut which you use in the worksheet).

2. ToolBar

The toolbar is the group of specific commands from the menu bar which you can use instantly while working on VBE.

visual basic editor toolbar

The toolbar in the above image is the default toolbar which you get when you open the VBE first time.

add delete toolbars in vbe

However, there are four different kinds of toolbars which you can add: Standard, Debug, Edit, and UserForm (View ➤ Toolbar)

3. Project Window

On the right side of the VBE, there’s the Project Window which lists all the open projects.

In simple words, every workbook or an add-in which is open is a project and every project further has a collection of objects:

project window in visual basic editor
  • Worksheet: Each worksheet in a workbook lists with its name as an object.
  • The Workbook: It represents the workbook itself as an object.
  • Chartsheet: If you have a chart sheet in your workbook then it will also be listed there as an object.
  • Module: It’s the place where you write code or your recorded macros stores. In case of writing a macro, you need to insert a module, but, while recording a macro Excel inserts it automatically. To insert a module all you need to do is right-click on the project ➤ Insert ➤ Module.
Note
When you open VBE, Project Window is always there but if you are not able to find it there you can simply go to the “View” in the menu bar and make it appear or simply use the shortcut key Control + R.

4. Properties Window

properties-window-in-visual-basic-editor

It’s just down from the Project window and you can hide and un-hide it is using the keyboard shortcut F4.

For example: when you select a worksheet, it shows you all the properties it has and you can change it.

  • Name of the worksheet
  • Enable auto filter
  • Make a Worksheet very Hidden
  • Much More

5. Code Window

The code window is where you write your code and Excel stores the code when you record a macro.

code in the code window

For every project, there is a code window and you can open it just by clicking on the object.

Whenever you open the VBE, code window is always visible but if it’s not there, you can activate it using the shortcut key F7.

And to close it you can simply click on the close button from the top right of the window.

6. Immediate Window

Let’s say if you want to test the below code...

Range("A1").Value = 9999

...all you need to do is type a question mark and then paste the code there and HIT enter.

add code direclty into the immediate window

It will immediately show the result of the code. The other ways are to add Debug.Print before the code gets its result value in the immediate window.

use immediate window with debug print

There's More

Below are some of the external links which could be useful for you to learn more about Macro Macro Securities and Visual Basic Editor, make sure to check out all of these...

© ExcelChamps 2019