VBAin't working

December 21, 2010 8:16 PM

VBA for Word help. A lot of help. I'm trying to find a string, then search and replace a space within that string. I can do parts of this, but not the whole thing and not for the whole document at once.

So I'm lazy. I like to automate certain Word tasks. I've been doing this with a few wildcard searches in Word, but wouldn't it be nice for me to have a script rather than having to type the search each time? It would!

Here's an example. I want to replace all instances of "X. Y." with "X.Y." (closed up space), for example. Manually, I can search for all instances of "X. Y." in the main document with Find > with wildcards > [A-Z]\. [A-Z]\.". Then I do a search and replace for " " (a space) with "" (nothing) within that selection.

I can't figure out enough VBA for Word to get this done automatically. I had it apparently working for a single instance, but I had to run the macro over and over again rather than it looping through the document.

Sub ScratchMacro()

With Selection.Find
.ClearFormatting
.Text = "[A-Z]\. [A-Z]\."
.MatchWildcards = True
.Wrap = wdFindStop
.Execute

Do While .Found = True

With Selection.Find
.ClearFormatting
.MatchWildcards = False
.Text = " "
.Replacement.Text = ""
.Execute Replace:=wdReplaceAll
End With

Loop

End With
End Sub

(And once that worked and then it seemed to not work.) I don't know what Word's problem is, but I just can't find the right VBA code for this one thing I want to do. If I get this working, I can modify it for other searches. Asking too much? Doomed to search manually forever? I'm in over my head, obviously.

posted by theredpen to Technology (11 answers total)

Response by poster: OK, I think I have it mostly working -- I really just need help with the looping through the whole document thing. I know there's For Each/Next and Do While but can't get them working quite right and all online examples seem to be doing a lot more than I need. Thanks in advance.
posted by theredpen at 8:21 PM on December 21, 2010

I'm confused why you have to do this in so many steps -- why aren't you just replacing all instances of "X. Y." with "X.Y." -- skip the searching within that for spaces and deleting the spaces separately, just replace the whole thing? Or are you simplifying here and there's a reason that doesn't work?
posted by brainmouse at 8:32 PM on December 21, 2010

Oh, i just realized that you're not actually replacing "X. Y." , you're replacing "[letter]. [letter]." Ignore me.
posted by brainmouse at 8:34 PM on December 21, 2010

Response by poster: Yep, two capitals -- sorry for confusion.
posted by theredpen at 9:14 PM on December 21, 2010

Can you post the new code you're using in full? Usually I just throw my action in a Do... Loop with the search function down within and a move to start of next line after whatever you're looking to replace. Before the end of the loop I have "If Not Selection.Find.Found Then Exit Do." Here's how I used to clear certain heading styles (before I realized you could search for them by using the search box):

Selection.HomeKey Unit:=wdStory
Do
Selection.Find.ClearFormatting
Selection.Find.Style = ActiveDocument.Styles("Heading 2")
Selection.Find.Replacement.ClearFormatting

With Selection.Find
.Text = ""
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchByte = False
.CorrectHangulEndings = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = False
.MatchFuzzy = False
End With
Selection.Find.Execute
If Not Selection.Find.Found Then Exit Do
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.MoveLeft Unit:=wdCharacter, Count:=1
Selection.TypeText Text:="=="
Loop


(Please note, I'm not a computer programmer, just someone who enjoys automating tasks, even if it takes me more time troubleshooting why the code keeps breaking than actually doing the darn thing manually...)
posted by jng at 10:30 PM on December 21, 2010

You can do this with a regular expression search, rather than needing VBA.

I don't have a proper computer with me currently, so I won't give a full example, but look at http://office.microsoft.com/en-us/support/add-power-to-word-searches-with-regular-expressions-HA001087305.aspx?redir=0. VBA is more flexible, true, but it's a lot quicker to write a regex.
posted by siskin at 12:29 AM on December 22, 2010

Best answer: Backreferences, my man.

Sub ScratchMacro()
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = "([A-Z])\. ([A-Z])\."
        .Replacement.Text = "\1\2"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchAllWordForms = False
        .MatchSoundsLike = False
        .MatchWildcards = True
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
End Sub

(Where \1 and \2 correspond to $1 and $2 in the javascript page I linked).
posted by Monday, stony Monday at 12:48 AM on December 22, 2010

Response by poster: doublehappy: " .Text = Replace(rng.Text, " ", "")"

Hi, thank you all. doublehappy: I get an error on this line that says "Sub or Function not defined" -- seems like I should be able to fix that but I'm not sure how.

Monday, stony Monday: "Backreferences, my man. "

Woman :) and thanks. However, nothing happens at all when I run that. I've also had problems (I'm on a Mac) with the /1/2 substitutions -- sometimes Word likes to put them back in the wrong order.

siskin: "You can do this with a regular expression search, rather than needing VBA. "

I know -- that's actually what I'm using now. But I still have to type it in each document, which seems like something I should be able to automate.

Thanks, everyone.

jng: "Can you post the new code you're using in full?

jng: That code is what I'm using in full. It works for one iteration. I think what you're talking about is close to what I need and am trying to get it working now.
posted by theredpen at 6:28 AM on December 22, 2010

Response by poster: Monday, stony Monday: that worked, and with no Word weirdness this time!! Thank you so much!
posted by theredpen at 6:51 AM on December 22, 2010

Response by poster: Note for those who may follow: I did move the parentheses in the marked right answer outside the periods -- I think as written in the answer it would strip the periods from the initials.
posted by theredpen at 6:52 AM on December 22, 2010

Response by poster: The bug I was talking about happens when you try to do a similar search with digits instead of letters:

Replacing ([0-9])-([0-9]) with \1–\2
changes 1-2 to 12– instead of 1–2
posted by theredpen at 6:59 AM on December 22, 2010