VBA Examples
Tips, Tricks, and Answers
The following articles are available for the 'VBA Examples' topic. Click the article''s title (shown in bold) to see the associated article.
Accessing Paragraphs in a Macro
Need to process a document, paragraph by paragraph, in a macro? It's easy to do once you understand that Word's object model allows you to access each paragraph individually.
Adding Quotes
Adding quote marks is normally as simple as typing them from the keyboard. However, if you want to add quote marks around both ends of a text selection in a single step, you'll love this tip and the macro it describes.
Adjusting Small Caps Text
If you use small caps text in a document, you know that there are several steps involved in properly formatting the text. These steps can be combined into a single macro that makes adjusting the text easier than ever before.
Aligning Paragraphs in a Macro
Using a macro to format your document (or portions of your document) is not all that uncommon. If you want your macro to change paragraph alignment within the document, there are just a couple of properties you need to focus upon.
Applying Bold Italics
Applying bold and italics formatting to text is easy in Word. If you want to apply bold and italics simultaneously, you can create a tool to handle this formatting easily.
Automatic Blank Pages at the End of a Section
If you want to have a blank page at the end of a document section, you can insert one manually or you can use the technique described in this tip. The technique makes the added pages dynamic and easy to use.
Automatically Adding Tabs in Footnotes
When you add a footnote to a document, Word's normal formatting adds a space after the footnote number and before the body of the footnote. You may want Word to use a tab instead of the space. There are a couple of ways you can approach this problem, as discussed in this tip.
Automatically Inserting Brackets
Want a fast way to add brackets around a selected word? You can use this simple macro to add both brackets in a single step.
Automatically Saving Document Copies on Floppy
WordPerfect included a command that allowed users to save a copy of their current document to the A: drive. Word has no similar command, but you can create your own that allows you to save document copies to any drive you want.
Batch Template Changes
Changing the template associated with a couple of documents is easy, but what if a whole directory needs to be changed? These macros will do it very quickly.
Bumping Numbers in a Document
If your documents include words that contain numbers (such as a list of parts numbers) you may need a way to increment those numbers. Here's a way you can do it quickly using a macro.
Calculated Dates
Word makes it easy to insert today's date in a document, but not as easy to insert a date X number of days in the future. This tip discusses some ways that you can get around this apparent limitation.
Changing Print Dialog Box Defaults
Some of the built-in defaults in Word can't be changed. Often times, however, you can work around these defaults by using macros. Here's how you can customize the print dialog box defaults.
Changing Text Case
Word provides a built-in shortcut to change the case of a text selection. Understanding how that shortcut works (and the other options available to you) can make some editing tasks easier.
Changing the Default Drive
Macros can be used to read and write all sorts of files. If those files are on a different drive than the current one, you'll appreciate that you can change the default drive very easily using the ChDrive command.
Changing Toolbar Buttons with VBA
Toolbar buttons can have a different appearance depending on their stateā"whether they have been clicked or not. This tip explains how you can create the two necessary versions of a toolbar button for your customizations.
Character Frequency Count
Word collects a wide range of statistics about your documents, but one of the things it doesn't collect is how many times individual characters appear in the document. (This would be considered a "frequency count" of each character.) You can create a macro to count the characters, but the approach you use to the macro will determine how efficiently the requisite counts are collected.
Checking for a Security Certificate
Although Word macro viruses haven't been commonly used for some time now, hackers often try old, known techniques just to see if there is an "easy" way into their target system. Using digital certificates to "sign" documents is a way to verify to origin of the document. This tip shows how to check if a document has been signed, but it does not verify the signature.
Checking for a Text Selection Length
Need to know if the user selected some text before running your macro? Here's how to make that check.
Clearing the Undo Stack in a Macro
When writing a macro, you may need a way to clear the undo stack. This can be done with a single command, as described in this tip.
Consistent Spacing
It is a good idea to make sure that the spacing following each sentence in your document is consistent. Here's a handy macro you can use to ensure that there is only a single space after each sentence.
Continually Saving Normal.dot
If your Normal.dot file is continually saved when you exit Word, even when you haven't made any changes to it, the culprit could be other programs you have operating on your system. This tip explains what you can do to help track down the problem.
Controlling the Italic Text Attribute
If you are formatting your document by using a macro, you may need to make some of your text italics. You do that by changing the Italic property for the selection.
Converting Inches to Points
Typographical measurements are often expressed in points. There are several formatting settings that, when accessed through a macro, require measurements to be made in points. Fortunately, VBA provides a function that can handle the conversion from inches into points for you.
Converting Numbers to Text
Got some numbers you need spelled out? Here's a handy macro that can convert numbers like "123" to words like "one hundred twenty-three."
Converting Text to Uppercase in a Macro
Macros are often used to process documents. If part of the processing involves making text selections uppercase, Word provides two ways that the conversion can occur.
Copying a File in VBA
Need to have your macro copy a file from one place to another? It's easy to do using the FileCopy command, described in this tip.
Correct Line Numbers When Printing Selections
Line numbers can be indispensable on some types of documents. When you print a portion of a document (a selection) Word won't always print the line numbers correctly. Here's why that happens and what you can do to get the line numbers you want.
Counting Characters in a Selection with VBA
Need to figure out the number of characters in a range of selected text? Here's how to do it in VBA.
Counting Characters in Text Boxes
When you do a word count, the value that is returned does not include any text contained in text boxes. This may not be a significant problem, unless you have a large amount of text in those boxes. This tip discusses ways you can deal with this issue.
Creating a New Document in VBA
When working with documents in a macro, it makes sense that you may need to create a document from time to time. Here's how easy it is.
Deriving an Absolute Value
Want to know the absolute value of a number? It's easy to derive in VBA by using the Abs function.
Determining a Paragraph's Style in VBA
When processing a document via a macro, it is often helpful to understand what style has been applied to a paragraph. You can figure this out by using the Style property, described in this tip.
Determining a Random Value
If you need to determine a random value in a macro, you can do so using the Rnd function. This tip presents the syntax and usage of the function.
Determining Differences Between Dates
Do you need to do some simple math using dates in a your macro? One of the easy functions you can use is the DateDiff function.
Determining How Many Styles are Available
Got a macro that processes or uses styles? You definitely need to know how many styles Word has available in the document. Use the Count property of the Styles collection to determine the figure.
Determining How Many Windows are Open
You can open multiple documents at the same time in Word, and each document occupies its own document window. Here's a way you can determine, in a macro, how many of those windows are open at the current time.
Determining if a File Exists
Your macro may need to know if a particular file exists. This is easy to figure out using the Dir command, and even easier if you use it in the function provided in this tip.
Determining if a Text Selection Exists
Macros are often designed to be run on just a selected portion of a document. It is a good idea to make sure that the user actually made a selection before running your code. Here's how to determine if that necessary selection exists.
Determining if Caps Lock is On
If your macro needs to determine the status of the Caps Lock key, you need the code in this tip. Just use the Information property to query the key's state.
Determining if Num Lock is On
Need to know if the Num Lock key is on or off? You can use a short bit of macro code to figure out the state of the key.
Determining if Overtype Mode is Active
Your macro may need to determine if the user has overtype mode turned on. You can find out the overtype status easily by using the Overtype property.
Determining Picture Size in a Macro
When processing a document using a macro, you may need for your macro to figure out the sizes of the images in your document. This information can be accessed by using the techniques described in this tip.
Determining the Current Directory
When creating macros, it is often necessary to know which directory is the default. Here's how you can find out by using the CurDir command.
Determining the Current Page Number
While your macro is processing the text in your document, you may need a way to determine the current page number where the insertion pointer is located. This is done by using the Information method, as described in this tip.
Determining the Day of the Year
Need to find out the day of the year for a particular date? It's easy to do if you are using a macro. All you need to do is use the DatePart function.
Determining the Hour of the Day
Need to figure out the hour represented by a particular time value? It's easy to do in a macro; just use the Hour function, described in this tip.
Determining the Number of Fonts Available
When creating a macro, you may need to figure out how many fonts are available to Word. You can do this using the FontNames collection.
Determining the Number of Pages in Your Document
If your macro needs to know how many pages are in your document, you can use the Information method to get the desired info. It just takes a single line added to your macro.
Determining the Size of a File
When processing a document using a macro, you may need to know the precise size of a particular file. The way you figure this out is to use the FileLen function, described in this tip.
Determining Word Frequency
How to construct a word frequency list.
Displaying a Message in the Status Bar
A great place for your macro to display status information is, well, in the status bar. Displaying the information is easy, as this tip points out.
Filling Table Cells with a Macro
Want to stuff a value into each cell of a table? You can either type the value over and over and over again, or you can use the short macros described in this tip.
Finding a Cell Reference
Want to know what the reference address is for a particular cell in a table? Word won't tell you, but you can use a macro to figure it out.
Finding a Lost Menu Bar
Once in a while Word can get confused and stop displaying an object you need to fully use Word. If your menu bar suddenly disappears one day, you'll appreciate the information in this tip.
Finding an Unknown Character
Sometimes the characters that appear in a document can be hard to figure out, especially if the document came from someone else. Here's how to get to the root of what a character really is.
Finding Formatted Bulleted Paragraphs
Want to find the bulleted paragraphs within a large document? Word doesn't have a built-in way to search for this formatting. There is, however, a way to get the information you need. This tip explains how to go about it.
Finding Long Lines
Word is very dynamic in how it "flows" text from one line to another and one page to another. In most cases we are willing to allow Word to do its job in this area. In some situations, however, you may need to know if a paragraph has flowed to a new line so that you can "force" it to fit all on one line. Making this determination is not as easy as you might like.
Finding Unused Styles
Use this VBA macro to determine which styles are being used in the current Word document.
Formatting an ASCII Table with Spaces
When you get a text file from a program other than Word, tabular information may be formatted with nothing but spaces in between columns. You can easily convert such information to Word's native table structure by using the macro in this tip.
Full Path Names in Word
An easy way to display the document's full path name in the title bar.
Getting Bookmark Information in VBA
Bookmarks are a handy way to "mark" locations within a document. If you are creating a macro that processes the document in some manner, it may be helpful for your macro to determine if the insertion point is within a macro and, if so, grab information about that bookmark. Here are the techniques you need in order to do this.
Grabbing the MRU List
The MRU (most recently used) list informs you which documents were the last to be opened and edited in Word. You can access the list in a macro by using the RecentFiles collection, as illustrated in this tip.
Hiding Macros
Don't want a particular macro to be visible in the Macros dialog box? Once you understand the criteria that Word uses when determining which macros to display, you can use this understanding to your benefit. This tip examines three ways you can keep your macros out of the dialog box.
Ignoring Smart Quotes when Comparing Text
When comparing two pieces of text, you may find that Word's smart quotes can mess up the comparison. Here's a quick way to get that obstacle out of the way so that the comparison can be done properly.
Importing AutoCorrect Entries
The AutoCorrect feature in Word is quite handy, but getting a lot of entries into the feature can be tedious. This tip provides a macro that will allow you to enter entries quite a bit faster.
Inserting a Break with a Macro
Inserting a break in your document is easy. You may think that inserting one using a macro is more complex, but it isn't. Just use the InsertBreak method, described in this tip.
Inserting Text with a Macro
Need to have your macro insert a bit of text into your document? It's easy to do using the TypeText method.
Intelligible Names for Macros
The names you use for macros can affect what you see when you add those macros to a toolbar. This tip explains how you can change macro names on toolbars, as well as change the ToolTip that appears for a macro.
Jumping to the Start or End of a Document
When creating macros, it is often necessary to move the insertion point around the document so that text can be processed in the manner desired. One of the most common movements is to move the insertion point to the beginning or ending of the document. Here's how to do it.
Jumping to the Top of a Page
Do you want to easily jump to the top of a page in your document? You can use the Go To command to make the shift, or you can use the short macro described in this tip.
Merging to Individual Files
The mail-merge feature in Word is a fast, easy way to create form letters addressed to many different people. Sometimes, however, you might need to save the resulting documents into separate files for each address. Here's how.
Misbehaving Rulers
When upgrading from Word 2000 to 2002, many people have a problem with the Ruler function. This tip explains how to fix the issue.
Moving Found Text Down On a Page
When you use the Search feature to find information, if the information is not on the visible page, then Word displays the page with the information and the occurrence of what you are searching for is highlighted on the top row of the window. It is often helpful for it to be on the second or third row, so you can see the context of what was located. This tip explains how you can "move" the page a bit so you can see what was found within its context.
Moving the Insertion Point to the Beginning of a Line
If you need to move the insertion point within your macro, then you'll want to note the HomeKey method, described in this tip. It can be used to move the insertion point to the beginning of a line of text.
Moving Through a Table in a Macro
Do you need to step through a table, cell by cell, in a macro? It's easy to do using the Move method, as described in this tip.
Moving to the Start or End of the Real Document
The main body of your text is only one part of what makes up the entire document. Documents can consist of other elements, as well, such as headers, footers, text boxes, footnotes, etc. How can you create a macro that will move the insertion point to the beginning or end of the main body of your text, regardless of where the insertion point is currently located? It's easier than you think.
Numbers to Text, Take Three (Over a Million)
So, you need to convert a number to text that is over one million? Here is a VBA macro that will convert up to 999,999,999.
Only Showing Readability Statistics
Perform a grammar check, and Word displays some statistics that represent an analysis of your words. By writing a macro you can access these statistics and display whatever part of them you want.
Picking Up in the Last Document Edited
Sometimes it seems that we focus on getting a particular document hammered out to the exclusion of other documents we could be working with. Here's how you can speed up the process of loading the last document you worked on and jumping to the proper place in that file.
Printer Name on the Status Bar
The status bar is a great place to display all sorts of information. It might not be the best place to put the name of the selected printer, however. Here are some ideas on how you can accomplish the task of knowing which printer you are printing to, without really relying on the status bar.
Printing a Bookmark List
Need to know what bookmarks are defined in a document? Here's a macro that creates a list of all your bookmarks so that you can print it at any time.
Printing a File List
It is often helpful to have a list of all the documents in a given directory or folder. Word doesn't have a built-in way to generate such a list, but there are a couple of ways you can get the desired information.
Printing a Font List
Getting a list of fonts available in a document is not something you can easily do in Word. That is, unless you put the macro in this tip to work.
Printing All Open Documents
Have a bunch of documents you need to print? If all the documents are open, you can use a handy little macro to print them all at once.
Printing Color Separations with VBA
When printing in color (at a commercial printer) it is necessary to print different colors of your document in different passes. For this purpose, commercial printers often deal with color separations, or separating a document into its component colors. Word can't perform such a complex task, but there is a way you can simulate color separations in simple documents.
Printing Copy Numbers
Copy 1, Copy 2, Copy 3... Do you want to mark your printouts so that they are numbered? Here's how you can do it.
Printing Graphic Thumbnails
If you are doing work with a lot of graphics, it may be helpful to create a summary page that contains thumbnail representations of all the graphics. Here's a handy macro that can make quick work of such a page.
Printing On Both Sides of the Paper
A VBA macro to allow duplex printing.
Printing Personalized Copies of a Document
Need to have a series of documents customized for individual users? Mail merge may be overkill, but the macro presented in this tip will help make short work of the individualized printouts.
Printing the Active Document from a Macro
When you process a document in a macro, you may also want to print that document from within the same macro. Here's how to do it using a single command.
Problems Using Words as Bullets
If you know the secret, you can use actual words as "bullets" in a bulleted list. The built-in bulleted lists in Word aren't the way to achieve what you want to do, and this tip explains why. It also provides a macro that you can use to apply the formatting you want to the list.
Protecting Hidden Text
Formatting some of your text as hidden can be a great help when you need to keep some things from being viewed or printed. The hidden text can be easily unhidden by anyone, however. Here's how to get rid of it so that it can't be uncovered.
Quickly Dumping Array Contents
If you store information within a variable array, at some point in your macro you may want to get rid of that information. Here's a quick and efficient way to do it.
Quickly Formatting Footers in Documents with Many Sections
Need to adjust all the footers or headers in a document that uses lots of them? It's easy to do if you understand how the footers and headers are related to each other.
Quickly Formatting Multiple Documents
Need to format a bunch of documents so they all look the same? If the documents use styles, doing the formatting is relatively easy, as described in this tip.
Removing Tabs Used to Indent a Paragraph
You get a document from a colleague and you notice that each paragraph starts with a tab character. Here are a couple of ways to get rid of those extraneous tabs very easily.
Removing Unused Styles
Got an older document that has a bunch of unused styles defined in it? You can get rid of those styles easily by using the short macro in this tip.
Repeating Actions
Need to repeat an action a whole bunch of times? You can do it a time or two using keyboard shortcuts, but you'll need a macro (like the handy one in this tip) to make repeating a lot of times possible.
Replacing Quoted Text with Italics
If you have text surrounded by quotes in a document, you may want to remove the quote marks and make the text that was within them italic. Here's an easy way to make the conversion.
Replacing Text in a Macro
When using a macro to process text in a document, it is not unusual to replace one portion of a text string with another piece of text. Some versions of VBA include commands to perform this task, but not all do. This tip presents a handy function you can use to make sure a replacement is done correctly.
Resetting Character Formatting in a Macro
Want your macro to get rid of the formatting applied to a selection of text? It's easy enough to do using the Reset method, described in this tip.
Saving a Document in a Macro
If you develop a macro to process your document, you may want the macro to save the document to disk. This is easily done using the Save or SaveAs commands in your macro code.
Saving Everything
Need to force users to save their work? It may be as simple as implementing a couple of macros that get a bit more aggressive when it comes to saving. It could, however, have something to do with training users.
Saving Information in a Non-Document Text File
Need to store some information in a plain text file? It's easy to do when you use a macro.
Screen Flip Flop with VBA
Word allows users to conveniently work with multiple documents at the same time. When writing macros, you may need to know how to switch which document is displayed. This tip presents a handy macro that switches which of two document windows is displayed on top.
Searching for Borders
Want to find all the paragraphs in your document that have borders applied to them? The regular Find and Replace tool won't work for this purpose. Here are a couple of ways you can find what you need, however.
Searching for Floating Graphics
Graphics can be added to a document so that they are either inline with the text or floating over the text. You can use Word's Find and Replace tool to locate the inline graphics, but not the floating ones. This tip provides ways you can find the latter type of graphics.
Selecting a Bookmark in a Macro
Bookmarks can be very handy in a document. Word provides a VBA command you can use to easily select any of those bookmarks.
Selective Formatting in Replacements
Do you need to replace text with a term or phrase that uses multiple formats? You can perform this seemingly complex task using the powerful find and replace tools in Word, or you can create a macro that will do the trick for you.
Setting a Default Table Border Width
When you insert a table into your document, it uses a standard-weight line around each cell in the table. If you want to change that default line weight, you may be out of luck. Here are a couple of macros you can use to make it easier to change the line weight.
Setting Maximum Line Lengths in Word E-mail Messages
When you use Word as your e-mail editor, it allows you to format the text of your e-mail messages using tools you are familiar with. One thing that is harder to do, however, is to limit the line length of e-mail messages created with Word. This tip explains why the difficulty arises and what you can do to work through the difficulty.
Setting the Right Indent of a Paragraph in a Macro
Need to format your document using a macro? You can easily set the right margin for an individual paragraph by using the RightIndent property, described in this tip.
Spacing After Sentences
Word can check to see if you have a consistent number of spaces at the end of your sentences.
Specific Capitalization
How to get around Word's AutoCorrect feature for having uncapitalized words at the start of a sentence.
Specifying a Paper Tray in a Macro
You may want to use a macro to process and then print your document. Part of that printing may involve specifying which of your printer's paper trays Word uses. Here are some considerations you need to take in account if you want to select a paper tray in your macro.
Strip Trailing Spaces
If you get tired of documents that always seem to have extra spaces at the end of lines, here's a quick way to get rid of them. Just use the quick macro provided in this tip.
Swapping Two Strings
Part of developing macros is learning how to use and manipulate variables. This tip examines a technique you can use to exchange the contents of two string variables.
Talking to Yourself
Need to keep notes about a document, but you don't want others to see those notes either on-screen or on-paper? Here's an easy way to add helpful notes throughout your document.
The Case of the Vanished Menu Bar
Can't find your menu bar any more? Here are some things you can try to get that important feature back on your screen where it belongs.
Toggling AutoCorrect Settings
If you need to turn AutoCorrect on or off, there is no built-in way to easily do it in Word. You can create your own toggle command, however, as described in this tip.
Transposing Two Paragraphs
Need to swap two adjacent paragraphs? Your editing arsenal can include a command to do this is you use the macro in this tip.
Trimming Spaces from Strings
When processing text with a macro, you often need to remove extraneous spaces from the text. VBA provides three handy functions to remove those leading or trailing spaces easily.
Turning Off Default First Page Numbering
Ever want to change the default settings for how Word handles page numbering? Word doesn't make this as easy as you would think. This tip explains how you can customize the defaults within a template.
Turning Off Screen Updating
When working with macros, you can often speed up processing by turning off the updating of the screen. Best news is that it takes only a line or two added to your macro.
Understanding Document Variables
When working with macros, you may want to create a variable that will remain constant from one instance of the macro to another. This is a great use for document variables, as described in this tip.
Understanding the For ... Next Structure
Spend any time creating Word macros, and sooner or later you will need to repeat some of your programming code a certain number of times. That's where the For ... Next structure comes into play.
Understanding the If ... End If Structure
One of the powerful programming structures provided in VBA allows you to conditionally execute commands. The If ... End If structure is easy to implement once you grasp what it does.
Understanding the Select Case Structure
Programming structures are an important tool used by any programmer. The VBA language used by Word's macros includes several powerful programming structures, including the Select Case structure, described in this tip.
Updating Fields in Locked Forms
Updating form fields in Word can be confusing, especially when the fields are locked in a form. This tips explains why the fields are difficult to update and a way you can work around the problem.
Using a Macro to Change the Formatting of All Instances of a Word
If you have a word that you need to make sure is formatted the same way throughout your document, there are several ways you can approach the task. One is to format manually, another is to use a style, and the third method (described in this tip) is to use a macro to handle the formatting.
Working with Multiple Printers
Word does not keep printer information associated with documents. You can define a macro for each printer you use and put buttons for the macros on a toolbar for easy access to all of the printers.
