Understand Your Visual Basic Editor in Excel - PART 1

Understand Your Visual Basic Editor in Excel

Before getting into programming, we will understand the interface and environment we use to create that. Technical we call it as Visual Basic Editor (VBE). In this article, we will find the information about VBE.

Why it is important to know your VBE? 
It is really vital to understand the basis of VBE in order to manipulate the code. 
Important fact is, this is interface we are going to use across all VBA supported applications.

In this topic,

• Understand the between the Excel environment and the Visual Basic for Applications environment.
• Learn the definition of some key terms used in the VBA environment.
• Learn the components of the Visual Basic Editor window.
• Learn the components of the Visual Basic Editor Toolbar.
• Learn the options available for customization in the Visual Basic Environment.
• Learn the components of the Project Explorer window, and how to navigate it.

          In this context, we can define this environment as non-programming environment. Here we can add content, do calculation using build-in functions, format information, apply filters, use pivot table to analyze data and so on. 

Excel 2003 Environment

Programming environment remains same for all Excel Environment since 2003. To access programming environment use following keystroke in windows ALT+F11.

or 

Can access from DEVELOPER ribbon (available from 2007 Version)

 

  It is usual this ribbon may not be available while starting the excel application for the first time. It is simple to enable, follow the below image. File  -->  Options -->



 

 Now we proceed to the business..... Programming Environment

 

The Programming Environment

 



          Even with loads of existing functionalities, we need more! We can’t help it which our basic quality, so we start personalizing it to our needs and wants. At this point, where we jump to customize and automated existing functions and create new functions. In order to achieve what we need, we can use Visual Basic for Application. Below Image shows the Programming Environment.

Let us know few Common abbreviations

Visual Basic for Applications (VBA): The programming language used to write programs in Excel and other applications.

Visual Basic Editor (VBE): The environment (or window) in which VBA code is written.

Procedures: Entities that perform a task and in which the code is written. Think of them as containers for code, if you will.

Modules: A Container for procedures. A module can hold many procedures or just one.

Projects: A Container for Modules. All of the VBA code in any workbook is called a Project.

The Visual Basic Editor

The VBE window is just like any other window. It has a Title Bar, a Menu Bar and a Tool Bar, all with some very familiar contents, although possibly also containing some options or icons you've not seen before. The main body of the VBE window consists of one or more sub-windows.

Visual Basic Editor

Understand VBE Toolbar and Menu bar

Now we go on step down to understand the tools available in VBE. Here we can see common menus and toolbars. From it, various tasks can be performed when using or writing code.



 Some of the buttons available on the VBE toolbar are as follows in the table below.

Icon	Description
	View Microsoft Excel - Switch focus from the VBE back to Excel.
	Insert - Select the drop down arrow and you are provided with choices to insert a new UserForm, Module, Class Module, or Procedure.
	Save - Allow us to save the modification done.
	Run - Press this button to make your code start to run.
	Break - Press this button to put your code in "Break" mode and stop it from running.
	Reset - If your code is in "Break" mode, press this button to reset your code to its normal status.
	Design Mode - Press this button to temporarily disable any code from running while you are working on your workbook, such as while editing a command button.
	Project Explorer - Displays the Project Explorer window.
	Properties Window - Displays the Properties Window.

As we move on we will discuss more about other tools.

VBE Options

The VBE has its own set of options that allow for customizing the interface to your own preferences. From the VBE Toolbar select Tools Options, to display the Option dialog shown below.

The Options Dialog in the VBE gives us the opportunity to modify the way the VBE looks and behaves. From the Editor tab, make sure that all the check boxes are checked. By default, all but one of these are checked; however, the box for Require Variable Declaration is not. We at VBA Express believe it is a good programming practice to enable this option. Listed below is a brief description for each option on the Editor tab. These options are discussed further in future lessons.

Auto Syntax Check: As code is typed, Excel will display a message for any syntax errors, or keyword misspellings.

Require Variable Declaration: This will add "Option Explicit" to the top of every module, and will force all variables to be declared. This ensures that there are no misspelled variables in our code, and also gives us full access to the Intellisense menu for each variable.

Auto List Members: As code is typed, Excel displays what it logically calculates the next part of the code should be. Excel provides an alphabetical list of the commands that can be used.

Auto Quick Info: Displays information on the syntax of statements as code is typed. The current argument is highlighted in bold, optional arguments are wrapped in brackets. The Data Type returned is also indicated.

Auto Data Tips: Valid only in "Break" mode while debugging code, this option displays the current value of a variable when the mouse is hovered over it.

Drag-and-Drop Text Editing: Allows for text to be dragged and dropped with the mouse to a new location.

Default to Full Mode View: Allows all Procedures within a module to be displayed at once.

Procedure Separator: Places a horizontal line separating each Procedure in the Main Code Window.

Auto Indent: Repeats the indenting of the current line when Enter is pressed.

Tab Width: Specifies the number of spaces to use when indenting.

The Editor Format tab allows the font, size, and color of the various types of text to be modified. Don't worry if you're not familiar with all the different types of text; they are all discussed throughout this training course. These settings can be changed at anytime to make your code look the way you want, based on your own preferences.

The General tab and Docking tab provide more options; however, those options should be left at their default settings for the purpose of our training.

To close the Option dialog press "Ok" to save any changes, or "Cancel" to dismiss any changes.

The Project Explorer

To navigate around the various elements in the programming environment, there is one very useful window: the Project Explorer. By default, it is located at the top-left of the VBE, and is shown below.

The Project Explorer window should be visible by default. If it is not, it can be shown by any of the following methods:

• Select View - Project Explorer from the VBE Toolbar.
• Use the Ctrl+R shortcut.

You should now be on more familiar ground; the Project Explorer is just like any other Windows Explorer. It shows a hierarchy of objects from which you can choose branches to expand and/or choose elements to view in detail in one of the other panes. Let's take a closer look.

VBAProject

The first item in the Project Explorer in our graphic is called VBAProject(Book1). Book1 is simply the name of the workbook. When the workbook is saved, the name changes to the name and file extension of the workbook. For example, if your workbook was saved as Lesson1.xls or Lesson1.xlsm, then in the Project Explorer it would change to display VBAProject(Lesson1.xls or Lesson1.xlsm). If multiple workbooks are opened, each one would be listed in the Project Explorer, as well as any loaded Add-ins. For now, we'll stick to having just one open workbook at a time.

The first item within the VBAProject is Microsoft Excel Objects. Just like the Windows Explorer, any of the items with a + or - can be expanded or retracted with a double-click of the mouse.

Listed within the Microsoft Excel Objects group is every worksheet that is in the workbook. You can also see ThisWorkbook. In later lessons we discuss the specialized code that can be placed in these sections.

Modules

While there are several places to store code, the vast majority of the code is stored in Standard Modules. Before we can start using a Module, we must first add one to our project. From the VBE Toolbar select Insert-Module as shown below. Or press the down arrow on the Insert Button and select Module.

Inserting Module

    We can insert as many Modules as we want; the only limitation to the number of modules an Excel file can hold is the limitations of your computer's memory. Each Module can have one or many Procedures. When writing a program you may find it easier to group certain Procedures together because they have similar functions. Storing them in their own Module, while storing other Procedures in a separate Module, helps to keep your project organized.

In VBA the word "Module" assumes a Standard Module, though the word "Standard" is rarely used to describe it.

In our graphic, the project is expanded to display the contents. Double-click to hide the contents. Click on the Toggle Folders button to display the projects as folders, as shown.

Now that we have inserted a Module, we need to give it a useful name. While we could leave it as Module1, a more descriptive name helps us remember the code contained therein, especially when you want to work on a program months after it was developed.

To rename a module, first make sure that the Properties Window is visible. By default, it is located at the lower-left of the VBE, and is shown below.

The Properties Window should be visible by default. If it is not, it can be shown by any of following methods:

• Select View Properties Window from the VBE Toolbar.
• Use the shortcut key, F4.

Change the name of the module by typing into the Name field. The name must start with a letter, cannot be more than 31 characters long, and cannot contain any spaces. Also, you cannot use most symbols; however, you can use the underscore (_).

The Main Code Window

After going through the objects that can appear in the Project Explorer, you should be itching to get your hands on some real code. Most of the elementary items we have seen in the Project Explorer are Code Modules and they can be edited in the main window. For the moment we'll just see how to open a Module within the Project Explorer.

To work with an existing Module, first select it by clicking on it in the Project Explorer. It can then be opened by any of the following methods:

• Double-click it.
• Select View - Code from the VBE Toolbar.
• Use the shortcut key, F7.

Good Luck.....👍👍👍 See you next time...👋👋👋