Close the Skill Gap -> Enroll to be a Certified Financial Modeling & Valuation Analyst (FMVA)® Today!

VBA Glossary

Learn VBA terms and definitions

VBA Dictionary

This VBA dictionary 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.

 

Absolute Reference

Used when recording macros where references relate to a particular cell or group of cells that we 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 sub procedure from another.

 

Code Window

The VBE Code Window is where we write, display or edit code. To quickly access the Code Window select 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 sub-procedure that is called automatically when an event occurs.

 

For…Next

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

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 sub procedure. The user input from the input box function is then assigned to a variable.

 

Locals Window

The name given to code that allows for a section of code to run more than once.

 

Looping

The VBE 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 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, range, 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 VBE 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 VBE Properties Window displays a list of properties a selected item. The shortcut is F4.

 

Relative Reference

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

The basic unit of VBA code. A sub procedure 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 use 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

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 we would write “Application.WorksheetFunction.Average”.

 

Resources beyond the VBA dictionary

We hope this VBA dictionary was 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 resources to help you advance your career include:

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

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

 

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!