Segments two and three of this series introduced VBA for Word and Excel. Adding power with custom programming to host applications like Word or Excel is a primary benefit of VBA, but the next level up is using VBA with Word and Excel together. The techniques demonstrated here will only use these two host applications, but keep in mind that you can use any combination of host functions that you need. Do you need to insert custom numbers into a Visio flowchart from Excel along with text from Word? Not a problem!

  Let's see how this works ...

As mentioned in the Introduction, this article is based on Word and Excel 2000 and VBA 6 because that's what most of our readers are using.

In this segment, we're going to use features from both Word and Excel to create a custom report.

The first thing we'll have to do is to decide which application will serve as the "host environment." In segment one, I pointed out that Visual Basic 6 can be thought of as simply another hosting environment for VBA - one that lets you build a stand-alone system that uses features from VBA as well as other sources. Whenever you need to use features from several host environments in the same program, VB 6 should be a top choice as a development environment. The techniques in this article work in about the same way.

On the other hand, VB 6 might not be available to you. Or your application might fit "naturally" into one of the other host applications. In this article, we're going to use Word as our host and then incorporate features from Excel.

The sample application developed in this segment is a "customizable letter" that lets someone choose just the right phrase from lists of pre-composed phrases in Word and also includes the most current values of key numbers from an Excel spreadsheet. To start out, I composed a document in Word with the "boilerplate" text that doesn't change and added bookmarks where pre-composed phrases will be inserted. If you're following along at home, you might have to change your Word display options before you can see the booknmarks. Go to Tools > Options and then click Bookmarks under the Show section of the View tab.

I also created an Excel workbook to provide numbers for the Word document. Names were assigned to the cells containing the numbers. The workbook is pretty simple since the goal is only to demonstrate Word and Excel working together. It provides two numbers: a calculation of days and an amount. The date calculation is a good example of the type of thing that Excel can do very well but Word has much less capability.