Chapter 4. Building VBA Expressions

     

In this chapter

  • Understanding the basic components of an expression

  • Learning the expression operators used by VBA

  • Controlling the order in which your expressions are calculated

  • Using VBA's math and financial functions

  • Creating string, logical, and date expressions

The VBA variables you learned about in the Chapter 3, "Understanding Program Variables ," don't amount to a hill of beans unless you do something with them. In other words, a procedure is merely a lifeless collection of Dim statements until you define some kind of relationship among the variables and your program objects. (I'll talk about the latter in Chapter 5, "Working with Objects.")

To establish these relationships, you need to create expressions that perform calculations and produce results. This chapter takes you through some expression basics and shows you a number of techniques for building powerful expressions using not only variables, but also VBA's built-in functions.

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), combine them with special symbols (called operators), and produce 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:

 might = "right" 

Here, the left side of the equation is a variable named might . 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 variables, object properties, function results, or literals. (A literal is a specific value, such as a number or a text string. In the first expression example, "right" 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.

This combination of operands and operators produces a result that conforms to one of the variable data types outlined in the last chapter: 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 chapter, but let's first run through all the available VBA operators.



Absolute Beginner's Guide to VBA
Absolute Beginners Guide to VBA
ISBN: 0789730766
EAN: 2147483647
Year: 2003
Pages: 146

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net