Macro hints and other tricks

This was drafted back in 2005, but never actually saw the light of day, because it was too hard to clarify, and I was busy. I have now revived it to help people to make sense of the talk I plan to give on July 5. Use it as a source of practical solutions which can be pillaged.

Using Macros in Word, and other tricks

Some years back, I mentioned on a list that there are many things that can be done with macros. I was asked to explain in more detail and I ducked, being a bit busy, but the time has come to actually talk to the issue, because there seems to be a bit of a lack of good background on the tricks of the trade.

I am going to start several steps back, because the effective use of macros works best if you have set the document up in a particular way, which brings us to styles. You don't have to do it my way, but if you are going to follow my discussion later on, it relates to how I use a particular setup.

I plan to discuss ways of improving productivity by spending a short period to get things right, and as well as macros, I will talk about autocorrect, which can be another useful tool. You will find that this relates closely to what I do (and why). My aim is to introduce you to some wrinkles that you can sift through for the ones that fit you. The adept will adapt and adopt, I hope.

Styles

I only use Times New Roman: I can change that if I need to for a special purpose, but I like to keep stuff plain and simple. Almost everything I do uses 4 levels of heading (18 point, 16 point, 14 point and 12 point bold), a Normal (no indents, 12 point, left-justified) and Normal indent, which is like Normal, but with the first line indented by 1 cm.

Styles can be set up to specify what the next paragraph will be: when you hit 'Enter' from one of my Headings, the next paragraph is automatically normal, and when I hit enter again, the paragraph after is Normal indent, as are the paragraphs that follow that. Eventually, I add another heading, and the cascade starts again.

For special cases, I may add a style called qquote, which is 10 point Times New Roman, justified, indented 1cm each side, with a box around it. The paragraph that follows is Normal again - this being the usual print convention.

You set up styles by going to Format - style and choosing a style to modify. If you are unfamiliar with this, it is worth doing carefully. To make them permanent, you need to open the file normal.dot (it is a good idea to make a back-up copy first!) which is a template - then modify the styles and save the result.

Getting creative with styles

This is a bit of a side-issue, but I have spent a number of years in my life working rather closely with multiple choice questions, and this led me to consider the time wasted in getting multiple choice questions to come out just right. That was when I had the bright idea of creating styles called Stem (the question part) and OptionA, OptionB, OptionC and OptionD.

As you will see later, these can be used in a macro that saves lots of time, but let's just look at the styles for now.

The style that is specified to follow Stem is OptionA, and the style after that is OptionB and so on. The style after OptionD is Stem once more. OptionD is different from the others, since it leaves a larger spacing after it (12 points instead of 3 points), and unlike the others, is not flagged with "keep with next", a neat little option that says in effect "keep all of a set of paragraphs on the same page" - so the questions never split over two pages.

The main part of this trick is that, having established the style for the first line, all of the rest is automatic. Now let's look at some of the handy things you need to know.

Did you know?

Some of the tips here do not relate directly to my topic, but were thrown in because I happened to think of them. If you are creative, you may see a way to use them. This is my essay, and I will amble and ramble as I please. Walk along with me!

Carriage return, tabs and other specials

You can search for a carriage return, and end of paragraph marker, by telling the Word program to look for ^p, while a tab is ^t and a soft return is ^l. (By the way, 'carriage return' is one of those leftovers from the era of the electric typewriter and the line printer, a character that required the system to perform a line feed, down one line, and back to the start of the line. A soft return is Shift-Enter, and it does not start a new paragraph. See the section on sorting for an application of this.

To see what else you can search for, click on Edit - More - Special to see a drop-down menu of things you can search for. Note that when I give you a sequence like that, we begin with the top menu bar, and after that, you need to look for a tab or a button that leads you to the next stage. If you are going to make Word work for you, you need to have explored most of these.

While you are in the area, click on Edit - More - Format to see how you can select only (say) for a word when it is in italics, or in a heading. Also, look over on the left to see that you can specify matching case, whole words only, and wildcards. I have never used "Sounds like" or "Find all word forms", so you are on your own there.

One key choice that you have in the extended search and replace dialogue box is to search up, search down or search all. This is of less use in a macro, but it can be very handy indeed.

Sorting

The Table - Sort function is excellent when you have a whole range of, say, dictionary definitions, because it sorts paragraphs (usually alphabetically). There is a catch, though, if you have the title on a separate line. The solution is to use soft returns, achieved with Shift-Enter, so that the whole lump of text remains a simple paragraph. Later, when the sorting is done, and you have saved the text, you can use Edit - Replace to get rid of the soft returns (^l), replacing them with carriage returns (^p).

I usually put a # on a separate line as an end-of-entry marker when I do this. It can be deleted later, but if I want to, I can create a macro that finds the # character, moves to the next paragraph (the title of the entry) and formats it, say, to Heading 4. You have to set that up first, and that is why every system needs to be thought about before you begin.

Autocorrect

Because I write a lot of historical material, I often need the symbol, and it is a nuisance recalling the code that generates it: I will get to those later. Because people in the US refer to the # symbol as 'pound' sometimes, I have an Autocorrect setting that converts the string #$ to .

This text was written in Word. Given that, you may wonder why #$ was not converted to here: the answer is that it was, but pressing backspace once undoes the autocorrection. You do need to think through the Autocorrects that you use, just in case something else, where you wanted that string to stand, gets converted.

For example, I created an Autocorrect to change dC to C, but there is no case sensitivity here. That meant I got into trouble with DC current and Washington DC, so I made degC the trigger, though mostly, I just use a macro that inserts a symbol: I will come to that later.

As far as I am concerned, I would only ever type NEJM when I meant the New England Journal of Medicine (I write about science, OK?), so I am happy to have the autocorrect change NEJM to New England Journal of Medicine.

To make that happen, I type the phrase I want to see appear, italicise it, highlight it, and go to Tools - Autocorrect, when a dialogue box comes up, with the completed phrase on the right. Then I add the letters NEJM to the left-hand side and click on the button for formatted text, and it is all ready to go.

This is useful for things like company names as well as schools and the like. Just think of the long phrases that you do all the time, like your name and your address, and set them up as autocorrects.

Another application of autocorrect is to handle El Nio and So Tom: at one stage, when I was wringing about sugar and slavery, some years back, I set up Autocorrect to deal with the special characters in So Tom, and I discuss El Nio often enough to have the tilde added to the second n as a matter of course.

Autotype

If you happen to write about hominin fossils a lot, you can use either Autocorrect or Autotext. I have added Sahelanthropus tchadensis to my Autotext, and I need only type the first four characters. When it comes to Australopithecus species, though, I have a number to deal with, all with the same genus name, so I need to use Autocorrect codes like A.afa and A.afr for Australopithecus afarensis and Australopithecus africanus respectively.

Codes for special characters

There are two systems of coding for special characters using the ALT key and the numbers on the numeric pad. One set is ALT-xxx, the second is ALT-0xxx. These are not to be confused with each other: ALT-129 is while ALT-0129 is which is pretty useless.

Note that you need to hold down the ALT key, type three or four numbers on the numeric pad (really annoying when you have a laptop!!) and then let go the ALT key.

You can also do special characters by using Insert - Symbol and selecting the appropriate character. If you look closely when you mouse over the character you want, you will often see that there is a keyboard shortcut. Where I create by typing ALT-164, I can insert it from the symbol menu which says that I can also do Control~ then letting go and typing n (note that ~ is a shifted character, so you need to hold down the shift key as well - fiddle with this one, because it also gives you and , if you ever need them. The menu also tells me to get by typing ALT-0163.

Suppose I want a degree symbol. This is , which I can get with Control-Shift-2, followed by a space, but I have always found that too hard to recall, so I have a macro, coded by Control-Shift-D, which does it for me.

Word count

My toolbar contains a lot of extras, but the most valuable is Word count, which tells me both the number of characters in a selection and the number of words in either the selection or the whole document. To add things like this, go to Tools - Customize - Command and explore, understanding that you can drag interesting commands up onto the tool-bar. Word count is found in the Tools sub-menu. Explore!

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 will 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 (unformatted), 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.


Related files:

Spreadsheet tricks used in Pioneers, Heroes and Fools (more about the conversion process);
The explorers' database that resulted (a zipped file that you will need to unzip;
My handy set of resources, created for the same talk that this was created for.
This file is http://members.ozemail.com.au/~macinnis/writing/macros.htm

It was created on June 30, 2011 and last revised on June 30, 2011


If you email me at macinnis at ozemail.com.au, you will reach a spam trap, but be read, eventually. If you put my first name in front of that address, you will reach me direct. This low-tech solution is to make email harvesting difficult.

The home page of this set is here.


Since I started this site, it has drawn visitors.