Code written with early binding executes faster than code with late binding. A reference is made to Word’s object library before the code is written so that Word’s objects, properties, and methods are available in the Object Browser. Tips also appear, as shown in Figure 18.1, such as a list of members of an object.

Figure 18.1. Early binding allows easy access to the Word object’s syntax.

The disadvantage of early binding is that the referenced object library must exist on the system. For example, if you write a macro referencing Word 2007’s object library and someone with Word 2003 attempts to run the code, the program fails because the program cannot find the 2007 object library.

The object library is added through the VB Editor:

After the reference is set, Word variables can be declared with the correct (Word) variable type. However, if the object variable is declared As Object, this forces the program to use late binding:

Sub WordEarlyBinding()
Dim wdApp As Word.Application
Dim wdDoc As Document
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open(ThisWorkbook.Path & _
    "Chapter 18 - Automating Word.docx")
wdApp.Visible = True
Set wdApp = Nothing
Set wdDoc = Nothing
End Sub

This example creates a new instance of Word and opens an existing Word document from Excel. The declared variables, wdApp and wdDoc, are of Word object types. wdApp is used to create a reference to the Word application in the same way the Application object is used in Excel. New Word.Application is used to create a new instance of Word.

When finished, it’s a good idea to set the object variables to Nothing and release the memory being used by the application, as shown here:

Set wdApp = Nothing
Set wdDoc = Nothing

Compile Error: Can’t Find Object or Library

If the referenced version of Word does not exist on the system, an error message appears, as shown in Figure 18.3. View the References list; the missing object is highlighted with the word MISSING (see Figure 18.4).

Figure 18.3. Attempting to compile a program with a missing reference library will generate an error message.

Figure 18.4. Excel will list the missing library for you.

If a previous version of Word is available, you can try running the program with that version referenced. Many objects are the same between versions.

When using late binding, you are creating an object that refers to the Word application before linking to the Word library. Because you do not set up a reference beforehand, the only constraint on the Word version is that the objects, properties, and methods must exist. In the case where there are differences, the version can be verified and the correct object used accordingly.

The disadvantage of late binding is that Excel doesn’t know what is going on—it doesn’t understand that you are referring to Word. This prevents the tips from appearing when referencing Word objects. Also, built-in constants are not available and, when compiling, Excel cannot verify that the references to Word are correct. After the program is executed, the links to Word begin to build, and any coding errors are detected at that point.

The following example creates a new instance of Word and then opens and makes visible an existing Word document:

Sub WordLateBinding()
Dim wdApp As Object, wdDoc As Object
Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Open(ThisWorkbook.Path & _
"Chapter 18 - Automating Word.docx")
wdApp.Visible = True
Set wdApp = Nothing
Set wdDoc = Nothing
End Sub

An object variable (wdApp) is declared and set to reference the application (CreateObject("Word.Application")). Other required variables are then declared (wdDoc), and the application object is used to refer these variables to Word’s object model.

The following sections describe how to create new objects as well as how to reference currently open objects.

Word.Application.Visible = True

Sub UseGetObject()
Dim wdDoc As Object
Set wdDoc = GetObject(ThisWorkbook.Path & "Chapter 18 - Automating Word.docx")
wdDoc.Application.Visible = True
Set wdDoc = Nothing
End Sub

Sub IsWordOpen()
Dim wdApp As Word.Application

ActiveChart.ChartArea.Copy

On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If wdApp Is Nothing Then
    Set wdApp = GetObject("", "Word.Application")
    With wdApp
        .Documents.Add
        .Visible = True
    End With
End If
On Error GoTo 0

With wdApp.Selection
    .EndKey Unit:=wdStory
    .TypeParagraph
    .PasteSpecial Link:=False, DataType:=wdPasteOLEObject, _
        Placement:=wdInLine, DisplayAsIcon:=False
End With

Set wdApp = Nothing
End Sub

In the previous example, we used constants that are specific to Word, such as wdPasteOLEObject and wdInLine. When you’re programming using early binding, Excel helps you out by showing these constants in the tip window.

With late binding, these tips won’t appear. So what can you do? You might write your program using early binding, then, after you’ve compiled and tested it, change it to late binding. The problem with this method is that it won’t compile because Excel doesn’t recognize the Word constants.

The words wdPasteOLEObject and wdInLine are for your convenience as a programmer. Behind each of these text constants is the real value that VBA understands. The solution to this is to retrieve and use these real values with your late binding program.

Using the Watch Window to Retrieve the Real Value of a Constant

One way to retrieve the value is to add a watch for the constants. Then, step through your code and check the value of the constant as it appears in the Watch window, as shown in Figure 18.5.

Figure 18.5. Use the Watch window to get the real value behind a Word constant.

Using the Object Browser to Retrieve the Real Value of a Constant

A second method is to look up the constant in the Object Browser. You’ll need the Word library set up as a reference. Right-click in the constant, select Definition, and the Object Browser opens to the constant, showing you the value in the bottom window, as shown in Figure 18.6.

Figure 18.6. User the Object Browser to get the real value behind a Word constant.

Note

You can set up the Word reference library to access it from the Object Browser, but you don’t have to set up your code with early binding. In this way, you can have the reference at your fingertips, but your code is still late binding. Turning off the reference library is just a few clicks away.

Replacing the constants in the earlier code example with their real values would look like this:

With wdApp.Selection
    .EndKey Unit:=6
    .TypeParagraph
    .PasteSpecial Link:=False, DataType:=0, _
        Placement:=0, DisplayAsIcon:=False
End With

But what happens a month from now when you return to the code and you’re trying to remember what those numbers mean? The solution is up to you. Some programmers just add comments to the code referencing the Word constant. Other programmers create their own variables to hold the real value and use those variables in place of the constants, like this:

Const xwdStory As Long = 6
Const xwdPasteOLEObject As Long = 0
Const xwdInLine As Long = 0

With wdApp.Selection
    .EndKey Unit:=xwdStory
    .TypeParagraph
    .PasteSpecial Link:=False, DataType:=xwdPasteOLEObject, _
        Placement:=xwdInLine, DisplayAsIcon:=False
End With

Word’s macro recorder can be used to get a preliminary understanding of the Word object model. However, much like Excel’s macro recorder, the results will be long-winded. Keep this in mind and use the recorder to lead you toward the objects, properties, and methods in Word.

The following example is what the Word macro recorder produces when adding a new, blank document.

Documents.Add Template:="Normal", NewTemplate:=False, DocumentType:=0

Making this more efficient (still in Word) produces this:

Documents.Add

Template, NewTemplate, and DocumentType are all optional properties that the recorder includes but are not required unless you need to change a default property or ensure that a property is as you require.

To use the same line of code in Excel, a link to the Word object library is required, as you learned earlier. After that link is established, an understanding of Word’s objects is all you need. Following is a review of some of Word’s objects—enough to get you off the ground. For a more detailed listing, refer to the object model in Word’s VB Editor.

Sub NewDocument()
Dim wdApp As Word.Application

Set wdApp = GetObject(, "Word.Application")

wdApp.Documents.Add

Set wdApp = Nothing
End Sub

wdApp.Documents.Add Template:="Contemporary Memo.dot"

wdApp.Documents.Open _
    Filename:="C:Excel VBA 2007 by Jelen & SyrstadChapter 19 - Arrays.docx", _
    ReadOnly:=True, AddtoRecentFiles:=False

wdApp.Documents.Save

wdApp.ActiveDocument.SaveAs "C:Excel VBA 2007 by Jelen & SyrstadMemoTest.docx"

wdApp.Documents.Close SaveChanges:=wdDoNotSaveChanges

wdApp.ActiveDocument.Close

wdApp.Documents("Chapter 19 - Arrays.docx").Close

wdApp.ActiveDocument.PrintOut

wdApp.ActiveDocument.PrintOut Range:=wdPrintRangeOfPages, Pages:="2"

wdApp.Selection.HomeKey Unit:=wdStory, Extend:=wdMove

wdApp.Selection.EndKey Unit:=wdStory, Extend:=wdExtend

Sub InsertText()
Dim wdApp As Word.Application
Dim wdDoc As Document
Dim wdSln As Selection

Set wdApp = GetObject(, "Word.Application")
Set wdDoc = wdApp.ActiveDocument
Set wdSln = wdApp.Selection

wdDoc.Application.Options.Overtype = False
With wdSln
    If .Type = wdSelectionIP Then
        .TypeText ("Inserting at insertion point. ")
    ElseIf .Type = wdSelectionNormal Then
            If wdApp.Options.ReplaceSelection Then
                .Collapse Direction:=wdCollapseStart
            End If
           .TypeText ("Inserting before a text block. ")
    End If
End With
Set wdApp = Nothing
Set wdDoc = Nothing
End Sub

Range Object

The Range object uses the following syntax:

Range(StartPosition, EndPosition)

The Range object represents a contiguous area, or areas, in the document. It has a starting character position and an ending character position. The object can be the insertion point, a range of text, or the entire document, including nonprinting characters (such as spaces or paragraph marks).

The Range object is similar to the Selection object but, in some ways, is better: It requires less code to accomplish the same tasks; it has more capabilities; and it saves time and memory because the Range object doesn’t require Word to move the cursor or highlight objects in the document to manipulate them.

Define a Range

To define a range, enter a starting and ending position, as shown in this code segment:

Sub RangeText()
Dim wdApp As Word.Application
Dim wdDoc As Document
Dim wdRng As Word.Range

Set wdApp = GetObject(, "Word.Application")
Set wdDoc = wdApp.ActiveDocument

Set wdRng = wdDoc.Range(0, 22)
wdRng.Select

Set wdApp = Nothing
Set wdDoc = Nothing
Set wdRng = Nothing
End Sub

Figure 18.7 shows the results of running this code. The first 22 characters, including nonprinting characters such as paragraph returns, are selected.

Figure 18.7. The Range object selects everything in its path.

Note

The range was selected (wdRng.Select) for easier viewing. It is not required that the range be selected to be manipulated. For example, to delete the range, do this:

wdRng.Delete

The first character position in a document is always zero, and the last is equivalent to the number of characters in the document.

The Range object also selects paragraphs. The following example copies the third paragraph in the active document and pastes it in Excel. Depending on how the paste is done, the text can be pasted into a text box (see Figure 18.8) or into a cell (see Figure 18.9):

Sub SelectSentence()
Dim wdApp As Word.Application
Dim wdRng As Word.Range

Set wdApp = GetObject(, "Word.Application")

With wdApp.ActiveDocument
    If .Paragraphs.Count >= 3 Then
        Set wdRng = .Paragraphs(3).Range
        wdRng.Copy
    End If
End With

'This line pastes the copied text into a text box
'because that's the default PasteSpecial method for Word text
Worksheets("Sheet2").PasteSpecial

'This lines paste the copied text in cell A1
Worksheets("Sheet2").Paste Destination:=Worksheets("Sheet2").Range("A1")

Set wdApp = Nothing
Set wdRng = Nothing
End Sub

Figure 18.8. Paste Word text into an Excel text box.

Figure 18.9. Paste Word text into an Excel cell.

Format a Range

After a range is selected, formatting can be applied to it (see Figure 18.10). The following program loops through all the paragraphs of the active document and bolds the first word of each paragraph:

Sub ChangeFormat()
Dim wdApp As Word.Application
Dim wdRng As Word.Range
Dim count As Integer

Set wdApp = GetObject(, "Word.Application")

With wdApp.ActiveDocument
    For count = 1 To .Paragraphs.count
        Set wdRng = .Paragraphs(count).Range
        With wdRng
            .Words(1).Font.Bold = True
        End With
    Next count
End With

Set wdApp = Nothing
Set wdRng = Nothing
End Sub

Figure 18.10. Format the first word of each paragraph in a document.

A quick way of changing the formatting of entire paragraphs is to change the style (see Figures 18.11 and 18.12). The following program finds the paragraph with the NO style and changes it to HA:

Sub ChangeStyle()
Dim wdApp As Word.Application
Dim wdRng As Word.Range
Dim count As Integer

Set wdApp = GetObject(, "Word.Application")

With wdApp.ActiveDocument
    For count = 1 To .Paragraphs.count
        Set wdRng = .Paragraphs(count).Range
        With wdRng
            If .Style = "NO" Then
                .Style = "HA"
            End If
        End With
    Next count
End With

Set wdApp = Nothing
Set wdRng = Nothing
End Sub

Figure 18.11. Before: A paragraph with the NO style needs to be changed to the HA style.

Figure 18.12. After: Apply styles with code to quickly change paragraph formatting.

Bookmarks

Bookmarks are members of the Document, Selection, and Range objects. They can help make it easier to navigate around Word. Instead of having to choose words, sentences, or paragraphs, use bookmarks to swiftly manipulate sections of a document.

Note

Bookmarks do not have to be existing; they can be created via code.

Bookmarks appear as gray I-bars in Word documents. In Word, click the Microsoft Office Button, go to Word Options, Advanced, Show Document Contents to turn on bookmarks (see Figure 18.13).

Figure 18.13. Turn on bookmarks to find them in a document.

After you’ve set up bookmarks in a document, you can use the bookmarks to quickly move to a range. The following code automatically inserts text after four bookmarks that were previously set up in the document. Figure 18.14 shows the results.

Sub UseBookmarks()
Dim myArray()
Dim wdBkmk As String

Dim wdApp As Word.Application
Dim wdRng As Word.Range
myArray = Array("To", "CC", "From", "Subject")
Set wdApp = GetObject(, "Word.Application")

Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(0)).Range
wdRng.InsertBefore ("Bill Jelen")
Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(1)).Range
wdRng.InsertBefore ("Tracy Syrstad")
Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(2)).Range
wdRng.InsertBefore ("MrExcel")
Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(3)).Range
wdRng.InsertBefore ("Fruit Sales")

Set wdApp = Nothing
Set wdRng = Nothing
End Sub

Figure 18.14. Use bookmarks to quickly enter text into a Word document.

Bookmarks can also be used as markers for bringing in charts created in Excel. The following code links an Excel chart (see Figure 18.15) to the memo:

Sub CreateMemo()
Dim myArray()
Dim wdBkmk As String

Dim wdApp As Word.Application
Dim wdRng As Word.Range
myArray = Array("To", "CC", "From", "Subject", "Chart")
Set wdApp = GetObject(, "Word.Application")

Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(0)).Range
wdRng.InsertBefore ("Bill Jelen")
Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(1)).Range
wdRng.InsertBefore ("Tracy Syrstad")
Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(2)).Range
wdRng.InsertBefore ("MrExcel")
Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(3)).Range
wdRng.InsertBefore ("Fruit & Vegetable Sales")

Set wdRng = wdApp.ActiveDocument.Bookmarks(myArray(4)).Range
ActiveSheet.ChartObjects("Chart 1").Copy
wdRng.PasteAndFormat Type:=wdPasteOLEObject

wdApp.Activate

Set wdApp = Nothing
Set wdRng = Nothing
End Sub

Figure 18.15. Use bookmarks to bring charts into Word documents.

You’ve seen how to modify a document by inserting charts and text, modifying formatting, and deleting text. However, a document may contain other items, such as controls, and you can modify those, too.

For the following example, I created a template consisting of text, bookmarks, and Form Field check boxes. (See the note following this paragraph for information on where the Form Fields are hiding in Word.) The bookmarks are placed after the Name and Date fields. The check boxes have all been renamed (right-click the check box, select Properties, and type a new name in the Bookmark field) to make more sense to me, such as chk401k rather than Checkbox5. Save the template.

Figure 18.16. You can use the Form Fields found under the Legacy Tools to add check boxes to a document.

I set up the questionnaire in Excel, allowing the user to enter free text in B1 and B2, but setting up data validation in B3 and B5:B8, as shown in Figure 18.17.

Figure 18.17. Create an Excel sheet to collect your data.

The code goes into a standard module. The name and date go straight into the document. The check boxes use logic to verify whether the user selected Yes or No to confirm whether the corresponding check box should be checked. Figure 18.18 shows a sample completed document.

Figure 18.18. Excel can control Word’s form fields.

Sub FillOutWordForm()

Dim TemplatePath As String
Dim wdApp As Object
Dim wdDoc As Object

'Open the template in a new instance of Word
TemplatePath = ThisWorkbook.Path & "Word Example.dotx"
Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.documents.Add(Template:=TemplatePath)
'Place our text values in document
With wdApp.ActiveDocument
    .Bookmarks("Name").Range.InsertBefore Range("B1").Text
    .Bookmarks("Date").Range.InsertBefore Range("B2").Text
End With

'Using basic logic, select the correct form object
If Range("B3").Value = "Yes" Then
    wdDoc.formfields("chkCustYes").CheckBox.Value = True
Else
    wdDoc.formfields("chkCustNo").CheckBox.Value = True
End If

With wdDoc
    If Range("B5").Value = "Yes" Then .Formfields("chk401k").CheckBox.Value = True
    If Range("B6").Value = "Yes" Then .Formfields("chkRoth").CheckBox.Value = True
    If Range("B7").Value = "Yes" Then .Formfields("chkStocks"). _
        CheckBox.Value = True
    If Range("B7").Value = "Yes" Then .Formfields("chkBonds"). _
        CheckBox.Value = True
End With

wdApp.Visible = True

ExitSub:

    Set wdDoc = Nothing
    Set wdApp = Nothing

End Sub

In Chapter 19, “Arrays,” you will learn how to use multidimensional arrays. Reading data into a multidimensional array, performing calculations on the array, and then writing the array back to a range can dramatically speed up your macros.