Word Macros

This section takes you through a few useful macros that you can use when working with Word documents.

Saving Frequently

Word enables you to save AutoRecovery info at specified intervals (select Tools, Options, Save and set the number of minutes using the Save AutoRecovery Info Every spin box). However, the shortest interval is one minute, and fast writers could still lose work. If you want a way to automatically save your work at a faster interval, use Word's handy OnTime method, which enables you to run a procedure at a specified time. Here's the syntax:

 Application.OnTime(When, Name [, Tolerance]) 

When	The time (and date, if necessary) you want the procedure to run. Enter a date/time serial number.
Name	The name (entered as text) of the procedure to run when the time given by When arrives.
Tolerance	If Word isn't ready to run the procedure at When, it will keep trying for the number of seconds specified by Tolerance. If you omit Tolerance, VBA waits until Word is ready.

The easiest way to enter a time serial number for When is to use the TimeValue function:

 TimeValue(Time) 

Time

A string representing the time you want to use (such as "5:00PM" or "17:00").

For example, the following code runs a procedure called MakeBackup at 5:00 p.m.:

 Application.OnTime _     When:=TimeValue("5:00PM"), _     Name:="MakeBackup" 

If you want the OnTime method to run after a specified time interval (for example, an hour from now), use Now + TimeValue (Time) for When (where Time is the interval you want to use). For example, if you want to save your work every 10 seconds, use the OnTime method as shown in Listing 12.1.

This Chapter's Examples

You'll find the Word and Excel files used as examples in this chapter on my website at www.mcfedries.com/OfficeGurus.

Listing 12.1. A Macro That Saves the Active Document Every 10 Seconds

 Public Sub FileSave()     ActiveDocument.Save     DoEvents     Application.OnTime _         When:=Now + TimeValue("00:00:10"), _         name:="FileSave"     Application.StatusBar = "Saved: " & ActiveDocument.name End Sub 

The FileSave procedure saves the current document by running the Save method on the ActiveDocument object. The DoEvents method processes any keystrokes that occurred during the save, and then the OnTime method sets up the FileSave procedure to run again in 10 seconds. To remind you that the procedure is on the job, the procedure closes by displaying a message in the status bar.

Giving a Macro the Same Name as a Word Command

FileSave is also the internal name of Word's File, Save command. By giving your procedure the same name (and procedure the Sub keyword with Public to make it available to all documents), you intercept any calls to the Save command and replace Word's internal procedure with your own. This isn't strictly necessary, but it's handy because it means that your procedure will run as soon as the File, Save command is chosen.

To find out the internal names of Word's commands, select View, Toolbars, Customize, and then click Keyboard to display the Customize Keyboard dialog box. Select an item in the Categories list and then look up the internal command name in the Commands list. Alternatively, hold down Ctrl and Alt, press the + key on your numeric keypad, and then either click the toolbar button or select the menu command associated with the command you want to use. Word displays the Customize Keyboard dialog box with only the selected command displayed.

Making Backups as You Work

We've all learned from hard experience not only to save our work regularly, but also to make periodic backup copies. The macro I use most often in Word is one that does both in a single procedure! That is, the macro not only saves your work, but it also makes a backup copy on another drive, such as a removable disk, a second hard drive, or a network folder. Listing 12.2 shows the code.

Listing 12.2. A Procedure That Creates a Backup Copy of the Active Document on Another Drive

 Sub MakeBackup()     Dim currFile As String     Dim backupFile As String     Const BACKUP_FOLDER = "A:\"     With ActiveDocument         '         ' Don't bother if the document is unchanged or new         '         If .Saved Or .Path = "" Then Exit Sub         '         ' Mark current position in document         '         .Bookmarks.Add Name:="LastPosition"         '         ' Turn off screen updating         '         Application.ScreenUpdating = False         '         ' Save the file         '         .Save         '         ' Store the current file path, construct the path for the         ' backup file, and then save it to the backup drive         '         currFile = .FullName         backupFile = BACKUP_FOLDER + .Name         .SaveAs FileName:=backupFile     End With     '     ' Close the backup copy (which is now active)     '     ActiveDocument.Close     '     ' Reopen the current file     '     Documents.Open FileName:=currFile     '     ' Return to pre-backup position     '     Selection.GoTo What:=wdGoToBookmark, Name:="LastPosition" End Sub 

The backupFile and currFile variables are strings that store the full pathnames for the active document and the backup version of the document. Use the BACKUP_FOLDER constant to specify the folder in which you want the backup stored.

The procedures first check to see if the backup operation is necessary. In other words, if the document has no unsaved changes (the Saved property returns TRue) or if it's a new, unsaved document (the Path property returns ""), bail out of the procedure (by running Exit Sub).

Otherwise, a new Bookmark object is created to save the current position in the document, screen updating is turned off, and the file is saved.

We're now ready to perform the backup. First, the currFile variable is used to stored the full pathname of the document, and the pathname of the backup file is built with the following statement:

 backupFile = BACKUP_FOLDER + .Name 

This will be used to save the file to the specified folder. The actual backup takes place via the SaveAs method, which saves the document to the path given by backupFile. From there, the procedure closes the backup file, reopens the original file, and uses the GoTo method to return to the original position within the document.

Returning to the Last Edited Position

Using a Bookmark object to reset the insertion point is useful because it takes you back to the exact point in the document where you were before the backup started. However, you may be interested only in returning to the last position within the document where an edit occurred. If that's the case, use the following statement in place of the

 Selection.GoTo statement:    Application.GoBack 

Note, however, that there's a bug in the GoBack method whereby Word doesn't save the last edit position (technically, it's a hidden bookmark named \PrevSel1) in some cases. Specifically, when you exit Word, if you elect to save changes in the last document that gets closed, Word doesn't save the last edit position in that document.

Opening the Most Recently Used Document at Startup

Word's RecentFiles object represents the collection of most-recently used files displayed near the bottom of Word's File menu. Each item on this list is a RecentFile object. You specify a RecentFile object by using RecentFiles (Index), where Index is an integer that specifies the file you want to work with. The most-recently used file is 1, the second most-recently used file is 2, and so on.

It would be handy to have Word open the most-recently used file each time you start the program. If you want Word to run some code each time it's started, open the Normal project in the VBA Editor's Project Explorer and then create a new module name AutoExec. In this module, create a Sub procedure named Main and enter your code in that procedure. Listing 12.3 shows such a procedure:

Listing 12.3. A Procedure That Opens the Most-Recently Used Document

 Sub Main()     With RecentFiles(1)         Documents.Open .Path & "\" & .Name     End With End Sub 

Creating and Opening a Word Workspace

In Excel, you can define a workspace of files. When you then open that workspace, Excel opens all the files at once. If you often work with two or more files as a group, you can use VBA to create your own workspace functionality. Listing 12.4 shows two procedures that act as a workspace function for Word:

• CreateWorkspace This procedure uses the Windows Registry to store a list of open documents. Before running this procedure, make sure that only those files you want to include in the workspace are currently open.

• OpenWorkspace This procedure accesses the Registry and runs through the list of saved files. For each setting, the procedure checks to see if the file is already open. If it's not, the procedure runs the Documents.Open method to open the file.

Listing 12.4. Procedures That Create and Open a Workspace of Files

 ' CreateWorkspace() ' Saves the path and filename data of all the ' open files to the Windows Registry. Before ' running this procedure, make sure only the ' files you want in the workspace are open. ' Sub CreateWorkspace()     Dim total As Integer     Dim doc As Document     Dim i As Integer     '     ' Delete the old workspace Registry settings     ' First, get the total number of files     '     total = GetSetting("Word", "Workspace", "TotalFiles", 0)     For i = 1 To total         '         ' Delete each Registry setting         '         DeleteSetting "Word", "Workspace", "Document" & i     Next 'i     '     ' Create the new workspace     '     i = 0     For Each doc In Documents         '         ' Make sure it's not a new, unsaved file         '         If doc.Path <> "" then             '             ' Use i to create unique Registry setting names             '             i = i + 1             '             ' Save the FullName (path and filename) to the Registry             '             SaveSetting "Word", "Workspace", "Document" & i, doc.FullName         End If     Next 'doc     '     ' Save the total number of files to the Registry     '     SaveSetting "Word", "Workspace", "TotalFiles", i End Sub ' ' OpenWorkspace() ' Accesses the Registry's workspace settings ' and then opens each workspace file. ' Sub OpenWorkspace()     Dim total As Integer     Dim i As Integer     Dim filePath As String     Dim doc As Document     Dim fileAlreadyOpen As Boolean     '     ' Get the total number of files from the Registry     '     total = GetSetting("Word", "Workspace", "TotalFiles", 0)     For i = 1 To total         '         ' Get the path and filename         '         filePath = GetSetting("Word", "Workspace", "Document" & i)         '         ' Make sure the file isn't already open         '         fileAlreadyOpen = False         For Each doc In Documents             If filePath = doc.FullName Then                 fileAlreadyOpen = True                 Exit For             End If         Next 'doc         '         ' Open it         '         If Not fileAlreadyOpen Then             Documents.Open filePath         End If     Next 'i End Sub 

Add the Workspace Macros to the Menus

For easy access to the workspace macros, add them to one of your Word menus. I use the Work menu, which I discussed in the Chapter 10 section "Getting Easy Document Access with Word's Work Menu."

Displaying Sentence Word Counts

When you grammar check a document, you can elect to display the document's readability statistics. (Select Tools, Options, Spelling & Grammar, and activate the Show Readability Statistics check box.) Among other values, the Readability Statistics dialog box offers the average number of words per sentence. This is useful because you don't want your average sentence to be too long or too short. However, writing gurus also stress that you should have a variety of sentence lengths in your prose, with some shorter and longer sentences thrown in for reading variety. Unfortunately, the Readability Statistics dialog box doesn't tell you the lengths of your sentences, but it's easy enough to get a VBA macro to do it for you, as shown in Listing 12.5.

Listing 12.5. A Macro That Displays the Lengths of Sentences in the Active Document

 Sub DisplaySentenceLengths()     Dim s As Range     Dim maxWords As Integer     Dim i As Integer     Dim sentenceLengths() As Integer     Dim str As String     With ActiveDocument         '         ' Run through all the sentences to find the longest         '         maxWords = 0         For Each s In .Sentences             If s.Words.Count > maxWords Then                 maxWords = s.Words.Count             End If         Next 's         '         ' Redimension the array of sentence lengths         '         ReDim sentenceLengths(maxWords)         '         ' Run through the sentences again to count         ' the number of sentences for each length         '         For Each s In .Sentences             sentenceLengths(s.Words.Count - 1) = sentenceLengths(s.Words .Count - 1) + 1         Next 's         '         ' Construct the string that displays the sentence lengths         ' and their frequencies         '         str = "Sentence Length:" & vbTab & "Frequency:" & vbCrLf & vbCrLf         '         ' The UBound() function tells you the upper bound of an array.         ' In this case, it tells us the largest value in sentenceLengths.         '         For i = 0 To UBound(sentenceLengths) - 1             '             ' The code below uses the IIf() function, which is similar to             ' the If...End If structure from Chapter 12.             ' Also, vbTab represents a Tab character and             ' vbCrLf represents a Control/Line Feed character.             '             str = str & IIf(i + 1 < 10, " ", "") & i + 1 & _                   IIf(i = 0, " word: ", " words: ") & _                   vbTab & sentenceLengths(i) & vbCrLf         Next 'i         '         ' Display the string         '         MsgBox str     End With End Sub 

Using the ActiveDocument object, the macro makes a first pass through all the sentences to find the one with the most words. The macro then uses this maximum word count to redimension the sentenceLengths array, which is used to hold the number of occurrences of each sentence length within the document. To calculate these frequencies, the macro then runs through all the sentences again and increments the array values for each length. The macro finishes by constructing and then displaying a string that holds the sentence lengths and frequencies. Figure 12.1 shows an example.

Figure 12.1. The DisplaySentenceLengths macro displays a message box such as this to show you the document's sentence lengths and the frequency with which each length occurs.

Displaying Paragraph Word Counts

If you're also interested in displaying data for each paragraph, replace .Sentences in Listing 12.5 with .Paragraphs. Because the variable s is a Range object, you can work with either the words in each paragraphs.Words.Countor the sentences in each paragraphs.Sentences.Count.

Finding the Longest Sentence

One of the hallmarks of good business writing is that it's succinct and to the point: no digressions, minimal adjectives and adverbs, and no run-on sentences. If you use the code in Listing 12.5 to study the sentence lengths of your document, you may find one sentence that's quite a bit longer than the others. For example, all your sentences might be fewer than 25 words, but there may be one that's 50 words. That one sentence is obviously far too long, and the problem may be mistaken punctuation (such as a comma instead of a period) or a too-long sentence that needs to be broken up into two or three smaller sentences. Either way, you need to find the problem sentence, and the code in Listing 12.6 does just that.

Listing 12.6. Finding the Longest Sentence in a Document

 Sub FindLongestSentence()     Dim s As Range     Dim maxWords As Integer     Dim longestSentence As String     With ActiveDocument         '         ' Run through all the sentences to find the longest         '         maxWords = 0         For Each s In .Sentences             If s.Words.Count > maxWords Then                 maxWords = s.Words.Count                 longestSentence = s.Text             End If         Next 's         '         ' Move to the top of the document         '         Selection.HomeKey Unit:=wdStory         '         ' Set up the Find object         '         With Selection.Find             '             ' Clear Find object formatting             '             .ClearFormatting             '             ' Check the length of the sentence             '             If Len(longestSentence) <= 256 Then                 '                 ' The length of the sentence is okay,                 ' so go ahead and find the text.                 '                 .Text = longestSentence                 .Execute             Else                 ' The sentence is too long for the Text                 ' property, so find just the first 256 characters                 '                 .Text = Left(longestSentence, 256)                 .Execute                 '                 ' Extend the selection to the entire sentence                 '                 Selection.MoveEnd Unit:=wdSentence             End If         End With         '         ' Display a message         '         MsgBox "The selected sentence is the longest in the document " & _                "at " & maxWords & " words."     End With End Sub 

As in Listing 12.5, the FindLongestSentence macro runs through the active document's sentences to find the longest one. In this case, however, the macro stores not only the length of the longest sentence, but its text, as well. The macro then uses this text to locate the sentence using the Find object. Note, however, that the Find object's Text property can accept only up to 256 characters. Because a long sentence can easily have more characters than that, the macro checks the length of the sentence: if it's too long, the Find object is set up to look for only the first 256 characters in the sentence.

Toggling Hidden Codes and Text

When you click the Show/Hide button, Word displays symbols that represent hidden "characters" such as tabs, spaces, paragraph marks, and optional hyphens, as well as any text formatted as hidden. This is handy for looking "under the hood" of the document. However, a thorough check of a document's inner workings should also include other normally hidden items: bookmarks, comments, revisions, and field codes. You can toggle all of these by hand individually, but if you need to do this often, the procedure in Listing 12.7 is much easier.

Listing 12.7. Toggling Hidden Codes and Text

 Public Sub ShowAll()     Dim currentState As Boolean     With ActiveWindow.View         currentState = .ShowBookmarks         .ShowBookmarks = Not currentState         .ShowComments = Not currentState         .ShowFieldCodes = Not currentState         .ShowHiddenText = Not currentState         .ShowHyphens = Not currentState         .ShowOptionalBreaks = Not currentState         .ShowParagraphs = Not currentState         .ShowRevisionsAndComments = Not currentState         .ShowSpaces = Not currentState         .ShowTabs = Not currentState         .Type = wdNormalView     End With End Sub 

The procedure is named ShowAll, which is the internal name of the command that Word runs when you click the Show/Hide button. Therefore, clicking Show/Hide will then run the ShowAll procedure. Using the active window's View object, the program first checks the current state of the ShowBookmarks property and stores the state in the currentState variable. Then each of the View properties is set to the opposite value. Figure 12.2 and 12.3 show the two states produced by the procedure.

Figure 12.2. The document view with the hidden codes and text turned off.

Figure 12.3. The document view with the hidden codes and text turned on.