How to set some sort of array for VBA find/replace scripts

Question

UPDATED SCRIPT I'M USING THAT CAUSES LOCKUP...I tried replacing the (Replace:=wdReplaceOne) with (Replace:=wdReplaceAll), but still no such luck:

Option Explicit
'Dim strMacroName As String
 Dim spellingcorrectionsrep As Long

 Public Sub SpellingReview()
 Dim oShell, MyDocuments

'Declaring the MyDocs filepath: Set oShell = CreateObject("Wscript.Shell") MyDocuments = oShell.SpecialFolders("MyDocuments") Set oShell = Nothing

'   Set values for variables of the actual word to find/replace
spellingsuggestionsrep = 0
spellingcorrectionsrep = 0

'   Replacements

SpellingCorrections "dog", "dog (will be changed to cat)", False, True

'    END SEARCHING DOCUMENT AND DISPLAY MESSAGE

MsgBox spellingcorrectionsrep

'strMacroName = "Spelling Review"
'Call LogMacroUsage(strMacroName)

 End Sub
  Sub SpellingCorrections(sInput As String, sReplace As String, MC As Boolean, MW As     Boolean)

'   Set Selection Search Criteria
Selection.HomeKey Unit:=wdStory
With Selection
     With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Replacement.Highlight = True
    .Text = sInput
    .Replacement.Text = sReplace
    .Forward = True
    .Wrap = wdFindStop
    .Format = True
    .MatchWildcards = False
    .MatchCase = MC
    .MatchWholeWord = MW
End With
Do While .Find.Execute = True
    If .Find.Forward = True Then
        .Collapse Direction:=wdCollapseStart
    Else
        .Collapse Direction:=wdCollapseEnd
    End If

    If .Find.Execute(Replace:=wdReplaceOne) = True Then
    spellingcorrectionsrep = spellingcorrectionsrep + 1
    End If
    If .Find.Forward = True Then
        .Collapse Direction:=wdCollapseStart
    Else
        .Collapse Direction:=wdCollapseEnd
    End If
  Loop
  End With
 End Sub

is your dictionary a fixed size, with fixed values (so you could hard code each word) or is it taken from some where else? — NickSlash
– NickSlash, Commented May 17, 2012 at 20:44

Siddharth Rout · Accepted Answer · 2012-05-21 12:36:36Z

2

Why not use it as a common procedure?

Option Explicit

Dim wordRep As Long

Public Sub SpellingReview()
    Dim oShell, MyDocuments

    wordRep = 0

    SpellingCorrections "Dog", "Dog (will be changed to DOG)", False, True

    MsgBox wordRep
End Sub

Sub SpellingCorrections(sInput As String, sReplace As String, MC As Boolean, MW As Boolean)
    With ActiveDocument.Content.Find
        Do While .Execute(FindText:=sInput, Forward:=True, Format:=True, _
           MatchWholeWord:=MW, MatchCase:=MC) = True
           wordRep = wordRep + 1
        Loop
    End With

    With Selection.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Replacement.Highlight = True
        .Text = sInput
        .Replacement.Text = sReplace
        .Forward = True
        .Wrap = wdFindContinue
        .Format = True
        .MatchCase = MC
        .MatchWholeWord = MW
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
        .Execute Replace:=wdReplaceAll
    End With
End Sub

edited May 21, 2012 at 12:36

answered May 17, 2012 at 21:13

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

20 Comments

MBlackburn Over a year ago

Worked like a charm and very simple. Many thanks to you my friend!

MBlackburn Over a year ago

Is there anyway to do something similar to my the code at the beginning of my first post? I tried to set it up similar to this reply but it doesn't like the "wordrep = wordrep + 1" which I need to keep track of the results as it increments each "found" instance, which I end up writing out to an INI file to keep track.

Siddharth Rout Over a year ago

Did you declare this at the top ? Dim wordrep as Long or do you mean the total number of replacements done?

MBlackburn Over a year ago

That's what I meant . . however, this may be a stupid question, but how can I make it so that the wordRep variable is passed back/available in the main sample sub as that is where the rest of my script will be?

Siddharth Rout Over a year ago

declare it on top, outside the procedure, as I have done above.

|

NickSlash · Accepted Answer · 2012-05-17 22:20:53Z

0

Creating an Array to store the information isn't too hard

Dim Dict() As Variant
' Integer ReplacementCount, String FindText, Boolean MatchCase, Boolean MatchWholeWord, String ReplaceText
Dict = Array( _
            Array(0, "Word", True, True, "word"), _
            Array(0, "Word1", True, True, "word1"), _
            Array(0, "Word2", True, True, "word2"), _
            Array(0, "Word3", True, True, "word3") _
        )

Using this you could loop through each item and store the replacement counter in the same array.

For Index = LBound(Dict) To UBound(Dict)
    Do While ReplaceStuffFunction(WithArguments) = True
       Dict(Index)(0) = Dict(Index)(0) + 1
    Loop
Next Index

When I tried your first example code it didn't seem to replace ALL instances, just one per run of the sub so either I did it wrong or something not right (or its not meant to do it)

answered May 17, 2012 at 22:20

NickSlash

5,1954 gold badges25 silver badges41 bronze badges

1 Comment

MBlackburn Over a year ago

I coudln't quite get this working right, but I appreciate you're reply very much.

iehrlich · Accepted Answer · 2017-07-17 22:32:34Z

'In this example, I used two arrays to shorten formal hospital names
'Define two arrays (I used FindWordArray and ReplacewordArray)
'The position of the word (by comma) in each arrays correspond to each other

Dim n as long
Dim FindWordArray, ReplaceWordArray As String 'Change information pertinent to your needs
Dim FWA() As String 'Find words array created by split function
Dim RWA() As String 'Replace array created by split function
Dim HospitalName As String 'This is the string to find and replace

FindWordArray = ("Hospital,Center,Regional,Community,University,Medical") 'change data here separate keep the quotes and separate by commas
FWA = Split(FindWordArray, ",")
ReplaceWordArray = ("Hosp,Cntr,Reg,Com,Uni,Med") 'change data here keep the quotes but separate by commas
RWA = Split(ReplaceWordArray, ",")
'Loop through each of the arrays
For n = LBound(FWA) To UBound(FWA)
    HospitalName = Replace(HospitalName, FWA(n), RWA(n))
Next n

Collectives™ on Stack Overflow

How to set some sort of array for VBA find/replace scripts

3 Answers 3

20 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

20 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related