5.14  Creating And Using Macros  

5.14.1 Creating Macros  

If you happen to perform certain tasks frequently in Microsoft Excel, such as applying bold, italics and a large type size to sheet titles or something similar, you can save time by automating these tasks. In fact, you can record a macro for almost any series of actions that you perform with Microsoft Excel.

In order to create a macro, you simply turn on the macro recorder, perform the sequence of tasks that you want to record and then turn off the macro recorder. As you record the macro, the commands are automatically translated into the Visual Basic language and stored in a separate module sheet in the workbook.

Once the macro is recorded, you can run it by choosing the macro name in the Macros dialog box. As you will see later in this lesson, you can also run macros by clicking a macro button or by using a shortcut key combination.  

5.14.2 Recording macros  

1.    On the Tools menu, point to Macro, and then click Record New Macro.

2.    In the Macro name box, enter a name for the macro.

3.    The first character of the macro name must be a letter. Other characters can be letters, numbers, or underscore characters. Spaces are not allowed in a macro name; an underscore character works well as a word separator.

4.    To run the macro by pressing a keyboard shortcut key, enter a letter in the Shortcut key box. You can use CTRL+ letter (for lowercase letters) or CTRL+SHIFT+ letter (for uppercase letters), where letter is any letter key on the keyboard. The shortcut key letter you use cannot be a number or special character such as @ or #. The shortcut key will override any default Microsoft Excel shortcut keys while the workbook that contains the macro is open.

5.    In the Store macro in box, click the location where you want to store the macro.

6.    If you want a macro to be available whenever you use Excel, store the macro in the Personal Macro Workbook in the Excel Startup folder.

7.    To include a description of the macro, type the description in the Description box.

8.    Click OK.

9.    If you select cells while running a macro, the macro will select the same cells regardless of which cell is first selected because it records absolute cell references. If you want a macro to select cells regardless of the position of the active cell when you run the macro, set the macro recorder to record relative cell references. On the Stop Recording toolbar, click Relative Reference. Excel will continue to record macros with relative references until you quit Excel or until you click Relative Reference again.

10. Carry out the actions you want to record.

11. On the Stop Recording toolbar, click Stop Recording

Tip   If you want a macro to select a specific cell, perform an action, and then select another cell relative to the active cell, you can mix the use of relative and absolute references when you record the macro. To record a macro by using relative references, make sure that Relative Reference is pressed in. To record with absolute references, make sure Relative Reference is not pressed in.  

5.14.4 Running a macro  

After you record a macro, you will usually run it in Microsoft Excel; however, you can run the macro from the Visual Basic Editor while you are editing the macro. To interrupt the macro before it completes the actions you recorded, press ESC.  

Run a macro in Microsoft Excel

To interrupt a macro before it completes its actions, press ESC.

1.       Open the workbook that contains the macro.

2.       On the Tools menu, point to Macro, and then click Macros.

3.       In the Macro name box, enter the name of the macro you want to run.

4.       Click Run.  

Run a macro from a Visual Basic module

1.       Open the workbook that contains the macro.

2.       On the Tools menu, point to Macro, and then click Macros 

3.       In the Macro name box, enter the name of the macro you want to run.

4.       Click Edit.

5.       Click Run Sub/User Form.

Copyright © 2001 Selfonline-Education. All rights reserved.