Massive List of Search-Replace in VB for Word 2003. Possible WTF.

  • I wrote this code months ago as a fairly basic script to replace some common misspellings from translations, but it has increased in size considerably.  All it does is search the selected text for each of the search terms and replace it.  There are hundreds of these commands in the macro.  It has come time for me to pass this thing on, but I'd like to make sure there isn't a quicker way to do it.  The line repeated hundreds of time is:

    Selection.Find.Execute Findtext:="term", ReplaceWith:="replacement", MatchWholeWord:=True, Replace:=wdReplaceAll

    The other problem with this is that I would like to have the list of terms searched and replaced in a common file on a shared drive instead of having to update macros on all the users utilizing this macro whenever I add new terms to the Search-Replace list.


  • It sounds like you've practically described the solution: have a file on a shared drive, with pairs of lines in it. search term, replacement term, search term.
    Then while not at end of file, read two lines. execute the replacement, Findtext:=line1, ReplaceWith:=line2
    Make sure to handle the case of a malformed file gracefully.

  • I'm aware that it would be a great solution, but I'm unaware of how to implement it using only visual basic for Word 2003.  Due to security restrictions and lockdown of security levels within the applications I cannot use any external applications (outside of other MS Office 2003 apps) to assist.  I know for cleanliness of code, I could always do an




    Where the sub runreplace is:

    Selection.Find.Execute FindText:=inword, ReplaceWith:=outword, MatchWholeWord:=True, Replace:=wdReplaceAll

    But I get the feeling this may be slower than the current megalist(s) (as it exceeds the size of a single module allowed) of search-replace.

  • Can your code open an external file? If so, I think there are enough APIs available to read that file a line at a time and call your runreplace sub every other line.


    subroutine performReplacements:
        Open the data file
        While not at end of file
           Read a line (done if fail due to EOF)
           Read another line (done if fail due to EOF)
           Call runreplace with the first line as inword and the second line as outword
    end sub

  • I'm not sure if the security settings would allow VB to open an external file.

    I suppose the major WTF is there being a better, faster way to perform these thousand+ search and replaces.

  • Try this

    You should be able to work with that to open the file.

  • Thanks, that is a big help.  The new method is only slightly slower. (about 1:03 for external replacement list vs. 58 sec with the replacements inside, on average over 3 tests.)


    For anyone with a similar problem in the future, here's the code now used:


    Sub newReplaceMethod ()

    ' Set options for the find-replace function for all of this type

    With Seleciton.Find



       .Forward = True

       .MatchCase = True

       .Wrap = wdFindStop

       .MatchWildCards = False

       .MatchWholeWord = True

    End With

    ' Open the file containing the comma/quotation mark delimited list

    Open "c:\lists\giantlist.txt" For Input As #1

    ' Loop through to the end of the file.

    Do While Not EOF(1)

    ' Get the replacement words from the opened file.

       Input #1, fileChangeFrom, fileChangeTo

    ' Replace all instances of the word to be replaced.

       Selection.Find.Execute Findtext:=fileChangeFrom, ReplaceWith:=fileChangeTo, MatchWholeWord:=True, Replace:=wdReplaceAll


    Close #1

    End Sub


    c:\lists\giantlist.txt looks like:




    Nothing but quotes and commas.

  • Now you just have to make the filename a configurable parameter so that you don't have to update everyone's macros when you change the file server name.  AT LEAST make it a constant and put it at the top of the sub.

    Then you can work on the code that checks to see if file #1 is already in use by another macro and opens the file under the next-available file number.

  • It's going to be a constant inside the sub, since I don't expect anybody to have to change it.  Making it more robust would add complications, making it harder for somebody unfamiliar with VB to understand, and I'm going off the assumption that my replacement is "just a user" as well.  If somebody from the replacing team has any VB skill, I'll leave it up to them to make improvements as necessary.  I'm not in any actual IT position, just the worker skilled enough to make my section's job easier.

    The macro does not have a problem with multiple users reading the file simultaneously, and the number of users accessing it is minimal (3 users at most), so the biggest error handling I expect to put in is on error msgbox .... and have them try again.

  • if speed is an issue you can speed up your proccess.  are you running through the entire file for every term?  try hashing all the words in the file and then running through the document.  hash each word and see if it has a match in the hashtable.  then replace it as necissary.  that way you only run through the file once. 

  • @Qwerty said:

    Then you can work on the code that checks to see if file #1 is already in use by another macro and opens the file under the next-available file number.


    Dim FileID As Integer
    FileID = FreeFile()

    Open FileName For Input As #FileID
    Do Until EOF(FileID)
       Input #FileID, Blah
    Close #FileID

  • Not leaving well enough alone, here's what I have it do:

    Set the file locations as constants.  These files are 1kb - 11 kb in size on a gigabit ethernet system.

    Checks to see if each of the networked file exists, if it does, it is copied to the user's system. This file is used for the read-only access search and replace macros.  If the networked file does not exist, a warning is displayed, advising users that the networked file was not found, and that the local backup is being used.  If neither exists, the user is notifed and the whole process is aborted.

    The only process which will have the networked file open is the one writing the new words to the list.

  • Getting that done must have given your morale a elevator. 🙂

  • Not quite as big as an elevation as getting out of this place will be.  But yes, barring the time that I thought I'd broke it without any recent saves, it's good to have complete and about robust enough to pass a WTF screening.


    Thanks again for all the help guys.

Log in to reply

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.