- Shopping Bag ( 0 items )
Ships from: Diamond Bar, CA
Usually ships in 1-2 business days
|3||Building VBA Expressions||59|
|4||Working with Objects||93|
|5||Controlling Your VBA Code||115|
|6||Word for Windows VBA Programming||139|
|7||Manipulating Excel with VBA||171|
|8||Programming PowerPoint Presentations||217|
|9||VBA and Access||247|
|10||Interacting with the User||277|
|11||Working with Microsoft Forms||295|
|12||Creating Custom Menus and Toolbars||333|
|13||Working with Other Applications||359|
|14||Programming OLE and ActiveX Objects||381|
|15||Controlling Applications Via OLE Automation||409|
|16||Rolling Your Own Objects with Class Modules||423|
|17||Using VBA to Work with Excel Lists||445|
|18||Programming Data Access Objects||469|
|19||Advanced Database Programming||493|
|20||Internet and Intranet Programming Topics||535|
|21||Web Page Programming: ActiveX and VBScript||567|
|22||E-Mail and Groupware Programming with Outlook||603|
|23||Trapping Program Errors||653|
|24||Debugging VBA Procedures||669|
|25||Programming the Windows API||689|
|26||VBA Tips and Techniques||727|
|27||A Check Book Application||769|
|28||Making Backups as You Work||799|
|29||Access and Outlook: E-Mail Merge||833|
|C||The Windows ANSI Character Set||883|
|D||An HTML Primer||891|
[Figures are not included in this sample chapter]
One types the correct incantation on a keyboard, and a display screen comes to life, showing things that never were nor could be...however, if one character, one pause, of the incantation is not strictly in proper form, the magic doesn't work.
The VBA variables you learned about in the preceding chapter 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 (we'll talk about the latter in the next chapter).
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, we 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 text string. In the first expression example, "right" is a string literal.) Operators: These are symbols that combine the operands to produce a result. 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: Date, String, Boolean, Object, Variant, or one of the numeric data types (Byte, Integer, Long, Single, Double, or Currency). 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:
VBA divides expressions into four groups: numeric, string, date, and logical. I'll discuss each type of expression later in this chapter, but let's first run through all the available VBA operators.
You've already seen the first of VBA's operators: the assignment operator, which is just the humble equals sign (=). You use the assignment operator to assign the result of an expression to a variable or to an object property.
Bear in mind that VBA always derives the result of the right side of the equation (that is, the expression) before it modifies the value of the left side of the equation. This seems like obvious behavior, but it's the source of a handy trick that you'll use quite often. In other words, you can use the current value of whatever is on the left side of the equation as part of the expression on the right side. For example, consider the following code fragment:
currYear = 1997 currYear = currYear + 1
The first statement assigns the literal value 1997 to the currYear variable. The second statement also changes the value stored in currYear, but it uses the expression currYear + 1 to do it. This looks weird until you remember that VBA always evaluates the expression first. In other words, it takes the current value of currYear, which is 1997, and adds 1 to it. The result is 1998, and that is what's stored in currYear when all is said and done.
NOTE: THE ASSIGNMENT OPERATOR IS NOT "EQUALS" Because of this evaluate-the-expression-and-then-store-the-result behavior, VBA assignment statements shouldn't be read as variable equals expression. Instead, it makes more sense to think of them as variable is set to expression or variable assumes the value given by expression. This helps to reinforce the important concept that the expression result is being stored in the variable.
VBA has a number of different operators that you use to combine functions, variables, and values in a VBA expression. These operators work much like the operators--such as addition (+) and multiplication (*)--that you use to build formulas in Excel worksheets and Word tables. VBA operators fall into five general categories: arithmetic, concatenation, comparison, logical, and miscellaneous.
VBA's arithmetic operators are similar to those you use to build Excel formulas. Table 3.1 lists each of the arithmetic operators you can use in your VBA statements. See "Working with Numeric Expressions" later in this chapter for a detailed look at each arithmetic operator.
|Mod||Modulus||10 Mod 5||0|
You use the concatenation operator (&) to combine text strings within an expression. One way to use the concatenation operator is to combine string literals. For example, the expression "soft" & "ware" returns the string software. Note that the quotation marks and ampersand aren't shown in the result. You can also use & to combine any kind of operand, as long as the operands use the String data type. For more information on the concatenation operator, check out the section "Working with String Expressions."
You use the comparison operators in an expression that compares two or more numbers, text strings, variables, or function results. If the statement is true, the result of the formula is given the logical value True (which is equivalent to any nonzero value). If the statement is false, the formula returns the logical value False (which is equivalent to 0). Table 3.2 summarizes VBA's comparison operators.
|>=||Greater than or equal to||"a">="b"||False|
|<=||Less than or equal to||"a"<="b"||True|
|<>||Not equal to||"a"<>"b"||True|
NOTE: THE IS OPERATOR VBA has an eighth comparison operator: Is. You use Is to compare two objects, so I'll leave it until we discuss objects in Chapter 4, "Working with Objects."
You'll normally use the comparison operators (except Like) with numeric values, and the implementation is quite straightforward in this context. (In other words, you don't have to worry about things like data types. VBA is quite happy to compare, say, an Integer value and a Single value.)
String comparisons are a bit more complex, however. Letters and symbols are stored internally as unique binary numbers, where the value of each letter is determined by the code page defined in Windows. The code page is an internal table that Windows uses to map keyboard keys to the characters you see on-screen. You can change the current Windows code page using the Regional Settings icon in Control Panel. The standard Windows code page is English (United States). I've provided the internal values for each symbol used in this code page in Appendix C, "The Windows ANSI Character Set."
In a standard string comparison, VBA compares letters based on their internal values. (This is called a binary comparison.) For example, if you examine the character set in Appendix C, you'll see that the character code for the letter a is 97 and the code for b is 98. Therefore, the expression "a"<"b" returns True.
Note, however, that Windows assigns a different code to uppercase letters. For example, the default code for A is 65, so the comparison "a"="A" will return False. If you would prefer that your string comparisons be case-insensitive, you can tell VBA to use a text comparison instead. To do that, add the following line at the module level (in other words, before any procedures or functions):
Option Compare Text
Another thing to keep in mind is that most string comparisons involve multiple-letter operands. In these situations, VBA compares each string letter-by-letter. For example, consider the expression "Smith"<"Smyth". The first two letters in each string are the same, but the third letters are different. The i in Smith is less than the y in Smyth, so this comparison would return True. (Notice that, once a point of difference is found, VBA ignores the rest of the letters in each string.)
Note, too, that a space is a legitimate character for comparison purposes. Its character code is 32, so it comes before all other letters and symbols (see Appendix C). In particular, if you compare two strings of different lengths, VBA will pad the shorter string with spaces so that it's the same length as the longer string. Therefore, the comparison "Marg">"Margaret" is equivalent to "Marg ">"Margaret", which returns False (because the fifth "letter" of "Marg " is a space, while the fifth letter of "Margaret" is a.
If you need to allow for multiple spellings in a comparison, or if you're not sure how to spell a word you want to use, the wildcard characters can help. There are three wildcards: the question mark (?) substitutes for a single character, the asterisk (*) substitutes for a group of characters, and the pound sign (#) substitutes for a single digit. You use them in combination with the Like operator, as shown in Table 3.3. In these examples, I'm assuming strVar is a String variable (the Like operator works only with strings).
|Example||Returns True if...|
|strVar Like "Re?d"||strVar is Reid, Read, Reed, and so on.|
|strVar Like "M?"||strVar is MA, MD, ME, and so on.|
|strVar Like "R*"||strVar begins with R.|
|strVar Like "*office*"||strVar contains the word office.|
|strVar Like "Fiscal_199#"||strVar is Fiscal_1997, Fiscal_1998, and so on.|
strVar Like "Re[ie]d"
You can also use character ranges, such as [a-m]. (Make sure you enter these ranges from lowest letter to highest letter. If you use, say, [m-a], VBA will generate an error.)
Finally, if you would like to compare strings based on their not having a list of characters in a particular position, include an exclamation mark inside the square brackets. For example, the following expression returns True if strVar isn't Read or Rend.
strVar Like "Re[!an]d"
You use the logical operators to combine or modify True/False expressions. Table 3.4 summarizes VBA's logical operators. I'll provide more detail about each operator later in this chapter (see "Working with Logical Expressions").
|Operator||General Form||What It Returns|
|And||Expr1 And Expr2||True if both Expr1 and Expr2 are True; False otherwise.|
|Eqv||Expr1 Eqv Expr2||True if both Expr1 and Expr2 are True or if both Expr1 and Expr2 are False; False otherwise.|
|Imp||Expr1 Imp Expr2||False if Expr1 is True and Expr2 is False; True otherwise.|
|Or||Expr1 Or Expr2||True if at least one of Expr1 and Expr2 is True; False otherwise.|
|Xor||Expr1 Xor Expr2||False if both Expr1 and Expr2 are True or if both Expr1 and Expr2 are False; True otherwise.|
|Not||Not Expr||True if Expr is False; False if Expr is True.|
You'll often use simple expressions that contain just two values and a single operator. In practice, however, many expressions you use will have a number of values and operators. In these more complex expressions, the order in which the calculations are performed becomes crucial. For example, consider the expression 3+5^2. If you calculate from left to right, the answer you get is 64 (3+5 equals 8, and 8^2 equals 64). However, if you perform the exponentiation first and then the addition, the result is 28 (5^2 equals 25, and 3+25, equals 28). As this example shows, a single expression can produce multiple answers, depending on the order in which you perform the calculations.
To control this problem, VBA evaluates an expression according to a predefined order of precedence. This order of precedence lets VBA calculate an expression unambiguously by determining which part of the expression it calculates first, which part second, and so on.
The order of precedence that VBA uses is determined by the various expression operators I outlined in the preceding section. Table 3.5 summarizes the complete order of precedence used by VBA.
|Operator||Operation||Order of Precedence|
|* and /||Multiplication and division||Third|
|+ and -||Addition and subtraction||Sixth|
|= < > <= >= <> Like Is||Comparison||Eighth|
|And Eqv Imp Or Xor Not||Logical||Ninth|
From this table, you can see that VBA performs exponentiation before addition. Therefore, the correct answer for the expression =3+5^2 (just discussed) is 28.
Also notice that some operators in Table 3.4 have the same order of precedence (for example, multiplication and division). This means that it doesn't matter in which order these operators are evaluated. For example, consider the expression =5*10/2. If you perform the multiplication first, the answer you get is 25 (5*10 equals 50, and 50/2 equals 25). If you perform the division first, you also get an answer of 25 (10/2 equals 5, and 5*5 equals 25). By convention, VBA evaluates operators with the same order of precedence from left to right.
Sometimes you want to override the order of precedence. For example, suppose you want to create an expression that calculates the pre-tax cost of an item. If you bought something for $10.65, including 7 percent sales tax, and you wanted to find the cost of the item less the tax, you would use the expression =10.65/1.07, which gives you the correct answer of $9.95. In general, the expression to use is given by the following formula:
Listing 3.1 shows a function that attempts to implement this formula.
Function PreTaxCost(totalCost As Currency, taxRate As Single) As Currency PreTaxCost = totalCost / 1 + taxRate End Function
NOTE: THIS CHAPTER'S CODE LISTINGS You'll find the code listings for this chapter on the CD that comes with this book. Look for the file Chaptr03.xls. Non-Excel users can use Chaptr03.bas instead.
Figure 3.1 shows an Excel worksheet that uses this function. The value in cell B4 is passed to the totalCost argument, and the value in cell B1 is passed to the taxRate argument.
FIGURE 3.1. A function that attempts to calculate the pre-tax cost of an item.
As you can see, the result is incorrect. What happened? Well, according to the rules of precedence, VBA performs division before addition, so the totalCost value first is divided by 1 and then is added to the taxRate value, which isn't the correct order.
To get the correct answer, you have to override the order of precedence so that the addition 1 + taxRate is performed first. You do this by surrounding that part of the expression with parentheses, as in Listing 3.2. Using this revised function, you get the correct answer, as shown in Figure 3.2.
Function PreTaxCost2(totalCost As Currency, taxRate As Single) As Currency PreTaxCost2 = totalCost / (1 + taxRate) End Function
FIGURE 3.2. The revised function calculates the pre-tax cost correctly.
In general, you can use parentheses to control the order that VBA uses to calculate expressions. Terms inside parentheses are always calculated first; terms outside parentheses are calculated sequentially (according to the order of precedence). To gain even more control over your expressions, you can place parentheses inside one another; this is called nesting parentheses, and VBA always evaluates the innermost set of parentheses first. Here are a few sample expressions:
|Expression||First Step||Second Step||Third Step||Result|
Notice that the order of precedence rules also hold within parentheses. For example, in the expression (5*2-5), the term 5*2 is calculated before 5 is subtracted.
Using parentheses to determine the order of calculations gives you full control over VBA expressions. This way, you can make sure that the answer given by a expression is the one you want.
CAUTION: MAKE SURE PARENTHESES MATCH One of the most common mistakes when using parentheses in expressions is to forget to close a parenthetic term with a right parenthesis. If you do this, VBA displays an Expected: ) message. To make sure you've closed each parenthetic term, count all the left parentheses and count all the right parentheses. If these totals don't match, you know you've left out a parenthesis.
Numeric expressions are what we normally think of when we use the generic term "expression." Whether it's calculating gross margin, figuring out commissions, or determining the monthly payment on a loan, many expressions perform some kind of number crunching. This section examines numeric expressions in more depth. I'll discuss the arithmetic operators and numeric data type conversion, and run through some of VBA's built-in math and financial functions.
Table 3.1 outlined VBA's eight arithmetic operators. This section takes a closer look at each operator and talks about the implications that the various numeric data types bring to the table in expressions that use these operators.
You use the addition operator to sum two operands. These operands must be either numeric literals, variables declared as one of the numeric data types, functions that return a numeric value, or any expression that evaluates to a number. (Having said all that, you can use Date values with addition and subtraction. See "Working with Date Expressions" later in this chapter.)
The data type of the result of a numeric expression depends on the data types of the operands. The general VBA rule is that the result takes on the highest precision of any operand in the expression. For example, if you add an Integer value and a Long value, the result will be a Long value.
NOTE: THE ORDER OF PRECISION FOR NUMERIC DATA TYPES Here is the order of precision (from lowest to highest) based on the definitions of VBA's numeric data types: Byte, Integer, Long, Single, Double, Currency. (See Table 2.1 in the preceding chapter to get the exact precision for each data type.)
However, there are a few exceptions to this rule:
In its basic guise, you use the - operator to subtract one number from another. The rules and requirements for subtraction are the same as those I outlined earlier for addition. In other words, the operands must use one of the numeric data types, and VBA preserves the highest precision of the operands (with the same exceptions).
However, don't confuse subtraction with negation. Both use the same operator, but you wield negation when you want to turn a number into its negative equivalent. For example, the expression -numVar takes the negative of whatever value is currently stored in the variable numVar. In this sense, negation is like multiplying a value by -1: if the value is positive, the result is negative; if the value is negative, the result is positive.
The asterisk (*) is the multiplication operator, and you use it to multiply two numbers together. Again, the operands you use must be numeric data types, and the resulting value takes on the data type of the highest-precision operand. The exceptions to this rule are the same as those outlined earlier for addition.
NOTE: THE ORDER OF PRECISION FOR MULTIPLICATION VBA uses a slightly different order of precision when performing multiplication: Byte, Integer, Long, Single, Currency, Double.
The forward slash (/) is the division operator, and you use it to divide one number (the dividend) by another (the divisor). The result (the quotient) is usually a Double value, with the following exceptions:
CAUTION: AVOID DIVIDING BY ZERO When using division in your procedures, you need to guard against using a divisor that has the value 0, because this will generate an error. In Chapter 5, "Controlling Your VBA Code," I'll show you how to use VBA's If...Then...Else statement to check for a 0 divisor before performing a division.
The backslash (\) is VBA's integer division operator. Unlike regular division (which is also known as floating-point division), integer division is concerned only with integer values. In other words, the divisor and dividend are rounded to Byte, Integer, or Long values (as appropriate), the division is performed using these rounded numbers, and only the integer portion of the quotient is returned as the result (which will be Byte, Integer, or Long).
For example, consider the expression 10 \ 2.4. VBA first rounds 2.4 down to 2 and then divides this value into 10 to give a result of 2. Alternatively, consider the expression 10 \ 2.6. In this case, VBA rounds 2.6 up to 3 and then divides the new value into 10. The decimal portion of the quotient is discarded, leaving you with a result of 3.
NOTE: INTEGER DIVISION ROUNDING Bear in mind that VBA rounds .5 values to the nearest even integer during integer division. This means that if your divisor is, say, 2.5, VBA will perform the integer division using 2, but if it's 3.5, VBA will use 4.
The caret (^) is VBA's exponentiation operator, which you use to raise one number to the power of a second number. Specifically, the expression x ^ y raises the number given by x to the power of the number given by y. Again, both operands must be legitimate VBA numeric values. However, you can raise a negative number to a power only if the power is an integer. For example, -5 ^ 3 is a legitimate expression, but -5 ^ 2.5 is not.
TIP: TAKING NTH ROOTS You can use the exponentiation operator to take the nth root of a number by using the following formula:
nth root = number ^ (1/n)
For example, the expression numVar ^ (1/3) takes the cube root of numVar.
The Mod operator works like Excel's MOD() worksheet function. In other words, it divides one number by another and returns the remainder. Here's the general form to use:
result = dividend Mod divisor
dividend The number being divided.
divisor The number being divided into dividend. result The remainder of the division. For example, 16 Mod 5 returns 1, because 5 goes into 16 three times with a remainder of 1.
What do you do if the data you want to use isn't stored in the correct data type? Or, what if you want to coerce the result of an expression into a particular data type? For these situations, VBA provides a half dozen data conversion functions. These functions take a string or numeric expression and convert it into a specific numeric data type. Table 3.6 outlines the available functions.
|Function||What It Returns|
|CByte(expression)||An expression converted to a Byte value.|
|CCur(expression)||An expression converted to a Currency value.|
|CDbl(expression)||An expression converted to a Double value.|
|CInt(expression)||An expression converted to an Integer value.|
|CLng(expression)||An expression converted to a Long value.|
|CSng(expression)||An expression converted to a Single value.|
NOTE: MORE CONVERSION FUNCTIONS Besides the preceding numeric data conversion functions, VBA also has a few other con-version functions that apply to different data types:
An expression converted to a Boolean value.
An expression converted to a Date value.
An expression converted to a Variant value.
An expression converted to a String value.
Here are some notes to bear in mind when using these functions:
The operands you use in your numeric expressions will usually be numeric literals or variables declared as one of VBA's numeric data types. However, VBA also boasts quite a few built- in math functions that your expressions can use as operands. These functions are outlined in Table 3.7.
|Function||What It Returns|
|Abs(number)||The absolute value of number.|
|Atn(number)||The arctangent of number.|
|Cos(number)||The cosine of number.|
|Exp(number)||e (the base of the natural logarithm) raised to the power of number.|
|Fix(number)||The integer portion of number. If number is negative, Fix returns the first negative integer greater than or equal to number.|
|Hex(number)||The hexadecimal value, as a Variant, of number.|
|Hex$(number)||The hexadecimal value, as a String, of number.|
|Int(number)||The integer portion of number. If number is negative, Int returns the first negative integer less than or equal to number.|
|Log(number)||The natural logarithm of number.|
|Oct(number)||The octal value, as a Variant, of number.|
|Oct$(number)||The octal value, as a String, of number.|
|Rnd(number)||A random number between 0 and 1, as a Single. You use the optional number as a "seed" value, as follows:|
|number||What It Generates|
|Less than 0||The same number every time (varies with number).|
|Equal to 0||The most recently generated number.|
|Greater than 0||The next random number in the sequence.|
|Sgn(number)||The sign of number. Returns 1 if number is greater than 0, -1 if number is less than 0, and 0 if number is 0.|
|Sin(number)||The sine of number.|
|Sqr(number)||The square root of number.|
|Tan(number)||The tangent of number.|
NOTE: GENERATING RANDOM NUMBERS The random numbers generated by Rnd are only pseudo-random. In other words, if you use the same seed value, you get the same sequence of numbers. If you need truly random numbers, run the Randomize statement just before using Rnd. This initializes the random number generator with the current system time.
TIP: GENERATING RANDOM NUMBERS IN A RANGE Instead of random numbers between 0 and 1, you might need to generate numbers within a larger range. Here's the general formula to use to get Rnd to generate a random number between a lower bound and an upper bound:
Int((upper - lower) * Rnd + lower)
For example, here's some code that generates a random eight-digit number (which would be suitable for use as part of a temporary filename; see the RandomFilename function in Chaptr03.xls):
filename = Int((99999999 - 10000000) * Rnd + 10000000)
VBA has quite a few financial functions that are new to version 5.0. These functions offer you powerful tools for building applications that manage both business and personal finances. You can use these functions to calculate such things as the monthly payment for a loan, the future value of an annuity, or the yearly depreciation of an asset.
Although VBA has a baker's dozen financial functions that use many different arguments, the following list covers the arguments you'll use most frequently:
|rate||The fixed rate of interest over the term of the loan or investment.|
|nper||The number of payments or deposit periods over the term of the loan or investment.|
|pmt||The periodic payment or deposit.|
|pv||The present value of the loan (the principal) or the initial deposit in an investment.|
|fv||The future value of the loan or investment.|
|type||The type of payment or deposit. Use 0 (the default) for end-of-period payments or deposits and 1 for beginning-of-period payments or deposits.|
For most financial functions, the following rules apply:
Table 3.8 lists all of VBA's financial functions.
|Function||What It Returns|
|DDB(cost,salvage,life,period,factor)||The depreciation of an asset over a specified period using the double-declining balance method.|
|FV(rate,nper,pmt,pv,type)||The future value of an investment or loan.|
|IPmt(rate,per,nper,pv,fv,type)||The interest payment for a specified period of a loan.|
|IRR(values,guess)||The internal rate of return for a series of cash flows.|
|MIRR(values,finance_rate,reinvest_rate)||The modified internal rate of return for a series of periodic cash flows.|
|NPer(rate,pmt,pv,fv,type)||The number of periods for an investment or loan.|
|NPV(rate,value1,value2...)||The net present value of an investment based on a series of cash flows and a discount rate.|
|Pmt(rate,nper,pv,fv,type)||The periodic payment for a loan or investment.|
|PPmt(rate,per,nper,pv,fv,type)||The principal payment for a specified period of a loan.|
|PV(rate,nper,pmt,fv,type)||The present value of an investment.|
|Rate(nper,pmt,pv,fv,type,guess)||The periodic interest rate for a loan or investment.|
|SLN(cost,salvage,life)||The straight-line depreciation of an asset over one period.|
|SYD(cost,salvage,life,period)||Sum-of-years' digits depreciation of an asset over a specified period.|
A string expression is an expression that returns a value that has a String data type. String expressions can use as operands string literals (one or more characters enclosed in double quotation marks), variables declared as String, or any of VBA's built-in functions that return a String value. Table 3.9 summarizes all the VBA functions that deal with strings.
|Function||What It Returns|
|Asc(string)||The ANSI character code of the first letter in string.|
|Chr(charcode)||The character, as a Variant, that corresponds to the ANSI code given by charcode.|
|Chr$(charcode)||The character, as a String, that corresponds to the ANSI code given by charcode.|
|CStr(expression)||Converts expression to a String value.|
|InStr(start,string1,string2)||The character position of the first occurrence of string2 in string1, starting at start.|
|InStrB(start,string1,string2)||The byte position of the first occurrence of string2 in string1, starting at start.|
|LCase(string)||string converted to lowercase, as a Variant.|
|LCase$(string)||string converted to lowercase, as a String.|
|Left(string,length)||The leftmost length characters from string, as a Variant.|
|Left$(string,length)||The leftmost length characters from string, as a String.|
|LeftB(string)||The leftmost length bytes from string, as a Variant.|
|LeftB$(string)||The leftmost length bytes from string, as a String.|
|Len(string)||The number of characters in string.|
|LenB(string)||The number of bytes in string.|
|LTrim(string)||A string, as a Variant, without the leading spaces in string.|
|LTrim$(string)||A string, as a String, without the leading spaces in string.|
|Mid(string,start,length)||length characters, as a Variant, from string beginning at start.|
|Mid$(string,start,length)||length characters, as a String, from string beginning at start.|
|MidB(string,start,length)||length bytes, as a Variant, from string beginning at start.|
|MidB$(string,start,length)||length bytes, as a String, from string beginning at start.|
|Right(string)||The rightmost length characters from string, as a Variant.|
|Right$(string)||The rightmost length characters from string, as a String.|
|RightB(string)||The rightmost length bytes from string, as a Variant.|
|RightB$(string)||The rightmost length bytes from string, as a String.|
|RTrim(string)||A string, as a Variant, without the trailing spaces in string.|
|RTrim$(string)||A string, as a String, without the trailing spaces in string.|
|Trim(string)||A string, as a Variant, without the leading and trailing spaces in string.|
|Trim$(string)||A string, as a String, without the leading and trailing spaces in string.|
|Space(number)||A string, as a Variant, with number spaces.|
|Space$(number)||A string, as a String, with number spaces.|
|Str(number)||The string representation, as a Variant, of number.|
|Str$(number)||The string representation, as a String, of number.|
|StrComp(string2,string2,compare)||A value indicating the result of comparing string1 and string2.|
|String(number,character)||character, as a Variant, repeated number times.|
|String$(number,character)||character, as a String, repeated number times.|
|UCase(string)||string converted to uppercase, as a Variant.|
|UCase$(string)||string converted to uppercase, as a String.|
|Val(string)||All the numbers contained in string, up to the first nonnumeric character.|
Function ExtractLastName(fullName As String) As String Dim spacePos As Integer spacePos = InStr(fullName, " ") ExtractLastName = Mid$(fullName, _ spacePos +1, _ Len(fullName) - spacePos) End Function Sub TestIt() MsgBox ExtractLastName("Millicent Peeved") End Function
The purpose of this procedure is to take a name (first and last, separated by a space, as shown in the TestIt procedure) and extract the last name. The full name is brought into the function as the fullName argument. After declaring an Integer variable named spacePos, the procedure uses the InStr function to check fullName and find out the position of the space that separates the first and last names. The result is stored in spacePos:
spacePos = InStr(fullName, " ")
The real meat of the function is provided by the Mid$ string function, which uses the following syntax to extract a substring from a larger string:
|string||The string from which you want to extract the characters. In the ExtractLastName function, this parameter is the fullName variable.|
|start||The starting point of the string you want to extract. In ExtractLastName, this parameter is the position of the space, plus 1 (in other words, spacePos + 1).|
|length||The length of the string you want to extract. In the ExtractLastName function, this is the length of the full string--Len(fullName)--minus the position of the space.|
A logical expression is an expression that returns a Boolean result. A Boolean value is almost always either True or False, but VBA also recognizes some Boolean equivalents:
In Chapter 5, I'll show you various VBA statements that let your procedures make decisions and loop through sections of code. In most cases, the mechanism that controls these statements will be a logical expression. For example, if x is a logical expression, you can tell VBA to run one set of statements if x returns True and a different set of statements if x returns False.
You'll see that these are powerful constructs, and they'll prove invaluable in all your VBA projects. To help you prepare, let's take a closer look at VBA's logical operators.
You use the And operator when you want to test two Boolean operands to see if they're both True. For example, consider the following generic expression (where Expr1 and Expr2 are Boolean values):
Expr1 And Expr2
You use the Or operator when you want to test two Boolean operands to see if one of them is True:
Expr1 Or Expr2
Xor is the exclusive Or operator. It's useful when you need to know if two operands have the opposite value:
Expr1 Xor Expr2
Eqv is the equivalence operator; it tells you whether or not two Boolean operands have the same value:
Expr1 Eqv Expr2
Imp is the VBA implication operator. What is being implied here? The relationship between two Boolean operands:
Expr1 Imp Expr2
The Not operator is the logical equivalent of the negation operator. In this case, Not returns the opposite value of an operand. For example, if Expr is True, Not Expr returns False.
A date expression is an expression that returns a Date value. For operands in date expressions, you can use either a variable declared as Date or a date literal. For the latter, you enclose the date in pound signs, like so:
dateVar = #8/23/97#
When working with dates, it helps to remember that VBA works with dates internally as serial numbers. Specifically, VBA uses December 31, 1899 as an arbitrary starting point and then represents subsequent dates as the number of days that have passed since then. So, for example, the date serial number for January 1, 1900 is 1, January 2, 1900 is 2 and so on. Table 3.10 displays some sample date serial numbers.
|366||December 31, 1900|
|16,229||June 6, 1944|
|35,430||December 31, 1996|
Similarly, VBA also uses serial numbers to represent times internally. In this case, though, VBA expresses time as a fraction of the 24-hour day to get a number between 0 and 1. The starting point, midnight, is given the value 0, noon is 0.5, and so on. Table 3.11 displays some sample time serial numbers.
You can combine the two types of serial numbers. For example, 35,430.5 represents 12 noon on December 31, 1996.
The advantage of using serial numbers in this way is that it makes calculations involving dates and times very easy. Since a date or time is really just a number, any mathematical operation you can perform on a number can also be performed on a date. This is invaluable for procedures that track delivery times, monitor accounts receivable or accounts payable aging, calculate invoice discount dates, and so on.
VBA also comes equipped with quite a few date and time functions. Table 3.12 summarizes them all.
|Function||What It Returns|
|CDate(expression)||Converts expression into a Date value.|
|Date||The current system date, as a Variant.|
|Date$()||The current system date, as a String.|
|DateSerial(year,month,day)||A Date value for the specified year, month, and day.|
|DateValue(date)||A Date value for the date string.|
|Day(date)||The day of the month given by date.|
|Hour(time)||The hour component of time.|
|Minute(time)||The minute component of time.|
|Month(date)||The month component of date.|
|Now||The current system date and time.|
|Second(time)||The second component of time.|
|Time||The current system time, as a Variant.|
|Time$||The current system time, as a String.|
|Timer||The number of seconds since midnight.|
|TimeSerial(hour,minute,second)||A Date value for the specified hour, minute, and second.|
|TimeValue(time)||A Date value for the time string.|
|Weekday(date)||The day of the week, as a number, given by date.|
|Year(date)||The year component of date.|
Listing 3.4 shows a couple of procedures that take advantage of a few of these date functions.
Function CalculateAge(birthDate As Date) As Byte Dim birthdayNotPassed As Boolean birthdayNotPassed = CDate(Month(birthDate) & "/" & _ Day(birthDate) & "/" & _ Year(Now)) > Now CalculateAge = Year(Now) - Year(birthDate) + birthdayNotPassed End Function ` Use this procedure to test CalculateAge. ` Sub TestIt2() MsgBox CalculateAge(#8/23/59#) End Sub
NOTE: THE CODE CONTINUATION CHARACTER Note the use of the underscore (_) in this listing. This is VBA's code continuation character--it's useful for breaking up long statements into multiple lines for easier reading. One caveat, though: Make sure you add a space before the underscore, or VBA will generate an error.
The purpose of the CalculateAge function is to figure out a person's age given the date of birth (as passed to CalculateAge through the Date variable named birthDate). You might think the following formula would do the job:
Year(Now) - Year(birthDate)
This works, but only if the person's birthday has already passed this year. If the person hasn't had his or her birthday yet, this formula reports the person's age as being one year greater than it really is.
To solve this problem, you need to take into account whether or not the person's birthday has occurred. To do this, CalculateAge first declares a Boolean variable birthdayNotPassed and then uses the following expression to test whether or not the person has celebrated his or her birthday this year:
CDate(Month(birthDate) & "/" & Day(birthDate) & "/" & Year(Now)) > Now
This expression uses the Month, Day, and Year functions to construct the date of the person's birthday this year and uses the CDate function to convert this string into a date. The expression then checks to see if this date is greater than today's date (as given by the Now function). If it is, the person hasn't celebrated his or her birthday, so birthdayNotPassed is set to True; otherwise, birthdayNotPassed is set to False.
The key is that to VBA a True value is equivalent to -1, and a False value is equivalent to 0. Therefore, to calculate the person's correct age, you need only add the value of birthdayNotPassed to the expression Year(Now) - Year(birthDate).
As you work through this book, I'll show you various methods for displaying data to the user. One of the best ways to improve the readability of your program output is to display the results of your expressions in a format that is logical, consistent, and straightforward. Formatting currency amounts with leading dollar signs, percentages with trailing percent signs, and large numbers with commas are a few of the ways you can improve your expression style.
This section shows you how to format numbers, dates, and times using VBA's built-in formatting options. You'll also learn how to create your own formats to gain maximum control over the appearance of your data.
The vehicle you'll be using to format your expressions is, appropriately enough, the Format function:
Format$( expression, format, firstdayofweek, firstweekofyear)
|expression||The expression you want to format.|
|format||Either the name of a predefined format (see the next section) or a user-defined format expression (see "User-Defined Numeric Formats" and "User-Defined Date and Time Formats").|
|firstdayofweek||A constant that identifies the first day of the week: vbSunday, vbMonday, and so on. You can also use vbUseSystem to set this value to the system default.|
|firstweekofyear||A constant that identifies the first week of the year. Use vbUseSystem to set this value to the system default, or use one of the following values:|
|vbFirstJan1||The year begins with the week that contains January 1.|
|vbFirstFourDays||The year begins with the first week that contains at least four days in the year.|
|vbFirstFullWeek||The year begins with the first week that contains seven days in the year.|
VBA comes with a small collection of predefined formats for numbers, dates, and times. If you've ever formatted data in an Excel worksheet, you should be familiar with most of these formats.
By default, VBA displays numbers in a plain style that includes no thousands separators or symbols. If you want your numbers to appear differently, you can choose from among VBA's nine built-in numeric formats, which are described in Table 3.13.
|General Number||The default numeric format.|
|Currency||Displays the number using the thousands separator, the dollar sign ($), and with two digits to the right of the decimal. Also, negative numbers are displayed surrounded by parentheses. Note, however, that the exact output of this format depends on the regional currency setting selected in Control Panel.|
|Fixed||Displays the number with at least one digit to the left of the decimal and at least two digits to the right of the decimal.|
|Standard||Displays the number with the thousands separator, at least one digit to the left of the decimal, and at least two digits to the right of the decimal.|
|Percent||Displays the number multiplied by 100, two digits to the right of the decimal, and a percent sign (%) to the right of the number. For example, .506 is displayed as 50.60%.|
|Scientific||Uses standard scientific notation: The most significant number is shown to the left of the decimal, and 2 to 30 decimal places are shown to the right of the decimal, followed by "E" and the exponent. For example, 123000 is displayed as 1.23E+05.|
|Yes/No||Displays No if the number is 0 and Yes for all other values.|
|True/False||Displays False if the number is 0 and True for all other values.|
|On/Off||Displays Off if the number is 0 and On for all other values.|
To use one of these predefined formats, enclose the name in quotation marks as part of the Format function:
profit = Format(sales - expenses, "currency")
The default date and time formatting used by VBA is based on the current settings selected in the Date and Time tabs of Control Panel's Regional Settings properties sheet. In general, though, if the date serial number doesn't include a fractional part, VBA displays only the date (usually in mm/dd/yy format). Similarly, if the serial number doesn't include an integer part, VBA displays only the time (usually in hh:mm AM/PM format). Table 3.14 shows a complete list of VBA's predefined date and time formats.
|General Date||The default date/time format.|
|Long Date||Displays the date according to the long date format defined for the system (for example, Saturday August 23, 1997).|
|Medium Date||Displays the date according to the medium date format defined for the system (for example, 23-Aug-97).|
|Short Date||Displays the date according to the short date format defined for the system (for example, 8/23/97).|
|Long Time||Displays the time according to the long time format defined for the system (for example, 10:30:45 PM).|
|Medium Time||Displays the time using only hours and minutes and with AM/PM (for example, 10:30 PM).|
|Short Time||Displays the time using only hours and minutes according to the 24-hour clock (for example, 22:30).|
Again, you use one of these formats by including it in quotation marks in the Format function:
MsgBox "The current time is " & Format(Now, "medium time")
VBA's predefined numeric formats give you some control over how your numbers are displayed, but they have their limitations. For example, unless you alter Windows' Regional Settings, no built-in format lets you display a different currency symbol (the British pound sign, £, for example) or display temperatures using, say, the degree symbol. To overcome these limitations, you need to create your own custom numeric formats. The formatting syntax and symbols are explained in detail later in this section.
Every VBA numeric format has the following syntax:
positive format ; negative format; zero format; null format
The four parts, separated by semicolons, determine how various numbers are presented. The first part defines how a positive number is displayed, the second part defines how a negative number is displayed, the third part defines how zero is displayed, and the fourth part defines how null values are displayed. If you leave out one or more of these parts, numbers are controlled as shown here:
|Number of Parts Used||Format Syntax|
|Three||positive format;negative format;zero format|
|Two||positive and zero format;negative format|
|One||positive, negative, and zero format|
Table 3.15 lists the special symbols you use to define each of these parts.
|#||Holds a place for a digit. Displays nothing if no number is entered.|
|0||Holds a place for a digit. Displays zero if no number is entered.|
|. (period)||Sets the location of the decimal point.|
|, (comma)||Sets the location of the thousands separator. Marks only the location of the first thousand.|
|%||Multiplies the number by 100 (for display only) and adds the percent (%) character.|
|E+ e+ E- e-||Displays the number in scientific format. E- and e- place a minus sign in the exponent; E+ and e+ place a plus sign in the exponent.|
|$ ( ) - + <space>||Displays the character.|
|\ (backslash)||Inserts the character that follows the backslash.|
|"text"||Inserts the text that appears within the quotation marks.|
Let's check out a few examples. Suppose your procedure is working with 10-digit phone numbers. To display these numbers with parentheses around the area code, use the (000) 000-0000 format, as shown in the following procedure fragment:
phoneNum = 2135556543 MsgBox "The phone number is " & Format(phoneNum, "(000) 000-0000")
Figure 3.3 shows the resulting dialog box.
FIGURE 3.3. The dialog box produced by the code fragment.
For nine-digit Social Security numbers, try the 000-00-0000 format:
ssn = 123456789 MsgBox "The social security number is " & Format(ssn, "000-00-0000")
If you're working with temperatures, you might want to display each value using the degree symbol, which is ANSI 176. Here's how you would do it:
temp = 98.6 MsgBox "The temperature is " & Format(temp, "#,##0.0" & Chr(176) & "F")
Figure 3.4 shows how VBA displays the temperature using this format.
FIGURE 3.4. You can include symbols (such as the degree symbol shown here) in your formats.
NOTE: THE CHR FUNCTION You can use VBA's Chr function to output any displayable ANSI character. The syntax is Chr(code), where code is the ANSI code of the character you want to display. Appendix C lists all the ANSI codes, but here are a few that you might find particularly useful in your numeric formats:
Character Code ANSI Character 163 £ 162 ¢ 165 ¥ 169 " 174 ® 176 °
Although the built-in date and time formats are fine for most applications, you might need to create your own custom formats. For instance, you might want to display the day of the week (for example, "Friday"). Custom date and time formats generally are simpler to create than custom numeric formats since there are fewer formatting symbols. Table 3.16 lists the date and time formatting symbols.
|c||Displays the date as ddddd and the time as ttttt.|
|d||Day number without a leading zero (1 to 31).|
|dd||Day number with a leading zero (01 to 31).|
|ddd||Three-letter day abbreviation (Mon, for example).|
|dddd||Full day name (Monday, for example).|
|ddddd||Displays the complete date using the system's short date format.|
|dddddd||Displays the complete date using the system's long date format.|
|m||Month number without a leading zero (1 to 12).|
|mm||Month number with a leading zero (01 to 12).|
|mmm||Three-letter month abbreviation (Aug, for example).|
|mmmm||Full month name (August, for example).|
|q||Quarter of the year (1 to 4).|
|w||Day of the week as a number (1 to 7 for Sunday through Saturday).|
|ww||Week of the year (1 to 54).|
|y||Day of the year (1 to 366).|
|yy||Two-digit year (00 to 99).|
|yyyy||Full year (1900 to 2078).|
|ttttt||Displays the complete time using the system's default time format.|
|h||Hour without a leading zero (0 to 24).|
|hh||Hour with a leading zero (00 to 24).|
|m||Minute without a leading zero (0 to 59).|
|mm||Minute with a leading zero (00 to 59).|
|n||Minute without a leading zero (0 to 59).|
|nn||Minute with a leading zero (00 to 59).|
|s||Second without a leading zero (0 to 59).|
|ss||Second with a leading zero (00 to 59).|
|AM/PM, am/pm||Displays the time using a 12-hour clock.|
|/ : . -||Symbols used to separate parts of dates or times.|
NOTE: MINUTE FORMATTING You might have noticed in Table 3.16 that VBA uses the same symbol (m) for both months and minutes. In general, VBA interprets "m" as a month symbol unless it immediately follows an "h" (the hour symbol). To avoid ambiguity in your formats, you might want to use "n" as the minutes symbol.
Here's a code fragment that uses some of the formatting symbols (see Figure 3.5 for the results):
thisMoment = Now MsgBox "It's now " & Format(thisMoment, "h:n AM/PM") & " on " _ Format(thisMoment, "dddd, mmmm d")
FIGURE 3.5. The results of the date and time formatting.
This chapter showed you how to build expressions in VBA. This is a crucial topic, because much of your VBA coding will involve creating expressions of one kind or another. With that in mind, I designed this chapter to give you a solid grounding in expression fundamentals. After first learning about basic expression structure, you were given a quick tour of the various VBA operators and the all-important topic of operator precedence. From there, you went through more detailed lessons on the four main expression types: numeric, string, logical, and date. For related information, see the following chapters:
© Copyright, Macmillan Computer Publishing. All rights reserved.