Understanding Macro Securities and Using Visual Basic Editor
Home ➜ Top 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:
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
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.
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.
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).
The toolbar is the group of specific commands from the menu bar which you can use instantly while working on VBE.
The toolbar in the above image is the default toolbar which you get when you open the VBE first time.
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:
- 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.
4. Properties Window
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.
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.
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.
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...