Visual Basic Editor in Excel (VBE)

To edit a Macro in VBE: On the Developer tab, in the Code group, click Macros, which opens the Macro dialog box. Select a Macro Name and click on Edit.

To launch VBE in Excel 2003:

Click Tools -> Macro -> Visual Basic Editor; or

Use the combination of keys ALT+F11; or

Right click the active worksheet tab at the bottom, then click on View Code to display VBE.

To launch VBE using macro code - refer below code(s) which will enable you to open / close the VBE code window with a command button on your worksheet:

Example: Open / close VBE.

Sub LaunchVBE()

'use this code to open / close VBE

'use the MainWindow Property which represents the main window of the Visual Basic Editor - open the code window in VBE, but not the Project Explorer if it was closed previously:
Application.VBE.MainWindow.Visible = True

'close VBE window:
Application.VBE.MainWindow.Visible = False

'using the ActiveCodePane Property, show the active or last active CodePane object (note - a code window can contain multiple code panes:

Application.VBE.ActiveCodePane.Show

End Sub

Example: Open VBE and Project Explorer.

Sub LaunchVBE()
'use this code to open VBE and Project Explorer

'launch VBE and go to the "VBACode" procedure in a Standard Module - the Goto Method selects the vba procedure:
Application.Goto "VBACode"
'To display the Project Explorer window, you can use the shortcut keys CTRL +R in VBE. Using the SendKeys Method for keystrokes wherein combining the CTRL key is done by preceding the keycode with ^ (caret), we can open the Project Explorer if it was previously closed:

Application.SendKeys ("^r")

End Sub

Visual Basic Editor components

Refer Image 1 to view Visual Basic Editor and its components.

Code Window

This is where you write and edit your code and procedures, and also where macros get recorded by you. On the top-right of the code window is the Procedure list with which you can quickly move to another procedure in the active module. At the bottom of the code window you can adjust between the Procedure view which displays a single procedure in the active module, and the Full Module view which is the default view and displays all procedures in the active module.

Project Explorer

The Project Explorer displays the list of all existing projects. It gives a tree-view wherein you can collapse to hide or expand to view the objects, userforms and modules contained in a project. Each project contains: an Objects folder, which is the Microsoft Excel Objects folder; Forms folder if it contains UserForms; Modules folder if it contains Modules; and Class Modules folder if it contains Classes. The Objects folder is always present and contains: a sheet object for each existing worksheet, and a ThisWorkbook object. Each sheet object has as its first name appearing outside the parentheses which is the sheet code name, and the second name which appears after the code name and within the parentheses is the sheet tab name which appears on the tab of the Excel worksheet. To display the Project Explorer Window, click View on the VBE Menu Bar and then select Project Explorer, or press CTRL+R in VBE.

Macros (viz. vba codes or sub-procedures) should reside in their appropriate modules or Excel will not be able to find and execute them. The Object Modules are used for Excel built-in event procedures and to create your own events. Object Modules in Excel are: ThisWorkbook module, Sheet modules (worksheets and chart sheets), UserForm modules and Class modules. General vba code in respect of events not associated with a particular object (like workbook or worksheet) are placed in the standard code module. A generally accepted practice is to place event procedures in the ThisWorkbook module, Sheet modules and UserForm modules, while placing other vba codes in Standard Code modules. For a detailed understanding of placement of macros in appropriate Modules, refer our section Excel VBA Procedures, Naming Rules, Public & Private Scope, Placement in Modules, Call & Execute.

Refer Image 2 which shows the Modules in VBE.

Properties Window

The Properties Window displays a list of properties for the selected object or item, which can be edited here. For Modules only their Name can be edited usually; while Worksheets have additional properties like DisplayPageBreaks, EnableCalculation, StandardWidth, etc. which can be edited here; however using the Properties Window becomes particularly useful to edit the UserForm properties (viz. BackColor, Size, Caption, ...) and also properties of its controls like TextBox, ComboBox, CommandButton, etc. because of the numerous settings available. To display the Properties Window, in VBE click on View in the Menu Bar and then select Properties Window or press F4.

The Properties Window has 2 tabs wherein properties appear in Alphabetic order or are Categorized. Each property has 2 columns, on the left is the property's name and on the right is the property's value. The property name is a single word without spaces and it is this name that is used to access the property in vba code.

For example, in a UserForm, to access a property of a UserForm control like TextBox using vba code, write the TextBox name, followed by a period, followed by the property name and then followed by =. In this example we create a UserForm and add the TextBox control. First we edit TextBox properties in the Properties Window. Select the TextBox in the UserForm, in the Properties Window select the BackColor property in the left column and then choose the yellow color in the right column and then press enter. This changes the BackColor of the TextBox toYellow. To change the Text property of the TextBox, select the Text property in the left column and then type "Hello" in the right column (the default text is blank) and then press enter. This will insert the text "Hello in the TextBox. Refer Image 3a which shows a TextBox before its properties are edited and Image 3b shows the TextBox after the changes. To do these changes with vba code:

Change TextBox BackColor to Yellow - note that the default name of the TextBox is TextBox1, and the property name is BackColor:

TextBox1.BackColor = vbYellow

Insert the text "Hello in the TextBox - note that the default name of the TextBox is TextBox1, and the property name is Text:

TextBox1.Text = "Hello"

Note that each worksheet has a code name and a sheet name as explained above: code name being the first name appearing outside the parentheses in Project Explorer while the sheet name (ie. sheet tab name) is the second name which appears after the code name and within the parentheses. The code name of the selected worksheet appears to the right of (Name) in the Properties Window while the sheet name appears to the right of Name when you scroll down in the Properties Window. By default, when a worksheet is added, the sheet name and code name are the same. You can change both the code name and the sheet name, but the code name can be changed only in the Properties window and not programatically with code. Both code name and sheet name can be used while writing code - using code name will safeguard and not affect the vba code if users change the sheet name. We show below how to use these names in vba code:

Refer Image 4 which shows the Project Explorer in VBE with 3 worksheets with names as follows - code name "Sheet1" and sheet name "VBA"; code name "Sheet2" and sheet name "VBE_Intro"; and code name "Sheet3" and sheet name "Sheet3".

Using the code name of a worksheet - the following code inserts the text "hello" in cell A1 of the worksheet whose code name is Sheet1:

Sheet1.Range("A1") = "hello"

Using the sheet name (ie. name on the worksheet tab) of a worksheet - the following code inserts the text "hi" in cell A1 of the worksheet whose sheet name is VBE_Intro:

Worksheets("VBE_Intro").Range("A1") = "hi"

Referencing the first worksheet (counting worksheet tabs left to right) in the workbook, by index - the following code inserts the text "bye" in cell A1 of the first worksheet:

Worksheets(1).Range("A1") = "bye"

The Programming Workspace

Menus and Toolbars:

VBE has a Menu Bar which has menus like File, Edit, View, Insert, … and other Toolbars, like Standard, Debug, Edit & UserForm Toolbars where you can add Commands (which appear as buttoms or icons) like Save, Print, Delete, Undo, Redo, …

Object drop-down Box and Procedure drop-down Box:

On top of the Code Window, there are two drop-down boxes - on the left is the Object box and on its right is the Procedure box. In the Object drop-down list you can select an object to work on viz. Worksheet, Workbook, UserForm (or any UserForm control like TextBox), etc. On selecting an Object, you can select the available event procedure of that object from the Procedure drop-down list viz. 'Open' event for Workbook object, 'Change' event for Worksheet object, and so on. When you are only working with code, the Object box displays 'General' which is the default object and the Procedure box contains a list of the existing individual macros or procedures within the active module under the 'Declarations' heading.

The Immediate Window:

The Immediate Window is used in VBE for debugging VBA code and allows you to check the results of an individual line of code. You can type the code and then press enter to execute, which enables immediate evaluation of the statement, method or procedure. To display the Immediate Window, click on View in the VBE Menu Bar and then select Immediate Window, or press CTRL+G.

The Locals Window:

The Locals Window is used in VBE as a debugging tool, and it automatically displays the name, value and type for all declared variables in the current procedure and updates the variables' values while the code is executed. To display the Locals Window, click on View in the VBE Menu Bar and then select Locals Window.

The Watch Window:

The Watch Window is used in VBE as a debugging tool, and allows you to see and monitor the current value of a variable or expression, whose name, value, type and context are displayed in the Window. You need to specifically add a variable or expression to the Watch window which you want to monitor, unlike in the Locals Window where all the variables in the current procedure are displayed automatically. To add the variable or expression to the Watch window, select it and right-click and then click Add Watch, or alternatively select it and click Add Watch or Quick Watch on the Debug menu, or yet another alternate is to select it and press the keys Shift+F9. The Watch window displays automatically on adding a variable or expression to the Watch window. You can also display the Watch Window by clicking on View in the VBE Menu Bar and then select Watch Window, and thereafter add a variable or expression you want to monitor.

Command Bars and Controls in VBE:

Excel contains both built-in command bars, and custom command bars which have been added by a user. A command bar can be of 3 types: Menu Bar, Toolbar, or a Pop-up Menu. Each command bar can further contain additional command bars and controls. The Controls collection contains all controls on a command bar - use the Controls property of the CommandBar object to return a CommandBarControls object, which is a collection of CommandBarControl objects and refers to all the controls on the command bar. You will use the Caption Property, and Index Property, of the CommandBarControl Object, to refer to a command bar control by its caption or index number (beginning with 1) respectively. A control also having a Controls collection containing all controls on a pop-up menu (pop-up menu control has a nested layer(s) of menus & submenus), will be of type msoControlPopup. Refer below example which returns Command Bars & Controls available in VBE.

Example: Get Command Bars & Controls available in VBE - refer Image 5

Sub CommandBarsControls()
'returns Command Bars & Controls available in VBE - refer Image 5.

Dim ws As Worksheet
Dim cmdBar As CommandBar
Dim ctlTL As CommandBarControl, ctlSL As CommandBarControl
Dim lRow As Long, lCol As Long

Set ws = Worksheets("Sheet1")
ws.Activate

'set start cell to range A1:
lRow = 1
lCol = 1

'Loop through Command Bars viz. Menu Bar, Standard, Edit, UserForm, Debug, ...

For Each cmdBar In Application.VBE.CommandBars

'enter command bar name in a worksheet cell, starting from range A1:
Cells(lRow, lCol).Value = cmdBar.Name
'set font to bold:
Cells(lRow, lCol).Font.Bold = True
'set backcolor of cells to red:
Cells(lRow, lCol).Interior.Color = vbRed
'enter next command bar in successive rows:
lRow = lRow + 1

'Loop through Top-Level menus viz. File, Edit, View, Insert, ...

For Each ctlTL In Application.VBE.

CommandBars(cmdBar.Index).Controls

'enter the control ID and caption:
Cells(lRow, lCol).Value = ctlTL.ID & " - " & ctlTL.Caption
'set backcolor of cells to green:
Cells(lRow, lCol).Interior.Color = vbGreen

'include controls also having a Controls collection containing all controls on a pop-up menu ie. of type msoControlPopup:

If ctlTL.Type = msoControlPopup Then

'alternatively: msoControlSplitButtonMRUPopup control type will include Align/Center/Size sub-level controls (Lefts, Centers, Rights, Width, Height, ...) for UserForm command bar:
'If ctlTL.Type = msoControlPopup Or ctlTL.Type = msoControlSplitButtonMRUPopup Then

'Loop through Sub-Level menus (of each Top-Level menu) viz. File->New Project, File->Open Project, ...

For Each ctlSL In Application.VBE.

CommandBars(cmdBar.Index).

Controls(ctlTL.Caption).Controls

'go to successive columns to enter next Sub-Level menu:
lCol = lCol + 1
'enter the sub-level control ID and caption, in successive columns:
Cells(lRow, lCol).Value = ctlSL.ID & " - " & ctlSL.Caption
'set backcolor of cells to yellow:

Cells(lRow, lCol).Interior.Color = vbYellow

End If

'enter next Top-Level menu in successive rows:
lRow = lRow + 1
'start from first column for next Top-Level menu:

lCol = 1

'autofit column width:

ws.UsedRange.Columns.AutoFit

End Sub

Excel & VBA

Ebook of Excel Formulas

VBA Tutorial Login

Excel VBA