Expression Statements: VBA’s Workhorse

by Sola Bode
in VBA Coding Constructs
March 6, 2021

Expression statements do the heavy lifting in VBA. That is because they are prevalent in typical VBA programs. This article introduces these workhorses.

3 minutes read

Expressions

Expressions are complete units of code that evaluate to a single value, of a specific data type. They consist of literals, value-holding program identifiers, and operators. Unlike expression statements, they don’t influence (e.g., start, stop, or control) program actions. They only produce, i.e. evaluate to, a single value.

Expressions are often classed based on the data type of the value they evaluate to. As such, Boolean, numeric, string, and date expressions are common categories.

The literals and program identifiers in an expression must be of the same type as the expression. For instance, a numeric expression must have numeric literals and identifiers.

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.

An expression statement influences (e.g., start, stop, or control) a program’s state or actions. For instance, an expression statement may assign an expression’s return value to a variable. Such a statement is called an assignment statement. And it influences the program’s state by changing a program identifier’s value.

Simple and Compound Expressions and Statements

Examples of simple expression statements are variable assignments and function or method calls. One example is a statement that sums numbers and stores the result in variables. Another example is displaying “Hello, World!” with the MsgBox function or Debug.Print method.

But expressions and expression statements are not always simple. In fact, they often comprise more than one expression. Moreover, it is common for function return values to be operands of other expressions. Furthermore, all these take place within even broader, more complex expressions.

That said, there is a crucial element necessary for such compound expressions to work. The enveloping (i.e., parent) expression’s data type must match that returned by the enveloped (i.e., child expression).

What about Length and Precedence?

There is no limit to an expression statement’s length. It may even split into several code-lines using the line continuation character (_). However, you have to pay special attention to the order of precedence of the operators used. That applies to both compound expressions and expression statements.

By default, VBA’s built-in operator precedence applies, and that may not be what you want. You should state your preferred operator precedence using open and close parentheses. That removes any ambiguity and makes it easier to read and maintain your code as well.

VBA Expression Statements

The different VBA expression statements are briefly introduced below. These summaries highlight VBA’s available tools vis-à-vis expression statements. Also, they include links to more detailed information (e.g., syntax and rules) on each statement.

Fun fact: Declaration statements, which name a program identifier, are also expression statements. That’s because they influence the program state by reserving memory for identifiers.

Generic VBA Expression Statements

Statement	Brief Description
AppActivate	Activates (i.e., gives focus to) a named application or window, without minimizing or maximizing it. Sizing depends on the application or window’s *WindowState* property-value or the *WindowStyle* argument’s value if the Shell function is used to run the application.
Let	Assigns an expression’s value to a variable or object’s property.
Set	Assigns an object reference to an object variable or an object’s property.
With	Creates a block (enveloped within *With* and *End With* statements) that enables the execution of a sequence of statements on the object (or a user-defined type) named in the block’s opening line. So, the object (or user-defined type) need only be fully qualified once, rather than in multiple lines inside the block.
End	If used by itself, it immediately ends program execution, closes files opened with the *Open statement, empties variables, destroys objects, and frees up memory. Moreover, it ends procedures or blocks if immediately followed by a procedure (Function, Sub, or Property) or block (If, Select, With, or Type*) keyword.
Stop	Suspends program execution but, unlike the *End statement, does not close files opened with the Open statement, empty variables, destroy objects, and free up memory. Basically, it works like a breakpoint and is likewise mainly used for debugging. However, if the program has been compiled into an executable file (i.e., having the “.exe” extension) then it works just like an End statement*.
Load	Places an object (e.g., a UserForm) in memory, however, users cannot interact with the object as the statement does not also show it. Making the object visible requires using the object’s Show method, until then the user can only interact with the object via code (i.e., programmatically) under its Initialize event.
Unload	Deletes an object from memory, freeing up all memory used by the object and preventing users from interacting with the object, via code or otherwise.
Rem	Adds remarks, comments, or explanatory notes in a program, and can be substituted with the *comment character*, i.e. apostrophe (‘).
Beep	Rings out a tone from the computer's built-in speaker.
Date	Assigns the current system date.
Time	Assigns the system time.
DeleteSetting	On Windows, it removes a segment or key setting from an application's *Windows Registry* entry. However, on the Macintosh, it does the same with information in the application's *Initialization File*.
SaveSetting	On Windows, it saves or creates an entry in an application's *Windows Registry. However, on the Macintosh, it does the same with information in the application's Initialization File*.
Randomize	Initializes the random number generator, i.e. the Rnd function.
SendKeys	Sends keystrokes to the active window as if typed at the keyboard.
Error	Is a backward compatibility statement that simulates the occurrence of an error. Modern VBA code should instead use the Err object’s Raise method to simulate runtime errors.

VBA Expression Statements related to Files, Folders/Directories, and Drives

Statement	Brief Description
ChDir	Changes the current/default directory or folder to the one specified by its *path* argument (a String expression).
ChDrive	Changes the current drive to the one specified by its *drive* argument (a String expression).
MkDir	Creates a new directory or folder, specified by its *path* argument (a String expression).
RmDir	Removes an existing directory or folder, specified by its *path* argument (a String expression).
Name	Changes the name of a disk file, directory, or folder (specified by its *oldpathname* argument – a String expression) to a new name specified by its *newpathname* argument (a String expression).
Open	Commences data input/output from/to a file, one file at a time.
Close	Completes data input or output (I/O) from or to single, multiple, or all files that were opened with the *Open* statement.
Seek	Assigns the *position, within a file opened by using the Open* statement, for the subsequent read/write action.
Get	Reads data from an open disk file into a variable; works counter to the *Put* statement.
Put	Writes data from a variable to a disk file; works counter to the *Get* statement.
Input #	Reads data from an open sequential file and assigns the data to variables; works counter to the *Write #* statement.
Line Input #	Reads a single line from an open sequential file and assigns it to a String variable.
Print #	Writes display-formatted data to a sequential file.
Write #	Writes data to a sequential file; works counter to the *Input #* statement.
Width #	Sets an output line width to a file opened by using the *Open* statement.
SetAttr	Assigns attribute data for a file.
FileCopy	Copies a file.
Kill	Deletes a file, specified by its *pathname* argument (a String expression), from a disk.
Reset	Closes all active files opened with the *Open* statement while writing the data in all file buffers to disk.
Lock/Unlock	Allows/prevents access to all or part of a file (opened by using the *Open* statement) by other processes.

VBA Expression Statements related to Module-level Program Identifiers

Statement	Brief Description
Deftype	Used at module level to assign the default data type of *variables, arguments* passed to procedures, and *Function* and *Property Get* procedures’ return type whose names begin with specified characters.
RaiseEvent	Executes a module-level *Event* within a class, form, or document.
Type	Used at the module level to define a user-defined data type containing one or more elements.

VBA Expression Statements related to Arrays, Strings, and Classes

Statement	Brief Description
Erase	Reinitializes the elements of fixed-size arrays and releases dynamic-array storage space.
Implements	Specifies an interface or class that will be implemented in the class module in which it appears.
LSet	Left aligns a string within a string variable or copies a variable of one user-defined type to another variable of a different user-defined type.
Mid	Replaces a specified number of characters in a Variant (String) variable with characters from another string.
RSet	Right aligns a string within a string variable.