We use a few dozen macros regularly for editing in Word and for working in Excel and PowerPoint. Most of these have either been hand coded by Michael or are modified versions of others’ macros. As a service to the writing and editing community, we will be providing some of these macros here, along with links to any related blog posts. If you have any feedback, please feel free to drop us a note.
Disclaimer: We are providing these macros and associated information without a warrantee of any kind. Users assume all responsibility for use of these macros. Always make sure that you understand how a macro functions before using it. Test each macro before running it on an important document, and always make sure that your Normal.dotm template and the document a macro is run on are backed up properly.
Copy Words to Style Sheet (CopyWordstoStyleSheet)
This macro, based on the simple StyleThat macro written by Hilary Powers and a later adapted version of that macro by Jack M. Lyon, copies a selection from one Word document to another and provides some formatting appropriate for a style sheet. A blog post discussing an earlier version of this macro is available here.
- It operates if two and only two Word documents are open.
- If it finds a heading consisting of “Word List” (the value of the variable “sSectionTitle”) it places the selection directly after the heading paragraph. You can create different versions of this macro with different sSectionTitle values to paste directly into different sections.
- If it finds no matching heading, it pastes the content at the end of the document.
- All formatting of the pasted content is removed, but if the entire selection is in italics, this formatting will be restored.
- The macro inserts brackets after the pasted content and places the cursor between them, to simplify adding a description.
- Running the macro a second time (with nothing selected) returns focus back to the source document.
Please note: I use a highly customized version of this macro tailored to my specific style sheet and am not currently maintaining this code, so please report any bugs you find.
Count Words by Chapter (CountChapterWords)
This macro identifies chapter names, starting page numbers, and word counts from a manuscript based on the chapter title heading style. It then creates a new Word document containing a summary. A blog post discussing an earlier version of this macro is available here.
- If text has been selected when the macro is run, its paragraph style name will be extracted as the heading style the macro will use. If text has not been selected the user is prompted to enter a style name.
- Data about the source file and export process will appear in the header of the report (created by code from the macro ExtractCommentsToNewDoc by Lene Fredborg).
- The word count reflects visible content, so the number for a document with tracked changes will vary based on the visibility of the changes.
- An entry of “[Before first heading]” will appear if the document does not start with a chapter heading.
- An entry of “[No chapter title]” will appear if the title of a section is just a hard return character. Content such as page and section breaks formatted with the selected style will appear in the final report, possibly leading to formatting issues.
- Tabs appearing in chapter titles are replaced with spaces to facilitate report formatting.
- This macro uses the subroutine ClearFindAndReplaceParameters (included), which is necessary for it to run properly.
Extract Comments For Read Aloud (ExtractCommentsForReadAloud)
This macro, based on ExtractCommentsToNewDoc by Lene Freborg, is tailored to allow Word’s Read Aloud feature to be used to review text from comments. (Using Read Aloud for proofreading is a handy technique for identifying easily overlooked errors.)
- Only the page number and comment text are included.
- The display text for any URL beginning with “http” is shortened to “[URL Replaced]” but the link is maintained.
- Hard returns within comments are replaced by soft returns for the purpose of formatting.
- Page numbers reflect the pagination with tracked changes not visible.
- This macro has not been tested with Word’s Modern Comments “feature” and will not be tested in the foreseeable future.
Find First Occurrence (FindFirstOccurrence)
This macro takes a selection from one Word document (for example, a style sheet) and looks for its first appearance in the body of a second document (for example, a manuscript). It is based on the StyleThat macro written by Hilary Powers and a later adapted version of that macro by Jack M. Lyon.
- This macro will only function properly if two Word documents are open.
- This macro also puts the selected term on the clipboard to facilitate searching for other appearances of the term.
Highlight All Term Appearances (HighlightTermsBody)
When a word or phrase is selected and this macro is run, it will highlight in light gray all whole-word, identically capitalized instances of that content in the body of the document.
- Formatting such as italics is ignored.
- The macro will not necessarily perform as expected with content that has capitalization applied via a Word style or using Word’s Change Case tool or with content containing tracked insertions or deletions.
- Only content in the current Word “story” will be highlighted; highlighting does not take place in headers and footers, end notes or footnotes, text boxes, or other portions of the document.
- If nothing is selected, the macro will select the word that the cursor is currently placed within or adjacent to. The macro also contains commented-out code that can be substituted that will instead alert you if the selection is empty.
Highlight Selection (HighlightWithColor)
This suite of macros highlights the selection in one of a range of colors, with the option of turning tracking on or off on a color-by-color basis. If you do not need to control tracking, a much shorter macro can provide highlighting in a specific color, though it will not provide significantly better performance.
- The code contains six macros to apply highlighting in cyan, green, pink, red, yellow, and no color. Each of these macros passes values to the HighlightWithColor subroutine, which does the actual highlighting and must be present.
- To highlight in additional colors, replicate one of the macros, changing the name and the WdColorIndex enumeration value.
- By default the macros base tracking on the current Track Changes setting. However, if the “Tracking” parameter in a macro is changed to “True” highlighting in that color will always be tracked. If set to “False” highlighting in that color will never be tracked.
- If no selection exists when the macro is run, the word the cursor is within or adjacent to will be selected.
Trim Spaces in Excel (TrimLeadingAndTrailingSpaces)
This Excel macro trims leading and trailing spaces from selected cells containing text (but not formula results) while leaving character-level formatting intact. It was built on the much simpler macro trimspace.
- Both regular spaces and nonbreaking spaces are deleted.
- If a cell contains only spaces, these are deleted.
- Cells that are adjusted are shaded in green; the macro also contains commented-out code to add a green border.
- Cells containing text longer than 255 characters are not edited but are shaded in yellow if leading or trailing spaces are found; the macro also contains commented-out code to add a red border.
- Please note: Excel stores macros differently than Word. See this page for more information.
Web Search Launcher (WebSearchLauncher)
This Word macro, based on Paul Beverly’s GoogleFetch macro, uses the current selection as the search string for one of a number of websites selected via a dialogue box.
- This requires a minimum of two keystrokes/mouse clicks rather than one (as Paul Beverly’s macro does) but avoids the need to have a separate macro for each website.
- The form allows the search string to be edited after selection but before the search is performed.
- The configuration file, accessible from within the form, allows the website list to be quickly adjusted.
- Because this macro uses a form and a configuration file, everything needed (including installation instructions) is provided in a .zip folder.
This file was updated June 19, 2021 to add missing content.