VBA Glossary

VBA Dictionary

This VBA dictionary[1] is a useful guide for anyone wanting to learn how to use VBA in Excel modeling. Review each of the terms and definitions in the VBA dictionary below to learn the fundamentals before taking CFI’s VBA Financial Modeling Course. Download our ultimate VBA cheat sheet for a comprehensive list of VBA definitions, functions, and more!

The Ultimate VBA Cheat Sheet

Download CFI’s Ultimate VBA Cheat Sheet to advance your knowledge and perform better analysis.

Absolute Reference

Absolute Reference is used when recording macros where references relate to a particular cell or group of cells that you do not want to change. This is the default setting when recording macros.

Boolean

A variable data type that uses 2 bytes of memory and can only have two possible values – true or false. It is named after George Boole.

Branching

The name given to code that ensures that certain sections of code are only executed under certain circumstances.

Call

We can use “Call” to run one subprocedure from another.

Code Window

The VBA Code Window is where we write, display. or edit code. Quickly access the Code Window by selecting F7.

Dim

We declare variables using the “Dim” statement. For example, “Dim InterestRate” declares “InterestRate” as a variable storage location.

Do…Until

A commonly used form of looping code that allows a section of code to run more than once.
“Do Until” loops continue until the condition is true.

Do…While

A commonly used form of looping code that allows a section of code to run more than once.
“Do While” loops continue while a condition remains true.

Event Handling Procedures

An event handling procedure is a subprocedure that is called automatically when an event occurs.

For…Next

This is a commonly used form of looping code that allows a section of code to run more than once.

Form Controls

Form Controls include buttons that can be used to let users know that macros are available and make code more accessible.

If…Then

This is a commonly used form of branching code which allows certain sections of code to be executed only under certain circumstances.

InputBox

An input box is used to prompt for specific user input during the execution of a subprocedure. The user input from the input box function is then assigned to a variable.

Locals Window

This is the name given to code that allows for a section of code to run more than once.

Looping

The VBA Locals Window allows us to display all the declared variables in the current procedure and their values.

Methods

Methods are actions that we perform with objects. For example, having pointed VBA at a cell (object) and given it a value (property), the action required may be to copy the contents of that cell to another cell (object). This is referred to as a “Copy Method”.

Module

A standard module contains code that can be shared among all modules in a project.

MsgBox

This VBA function displays a pop-up message box. Message boxes are typically used to provide information and guidance.

Object Browser

The Object Browser enables us to see a list of all the different objects with their methods and properties. The browser can be accessed by hitting F2 or by selecting it from the “View” pull-down menu.

Objects

Objects are things that we work with in VBA. There are over 250 objects in Excel that we can work with. In Excel, we are most often going to work with the following objects – workbooks, worksheets, ranges, and cells.

On Error Statements

On error statements bypass Excel’s built-in error handling procedures and allow us to create our own error handling procedures.

Option Explicit

With an “Option Explicit” statement, code cannot be run that contains any undeclared variables. Excel will generate an error if it encounters a misspelled variable.

Project Explorer Window

The VBA Project Explorer Window displays a list of projects and project items in Excel documents. The shortcut is CTRL+R.

Properties

Properties are attributes of objects. Each object has a set of properties associated with it, such as its numeric value, its font style, or color.

Properties Window

The VBA Properties Window displays a list of properties a selected item. The shortcut is F4.

Relative Reference

This is used when recording macros where all movements are recorded as relative to an active cell. This needs to be turned on, as it is not the default setting for recording macros.

Sub Procedure

This is the basic unit of VBA code. A subprocedure starts with “Sub” and ends with “End Sub”.

Subscript Out of Range Error

This error is often caused by referencing a nonexistent object.

User Defined Functions

VBA in Excel allows us to create custom functions. To create a user-defined function, the code must start with “Function” and end with “End Function”.

Variables

Variables are named storage locations. Declaring variables makes code run faster and uses memory more efficiently. We declare variables using the “Dim” statement.

VBA

Visual Basic for Applications is the name given to Microsoft’s programming language that allows users to build user-defined functions and automate processes.

Visual Basic Editor

This is the tool used to create, modify, and maintain VBA procedures and modules in Microsoft Office applications.

WorksheetFunction

To use Excel functions in VBA code, the function must be preceded by “Application.WorksheetFunction”. For example, if we want to access the average function, then we write “Application.WorksheetFunction.Average”.

Resources beyond the VBA dictionary

We hope this VBA dictionary is a useful guide for learning how to use VBA in your Excel modeling. You should now be ready to take CFI’s VBA Financial Modeling Course!  More CFI resources to help you advance your career include:

• Types of Financial Modeling
• Corporate Valuation Methods
• List of Excel Formulas
• Financial Modeling Analyst Certification
• See all Excel resources