If you already thought Microsoft Office was powerful, you're in for a surprise when you see what Visual Basic for Applications (VBA) can do with it: VBA isn't just an add-on to Office; it's Office put on automatic pilot -- if you know how to use it. Work through a few examples to learn how to configure this autopilot.
Text finding and replacement
Finding and replacing text is one of the most commonly performed Word tasks. Because it's so common, any improvement in speed you can bring to this task stands a good chance of greatly cutting down your workload. VBA can deliver that speed improvement.
Use VBA to replace the first and last fields in a comma-separated table of data. Start by entering some sample data in Word:
Email1@yahoo.com, Dolores Claiborne, 555-1234
Email2@hotsnail.com, Charles Dickens, 555-4321
Email3@atp.com, Albert Einstein, 555-5005
Start the macro recorder
- Fire up the macro recorder: press the small rectangular button near the lower left corner of your screen. Enter "swapper" for the Macro name.
- Press control-H, select "Use Wildcards," and enter these text strings in the Find and Replace boxes, respectively:
Find: (<?@>), (???\-????)
Replace: \2, \1
- Click "Replace All," then stop the macro recording by pressing the small square, again in your screen's lower left corner.
Replay your macro to test it: restore the sample data to its original form before you did the replace operation. Press "alt-F8" to bring up the list of macros to run, and double-click the "swapper" macro. The table's first and last fields should again switch places.
Learn more about recording macros here.