Microsoft Office

Using Visual Basic for Applications

In the following exercises, you will use Visual Basic for Applications with Word, and with Excel.

* To use VBA with Microsoft Word

1. Start Microsoft Word and open the VBA.doc file in WA\Practice\Ch04 directory.
2. If prompted, click Enable Macros .
3. From the View menu, point to Toolbars and then click Visual Basic .
4. From the toolbar, click Design Mode .
5. Place the insertion point at the bottom of the document.
6. From the Control toolbox, click the Command Button icon.
7. Right-click the Command button and click Properties .
8. Change the Caption property of the Command button to Calculate .
9. Close the Properties dialog.
10. Double-click the Command button to open the VBA code window.
11. In the click event of CommandButton1 , type the following code to calculate a monthly payment based on loan information.

     
     On Error GoTo ErrorHandler 
     
     
     
     
     If APR > 1 Then APR = APR / 100 ' Ensure proper form. 
     
     If chkEOM = True Then 
     
     PayType = 0 
     
     Else 
     
     PayType = 1 
     
     End If 
     
     
     
     
     lblMOpayment = Format(Pmt(APR / 12, TotPmts, -PVal, 0, PayType), "currency") 
     
     Exit Sub 
     
     
     
     
     ErrorHandler: 
     
     lblMOpayment = 0 
     
     

12. Close the Visual Basic Editor.
13. Switch to the Word Document.
14. From the toolbar, click Exit Design Mode .

    At this point, you can enter in values for each field.

15. Enter in values for each field and click Calculate .
16. Save changes to the document and exit Word.

* To use VBA with Microsoft Excel

1. Start Microsoft Excel.
2. From the View menu, select Toolbars , then click Visual Basic .
3. From the Visual Basic toolbar, click Design Mode .
4. From the Visual Basic toolbar, click Control Toolbox .
5. Add a command button control to the Excel spreadsheet.
6. Double-click on CommandButton1 to open the VBA editor.
7. In the Click event of the command button, type the following code:

    Cells.Range("A1") = "Hello World!" 

8. Click Exit Design Mode on the Visual Basic toolbar.
9. Click CommandButton1 in Microsoft Excel.

   "Hello World!" will be displayed in the first cell of the spreadsheet.

Lesson Summary

Visual Studio 97 is a comprehensive set of visual development tools for creating various types of applications and components . Many of the tools perform similar functions. Select the appropriate tool to use based upon your desired deliverables. The tools in the suite include:

• Visual Basic
• Visual C++
• Visual InterDev
• Visual J++
• Visual FoxPro

Microsoft Office is a suite of applications for developing business solutions. The applications include:

• Word
• Excel
• Powerpoint
• Outlook
• Access

Visual Basic for Applications (VBA) is a new version of Visual Basic included in Office that allows you to create customized Office solutions.