An American Editor

May 15, 2013

The Only Thing We Have to Fear: Wildcard Macros

Whenever I talk to colleagues about macros, it is as if a funereal pall has enclosed us. My colleagues, generally, tell me that they cannot write macros, that it is much too complicated, especially wildcard macros.

If I ask if they ever use Word’s Find & Replace, they all admit that, yes, they do. “Congratulations,” I say, “because each time you use Find & Replace, you have written a macro! You just haven’t recorded it.”

The only thing we have to fear about macros is our fear of macros.

I suppose, technically, Find & Replace is not macro writing, but truly, a macro is just a way to find some sequence and do something to that sequence — be it bold the sequence, highlight it, replace it with another sequence, delete it, whatever.

Most everyone who uses Microsoft Word has recorded a keyboard macro. Word makes doing so very easy. Again, congratulations if you have written a keyboard macro, because you are on your way to macro wizardship.

There is a key to writing macros. It is a secret that macro wizards rarely share, but I’m going to share with you. The secret is wrapped up in a single magical word: analysis. Analysis of what you need a macro to do is the key to writing a macro. Sure you need to have some arcane language (what good is wizardry without arcane language all its own?) and all of the arcane language you need to write the macros can be found in Jack Lyon’s Macro Cookbook for Microsoft Word and in Wildcards in MS Word Macros, which is a compilation of information on wildcards that Jack Lyon wrote for his blog years ago and which you can download for free by clicking the title-link. Alternatively, you can use the Wildcard Find & Replace Macro found in EditTools to “write” the macros for you, but analysis is the real key to writing macros.

Consider this problem: You have a list of 100 references in which the styles are all over the place. Author names are often listed like this:

Arnold, J. H., K. L. Swift, and A.J.H. Archimedes.

but you need the author names to look like this:

Arnold JH, Swift KL, Archimedes AJH:

You can fix the names manually or by using macros. Manually will take nearly forever, so the better method is to use macros. Here is where analysis matters.

When I began using macros, I saw this problem and thought, “How can I write a macro to fix these author names?” My thinking was a single macro to take care of it all. I quickly discovered that a single macro can’t do the job, but a series of macros that can be combined into a single macro could. The key was series of macros, which meant that I needed to break the problem down into solvable (or macroable) parts.

The first part is Arnold, J. H., which I need to change to Arnold JH,. What I need to find is as follows:

  1. any mix of letters of varying length
  2. that is followed by a comma
  3. and a space
  4. a single uppercase letter
  5. followed by a period and a space
  6. a single uppercase letter
  7. followed by a period
  8. and a comma and a space

I need to replace the find list with

  1. the mix of letters found in 1
  2. the space found in 3
  3. the single uppercase letter found in 4
  4. the single uppercase letter found in 6
  5. and the comma and space found in 8

Note that what I no longer need is not included in the list of replace with items (i.e., find items 2, 5, and 7). Also note that, in analyzing what needs to be found, items that I no longer want are listed on their own lines in the find list.

If you are using Word’s Find & Replace dialog with Use Wildcards checked, you would manually enter the following Find string [paired parens represent the information on a single line in the find list, thus, ([A-z]@) represents line 1: any mix of letters of varying length]:

([A-z]@)(,)( )([A-Z]{1,1})(. )([A-Z]{1,1})(.)(, )

And the following Replace string (the backslash+number represents the corresponding find item, e.g., \1 represents line 1: any mix of letters of varying length and \8 represents line 8: a comma and a space):


I can hear you groan. But it isn’t as difficult as it appears. All of the information to write the strings is available in the downloadable Wildcards in MS Word Macros document (just click on the link).

If you are using the EditTools’ Wildcard Find & Replace Macro, you click buttons to make your selection and the code is written for you. An added feature with the Wildcard Find & Replace Macro is that you can save this find and replace so that you can reuse it in the future; with Microsoft’s Find & Replace, the strings cannot be saved. However, what I used to do before I created the Wildcard Find & Replace Macro — and recommend that you do — was keep a special Word document with these strings in it so I could copy and paste when needed in the future. I set up the file like this:

1. Change Arnold, J. H., to Arnold JH,
Find: ([A-z]@)(,)( )([A-Z]{1,1})(. )([A-Z]{1,1})(.)(, )
Replace: \1\3\4\6\8

Once you have entered the strings in either Microsoft’s Find & Replace dialog or in the Wildcard Find & Replace Macro, click Replace All and all author names that fit this particular format will be altered. Then move to the next series to analyze, which is to change K. L. Swift, to Swift KL,. In this instance, what I need to find is as follows:

  1. a single uppercase letter
  2. followed by a period and a space
  3. a single uppercase letter
  4. followed by a period
  5. and a space
  6. any mix of letters of varying length
  7. that is followed by a comma
  8. and a space

I need to replace the find list with:

  1. the mix of letters found in 6
  2. the space found in 8
  3. the single uppercase letter found in 1
  4. the single uppercase letter found in 3
  5. the comma found in 7
  6. and the space found in 5

What I no longer need is not included in the list of replace with items (i.e., find items 2 and 4). Also note that, in analyzing what needs to be found, items that I no longer want are listed on their own lines in the find list.

If you are using Word’s Find & Replace dialog with Use Wildcards checked, you would manually enter the following Find string:

([A-Z]{1,1})(. )([A-Z]{1,1})(.)( )([A-z]@)(,)( )

And the following Replace string:


I said that you can’t save the strings as a macro if you are using Word’s Find and Replace dialog. That is true as far as it goes, but it doesn’t go all that far. There is a way to save the strings as a true macro without using EditTools’ Wildcard Find & Replace Macro. What you do is record a simple Find and Replace macro, for example, find bush and replace it with blues, using Word’s Record Macro feature, and give it a name like WildcardAuthorCorrection1; be sure to keep a list of what that macro does (or will do once you edit it). (If you don’t know how to record a simple macro, the fastest and best way to learn is to use Jack Lyon’s Macro Cookbook for Microsoft Word. Within a few minutes you will be a master at recording simple macros and even at editing them.)

Open the newly recorded macro to edit it, and replace the .Text = bush entry with .Text = [your find string] and replace the .Replacement.Text = blues with .Replacement.Text = [your replace string]. Make sure all the items labeled as True are changed to False except change .MatchWildcards = False to .MatchWildcards = True.

Once you get hooked on macros, the possibilities are endless and you’ll never let go. More importantly, you will improve your editing speed, accuracy, and efficiency, which translates into a higher effective hourly rate and a more profitable editing business.

You’ve got nothing to fear — macros are conquerable!

January 2, 2012

The Professional Editor: Working Effectively Online VII – Macros Again

In prior posts, I talked about how effective online editing includes mastering macros. See, for example, The Professional Editor: Working Effectively Online IV — Mastering Macros. What wasn’t discussed is how to plan a macro.

For the simple macro, the macro that, for example, replaces two spaces with one space, not much planning is required; what is needed is fairly obvious. As a macro grows more complex, however, the difference between success and failure is often how much effort was placed in the planning of the macro. A well-planned macro nearly writes itself.

Consider most of the macros in the EditTools collection. These are complex macros that require multiple routines to accomplish designated goals and tasks. Because of their complexity, it is easy to get lost in the programming and thus not produce a usable macro.

Consequently, when planning a macro I use a decision-tree process. I also use Storyboard paper that I buy from Levenger because it helps me visualize what I need to do. More importantly, it breaks what I need to do down into manageable chunks.

I begin at the end of the process: I define what I want the macro to accomplish. I then try to define each step that will take me to that endpoint. I use the If…Then… process: If abc is found, Then do xyz, but If abc is not found, Then do pqr.

Using the storyboard, I make each If…Then… its own entry. In the blank box on the left, I write the If…Then…; on the lines to the right, I write the code, line by line to make the If…Then… happen. It takes at least a pair of boxes to make a single whole If…Then… phrase because the first is the found and the second is the not found. Sometimes more than one not found is required so a single If…Then… process may need more than a pair of boxes.

Note, however, that I am using the If…Then… concept as a substitute for a lot of possibilities. It should not be taken literally as an If…Then… in coding terms. It is simply a way of breaking the process down into manageable chunks.

Making these small blocks of code serves many purposes. To make them reusable, I also number them. The numbers are not used except as a way to cross-reference. If I have already written a chunk of code that will do what I need done in the next step, I simply refer to the block of code by number for later copy and paste.

The small chunks also serve a much more important purpose: they make it easier to figure out why something is not occurring as I intend. Plus they can be reused in other future macros — no sense reinventing the macro. And they make it much easier to rearrange a macro’s coding when I subsequently think of a better or more efficient way to accomplish a task.

Yet I can hear the question now: Why do on paper what you need to do online? Yes, it can be repetitive work to first do the coding on paper and then transfer the coding to online, but the process allows me to think twice about what I am doing and — definitely of more importance — coding online takes away the storyboard decision-making tree, thus making it harder to visualize the entire process or even how a small portion fits within the scheme of the macro. A little extra work now often saves a lot of extra work later.

If you look at the Storyboard paper, you see the box at the left in which I place my decision-tree information. That information serves much the same purpose as inserting a comment into a macro. But on the Storyboard I can readily see what comes before and what comes after the block on which I am working, which can be difficult to do onscreen.

No matter what method you ultimately choose, you need to have a decision-tree method at hand so as to avoid missing important steps in the macro process or leaving out things you want the macro to do depending on what is found or not found. If you use or have available Microsoft PowerPoint (or a similar program), you can use it to create an onscreen storyboard. I have tried it, but found it too cumbersome for me; at heart, for these kinds of tasks, I’m still a creature of habit and my generation and paper and pencil seem to work best.

The key is, however, to plan your macro. Even if the coding is beyond your capabilities and so you intend to hire someone to code the macro for you, having a decision tree that can be given to the coder will reduce your costs and ensure that the coder understands what you want. The coder may have suggestions for improvement, but the decision tree ensures that everyone is on the same page.

The decision tree can also make your learning to code complex macros easier and quicker. Combine it with Jack Lyon’s Macro Cookbook for Microsoft Word, and you will discover that as you learn to code a small portion of a macro you are mastering macros. You will also find a greater sense of accomplishment as one coding success follows another in logical sequence.

The combination of the decision-tree process and the Macro Cookbook is a sure way to master the macro process a professional editor needs. Remember that the more efficiently you can edit the more money you can make over the long-term. The biggest failing professional editors have is the inability to get beyond the short-term outlook. Taking on the challenge of mastering macros will help you extend your outlook to the longer term.

Create a free website or blog at

%d bloggers like this: