Find Function VBA Named Argument Not Found-Runtime error 448

[meibr2]

I am writing a macro( late binding) that runs through a word document and highlight all the values that is contained in my cell range in Excel. I have used the find function which works fine until I add the argument IgnoreSpace:= True, which I don't understand why it's resulting in error. Appreciate any help

Sub Highlight()
    Dim lrow As Long
    lrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
     Dim i As Long
       Dim myRange As Object
        Dim FindText As Object

        Set myRange = ActiveDocument.Content


       For i = 1 To lrow Step 1

       myRange.Find.Execute FindText:=Sheet1.Range("A" & i).Value, MatchCase:=False, _
        IgnoreSpace:=True, Forward:=True, MatchWholeWord:=True

      If myRange.Find.Found = True Then myRange.HighlightColorIndex = wdYellow
      Next i
      End Sub

 

[Joe4]

Where did you find something that suggests “IgnoreSpace” is a valid VBA argument for the Find function in Excel VBA? The documentation I found does not seem to show that: Range.Find method (Excel)

It looks like it might be something associated with Word VBA, not Excel VBA.

 

[meibr2]

Where did you find something that suggests “IgnoreSpace” is a valid VBA argument for the Find function in Excel VBA? The documentation I found does not seem to show that: Range.Find method (Excel)

It looks like it might be something associated with Word VBA, not Excel VBA.

it is an argument for the word vba which can be written on Excel VBA once you check the word references

Find.Execute method (Word)

Office VBA reference topic

learn.microsoft.com

 

[meibr2]

Where did you find something that suggests “IgnoreSpace” is a valid VBA argument for the Find function in Excel VBA? The documentation I found does not seem to show that: Range.Find method (Excel)

It looks like it might be something associated with Word VBA, not Excel VBA.

I am assuming that one could use the word vba on excel vba since we could reference the document on excel vba right? Or am I wrong?

 

[Joe4]

I am assuming that one could use the word vba on excel vba since we could reference the document on excel vba right? Or am I wrong?

I don't believe that is correct. I do not think you can use Microsoft Word VBA commands in Excel VBA code (at least not in the way you have tried).
You can use Microsoft Word objects in Excel VBA, but you still need to use Excel VBA code.

In VBA, you can set a reference to use the Microsoft Word Object Library to use the commands and functions of that particular library in Excel VBA.
I am not familiar with what all is available in that library, you would need to research that.
See here: VBA Libraries

 

[meibr2]

I don't believe that is correct. I do not think you can use Microsoft Word VBA commands in Excel VBA code (at least not in the way you have tried).
You can use Microsoft Word objects in Excel VBA, but you still need to use Excel VBA code.

In VBA, you can set a reference to use the Microsoft Word Object Library to use the commands and functions of that particular library in Excel VBA.
I am not familiar with what all is available in that library, you would need to research that.
See here: VBA Libraries

Actually, you could. I just got an answer from a user elsewhere.

Sub HighlightInWord()
    
     ' Late Binding (not recommended because you don't have access
     ' to the Word Intellisense, you have to replace the word constants
     ' with numbers,...)
'    Dim wdApp As Object: Set wdApp = GetObject(, "Word.Application")
'    Dim wdDoc As Object: Set wdDoc = wdApp.ActiveDocument
'    Dim wdRange As Object: Set wdRange = wdApp.ActiveDocument.Content
    
    ' Early Binding (Recommended)
    ' Needs a reference to
    '     'Tools->References->Microsoft Word ??.0 Object Library'
    
    ' Attempt to create a reference to an instance of Word.
    Dim wdApp As Word.Application:
    On Error Resume Next
        Set wdApp = GetObject(, "Word.Application")
    On Error GoTo 0
    
    ' Check if the reference to an instance of Word was not created.
    If wdApp Is Nothing Then
        MsgBox "Word is not open!", vbExclamation
        Exit Sub
    End If
    
    ' Check if there is no open Word document.
    If wdApp.Documents.Count = 0 Then
        MsgBox "No open Word document found!", vbExclamation
        Exit Sub
    End If
    
    Dim wdDoc As Word.Document: Set wdDoc = wdApp.ActiveDocument
    Dim wdRange As Word.Range: Set wdRange = wdDoc.Content
    
     ' Store the current default highlight color in a variable.
    Dim DefaultColor As Long:
    DefaultColor = wdApp.Options.DefaultHighlightColorIndex
    wdApp.Options.DefaultHighlightColorIndex = wdYellow ' 7 ' wdYellow
    
    Dim lRow As Long: lRow = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
    
    Application.ScreenUpdating = False
    
    Dim i As Long, CellString As String
    
    With wdRange
        ' Clear existing highlights.
        .HighlightColorIndex = wdNoHighlight ' 0 ' wdNoHighlight
        With .Find
            .MatchCase = False
            .MatchWholeWord = True
            .Replacement.ClearFormatting
            .Replacement.Highlight = True
            .IgnoreSpace = True
            For i = 1 To lRow
                CellString = CStr(Sheet1.Cells(i, "A").Value)
                If Len(CellString) > 0 Then ' the cell is not blank
                    .Text = CellString
                    .Execute Replace:=wdReplaceAll ' 2 ' wdReplaceAll
                'Else ' the cell is blank; do nothing
                End If
            Next i
        End With
    End With
    
     ' Reset the default highlight color.
    wdApp.Options.DefaultHighlightColorIndex = DefaultColor

    Application.ScreenUpdating = True

    MsgBox "Words highlighted.", vbInformation

End Sub

 

[Fluff]

I just got an answer from a user elsewhere.

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Please provide links to all sites where you have asked this question. Thanks

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[6StringJazzer]

I don't believe that is correct. I do not think you can use Microsoft Word VBA commands in Excel VBA code (at least not in the way you have tried).
You can use Microsoft Word objects in Excel VBA, but you still need to use Excel VBA code.

In VBA, you can set a reference to use the Microsoft Word Object Library to use the commands and functions of that particular library in Excel VBA.
I am not familiar with what all is available in that library, you would need to research that.
See here: VBA Libraries

What the OP did in the code is perfectly valid. It sets a reference to Word and sets a Word Range object to the current active Word document. The problem was that VBA was not recognizing the parameter IgnoreSpace, which is documented for this method. The code works fine without it.

The solution posted treats IgnoreSpace as a property of a Find object, rather than a parameter of the Execute method. I am baffled as to why the original code didn't work, but this workaround seems to fix the problem.

 

[Joe4]

Actually, you could. I just got an answer from a user elsewhere.

What the OP did in the code is perfectly valid. It sets a reference to Word and sets a Word Range object to the current active Word document. The problem was that VBA was not recognizing the parameter IgnoreSpace, which is documented for this method. The code works fine without it.

OK, perhaps I was a bit clumsy in how I responded. I did not mean it was impossible, but I did not think it was possible just with the standard Excel VBA, which appears to be correct.
However, I tried to allow for the fact that it MIGHT be possible if you add the Microsoft Word Object Library (I don't work enough with that to say either way).
I tried to allow for that possibility here:

In VBA, you can set a reference to use the Microsoft Word Object Library to use the commands and functions of that particular library in Excel VBA.
I am not familiar with what all is available in that library, you would need to research that.
See here: VBA Libraries

And indeed, the code solution posted only works if you add that Object Library.
Section from documentation of your code solutionL

' Early Binding (Recommended)
' Needs a reference to
' 'Tools->References->Microsoft Word ??.0 Object Library'

Anyway, I am glad you were able to get a working solution.

 

[Jaafar Tribak]

The Object Browser shows you all the details of the object model and it turns out, IgnoreSpace is not a valid argument of the Find.Execute Method, hence the compile error.

Function Execute([FindText], _
[MatchCase], [MatchWholeWord], [MatchWildcards], [MatchSoundsLike], _
[MatchAllWordForms], [Forward], [Wrap], [Format], [ReplaceWith], _
[Replace], [MatchKashida], [MatchDiacritics], [MatchAlefHamza], [MatchControl]) As Boolean

It is however a valid optional argument in the funnily named Find.Execute2007 Method

Function Execute2007([FindText], [MatchCase], [MatchWholeWord], [MatchWildcards], [MatchSoundsLike], _
[MatchAllWordForms], [Forward], [Wrap], [Format], [ReplaceWith], [Replace], [MatchKashida], _
[MatchDiacritics], [MatchAlefHamza], [MatchControl], [MatchPrefix], [MatchSuffix], _
[MatchPhrase], [IgnoreSpace], [IgnorePunct]) As Boolean

That's probably where the confusion stems from.