Forum Discussion

Copper Contributor

Mar 02, 2023

Solved

Ways to improve the performance of my Word macro that hides hard coded text

Hello, I recycled an old macro in a https://www.techrepublic.com/article/macro-trick-how-to-highlight-multiple-search-strings-in-a-word-document/#:~:text=Click%20Reading%20Highlight%20and%20choos...

Doug_Robbins_Word_MVP

Mar 02, 2023

jdvelasquez Use the following code

Dim arrWords As Variant
arrWords = Split("EVALUACIÓN INTRODUCCIÓN|EVALUACIONES SOBRE EL DESEMPEÑO ACADÉMICO|administrada por", "|")
For i = LBound(arrWords) To UBound(arrWords)
    Selection.HomeKey wdStory
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = arrWords(i)
        .Replacement.Font.Hidden = True
        .MatchWildcards = False
        .Wrap = wdFindContinue
        .MatchCase = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
Next i

Doug_Robbins_Word_MVP

MVP

Mar 02, 2023

jdvelasquez Use the following code

Dim arrWords As Variant
arrWords = Split("EVALUACIÓN INTRODUCCIÓN|EVALUACIONES SOBRE EL DESEMPEÑO ACADÉMICO|administrada por", "|")
For i = LBound(arrWords) To UBound(arrWords)
    Selection.HomeKey wdStory
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = arrWords(i)
        .Replacement.Font.Hidden = True
        .MatchWildcards = False
        .Wrap = wdFindContinue
        .MatchCase = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
Next i

jdvelasquez

Copper Contributor

Mar 03, 2023

Hi Doug,

Happy Friday!

Indeed, this did the trick since it will not execute the whole process per string.

My next step will be to use a document list instead of putting all the text to hide inside the macro.

Thank you so much for passing by and helping me.

Kind regards,
Joshua

Doug_Robbins_Word_MVP

MVP

Mar 03, 2023

jdvelasquez The following code will act on a list of words in Column A of the Excel workbook for which the path\filename is included where indicated in the Set xlbook command in the code

Dim arrWords As Variant
Dim xlapp As Object
Dim xlbook As Object
Dim xlsheet As Object
Dim myarray As Variant
On Error Resume Next
Set xlapp = GetObject(, "Excel.Application")
If Err Then
    bstartApp = True
    Set xlapp = CreateObject("Excel.Application")
End If
On Error GoTo 0
Set xlbook = xlapp.Workbooks.Open("C:\path\filename.xlsx") 'Modify as necessary
Set xlsheet = xlbook.Worksheets(1)
arrWords = xlsheet.Range("A1").CurrentRegion.Value
If bstartApp = True Then
    xlapp.Quit
End If
Set xlapp = Nothing
Set xlbook = Nothing
Set xlsheet = Nothing
For i = LBound(arrWords) To UBound(arrWords)
    Selection.HomeKey wdStory
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = arrWords(i)
        .Replacement.Font.Hidden = True
        .MatchWildcards = False
        .Wrap = wdFindContinue
        .MatchCase = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
Next i

jdvelasquez
Copper Contributor
Mar 03, 2023
Thanks, Doug. I appreciate the extra hand. In the end, I didn't need the external document since the usage will be specific, and the number of terms won't scale over time. I am all set. Have a good one!