Using Excel Objects

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.

Creating the ExcelPmt Macro

Follow these steps to build a macro that computes the monthly payment for a loan using the Excel PMT function:

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.

  1. In Word, choose Macros from the Macro submenu of the Tools menu.
  2. Type ExcelPmt, and then click Create.

    Notice that there is no space between the words Excel and Pmt— spaces are not allowed in macro names.

  3. From the Visual Basic Tools menu, choose References.
  4. Select the Microsoft Office 9.0 Object Library reference.
  5. The Office 9.0 object library is required for automating Office objects. Your References dialog box will look similar to this:

    click to view at full size.

  6. Click OK to add the reference to your project. Visual Basic adds the Office object library to your project.
  7. Type the following code in the ExcelPmt macro using the Code window:
  8.   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 

  9. Click the Save button and specify a new document name to save the macro to disk.

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.

Running the ExcelPmt Macro

Follow these steps to run the ExcelPmt macro:

  1. Click the View Microsoft Word button on the Visual Basic Editor toolbar.
  2. In the Word document, type $1000, and then select the amount, which represents the loan principal. (You can include the dollar sign or spaces in your selection, but don't select the paragraph marker— it will cause the macro to fail.)
  3. Press Alt+F8 to open the Macros dialog box, and then double-click 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:

  4. Click OK, and then move the insertion point elsewhere in your document (make no selection) so that you can test the Else statement in the macro.
  5. Run ExcelPmt again. The message No principal amount selected is displayed on the screen.

Using the Object Browser to Learn About Objects

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:

  1. Choose References from the Tools menu to see the list of active object libraries in your macro.
  2. If you'd like to view the contents of additional object libraries, click them now, and then click OK.
  3. TIP
    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.

  4. From the Visual Basic View menu, choose Object Browser.

    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.

    click to view at full size.

    Figure 41-1. The Object Browser helps you learn more about the programmable objects in Office applications.

  5. Open the Project/Library list, and then click the object library you want to examine.

    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.

  6. Click the Search list, type the keyword you want to search for, and then press Enter.
  7. If items are found in the search, you can click them now in the Search list box to get a more detailed description.
  8. (optional) Click Help in the Object Browser to display the Help file associated with the function or keyword.
  9. Review the article, and then close the Help file.
  10. Click the Close button in the Object Browser to close the search tool.

Using this technique, you can learn much more about the objects provided by each Office application.

Running Microsoft Office 2000 Small Business
Running Microsoft Office 2000
ISBN: 1572319585
EAN: 2147483647
Year: 2005
Pages: 228 © 2008-2017.
If you may any questions please contact us: