Using Microsoft Office Macros

This post is an introduction to using macros in Office applications. Check Bill's recent post to enable macros. Bill has hinted a little bit at the power of Office automation and I wanted to jump in quick and get some initial explanations out of the way.
I'm going to do something we can build on later, a macro that finds and replaces paragraphs with a space so that all the text ends up in one line (this will be handy for cleaning up messy pastes from Acrobat - see the description of the problem here).

First lets start recording a macro. In Word 2007, go to Developer -> Record Macro . After hitting the Record Macro button, Word will ask you to name the macro, just pick something descriptive like "replace paragraphs." After hitting OK Word will be in record mode and your cursor will have a record symbol .




Next we do the actions in Word that we want to repeat with our macro. In this case, we are going to find and replace paragraphs with spaces. Go to the Find and Replace dialog (Home->Replace in Word 2007 or Ctrl+H). We need the advance options to replace paragraphs. Hit the More button in the lower left of the dialog box. Once the advanced options are visible, hit the Special button and select paragraph mark. The Find what box will show a "^p" to symbolize that it will find paragraphs. Now, put a " " (space) in the Replace with box. Now hit the Replace All button. Whether or not anything was replaced doesn't matter, we are saving this for the future. Now hit the Developer ->Stop Recording button. You've just created your first macro.

You can go to the Visual Basic editor to see the code generated (Developer -> Visual Basic or Alt+F11). The code looks like this (don't get intimidated, we will explain later).

Sub Macro1()
'
' Macro1 Macro
'
'
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "^p"
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub

Now, lets test the new macro. Type some text into Word in different paragraphs (type text, hit enter, type some more, repeat). Then run the macro. Go to Developer ->Macros (or hit Alt+F8). The macro window will pop up, pick yours from the list and hit run.


Viola! This:

Turns into this:


In a future post, Bill will show how to assign a keyboard shortcut to our new macro.
__________
Share This Post!