Building Formulas
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
| Operator | Mathematical Operation | Example |
|---|---|---|
| + | 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" |
Computing Formulas in Your Documents
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.
- In Word, choose Macros from the Macro submenu of the Tools menu.
- Specify the document in which you want to store the macro (Chap59.doc or other) using the Macros In list box.
- Type SalesTax in the Name text box, and then click the Create button.
Word starts the Visual Basic Editor and opens a new macro procedure named SalesTax in the Code window.
- Type the following program statements:
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.
TIP
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:
- Click the View Microsoft Word button on the Visual Basic Editor toolbar.
Word displays the active document.
- The SalesTax macro requires that you select a number in your document, so clear some room and type the following test values (one per line) so that you can evaluate the macro:
10 $1,000.00 five bucks
- Select 10 as the first test number.
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.
- Click OK to close the dialog box, and then select $1,000.00 and run the macro again. (You should verify that the macro can handle currency formatting.)
Fortunately, you are using Variant variables in you macro, which can handle the switch between different types of numbers.
- Click OK, and then select five bucks and run the macro.
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.
- Click the Debug button.
Visual Basic highlights the program statement that caused the run-time error in the Code window.
- Click the Reset button on the Visual Basic toolbar to stop the program.
- Click the Return To Word button on the Visual Basic toolbar to return to Word, and then click the Save button on Word's Standard toolbar to save the SalesTax macro to disk.
You're done working with macros in this chapter.
EAN: 2147483647
Pages: 228