VBA using Excel version of Find on a Word Object?

Question

I'm trying to find the first two dates in a Word document save them as variables in an Excel macro. Other parts of my project require this to be written as an Excel macro.

Dim wordObj As New Word.Application
Dim wordDoc As New Word.Document
Dim Rng As Range
Dim myDate As String
Dim Fnd As Boolean
Dim i As Long
i=0
Set wordDoc = wordObj.Documents.Open(path)
Set Rng = wordDoc.Range
Do While i < 2
    With Rng.Find
        .Execute FindText:="??/??/????", MatchWildcards:=True
        Fnd = .Found                                                             
    End With
    If Fnd = True Then
        mydate = Rng
        If i = 0 Then 
            firstdate = mydate
        ElseIf i = 1 Then
            seconddate = mydate
        End If
    End If
    i = i+1
Loop
End Function

I get Compile Error: Argument not optional. The issue is, VBA is interpreting it as Excels' .Find with arguments(Target, StartLine, StartColumn... etc), instead of a Word Find object. I am new to VBA, and managing the different types of applications is a little confusing.

Mathieu Guindon Mathieu Guindon · Accepted Answer · 2019-07-01T19:16:27

The issue is, VBA is interpreting it as Excels' .Find with arguments(Target, StartLine, StartColumn... etc), instead of a Word Find object
Dim Rng As Range

But it is an Excel Range with a Find method ;-)

This line would be throwing a type mismatch error at run-time:

Set Rng = wordDoc.Range

Qualify it with the library you're pulling type from:

Dim Rng As Word.Range

That will help VBA understand where that Range interface comes from.

If you look at the VBA project's references, you'll see why:

VBE references dialog showing Word library priority 5 and Excel priority 2

When VBA encounters this instruction at compile-time:

Dim Rng As Range

It needs to bind this Range type to a specific type. Maybe there's a Range user-defined-type (UDT) in the VBA project? Or a class by that name? Assuming there's none of that, then VBA tries to locate the type in the referenced libraries - in priority order. The VBA standard library can't be moved or removed. The host application's object model library neither.

If we search for "range" in the VBE's object browser (F2), we see that there are two classes by that name:

object browser listing Excel.Range and Word.Range classes

Therefore, because the Excel library comes first in the priority order when VBA is resolving the name Range, it binds to Excel.Range at compile-time.

By explicitly qualifying types with the library they're meant to be pulled from, we fix the ambiguous type reference, and VBA binds to Word.Range as intended.

member list for a Word.Find object

VBA using Excel version of Find on a Word Object?

1 Answers