[In which I moan about the abysmal macro-recorder in M/S Office, and show how to create and edit a macro.]
I finished my previous article with a comment that I've long wanted to have a bit of a whinge about M/S Word's macro recorder. Before I do that, though, I suppose I'd better explain what a macro recorder is. Indeed, what a macro is, given the number of people I've run into who seem have no idea that such things exist. Not to mention the number who start to back away slowly from my apparent geekism if I actually start discussing the ins and outs of them!
A macro is simply a small program which does some task or other for you that you'd rather not do by hand. Usually because it's repetitive and/or time-consuming. In text-editors and word-processors, this generally entails insertion of an oft-used text-string or a set of find/replace operations. As an example, one of the bunch of macros I use to turn the text I type in M/S Word into an HTML-formatted blog-post places the cursor at the end of the final paragraph of my latest inane rant and inserts the text-string " <br /><span style="margin-left:25px;">—<em>Daz</em></span>," which produces my signature, nicely indented, at the end of the post. As another, perhaps more familiar example, the copy/paste function on your computer is, essentially, a pair of macros. Ctrl+C runs a macro which copies whatever's been selected into a sub-program named (in Windows) "Clipboard," and Ctrl+V runs another macro which inserts the contents of "Clipboard" at the position of the cursor.
(Oh, I'd better warn you, I suppose; this post is about using Find/Replace and writing/editing a macro in M/S Word. If you don't use that program, you'll probably want to stop reading now. Unless you're just plain curious, I suppose. Each to their own.)
A macro recorder is a sub-program within the text editor or word-processor which, while it's running, records your keystrokes and mouse clicks. This, in theory, means that when you use the macro you've recorded, it'll perform all those keystrokes and clicks for you; much quicker, and without typos. When I made the macro above, I hit the Record new macro button, then used Ctrl+End to go to the end of the document, and typed in the text-string which produces my HTML-ised signature. Then I hit the Stop button, added an instruction into my "Blog_HTML" macro to run the signature-macro along with the others in the bunch I mentioned, and presto! I've never had to type that text-string since.
Did you notice I said "in theory"?
Visual Basic Editor is a program, included with M/S Office, in which we can manually edit macros. It's actually a pretty darn good bit of kit, though most Office-users don't even realise it's there. Indeed, when I was first learning to write and edit macros for Word, I'd often google a problem, only to land on a post where someone would be positively raving about what a great program it is. Here's the thing though. The major reason, as I see it, that many of us get to notice what a good program the macro-editor is, is that the macro-recorder is a heap of shite only beaten in its smelly-gooey-crappiness by that other Microsoft product, Internet Explorer. Most of us, in fact, end up learning to edit macros, not because we want to modify what we recorded, but because the awful bloody fly-infested dung-heap of a "recorder" didn't, in fact, record a good portion of what we did.
(I should, for the sake of openness, mention that I'm using the admittedly rather antiquated Office 2003. In the course of googling the above-mentioned problems, however, I've run across plenty of posts which lead me to believe that the more modern versions are no better in this aspect. Indeed, the makers seem to have invested far more effort into making Office look all modern and shiny than into improving its functionality. Mind you, having experienced Windows 8 on my sister's non-touch-screen computer, it seems that attitude isn't just confined to Office…)
It's not that I don't appreciate the ability to edit macros. Having spent ages recording complicated, multi-operation macros in Crimson Editor (where you can't edit them), only to find I've missed a step or included a typo, I really do appreciate that ability. It's just that I want to edit them because they need editing to suit them to a new purpose, to expand or narrow their effect or to correct a cock-up I made, not because the software didn't do its job!
Basically, it's fine at recording what you did to plain text; tell it to work with particular formatting, though, and it completely ignores you.
Say you write a lot about Admiral Horatio Nelson. He sailed, as we all know, in a ship called HMS Victory. But in any biography of the chap, the word "victory" is also likely to appear a lot as a common noun. So you record a macro to search for every occurrence of "Victory" and format it in italics only if it's preceded by "HMS." It's an easy enough operation:
Find all occurrences of "HMS Victory," and replace with italicised "HMS Victory." Find all italicised occurrences of "HMS," and replace with non-italic "HMS."
This is what the macro will do, when you run it:
Find all occurrences of "HMS Victory," and replace with "HMS Victory." Find all occurrences of "HMS," and replace with "HMS."
In other words, like a politician, it will say a lot, but the end-product will be devoid of useful content.
And that example reminds me of something else I've noticed, somewhat to my own continual surprise. Many people don't seem to realise that the Find/Replace function can be used for anything more than simple replacements of text-strings. "Stephen" for "Steven," say. While it's peripheral to this article, and I'm trying like hell not to digress (because judging by the length of the list of "things to cover" I've got scrawled on a bit of paper, this post is likely to be one of my longest ever, even without my usual rambling digressions), I really would urge a little googling on the subject. You might be amazed at how versatile that little dialogue box can be. The only thing I'd really like to see added would be an ability to work with consecutive, not combined, formats. To, for example, be able to search for and manipulate all instances of a red "r" followed by a blue "k". As it is, you have to go around the houses somewhat, to do stuff like that.
Anyways, I thought I'd write a step-by-step description of how to use Find/Replace to turn Word-formatted text into HTML-tagged text, ready to paste into a web-page, and then create a macro to do the same. I'm using the fractions I was talking about in my previous post as an example. The reason I'm doing it step-by-step is that, in my experience, merely dumping a finished, fully-working bunch of code on someone and saying "use that" doesn't teach them anything. Walk them through it, however, and they get an idea of the process, which means they stand a better chance of being able to adapt and apply it to other scenarios. And the reason I'm using that fractions-example is that it's a several-part process, and fairly but not too complicated. (In fact, the only drawback to the step-by-step method is that breaking simple processes down into several sub-processes can sometimes make them appear more complicated than they actually are. Ho-hum.)
Before we begin, a note on word-usage. To avoid possible confusion, I'm going to refer to Word's own formatting using the word "format" and its variants. HTML/CSS formatting, I shall refer to as "tags," "tagging," etc. If it's actually italicised in Word, it's 'formatted.' <em>If it's between emphasis-tags, it's 'tagged.'</em> Oh, and instructions to "Go to such-and-such, in such-and-such menu" are based on my 2003 version. The same functions will be there in your version, but might be accessed by a different method.
Let's start by listing what we want to achieve. We want to:
- Be able to type a fraction using as few extra keystrokes and mouse-clicks as possible (so that the writing process doesn't get bogged down in distracting formatting or tag-creation), whilst marking the phrase so that the macro has something unique to fractions to look for.
- Mark the phrase using nothing but formatting if possible, so that there's no tagging-gobbledegook to interfere with proof-reading.
- Specifically, we need to:
- Change the easily-typed forward-slash into the web-friendly ⁄.
- Tag the portion before the slash as (styled) superscript.
- Tag the portion after the slash as (styled) subscript.
- Remove the formatting we applied to mark the phrase (for reasons I'll get into later).
In other words, we want to change this: 23/3 into this:
<span class="superscript">23</span>⁄<span class="subscript">3</span>
(Or the inline equivalent for Word Press, but the above version is less cluttered, as an example.)
Well, the obvious formatting to use is super- and subscript (and if you already know how to do this, skip to the next paragraph); achieved using Ctrl+shift+= (the equals sign) and Ctrl+= respectively. So, in Word, type "(Ctrl+shift+=)23/(Ctrl+=)3(Ctrl+=)". Note the lack of spaces, except possibly at the end. The slash is just a plain, ordinary forward-slash, which you might notice we made superscript. There's a reason for that, which we'll get to.
You should now have something which looks like this: 23/3 .
Which, in theory, is all we need, in order to use a series of Find/Replace operations to add the tags we want. But we might have other super and subscript characters in the document, which need to be surrounded by <sup> and <sub> tags—exponentials or bases, maybe—so we don't want them to be targeted for inclusion in style-tags.
Okay then, we need a combination of formatting which is unique to fractions and other sub/sub strings which need style, not semantic, tagging. Seeing as fractions are something I type only once in a blue moon, let's, for mnemonic reasons, make the whole thing blue. I'm going to assume you know how to do that, Gentle Reader! So now we have a format-combination which should be—under most circumstances, at least—unique, in our document, to fractions. All the text we'll be finding and replacing will be both blue and sup/sub script. Neither plain-blue nor auto-coloured sup/sub will be affected. Just in case, though…
On a new line, repeat the process, but don't make it blue, and then under that do it again but make it blue only, with no sup/sub. These two lines will act as a check on what follows. If any changes you make via Find/Replace affect one of your check-examples, you've gone wrong somewhere; hit Ctrl+Z a few times to undo those changes, and try again.
So now you should have this:
First we're going to deal with the slash. Hit Ctrl+H to bring up the Find/Replace dialogue, click in the Find what box, type a forward-slash and click the More button, then the Format button which will appear below. Click Font to bring up the Find Font dialogue, and select Blue in the Font color section. Then click the Superscript item in the Effects list, so that its tick becomes not-greyed-out, and at the same time the Subscript tick disappears. The box should now look like this:
Click OK to get back to the main Find/Replace dialogue, click into the Replace with box and type ⁄ then go to Format→Font again. Remove the tick from the Superscript box, so that both it and the Subscript box are empty, then change the colour to Automatic (or whatever colour the main body of your text is in. Automatic is the default, even though it looks like black). Click OK. The Find/Replace dialogue should now look like this:
Click Replace all. What should happen is that the digits in the fraction will stay blue and sup/sub, whilst the slash should be replaced by ⁄ with no formatting, like this: 23⁄3 and the check-versions shouldn't be affected. If that's not what happened, you'll need to undo the changes, if any, that were made, and go through it again. (From experience, the most common fubar is that the formatting that was supposed to be applied to the Replace with box gets applied to the Find what box instead.)
Before we go on to tag the rest of the fraction, let's get what we've done recorded as a macro. Hit Ctrl+Z to undo the change we just made, so that the superscripted slash is back. Now go to Tools→Macro→Record new macro. Supply a name for it (no spaces or dashes) in the dialogue which appears, and click OK. A box will pop up with recording controls on it, and the cursor will gain a little picture of a cassette tape (how quaint!) to show that you're already recording:
Hit Ctrl+H again and re-run the Find/Replace operation you've just done (it'll probably still be in place in the dialogue box), then hit the stop button on the recording control; which will then disappear. You have just recorded a macro.
Undo the change again, and go to Tools→Macro→Macros. Make sure the one you've just created is selected, and click Run. Then swear. No, you didn't bugger up, I promise! (Remember the whinge I began this post with?) All three slashes will have been changed. You need to edit the macro. Fear not, Gentle Reader, for I shall be thy rod and thy staff!
Hit undo again, and go back into Tools→Macro→Macros. Select your recent creation again, but this time click the Edit button. A new window will open up, in the Visual Basic Editor. Don't worry about all the side-panes, folder-trees and stuff; the bit you want will be in the main window, looking (hopefully) like this:
Sub A_test() ' ' A_test Macro ' Macro recorded 01/05/2013 by daz ' Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "/" .Replacement.Text = "⁄" .Forward = True .Wrap = wdFindContinue .Format = True .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.Find.Execute Replace:=wdReplaceAll End Sub
Where my example says "A_test" yours will have whatever name you gave to the macro. And here's the reason it didn't work as planned. Looking at it, you can probably make out that it contains an instruction to replace "/" with "⁄". But nowhere does it mention superscript or the colour blue. I've already had my little moan about this, so I won't bore you again; let's just press on and fix it.
Before that, though, let's trim it down a bit. Trial-and-error has taught me that we can delete a few lines, leaving us with:
Sub A_test() ' ' A_test Macro ' Macro recorded 01/05/2013 by daz ' Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "/" .Replacement.Text = "⁄" .Forward = True .Wrap = wdFindContinue .Format = True End With Selection.Find.Execute Replace:=wdReplaceAll End Sub
And after that little bit of tidying up, let's get to that fixing I mentioned.
First we want to tell it to only look for blue superscripted text to replace. At the end of the line .Text = "/" hit enter to create a new line, and in it type:
.Font.Superscript = True
Then make another new line and type:
.Font.Color = wdColorBlue
And now we need to tell it to remove the formatting, so make a new line after .Replacement.Text = "⁄" and in it type:
.Replacement.Font.Superscript = False
Then make yet another new line and type:
.Replacement.Font.Color = wdColorAutomatic
So now your macro should now look like this:
Sub A_test() ' ' A_test Macro ' Macro recorded 01/05/2013 by daz ' Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "/" .Font.Superscript = True .Font.Color = wdColorBlue .Replacement.Text = "⁄" .Replacement.Font.Superscript = False .Replacement.Font.Color = wdColorAutomatic .Forward = True .Wrap = wdFindContinue .Format = True End With Selection.Find.Execute Replace:=wdReplaceAll End Sub
In VBE's File menu, click Save normal, and then minimise (don't close) the VBE window. Back in your Word document, try running the macro again. This time it should work as planned, and do exactly what our original Find/Replace operation did. Except that operation is now permanently available at a mere few mouse-clicks. But, of course, we've only done a third of the entire operation we set out to do, and you may be forgiven for feeling that there's still a long way to go. Far from it! Now that we have the basics in place, we can positively zoom through the rest.
Hit undo again, in Word.
Bring VBE back up, and copy everything from the beginning of the line Selection.Find.ClearFormatting to the end of the line Selection.Find.Execute Replace:=wdReplaceAll. Make a new line after wdReplaceAll and paste what you've just copied. All the formatting instructions are already in place, so all we need to do is change the terms specifying what is being replaced with what, in the second half of the now doubled-in-length macro.
Delete the slash from between the two quotes in the bit you just pasted, so that it looks like this:
Text = ""
Note that we leave no gap between the quotes. If we did, it would look for blue, superscripted spaces to replace. As it is now, it'll look for any text formatted in that manner. (Which is why we got rid of the superscript formatting on the slash first. It had served its purpose in marking out any slashes which needed to be changed to fraction-slashes, and we don't want it to be included in the superscript tag.) Now we have to tell the macro what to replace that blue superscript with.
Change the .Replacement.Text = "⁄" line to read like this, if you're using CSS classes:
.Replacement.Text = "<span class=""superscript"">^&</span>"
Or this, if you need to use in-line styles, as in the free version of Word Press:
.Replacement.Text = "<span style=""font-size:.7em; vertical-align:super;"">^&</span>"
Just a couple of things to note, here. The ^& is an instruction to "place found-text here." Think of it as cutting a selection, typing the first of the surrounding tags, pasting the cut selection back in, then typing the closing-tag. The other thing to note is that anywhere we want the macro to place a double-quote, we have to place two double-quotes side by side in the editor.
Okay, now for the subscript. Hopefully you're ahead of me here; copy everything from the beginning of the second Selection.Find.ClearFormatting line (the one you just pasted in) to the end of the second Selection.Find.Execute Replace:=wdReplaceAll line. Make a new line after that wdReplaceAll and paste what you've just copied.
And this time it's even easier. Change the line after Text = "" so that it's looking for Subscript. In the CSS-class version, change the replacement-text superscript to subscript, and in the in-line version, change super to sub. In the .Replacement.Font.Superscript = False line, change Superscript into Subscript.
Job done! Save the macro and check, in Word, that it's working.
But let's be completist about this while we're here, shall we? It's possible you'll have "normal" sup/sub-formatted text-strings in the document, which you'll want to render in <sup> and <sub> tags. Exponentials and bases, for instance. These won't have been affected by what we've already done, because you'll (hopefully!) have formatted them as sup/sub but not blue. It'd be nice to include them in this macro too, though. The more birds we can get with this one stone the better, an' all that jazz. (And remember that the auto-coloured check-example in your test document will be affected by this.)
Okay, do the copy/new line/paste thing again. Delete these lines from the newly-pasted bit:
.Font.Color = wdColorBlue
.Replacement.Font.Color = wdColorAutomatic
You're probably getting the hang of this by now, but I'll explain anyway. We had to delete the first, because the only formatted superscript left in the document won't be blue, and the second isn't needed anymore—it wouldn't change anything so we could have left it in, but it would be unwanted clutter.
In this latest-pasted instance, leave the Text = "" line alone.
Make sure the next line specifies Superscript.
Change the replacement-text to:
.Replacement.Text = "<sup>^&</sup>"
Make sure the .Replacement.Font.… line specifies Superscript.
And repeat from "Okay, do the copy/new line/paste thing," above, this time making sure it's searching for Subscript and change the replacement-text to:
.Replacement.Text = "<sub>^&</sub>"
Change the .Replacement.Font.… line so that it specifies Subscript.
Your completed macro should look like this:
Sub A_test() ' ' A_test Macro ' Macro recorded 01/05/2013 by daz ' Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "/" .Font.Superscript = True .Font.Color = wdColorBlue .Replacement.Text = "⁄" .Replacement.Font.Superscript = False .Replacement.Font.Color = wdColorAutomatic .Forward = True .Wrap = wdFindContinue .Format = True End With Selection.Find.Execute Replace:=wdReplaceAll Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "" .Font.Superscript = True .Font.Color = wdColorBlue .Replacement.Text = "<span class=""superscript"">^&</span>" .Replacement.Font.Superscript = False .Replacement.Font.Color = wdColorAutomatic .Forward = True .Wrap = wdFindContinue .Format = True End With Selection.Find.Execute Replace:=wdReplaceAll Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "" .Font.Subscript = True .Font.Color = wdColorBlue .Replacement.Text = "<span class=""subscript"">^&</span>" .Replacement.Font.Subscript = False .Replacement.Font.Color = wdColorAutomatic .Forward = True .Wrap = wdFindContinue .Format = True End With Selection.Find.Execute Replace:=wdReplaceAll Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "" .Font.Superscript = True .Replacement.Text = "<sup>^&</sup>" .Replacement.Font.Superscript = False .Forward = True .Wrap = wdFindContinue .Format = True End With Selection.Find.Execute Replace:=wdReplaceAll Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "" .Font.Subscript = True .Replacement.Text = "<sub>^&</sub>" .Replacement.Font.Subscript = False .Forward = True .Wrap = wdFindContinue .Format = True End With Selection.Find.Execute Replace:=wdReplaceAll End Sub
And that really is it. We can now fill a document with as many fractions, superscripts and subscripts as we like, and be able to proof-read with ease, then apply the correct tags for all of them with just a few mouse-clicks. (And at this point I would rename the macro by changing the two instances of "A_test" to something like "Sub_Sup_Fraction_to_HTML".)
As regards applying similar techniques to perform other jobs, I hope I've shown that—even though we shouldn't need to do it—editing a basic find/replace macro in M/S Office to include and alter formatting really isn't as scary as you might think it is. I also hope I've made the point that the order in which we instruct the macro to perform its various operations makes a difference. Think what would have happened for instance, if we'd not done the fraction-slash first, and removed its superscript formatting. Take a look at this macro, which is an instruction to run three other macros:
Sub A_Temp() ' ' A_Temp Macro ' Macro recorded 01/05/2013 by daz ' Application.Run MacroName:="Blog_Para" Application.Run MacroName:="Sub_Sup_Fraction_to_HTML" Application.Run MacroName:="Blue_Text_to_HTML_Span" End Sub
The first surrounds every paragraph with <p> … </p> tags. [See here for how to do that.] The second is the one we just created. The third surrounds all blue text it finds with <span style="color:blue"> … </span> which, as you can see, tells the browser to display the enclosed text in blue. We included in our macro an instruction to reformat the blue we'd used as a marker as "Automatic" so our fractions won't be picked out by that third macro. If I'd written the above macro with the last two commands swapped, however, it would—because it works, like all macros, from top to bottom—have surrounded all our fractions with that span-tag, and they'd have all displayed in blue font when the document was pasted into a web-page. Ordering matters.
I hope I've also at least hinted that the Find/Replace dialogue can be a much more useful tool than merely a method of changing a recurrent spelling-mistake or the like. It's worth taking a few minutes to look through the Format and Special menus which become available when you've clicked the More button. Type some random stuff into a document and just play around; it's amazing what you can learn by a little experimentation!
PS: Okay, that red/blue r/k thing I mentioned, back near the beginning. If you're just itching to know how:
- Find a character or pair of characters which doesn't appear in the document. "£$" is always a good bet.
- Using Find/Replace, replace all red 'r' characters with r£$.
- Replace all blue 'k' characters with £$k.
- Search for r£$£$k with no formatting specified, and replace with a colour not used in the document. Pink, say.
- Delete all instances of £$ by replacing all £$ while leaving the Replace with box empty.
- Search for pink rk and perform whatever operation on them you had in mind.
- Replace all pink 'r' characters with red, and all pink 'k' characters with blue.
And the HMS Victory macro:
Sub Victory() ' ' Victory Macro ' Macro recorded 01/05/2013 by daz ' Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "HMS Victory" .Replacement.Text = "^&" .Replacement.Font.Italic = True .Forward = True .Wrap = wdFindContinue .Format = True End With Selection.Find.Execute Replace:=wdReplaceAll Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .Text = "HMS " .Font.Italic = True .Replacement.Text = "^&" .Replacement.Font.Italic = False .Forward = True .Wrap = wdFindContinue .Format = True End With Selection.Find.Execute Replace:=wdReplaceAll End Sub
You may use these HTML tags in comments
<a href="" title=""></a> <abbr title=""></abbr>
<acronym title=""></acronym> <blockquote></blockquote> <del></del>* <strike></strike>† <em></em>* <i></i>† <strong></strong>* <b></b>†
* is generally preferred over †