VBA Statements: The Building Blocks of VBA Programs

by Sola Bode
in VBA Coding Constructs
April 2, 2021

VBA programs are nothing but a sequence of VBA statements. They are the cornerstone of VBA programs. Here, we explore these vital elements of VBA coding.

7 minutes read

First of, What is a Statement?

A statement is a complete instruction written in a high-level programming language. It directs a program to start or control actions. In fact, VBA programs are nothing but a sequence of statements. Each statement is often comprised of other simpler statements, identifiers, operators, or expressions.

Based on program design, the actions performed by statements may have side effects. That is, they may trigger a cascade of other program actions besides their primary task.

Unlike statements, expressions don’t influence program actions. They simply evaluate to a single value. That return value is only useful if the expression is part of a statement or is itself a statement (an Expression statement).

VBA is a high-level imperative programming language. So, it differentiates declaration (or non-executable) from executable statements. In VBA:

Declaration statements are units of code that name program identifiers. They also reserve space for their values in the computer’s memory (i.e., RAM).
Executable statements are units of code that implement program actions.

Declaration statements may also specify an identifier’s data type, initial value, scope, and lifetime. Also, they become definitions when they assign an initial value to an identifier.

Declarative VBA Statements: Say Hello to your Little Friends

As mentioned earlier, declaration statements:

Introduce program identifiers;
Reserve memory space for them, and;
May specify their data type, initial value, scope, and lifetime.

Declaration statements vary according to the type of identifier they declare. The table below summarizes the declaration statements for common VBA program identifiers.

Identifier	Brief Description	Declaration
Variable	A named memory location that stores data (of a certain type) whose value can be altered during program execution (i.e., at runtime).	Dim statement.
Constant	A symbolic and meaningful name assigned a value, at compile time (i.e., before running a program), that cannot be altered at runtime.	Const statement.
Enumeration (or Enum)	A predefined set of related constants, each assigned an integer value.	Enum statement.
Procedure	A code block beginning with a *Sub, Function, Property, or Event* statement and finishing with a *End Sub, End Function, or End Property*) statement.	Sub, Function, Property, or Event statements.
Procedure Argument	Data (of specific type and value) fed into procedures to facilitate execution.	*Sub, Function, Property, or Event* statements. The ByRef and ByVal keywords also play a crucial role in argument declaration.
Function Return	Data (of specific type and value) returned by a function upon execution.	*Function* statement.
Objects	Instances (i.e., clones) of a class. A class is a code template for creating objects, it is a structure comprised of both data and the procedures for manipulating it.	*Dim* statement.
Property	A class/object’s data denoting its attributes or characteristics.	*Property* statement.
Method	A class/object’s procedures designed to manipulate its data (i.e., properties).	*Sub* or *Function* statement.
Event	Procedures triggered by internal (e.g. running out of memory) and external (e.g. mouse clicks) actions or occurrences detected by a program.	*Sub* or *Event statement* (for user-defined events).

Executable VBA Statements: Your Army of Worker Ants

As mentioned earlier, executable statements elicit processing actions. Such actions include arithmetic operations and reading (writing) data from (to) memory.

In VBA, all executable code must be in procedures in Standard, Form, or Class modules. So, you must write them in Subroutines, Functions, Methods, or Property procedures.

Fun Fact: the Dim statement is technically an executable statement. That’s because it makes the compiler reserve memory space for the identifier it declares.

In general, there are two types of executable statements in VBA, simple and compound:

Simple statements are single-line statements comprised of individual statements.
Compound statements are single or multiple line statements comprised of several individual statements. So, the body of a Subroutine, Function, Method, or Property procedure is syntactically one compound statement.

In VBA, programs run executable statements in the sequence in which they appear. However, Jump statements make it possible to bypass this strict sequential execution. Speaking of different types of executable statements, VBA supports the following types:

Expression statements;
Selection statements;
Labelled statements;
Jump statements;
Iteration statements.

Expression Statements

Expression statements do the heavy lifting in VBA programs. That is because they are the most prevalent statement class in typical VBA programs. In a nutshell, they are statements made up of one or more expressions.

The execution of an expression statement causes the evaluation of its expression(s). In the end, what you get is a single value of a specific data type.

Expression statements influence (e.g., start, stop, or control) a program’s state or actions. For instance, they may assign an expression’s return value to a variable. Such assignment statements affect the program’s state by altering the value of its identifiers.

Selection Statements

Humans often decide on a course of action by evaluating one or more criteria. Likewise, computer programs select a flow of execution (a.k.a. control) by evaluating expressions.

There are two types of selection statements in VBA:

If–Then statements run an execution path if their control expression evaluates to a specific Boolean value. The Boolean return is either True (non-zero, ≠ 0) or False (zero, = 0) value;
Select Case statements run an execution path only if their control expression evaluates to a specific value.

Labelled Statements

Any statement becomes a labelled statement when preceded by a line label or number. Line labels or numbers are markers that flag a code-line. Usually, they mark code-lines as a target for jump statements.

Both line labels and numbers must start in the first column of a code-line, so no indenting. Also, line labels can be any case-insensitive alphanumeric that starts with a letter and ends with a colon. Whereas, line numbers can be any unique (i.e., relative to their module of use) blend of digits.

Jump Statements

VBA programs execute code-lines in a sequential manner. However, situations often arise where you need to skip ahead or circle back to a code-line. That is where jump statements come into play.

They allow the non-sequential transfer of control (i.e., execution) in programs. As such they are a very important coding that aspiring VBA programmers should know.

They are often used to alter the behaviour of selection and iteration statements. And they also play a role in implementing Sub procedures.

What follows is a brief description of the different jump statements in VBA:

Exit statements instantly leave a code-block. They also transfer control to the code-line right after the block’s closing statement. However, they only work for specific blocks.
Call statements instantly transfer control to Sub and Function procedures and dynamic-link libraries.
GoTo statements unconditionally transfer control to any labelled statement in the same procedure. After running a labelled statement, control goes to the line right after that statement.
On – GoTo statements conditionally transfer control to one of several labelled statements. The line label or number they send control to depends on the value of their numeric control expression.
GoSub – Return statements unconditionally transfer control to any subroutine in the same procedure. After running a subroutine, they send control back to the line right after themselves.
On – GoSub statements conditionally transfer control to one of several subroutines. The subroutine they send control to depends on the value of their numeric control expression.
On – Error statements instantly transfer control to error-handlers when runtime errors occur. Also, these statements and their error-handlers must be in the same procedure.

Iteration Statements

In everyday life, we often need to repeat certain actions. For instance, Americans celebrate their independence every 4th of July. Likewise, your program may need to repeat one or more statements several times.

Iteration statements make such looping possible in VBA. They are code-blocks comprised of opening and closing declaration statements. These declaration statements envelop the statements that we want to repeat.

They are vital elements of any programming language. In Excel VBA, they enable efficient looping through a range of cells, worksheets, workbooks, charts, arrays, strings, and other collections. Moreover, they present major advantages, compared to using repetitive, redundant code:

The reduced amount of code leads to more concise and easily understood code;
The appropriate use of loops avoids hardcoding, making code more flexible. That is, we can run a variable number of repetitions instead of a fixed or inflexible count;
The reduced amount of code also leads to easier testing, debugging, and editing.

Of course, the repetition (or looping) done by an iteration statement must end at some point. That leads us to the two broad types of iteration statements:

Fixed-iteration loops – here, the program knows the number of repetitions before the loop starts. So, the loop ends when the cycle-count exceeds the pre-fixed repetition limit;
Indefinite loops – here, the loop’s endpoint occurs when its control expression (a.k.a. loop invariant or loop determinant) evaluates to False (or zero, = 0). So, the loop ends when a certain condition is met. And the program doesn’t know how many cycles will run beforehand.

So, what iteration statements does VBA support? And how does one decide which to use at any given time? The table below summarizes the answers to these questions:

Statement	Description	Iterations	Use Case
For – Next	Repeats a set of statements a fixed number of times.	Fixed	When the required number of iterations is known by (or would be known to) the program prior to looping.
For Each – Next	Repeats a set of statements once for each object (or element) found in a collection (or array).	Fixed	When a set of statements should be run once for each member of a group. By default, the required number of iterations (i.e., a collection’s object-count or an array’s size) is known prior to looping.
Do While – Loop	Repeats a group of statements while a condition (i.e., *control expression) is True*.	Indefinite	When the required number of iterations is unknown at the start of the loop; When a scenario where *not even one* iteration runs is allowed; When the preference is to repeat the statement(s) *while a condition IS met*.
Do Until – Loop	Repeats a group of statements until a condition is *True*.	Indefinite	When the required number of iterations is not known at the start of the loop; When a scenario where *not even one* iteration runs is allowed; Preference is to repeat the statement(s) *while a condition IS NOT met*.
Do – Loop While	Repeats a group of statements while a condition is *True*.	Indefinite	When the required number of iterations is unknown at the start of the loop; When a scenario where *at least one* iteration runs is allowed; When the preference is to repeat the statement(s) *while a condition IS met*.
Do – Loop Until	Repeats a group of statements until a condition is *True*.	Indefinite	When the required number of iterations is unknown at the start of the loop; When a scenario where *at least one* iteration runs is allowed; When the preference is to repeat the statement(s) *while a condition IS NOT met*.
While – Wend	Repeats a group of statements while a condition is *True*.	Indefinite	When the required number of iterations is unknown at the start of the loop; When a scenario where *not even one* iteration runs is allowed; When the preference is to repeat the statement(s) *while a condition IS met. However, it is both outdated and inflexible. So, use either the Do While – Loop* or *Do – Loop While* statements instead.