Your VBA/Automation Headquarters!

 

 

 

 

 
 

Home Access Excel FrontPage Outlook PowerPoint Word Office VBA VBA Samples Newsletter Free Downloads

 

A truly global firm, The Office Experts provides services to clients all over the world, and hires consultants from all over the world.

Not only do we provide automation, we'll fix, update, or convert your current automated applications.

 

Services Store Contact Us
 

Office VBA

 

About Visual Basic and Macros

Excel VBA

Word VBA

What is a Macro

Macros are used to create a set of commands or tasks to be performed automatically. When you have repetitive tasks, you can create a macro that will automatically run a small "program" to perform those tasks for you.

Visual Basic for Applications (VBA) is a mini-version of Visual Basic that is used within the Microsoft Office suite and certain other programs, like Visio. When you record a macro, the VBA code is recorded for you. Many tasks that you would like to perform cannot be recorded, but must be written in VBA; and many tasks that you would like to perform all at once might need to be written by a VBA programmer.

Recording macros in Word and Excel and other programs becomes easy once you know how.

Excel VBA

A very important piece of information about Excel VBA: When you want a macro to be available for your use in ANY Excel file, it must be stored in the Personal.xls file. Read about it in your help files.

How to Record a Macro in Excel

The following is step-by-step instructions to record a macro, make the macro available for use in any single file or for use in any file you open, and assign a toolbar button to run the macro.

One of the best reasons for creating a macro could be that you must format files from external sources on a daily basis. If you are fairly accomplished in Excel, you will know that there are several steps you must take to accomplish it. The next time you need to perform this or a similar task, record the tasks as a macro and you will only have to press a button to perform them again.

Step 1: Prepare to Record

If you would like the macro to be available in a specific file, open that file.

From the menu, choose Tools-Macro-Record New Macro. The following dialog box will appear:

In the Macro name box, enter the name you would like to use for your macro. It must not have any spaces and should not contain special characters.

Decide where to store the macro. If you would like it available in the current workbook only, hit the Store Macro in dropdown box and select your active workbook's name. If you would like the macro to be available for use with any file, select Personal Macro Workbook (which is a file that will be called Personal.xls).

Give the macro any description you like in the Description box. By default, the description will state the date the macro was recorded, and the user name. The user name automatically comes from the information under Word's Tools-Options, User Info tab.

The Stop Recording toolbar will appear. There are two buttons on the Stop Recording toolbar. The square is used to STOP recording and the lines and circle are used to PAUSE recording.

NOTE: If you accidentally close the Stop Recording toolbar, go to View-Toolbars and click on the Stop Recording toolbar. If you accidentally use the X on the Stop Recording toolbar to stop recording a macro, go to View-Toolbars and click on the Stop Recording toolbar.

Step 2: Record the Macro

Perform the tasks you would like your new macro to perform for you. If you need to interrupt the macro recording to perform some non-related work, press the Pause button on the Stop Recording toolbar. Press the Pause button again to continue recording. Hit the Stop button to Stop recording when you have finished recording your tasks.

Step 3: Test the Macro

Always test a macro, particularly if you have created it to run for someone else. To test the macro, hit Tools-Macro-Macros, and double-click the macro name.

Step 4: Assign the Macro to a Toolbar Button

Hit Tools-Customize and choose the Commands tab. The following dialog box appears:

Under the Categories (left), scroll down and choose Macros. Click on the smiley icon and drag it up to any location you choose on any toolbar you choose. Your mouse pointer must have a plus sign (+) hanging on it before you lift your finger from the mouse or the icon will not be placed. While dragging, you will see an X hanging on your mouse pointer.

Once your icon is placed on a toolbar, click the Modify Selection button, and various options will appear. The following describes only those options that apply for our purposes here:

Name: Type in the name of the macro as it should appear on your toolbar if you are going to use the text style button (described below).

Reset Button Image: Select this only if you have begun to edit an existing button image and you would like to reset it to the default.

Edit Button Image: Select this option to "draw" your own button image.

Change Button Image: Select this option to change the existing button image to one of your choosing from Office's collection of button images.

Default Style: Clicking this button makes the button on the toolbar show just the icon and no text.

Text Only (always): Clicking this button makes the button on the toolbar show just the text as it is shown in the Name box, which you can change.

Image and Text: Clicking this button makes the button on the toolbar show both the icon and the text.

When the button appears exactly as you would like it to appear on the toolbar, hit the close button on the Customize dialog box.

The first time you hit your new toolbar button, you are asked which macro you want it to run. You'll pick your new macro from the dialog that pops up.

Word VBA

How to Record a Macro in Word

The following is step-by-step instructions to record a macro, make the macro available for use in any single file or for use in any file you open, and assign a toolbar button to run the macro.

One of the best reasons for creating a macro could be that you must convert WordPerfect documents into Word documents. If you are fairly accomplished in Word, you will know that there are several steps you must take to accomplish it. The next time you need to perform this or a similar task, record the tasks as a macro and you will only have to press a button to perform them again.

Step 1: Prepare to Record

If you would like the macro to be available in a specific file, open that file. If you would like the macro to be available in all files, create a new, blank document.

From the menu, choose Tools-Macro-Record New Macro. The following dialog box will appear:

In the Macro name box, enter the name you would like to use for your macro. It must not have any spaces and should not contain special characters.

Decide where to store the macro. If you would like it available in the current document only, hit the Store Macro in dropdown box and select your active document's name. If you would like the macro to be available for use with any file, select All Documents (Normal.dot).

Give the macro any description you like in the Description box. By default, the description will state the date the macro was recorded, and the user name. The user name automatically comes from the information under Word's Tools-Options, User Info tab.

Step 2: Assign the Macro to a Toolbar Button

Click on Tools-Customize and click on the Commands tab.

The following dialog box appears:

The Commands tab will be selected. In the right column, you will see an icon with a title similar to your macro's name. Click on the icon and drag it up to any location you choose on any toolbar you choose. Your mouse pointer must have a plus sign (+) hanging on it before you lift your finger from the mouse or the icon will not be placed. While dragging, you will see an X hanging on your mouse pointer.

Once your icon is placed on a toolbar, click the Modify Selection button, and various options will appear. The following describes only those options that apply for our purposes here:

Name: Type in the name of the macro as it should appear on your toolbar if you are going to use the text style button (described below).

Reset Button Image: Select this only if you have begun to edit an existing button image and you would like to reset it to the default.

Edit Button Image: Select this option to "draw" your own button image.

Change Button Image: Select this option to change the existing button image to one of your choosing from Office's collection of button images.

Default Style: Clicking this button makes the button on the toolbar show just the icon and no text.

Text Only (always): Clicking this button makes the button on the toolbar show just the text as it is shown in the Name box, which you can change.

Image and Text: Clicking this button makes the button on the toolbar show both the icon and the text.

When the button appears exactly as you would like it to appear on the toolbar, hit the close button on the Customize dialog box. The Stop Recording toolbar will appear. There are two buttons on the Stop Recording toolbar. The square is used to STOP recording and the lines and circle are used to PAUSE recording.

NOTE: If you accidentally close the Stop Recording toolbar, go to View-Toolbars and put a checkmark in the Stop Recording toolbar checkbox. If you accidentally use the X on the Stop Recording toolbar to stop recording a macro, go to View-Toolbars and put a checkmark in the Stop Recording toolbar checkbox and then use the appropriate button.

Step 3: Record the Macro

Perform the tasks you would like your new macro to perform for you. If you need to interrupt the macro recording to perform some non-related work, press the Pause button on the Stop Recording toolbar. Press the Pause button again to continue recording. Hit the Stop button to Stop recording when you have finished recording your tasks.

Step 4: Test the Macro

Always test a macro, particularly if you have created it to run for someone else. To test the macro, click on the toolbar button you've created, or hit Tools-Macro-Macros, and double-click the macro name.

View Your VBA Code

To view the code you just recorded, hit Alt-F11. You will now have the Visual Basic Editor (VBE) window open. Make sure that you see at least two panes in your window: the Project Explorer and the Code pane. If you do not see them, hit the View menu to select them.

In the Project Explorer pane, there are + and - symbols just as there are in Windows Explorer. A plus sign indicates there are objects you can view if you click it. A dash indicates that all objects are open and in view. As in Windows Explorer, you must click the plus or dash once, or double-click the folder twice to open/close the folder.

Unless you have designated otherwise, you should find your code under the Modules folder in normal.dot in the Project Explorer window. You can view your code in the Code pane.

If you have recorded several macros, and would like to have them run all at once, you can generally copy the contents into one macro. Just copy the code between the Sub MacroName() and the End sub lines and paste between the same items in another macro before or after the code for that macro. Be sure to delete the remaining Sub MacroName() and End sub lines that you left behind.

Events in Word 

VBA sometimes uses events in Word. For instance, you may want to have a menu that is available only in one template. Whenever you use that template, you want a menu that has buttons that run various macros for you. Dreamboat recently created a template for a client that would do just that: have an extra menu that is created on the Document_New event. Why? Because to properly use a template, you hit File-New. Here are just a few common Word events:

Document_Open

Document_Close

Document_Save

To see more events, just open the VBE window (Alt-F11), hit Help-Microsoft Visual Basic Help, then type in "event" without the quotes and hit enter.

Access ] Excel ] FrontPage ] Outlook ] PowerPoint ] Word ] [ Office VBA ] VBA Samples ] Newsletter ] Free Downloads ]

Copyright© 2003 - 2010 The Office Experts and Tickling Keys, Inc.

Microsoft Office®, Access®, Excel®, FrontPage®, Outlook®, PowerPoint®,
Visio®, and Word®, are registered trademarks of the Microsoft Corporation