Understanding Expressions
You can think of an expression as being like a compact version of a user-defined function. In other words, in the same way that a function takes one or more arguments, combines them in various ways, and returns a value, so too does an expression take one or more inputs (called operands) combines them with special symbols (called operators) and produces a result. The main difference, though, is that an expression must do all its dirty work in a single VBA statement.
For example, consider the following statement:
frequency = "Monthly"
Here, the left side of the equation is a variable named frequency. The right side of the equation is the simplest of all expressions: a text string. So, in other words, a string value is being stored in a variable.
Here's a slightly more complex example:
energy = mass * (speedOfLight ^ 2)
Again, the left side of the equation is a variable (named energy) and the right side of the equation is an expression. For the latter, a variable named speedOfLight is squared, and then this result is multiplied by another variable named mass. In this example, you see the two main components of any expression:
- Operands-These are the "input values" used by the expression. They can be constants, variables, object properties, function results, or literals. (A literal is a specific value, such as a number or text string. In the first expression example, "Monthly" is a string literal.)
- Operators-These are symbols that combine the operands to produce a result. Common operators are the familiar + (addition) and - (subtraction). In the example just shown, the * symbol represents multiplication and the ^ symbol represents exponentiation.
Combining operands and operators produces a result that conforms to one of the variable data types outlined in the previous tutorial: String, Date, Boolean, or one of the numeric data types (Integer, Long, Currency, Single, or Double). When building your expressions, the main point to keep in mind is that you must maintain data type consistency throughout the expression. This means you must watch for three things:
- The operands must use compatible data types. Although it's okay to combine, say, an Integer operand with a Long operand (because they're both numeric data types), it wouldn't make sense to use, say, a Double operand and a String operand.
- The operators you use must match the data types of the operands. For example, you wouldn't want to multiply two strings together.
- If you're storing the expression result in a variable, make sure the variable's data type is consistent with the type of result produced by the expression. For example, don't use a Boolean variable to store the result of a string expression.
VBA divides expressions into four groups: numeric, string, date, and logical. I discuss each type of expression later in this tutorial, but let's first run through all the available VBA operators.