A formula is a statement that combines numbers, variables, operators, and keywords— or some of these elements— to create a new value. Visual Basic contains several language elements designed for use in formulas. In this section, you'll practice working with mathematical operators, the symbols used to tie together the parts of a formula. With a few exceptions, the mathematical symbols you'll use are the ones you use in everyday life, and their operations are fairly intuitive.
Visual Basic provides the mathematical operators shown in Table 38-1.
Table 38-1. Visual Basic Mathematical Operators
|+||Addition||Sum = 15.95 + 22.50|
|-||Subtraction||Balance = 100 - 75|
|*||Multiplication||Product = 88 * 2|
|/||Division||Ratio = 6 / 5|
|\||Integer (whole number) division||FullDinners = 8 \ 3|
|Mod||Remainder division||Scraps = 8 Mod 3|
|^||Exponentiation (raising to a power)||AreaOfSquare = 5 ^ 2|
|&||String concatenation (joining words together)||FullName = "Bob " & "James"|
Periodically, you might have to total numbers in an Office document or perform some sort of numeric calculation. The following exercise demonstrates how you can compute the sales tax for a number that is selected in the active document.
Word includes a formula feature that lets you total numbers in a table and perform other simple calculations. However, it doesn't contain a command that lets you make numeric computations on the fly using a selected number. Complete these steps to build a macro that computes the total cost of an item including sales tax.
ON THE WEB
The SalesTax macro is located in the Chap59 document on the Running Office 2000 Reader's Corner page.
Word starts the Visual Basic Editor and opens a new macro procedure named SalesTax in the Code window.
Dim CostOfItem, TotalCost, TaxRate TaxRate = 1.091 CostOfItem = Selection.Text TotalCost = CostOfItem * TaxRate MsgBox Format(TotalCost, "$#,##0.00"), , "Total Cost with Tax"
Copy Macros to Word's Normal Template
This chapter contains five Word macros, each located in the NewMacros module in the Chap59 document. If you'd like to copy one or more of these macros to the Normal.dot template (so that you can use them in Word without the Chap59.doc file open), complete these steps:
- Open the Macros dialog box, and click the Organizer button.
- Verify that the Chap59 document is open in the left text box, and then select the NewMacros module in it.
- Click the Rename button, and change the name of the NewMacros module to Chap59. (You can't copy one NewMacros module over another.)
- Verify that the Normal template is open in the right text box, and then click the Copy button to copy the Chap59 module into the Normal template.
When you're finished copying macros, click the Close button.
The Dim statement declares three variables of the Variant type: CostOfItem, TotalCost, and TaxRate. Variant is a good choice in this case because the exact format of two numbers in your Word document will be unknown. (They could be large or small, integers or floating-point values, and so on.) The third variable holds the current sales tax rate (in this example, a chilly 9.1 percent). Change this number to reflect your local sales tax rate, if any.
The third statement in the macro uses the Text property of the Selection object to return the currently selected text to the CostOfItem variable. The fourth statement then uses a formula and the multiplication operator to compute the total cost of the item plus sales tax. Finally, the MsgBox function displays the total with the help of the Format function, so the total appears with the proper currency formatting.
The Format function can display the results of a calculation in a variety of formats, including percent, integer, date, string, and other custom formats. For more information, search for "Format function" in the Visual Basic online Help.
Now run the SalesTax macro in your Word document. Follow these steps:
Word displays the active document.
10 $1,000.00 five bucks
Run the SalesTax macro by using the Macros dialog box.
Word immediately displays a message box containing the total cost of a $10 item with a 9.1 percent sales tax.
Fortunately, you are using Variant variables in you macro, which can handle the switch between different types of numbers.
This time, Visual Basic generates a run-time error that stops the macro and displays a dialog box explaining the problem. The words type mismatch mean that the value selected in the document (five bucks) cannot be multiplied by the value in the TaxRate constant (1.091). Unfortunately, this macro will work only with numbers, not text.
Visual Basic highlights the program statement that caused the run-time error in the Code window.
You're done working with macros in this chapter.