Accessing the Visual Basic Editor (VBE)
- Shortcut key (ALT + F11)
- View tab
- Under the View tab, select Macros > View Macros
- Select a macro name and click Edit
- Developer tab
- If not yet shown, right click on the Ribbon and Customize the Ribbon. Put a check mark for Developer
- Under the Developer tab > Macros > Edit
- Another option is to select the Visual Basic icon under the Developer
- Right click on the spreadsheet > View Code
Parts of the VBE
1 – Switch to Excel (Alternative: ALT + F11)
2 – Project explorer: outline of all open projects, workbooks and worksheets and the objects they contain.
- PERSONAL_XLSB: Personal Macro workbook. Any macros saved in this VBAProject will be available to all workbooks
3 – Insert menu bar: insert procedures, modules, and userforms (Alternative: right click > Insert module)
4 – Code editor window: Contains all the written code for a module and are written in Subs
5 – Switch views
- Full module view: include all codes in a particular module
- Procedure view: view code of a selected Sub and exclude any codes nested below
6 – Edit Toolbar: editing options. Activated from View tab > Toolbars > Edit.
7 – Properties Window: Properties of the items found in the Project Explorer (modules, sheets, etc). Activated from View tab > Properties Window
8 – Immediate Window: Used for code testing. Activated from View tab > Immediate Window.
9 – Run / Stop code during testing: Reduces the need of going back to Excel to run the code.
Featured Course
Unlock Excel VBA & Excel Macros
Opening an object or module
In the VBE, macros that applies to the entire workbook are written and saved in modules.
Code that is specific to a sheet is written and saved in its own sheet.
Generally, the latter is used for special worksheet events which trigger certain actions (e.g. selection change, table update, etc).
To edit a macro, select the module in the Project Explorer.
Double clicking on another object or module on the Project Explorer allows you to shift views.
The active object is highlighted in gray.
When opening a workbook object, select Workbook under the first dropdown on the Code Window.
On the right, you will see a list of events that apply.
For example, if you want a certain macro to run when the workbook opens, select the Open event and write code under that Sub.
While additional macros and code can be written on the “ThisWorkbook” object, it is recommended to restrict it to the events that are available in the object, and write all general procedures inside a separate module.
Inserting a module
While the macro recorder automatically inserts a new module, there are two ways to do this manually:
- Click on Insert > Module.
2. Right click on the Properties Window > Insert > Module
Switching views
Views can be changed by clicking on either the Full module view or Procedure view at the bottom left corner of the Code Window.
The latter will show you the Sub procedure you are currently working on.
This can also be done by selecting from the event dropdown on the upper right corner of the Code Window.
Changing module and object properties
Select the object or module in the Project Explorer Window.
The properties attributed associated with the selection is found in the Properties window.
To change the name of a module, click on the text field beside the (Name) and specify the name.
Changing the name of a sheet on Excel will automatically update the name of the sheet on the VBE side.
Editing option toolbar
To activate this, go to View > Toolbars > Edit.
VBA Procedures
- Sub procedure
A set of commands the code should execute and is enclosed by the Sub and End Sub statements. Writing a custom VBA code includes creating your own modules. Each module can be composed of multiple codes and macros.
TIP: Work with lesser modules by putting multiple Subs in each module.
- Function Procedure
Commands that create formulas that returns one value or an array of values. This can be used as “normal” formulas in Excel or inside other procedures.
VB Basics and Color Guidelines
Blue – Code keywords (capitalized)
Red – Wrong use of code
Green – Comments
Comments
Comments can be added anywhere in the code space for ease of navigation as long as it starts with a single quotation mark (‘).
These are commonly used to insert brief descriptions of the code for reference.
Split long code into two lines
For readability, a single long line of code can be split into two lines by adding a space and underscore (_) in the middle of a line of code and hitting Enter.
Alignment and Structure
VBE automatically adds an indent for every level to keep things organized.
This can be done manually by pressing Tab, and can be undone by pressing SHIFT + Tab.
This can be applied to multiple lines of code by highlighting them and pressing Tab.
The Indent and the Outdent icons on the Edit Option Toolbar can also be used.
Auto Syntax check
This is useful in alerting you with errors in your code through a popup.
To enable this, go to Tools > Option. Tick the box for Auto Syntax Check.
Variable Declaration
Helpful when creating your own procedures or modifying codes.
This automatically puts Option Explicit on top of all the modules created.
To activate this, go to Tools > Option.
Tick the box for Require Variable Declaration.
Auto List Members
Aids in having the correct name references in the code by providing a list of members found in the module.
There are three ways to activate this:
- Go to Tools > Option. Tick the box for Auto List Members.
- Under the Edit options toolbar, select the first icon
- CTRL + J
Complete Word
It helps you complete a word in the code by giving you a list of options.
This is enabled through the Edit Options toolbar or by pressing CTRL + Space.
Disabling parts of the code
When testing the code, there might be some lines that need to be disabled.
To do this in a quick manner, highlight the lines and click on Comment Block in the Edit Options Toolbar.
This converts those lines into a block of comments, indicated by the single quotation mark at the start and green color.
This can be switched back by clicking on the icon for Uncomment Block on the Edit Options Toolbar.
Select
This is automatically added to the lines of code when the macro recorder is used.
The Select statement is usually not necessary and can be removed to improve readability.
This is because with VBA, objects don’t need to be selected to manipulate them as long as they are referred to.
= signThis is usually used as a sign of equality between sides, but for VBA, its main function is t o assign the right side of the code to the left side.
Cell and Range References
VBA refers to cells in different ways.
ActiveCell refers to the cell that is currently active or is currently clicked on.
When referencing to a range, it uses syntax that is similar to Excel formulas (e.g. RANGE(“A1:E1”)).
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
