As you learned earlier in this book, Excel contains a wealth of functions designed to calculate financial values, mathematical and statistical results, date and time calculations, and much more. Word doesn't provide access to these tools, so most Word users simply switch to Excel or pull out their Hewlett-Packard calculators when complex formulas come up in their documents. Automation solves this problem, however; using a few program statements, you can use any Excel function in a macro.
Follow these steps to build a macro that computes the monthly payment for a loan using the Excel PMT function:
ON THE WEB
The ExcelPMT macro is located in the Chap62 document on the Running Office 2000 Reader's Corner page. For information about connecting to this Web site, read the Introduction.
Notice that there is no space between the words Excel and Pmt— spaces are not allowed in macro names.
The Office 9.0 object library is required for automating Office objects. Your References dialog box will look similar to this:
Dim xl As Object 'create object variable Dim loanpmt, msg$ If Selection.Type = wdSelectionNormal Then 'if text is selected Set xl = CreateObject("Excel.Sheet") 'create Excel object loanpmt = xl.Application.WorksheetFunction.Pmt(0.19 / 12, _ 36, Selection.text) 'call Excel Pmt function msg$ = "The monthly payment at 19% interest over 36 months is " MsgBox msg$& Format(Abs (loanpmt), "$#.##") 'show payment xl.Application.Quit 'quit Excel Set xl = Nothing 'release object variable Else MsgBox "No principal amount selected." End If
This macro creates an object variable named xl and uses the CreateObject function to assign the Excel.Sheet object to the variable if a number has been selected in the Word document. (The macro uses the selected number for the principal amount. If no text is selected, the Else statement displays the message No principal amount selected.) The macro then calls the Excel Pmt function using the WorksheetFunction object, which is a member of the Excel Application object.
This particular call to the Pmt function specifies a 19 percent annual interest rate (calculated monthly), a 36-month payment term, and a principal amount that the user has entered. In this case, Excel runs invisibly behind the scenes, but you can also make Excel appear and interact with the program, as you'll see in the next macro. After the Pmt function call, the macro displays the calculation results in a message box formatted for currency using the Format function. Normally, Excel displays a negative number for loan payments (because it's money that you pay out), but here we've used the Abs (absolute value) function to make the output a positive number.
Follow these steps to run the ExcelPmt macro:
Using Automation, Word starts Excel and computes the periodic payments that are required for the loan principal you selected. The result ($36.66) is displayed in a message box:
The previous Automation example worked well when we identified exactly which Excel objects and functions you should use and how you should go about typing them. But what if you want to experiment with a few different Excel objects using Automation? How would you determine which objects, properties, and methods to use? The solution is the Visual Basic Object Browser, which lets you explore each of the objects in the object library that you're connected to.
To use the Object Browser, follow these steps:
Detailed Object Help Is Available
Although the Office 9 Object Library has many of the general commands and features you'll be using in macros, you can also find more detailed commands in the object libraries devoted to each Office application. For example, to learn more about the Excel Rate function, select the Microsoft Excel 9.0 Object Library in the References dialog box, and then search for Rate.
The Object Browser opens in the Visual Basic Editor, as shown in Figure 41-1. The Object Browser contains a Project/Library drop-down list box, which you can use to display the object libraries included in your project. It also contains a Search list for creating keyword searches, and a Classes list, which you can use to select a particular object to examine. When you select an object in the Classes list, the methods, properties, and events featured in the object are listed in the Members list.
Figure 41-1. The Object Browser helps you learn more about the programmable objects in Office applications.
A list of the Automation objects exposed by the library fills the Classes list. By selecting only one library, you can narrow your search to only the objects in that library.
Using this technique, you can learn much more about the objects provided by each Office application.