Macro tricks in Word, part 2

This completes a series that started with a previous series that began with Effective Science Writing Part 1.  The other three parts of this second series are listed at the end of this entry.

I will now return to less brain-hurting stuff in the next few posts, because I am currently in pursuit of bushrangers, rocks and crash remedies and all my hurt brain facilities are overloaded.

Creating a macro

A macro is simply a collection of recorded key strokes that can be run again, using a shortcut. These are created in a form of Visual Basic that looks quite worrying if you try editing a macro, so don't bother (yet), just be aware that you can create and record complicated sequences without knowing anything at all about how they are coded.

The starting point is Tools - Macro - Record New Macro, but before you get to that stage, you need to know what you will name it, so you need to do some experimenting to see what macros already exist for you, created by the Microsoft boffins. If there is a standard shortcut like Control-C for copy, and you overwrite it, that can be really annoying.

Macro ideas

The first thing to be said is that it is hard to keep track of the macros and what they are called. It is also important to know that some of the special key commands like Control-C, Control-V, and Control-X, (copy, paste and cut highlighted text, respectively) are valuable. Word is set up with those (effective) macros ready to run, and you need to experiment to see if you need Control-I, Control-B, or Control-U, which convert highlighted text to italics, bold and underline.

I give every macro a name that makes sense and also tells me what keystrokes bring it into operation. Macro names cannot have spaces in them, so I capitalise every word when it starts.

Note that macros can call macros, so if you build up a good library, you can wreak major havoc - until you know what a new macro does, always save your work before you test it out!

Paste unformatted text, PasteUnformattedCtrlU-my only failure

From time to time, you will get text such as HTML or fields and if you insert a table of contents into a document, the table is not real text, which can be a nuisance in some cases. The same thing applies if you are using Excel, and later want to paste material into Word: if you hit Edit-Paste Special-Unformatted text, you can avoid this, but I have never yet been able to make a macro to do this. If any reader succeeds, please tell me how you did it!

Note well: If you copy a text cell from Excel use Control-V, you will end up with the text in a table which then has to be converted.

DegreeSymbolCtrlShftD

As mentioned above, this character is something I need all the time, so I have a macro to do it. All the macro does is produce the appropriate degree character ° (of course, if you hold down the Alt key and type 0176 on the numeric pad, that does the same thing).

Multiple operations

There are times when I need to do the same thing, many times over: it may be as simple as finding all the carriage returns that are Heading 4 and converting them to Heading 3. You can do that with a simple replace command, but suppose you want to autonumber the header paragraphs (using Insert - Field - Autonum): in that case, you need a macro. First, you search for the next instance of Heading 4, change the paragraph to Heading 3, hit Home to get to the start of the line, and then insert the field.

In all probability, this is not the sort of macro you will need very often, so I save things like that as Temp1, for which the code is Control-Shift-Z. I also have (and in spite of their names, they are permanent), Temps, Temp3 and Temp4. These are coded by Control-Shift-X, Control-Shift-C, and Control-Shift-V. Look at the keyboard to work out my logic here.

The next bit is where it gets interesting: Temp2 is merely ten repetitions on Temp1 (whatever that may be), Temp3 is ten repeats of Temp2, and Temp4 is ten of Temp3, so when I hit Control-Shift-V, I create a thousand repetitions of Temp1.

Then, if I want to run some existing macro a thousand times, I just create a new Temp1 which is just a single instance of that macro.

Jumping in and out of Excel

Call me weird if you will, but I have written a whole book in Excel. Not on Excel, but in Excel. There was a reason for doing this: it was an illustrated children's book on reef life, and I needed to offer species lists for each page for the artist. Once I had created a record on a single line for a fish that was on page 5, complete with references to where the artist would find models or photos, I could make extra copies of that line and assign one of the lines to page 9, and another to page 14, where the same fish was also to appear. Other lines had the text for the page, and so on.

I knew, of course, that one of the earliest users of Visicalc in Sydney was a 'Sun' sporting journalist who used the program to create stories, and when I heard the yarn, I guessed straight away what he was doing. The old tradition of hot-metal print was the use of 'copy-paper', octavo sheets on which a single paragraph was typed, double-spaced. At the end, the pages were numbered and passed to a sub-editor to be marked up for setting.

Now as you do a story on copy paper, you often move a paragraph-page to a new place, and I suspect that this cunning journo was adding numbers in a separate column, and then sorting on that column before he printed out the story and handed it over. Whether he was doing that or not, I thought it a good idea, and I decided to adapt it, more or less.

When I set out to research a new book, I use a spreadsheet at the start with three columns: ch (chapter), pt (part) and ss (section). Then I add columns like Name, Location, Date, Keywords, Text and Source. Any text which is lifted directly from another source goes in as italics, to remind me not to use it as mine (failing to recognise direct quotes as such gets many writers into trouble). I add other codes that seem appropriate.

At any stage, I can sort my entries by any three of the headings I have assigned, including ch, pt and ss. In any chapter, values of zero for pt and ss ensure that the synopsis comes at the start, but I can also sort by name, and see that two of the Blamey references in chapter 9 really belong in chapter 3, and so on.

Complex? One book that I wrote was based on a set of 5000 selections from journals of the explorers, some gleaned from Project Gutenberg, some from obscure learned journals, some from microfilm, typed into my Palm Pilot with a portable keyboard, then transferred to Word, with other details pasted in using tabs between fields before going into Excel. The total is about a million words, the book will be about 70,000, but along the way, I needed to see what different writers have said about crocodiles.

Problem: the early writers used 'alligators', or even 'alegators' or 'gavials', so how do I pull all of those out. The answer was to use keywords, but keywords can go wrong, so I created a set of mnemonic codes, where z indicates animals, d indicates dangerous and c is crocodiles. All I need do is add the code \zdc to tag it as one of the 54 croc-related incidents. (The logic here: if I type the descriptors in full, that is onerous, and some will be wrong, which means they won't show up in searches. But if I use short mnemonics, I should avoid much of that. Not all, but much of it.)

Later, though, I wanted to convert all of those codes to real words that ordinary people can interpret, and that gave me a set of problems. It can be done with macros, but with 220 codes, it would be a big job.

That was when I began generating Word macros in Excel. Hold onto your seats, because now the ride gets a bit bumpy. We start with a piece of code from inside one of the macros that I use to convert the keyword codes:

With Selection.Find
.Text = "\enfm"
.Replacement.Text = "female explorers, "
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

All this does is to look through for instances of a particular code, and replace them. In this case, \enfm is replaced by the phrase 'female explorers', with a comma and a space  added, the bits in bold if you are reading this in Word. All the blue text (in the example above) is a constant. (As a side note, there were more female explorers than we encounter in the history books, but to find out about that, you may need to read my book. It's called Australia's Pioneers, Heroes and Fools, Pier 9.)

The careful observer may be wondering why I am talking about this Excel stuff when I ought to be talking about Word. The answer is that I couldn't get a Macro to work in Excel, so I port an entire column across to Word, alter it there, and then transfer it back. I could have just typed all the keywords, but sometimes I want to change a phrase, and universal search and exchange can be a pain.

I thought there would be some way that I could use a look-up table in Excel or somewhere to go through all of the permutations, but that didn't work. I could have created my macros by laboriously typing in all of the codes and their meanings, but that failed to attract. I turned instead to a lovely Excel command, =CONCATENATE.

This is used to chain strings together, bits of text. Each complete macro has five bits: a fixed start, a first variable, a linking bit, a second variable, and all of the end material. All I need to do is add those three constant sections to a spreadsheet in which the two columns, text and replacement, are given.

Whoops, not quite. Those pesky carriage returns are about to get in the way. I need to take out the line breaks and replace them later, so now I look at the macro like this:

With Selection.Find# .Text = "
\enfm "# .Replacement.Text = "
female explorers,
"# .Forward = True# .Wrap = wdFindContinue# .Format = False# .MatchCase = False# .MatchWholeWord = False# .MatchWildcards = False# .MatchSoundsLike = False# .MatchAllWordForms = False#End With#Selection.Find.Execute Replace:=wdReplaceAll

Then I just delete the two variables and because there are 293 characters in he last string, which Excel may gag on, I will chop it into two bits, just in case. I take a new worksheet, paste the codes and their translations into columns A and B, paste the fixed strings into cells F1, F2, F3 and F4.

Then in cell C1, I have 

=CONCATENATE(F$1&A1&F$2&B1&F$3&F$4): note the lack of spaces, and note the $ symbol, which makes that an absolute address. When I copy this into C2, it will read 

=CONCATENATE(F$1&A2&F$2&B2&F$3&F$4), and this will continue all the way down. I copy C1 all the way down to C220

Now I can grab column C and paste it into Word (it has to be pasted as unformatted text, so you must use Paste Special), and I can replace each # with a carriage return.

Now I have all of the codes I need to convert all of my keywords.

No I haven't, not quite - I omitted to allow for the fact that I have codes \zd (dangerous animals) and \zdc (dangerous animals: crocodiles). I need to change all the cases of \zdc before I attack the cases of \zd. Not a problem: in column D, I add =LEN(A1) and copy that all the way down. Now I select the 220 rows, click on Data- Sort, and I choose to sort by column D in Descending order. Now the macro will come to and deal with \zdc before it converts \zd.

One thing I discovered is that there seems to be a limit for the size of each macros, possibly a limit of around 32768 characters, but I decided to put just 60 conversions in each macro. That raises the question: how do I insert them?

My solution is a kludge, but it works I create KeywordConv1CtrlShG, KeywordConv2CtrlShH, KeywordConv3CtrlShJ, and KeywordConv4CtrlShK, each of which converts <\enfm> to (leave out the angle brackets). Now I have dummies that I can work on.

I go to the Word file that has the codes, and I select the first 60 (I look at the spreadsheet to see where the cut goes). I CUT these, then I go to Tools - Macro - Macros (or ALT-F8) and I choose Edit and scroll down to KeywordConv1CtrlShG and click the Edit button. Then I carefully select the target portion, the same bit that I quoted above, and click on Control-V to paste in the material that I cut before. And voila! a neat and tidy macro.

I suggest reading that three times to get it right. OK, maybe four: it's worth it, because this can save you significant effort.

Running the keywords macro

We aren't finished yet: we need to run the macros in order, but I often forget where I am at, so I have created a super macro, KeywordsAllCtrlShL, which simply runs the four conversion macros. Then having pasted the spreadsheet column over and having run the macro, each paragraph ends in a comma and a space.

Remember that we can search for a carriage return? I can target the ends by replacing ,<^p> with <^p> - leave out the angle brackets, of course.

Acknowledgements

My thanks to Bryn Jones who reminded me to mention the use of abbreviations that are converted by Autocorrect.

The other bits

Planning a book with a spreadsheet;

Practical use of macros and spreadsheets; and

Macro tricks in Word, part 1.