Visual Basic for Applications - The Appearance

Hello again!! We saw a brief history and uses and problems of VBA in our last two posts. Now let's see how to access VBA from Excel and how does it look.

We know that VBA is a special program that is hosted by Excel. To access this program, follow any one of these steps.

In Excel 2007 or later

• Press Alt+F11
• Right click on any sheet name tab and click on 'View Code'
• If macros are already available, click on View Tab > Macros. From the Macros dialogue box, select any macro available and click on Edit. 
• If you make Developer tab visible, click on 'Visual Basic' in it.
• Right click on any object in your workbook and click on Assign macro. In the Assign Macro dialogue box, click on New or Edit (if macro is already assigned).

In Excel 2003 or earlier

• Press Alt+F11
• Right click on any sheet name tab and click on View Code
• If macros are already available, click on Tools Menu > Macros > Macros. From the Macros dialogue box, select any macro available and click on Edit. 
• Click on Tools Menu > Macros > Visual Basic Editor
• Right click on any object in your workbook and click on Assign macro. In the Assign macro dialogue box, click on New or Edit (if macro is already assigned).

Performing any one of the above options will open up VBA. Windows and appearance of VBA windows that Excel opens up will be different in different options. Let's try to learn about each one of them.

Best way to learn VBA is to experience by yourself. So, along with reading below info, make sure you open and do a little R&D in VBA module.

1. Project Explorer

When you open the VBA window for the first time, project explorer is the most likely item you see. If you don't see this by any chance, make it visible by clicking on View > Project Explorer or simply press Ctrl+R. (We are talking about VBA menus and short cuts here, so make sure you are clicking in VBA window and not in Excel).

Project Explorer displays a hierarchical list of the projects and all of the items contained in and referenced by each of the projects. See below a closer look at project explorer window.

What this window contains: (Refer numbering in above picture)

1. View Code - Displays the Code window so you can write and edit code associated with the selected item. 
2. View Object - Displays the Object window for the selected item, Excel front end or UserForm.
3. Toggle Folders - Hides and shows the object folders while still showing the individual items contained within them.
4. Project Name - Excel book name will be the project name
5. List window - Lists the all loaded projects and the items included in each project. For a plain Excel book, it lists down all the sheets and one more item called ThisWorkBook.

Though we will cover about them during our future posts, I will give a heads up now. You will save code that is specific to the sheets under respective sheet's code window (example: macro code to execute when a sheet is activated). Code that is applicable to entire workbook should be saved under ThisWorkbook  (example : code to execute before saving a workbook). When you record a macro from Excel, VBA creates another list item called 'Module' and recorded macro code will be saved under it.

Double clicking any of the list item opens up a white space towards the right which is the container for code for that item. Clicking on + / - will expand/collapses the list.

Right clicking on any list item or in the white space in Project Explorer will give you further options like rename your project name, lock and protect your code, insert UserForms, import / export code from other files etc.

2. Code Window

This is where a developer spends most of the time. You write, save and display your code here. You can open as many code windows as required (one for each item in the list window). Just double click on each item in the Project Explorer and you can notice the code window for that object opens up. You can minimise, maximize, restore and close the code windows by clicking on the respective buttons on top right corner which are just below the main VBA buttons. You can select the code from one code window and drag it to another window.

But be careful, it is not possible to identify which item's related code window is open at any given point of time. So if you want to open a particular item's code window, better double click on that item rather than toggling.

As said, we will be working mostly in the code window. So surely we will learn more about this window while coding.

3. Properties Window

Every object in Excel will have properties. For example, if you take a sheet, name of the sheet is a property of the sheet, visibility of the page (hidden or visible) is one of the property.

Properties window contains properties for the item selected in Project Explorer. You can view and modify the properties of the item from code window. Each item might have view only properties or few properties will be editable. So make sure the property you are trying to modify is editable, otherwise VBA complains. Code for the item need not be displayed in order to show the properties.

4. Immediate Window

Immediate window allows you to give a line of code and it will show the result instantly without actually executing the code. This is used extensively while writing code to see if the code is resulting into an error.

5. Locals Window

Locals window displays all of the declared variables in the current procedure and their values. It is automatically updated every time a code line is executed. This is used to observe the variables behaviour in the code testing.

Using the Locals window is easier to display variable values than examining the value from the Immediate window. For simple variable types (e.g., Long and String variables), the value is displayed in one line. For complex types or objects (e.g., a Range variable), its properties are displayed in a collapsible tree-like structure.

6. Watch Window

You can set up watch criteria to either momentarily stop executing your code when the condition is met. The Watch window displays all the Watches in effect. You can display the Watch window by choosing Watch Window from the View menu. A Watch is an instruction to VBA to pause code when an expression is True or when the variable being watched changes value. To create a Watch on a variable, open the Watch window and right-click in the Watch window and choose Add Watch from the popup menu or choose Add Watch from the Debug windows. In the Add Watch dialogue  enter in the Expression text box a variable name whose value you want to watch. Then choose 'Break When Value Changes'. When you run the code, execution will pause at the line after the line that modifies the variable's value. When code pauses, the value of the variable will have already been updated.

To remove a Watch, right-click it in the Watch window and choose Delete Watch from the popup menu. To modify a Watch, right-click it in the Watch window and choose Edit Watch from the popup menu.

Okay, guess that's enough to get you started. As I mentioned above, the best way to learn VBA is to experience it by yourself. So make sure you open and do a little R&D in VBA module. Happy learning!!

Click here to know about VBA object structure.