Network Productivity Group

... a computer consultancy


The tip...

We wish we wrote to you this morning in a better spirit.  The last week has been tragic for our country and the world.  We fear that we've lost at least two personal fiends in New York, although we continue to hope.  What we do know is that this country and the people in it are marvelous and will show the world their character and strength.  God bless America! 

I trust you have all now done something about your backups, right? 

It has been suggested that we should describe the "macro" facility of Office.  We have used it extensively in WORD and Excel.  We assume it exists for PowerPoint and Access as well although we have not used it there. 

Why would one use a macro?  This is what we were doing the last time we used it and led our client to recommend creating this tip.  We had a text document that was created using a word processor other than WORD.  All of the lines of text had an extra space between then (that is how that word processor created double spaced text).  Between paragraphs there were also extra lines and between each page there were more lines, headings and footings.  The document was almost one hundred pages in length. 

To fix the extra spaces between lines was numbingly dull but not hard: press "delete" twice and Down Arrow to next line.  At the beginning of the next paragraph it was similar.  At the beginning of a new page it was similar but more variable.  The number of characters that had to be deleted varied but we could determine a minimum. 

The macro facility of MS Office is very clever.  While you can "record" a macro, Office is actually creating Visual Basic program statements.  If you wish to create and extend macros by creating these statements yourself you can.  If you don't know how Visual Basic would do something you can go to "record" mode, perform that action, then go back and edit what you recorded and see how it was done.  If you have a bit of a flair for programming, you can do quite extensive operations in macros by using the Help facility of Visual Basic Editor and the recorder. 

You can also assign a macro to a shortcut keystroke combination (in our example we used ALT+CTL+W to execute the end-of-line macro, ALT+CTL+P for the end of paragraph macro and ALT+CTL+X for the end of page macro).  You can also assign a macro to a toolbar, find an icon to represent it and when you want to use it, just click that icon. 

To record a macro in WORD or Excel go to Tools then Macro then Record new macro.  A dialog box will appear asking you to name the macro and if you want to assign it to a toolbar or the keyboard.  If you chose Keyboard you will be given the opportunity to select a keystroke combination to active the macro.  If that combination is already assigned to another operation you will be warned.  You can go ahead and reassign that combination or try another.  (Just a warning, many are already used.) 

That done, a little icon will appear that has two buttons: stop and pause.  From that point forward, everything you do will be recorded until you click on stop or pause.  In our case, we positioned the cursor at the end of a line, started recording, pressed Delete twice and Down arrow.  That removed the extra lines between each line and moved the cursor to the end of a new line.   

We stopped recording and looked at the macro.  To look at (or edit) a macro go to Tools then Macro, select the macro name and then Edit.  The Visual Basic Editor will open and show you the actual code you have created.  In our case it seemed to be correct.  We returned to WORD, positioned the cursor at the end of the next line and pressed ALT+CTL+W.  It worked as expected.  Now we could sail through the text by just pressing W with the other two keys already down.  When the end of line was also the end of a paragraph or page we just pressed one of the other keys and the action was done (page usually required a little extra cleanup but it was much quicker that the alternative). 

If you have any highly repetitive tasks you do in WORD, Excel, PowerPoint or Access, give macros a try.  You may find them much easier than the alternative.

Contact us:

Telephone US (562) 930-1343- Fax (562) 439-7174