A spreadsheet macro can do many tasks. This article is concerned with macros that type keystrokes. Mastering keyboard macros is the first step in learning to create menus and custom prompt lines as well as use looping and branching techniques.

Keyboard macros can automate almost any spreadsheet task, such as printing, formatting, or adjusting a worksheet. A keyboard macro is a series of keystrokes that the user chooses which are executed automatically whenever the user runs (invokes) the macro. Macros are a quick and easy way to execute tasks that you repeat often, or taks which you repeat less often but are tedious. Generally, if you repeat a task five times in one spreadsheet, that task is a candidate for a macro.

Keyboard macros consist of a series of sequentially executed keystrokes. They are executed exactly as stated each time the macro is invoked. Invoking a macro requires only two keystrokes rather than the many required to manually execute a task. A keyboard macro is stored as part of a worksheet and can be invoked whenever that worksheet is in memory.

For example, in planning a worksheet, you decide that it will contain eight columns that display dates. These columns need to be widened to 10 spaces and a cell in that column needs to be formatted for dates. Additionally, each of these date columns will be separated by two regular columns. To manually prepare this worksheet, you would go to the first cell that would contain a date and type /Worksheet Column Set- Width 10 Enter. ext, you would format the cell for dates by typing /Range Format Date 2 Enter. Finally, you would need to press the right arrow key three times to move the cell pointer to the next date column. This process requires 16 keystrokes repeated eight times, for a total of 128 keystrokes. Once created on a worksheet, a macro can do the same thing with two keystrokes repeated eight times, for a total of 16 keystrokes.

Creating and Using Keyboard

Macros

A keyboard macro can:

* Type a combination of letters, numbers, and symbols;

* Enter formulae and @functions;

* Press Esc, Tab, Enter, Insert, and Delete keys;

* Move the cell pointed around the worksheet or use the cell pointer to highlight a range;

* Select menu items from a menu;

* Select a file name or range name; and

* Activate most of Lotus 1-2-3's standard keys, including EDIT, WINDOW, and CALC.

The keystrokes in a macro are represented by a single character key (/WCS) or by key names enclosed in curly braces UP. The single character keystrokes represent the typewriter keys on the keyboard and are identical to the keys they represent. The only exception to this is the Enter key. The single character which represents the Enter key is the tilde ( ). The Lotus 1-2-3 standard keys such as the Function keys, Backspace, Home, and pointer-movement keys are represented by characters typed within curly braces(). Some special key indicators have an optional abbreviation, such as r for right arrow movement. You can type special key indicators in either uppercase or lowercase letters. The only keys that you cannot include in a macro are Lotus 1-2-3's HELP, STEP and COMPOSE keys and the computer's Ctrl, Alt, Shift, Caps Lock, Num Lock, Scroll Lock, and Print Screen. The macro representations for many of the Lotus 1-2-3 standard keys and pointer-movement keys are shown in Table 1.

A macro consists of a column of cells containing labels made up of the keyboard keystrokes. As you may remember, a label normally begins with an apostrophe. If the first keystroke in the cell is a letter, Lotus 1- 2-3 will insert the apostrophe automatically. If, on the other hand, the first keystroke in a cell is a digit, slash (/), one of the numeric characters (+.#@- or $), or one of the label characters ('caret or "), you must begin the entry by typing a label-prefix character, normally the apostrophe.

The column of cells that comprise the macro must begin with a cell that is in a named range. To name a range for a macro, move the cell pointer to the first (top) cell in the column of cells. Next, give the range a name by using the sequence: /range Name Create. Lotus 1-2-3 will ask for a name. Macro names always consist of a backslash (\) followed by a single letter (for example, \W). Ensure that you use the backslash (\) and not the slash (/). Lotus 1-2-3 does not distinguish between uppercase and lowercase letters in macro range names.

To invoke the macro, hold the Alt key down while pressing the letter that is the macro's name. You do not type the backslash to invoke the macro. When you invoke the macro, the keystrokes that make up the column of labels are then executed.

When the macro is invoked, it executes each keystroke in the top cell, then moves to the next lower cell and executes those keystrokes. It continues to execute keystrokes in a cell until there are no more keystrokes in that cell, then it moves to the cell immediately below. If the cell below is blank, the macro stops. The macro will continue as long as

TABULAR DATA OMITTED

there are keystrokes in the column of cells. All of the cells of a macro need not be in the named range; only the top cell must be in the named range for the macro to run. However, it is often desirable to include all the cells taht contain keystrokes within the named range in order to facilitate moving and copying the macro and combining the macro into other worksheets.

To ensure that the position of the range of cells that make up the macro does not interfere with the worksheet, the macro should be placed outside the working area of the worksheet. Normally, macros are placed in the lower right corner of the worksheet. However, you may place the macro in any unobtrusive place on the worksheet.

Example 1. This simple macro automatically types the word Sales and centers it in the current cell. You need only position the cell pointer in the cell that should contain the word Sales and invoke the macro. First, move to an unused portion of the worksheet, such as cell Z60. In cell Z60, type an apostrophe (') followed by a caret (caret), then the word

Sales followed by a tilde ( ) and press

Enter. The apostrophe indicates that this is a label. The caret will center the word Sales. the tilde ( ) has the effect of pressing the Enter key. The cell should appear as caretSales. . Remember, the apostrophe is not displayed in the cell. Next, use the keystrokes /Range Name Create and give this macro the name \S by typing \S, then press Enter. Since this is the only cell in this range, press Enter again to confirm that cell Z60 is the named range. Third, to help you to remember the name of this macro, move to cell Y60 and enter \S as a label--you must type the apostrophe first. If you prefer for this label to be centered, type caret\S. Alternatively, type "\S to have this reminder label right aligned in cell Y60.

Move to the first cell that you want to display the word Sales. While holding Alt down, press S. Move the cell pointer to the next cell you wnat to display Sales and again, while holding Alt down, press S.

Example 2. This macro widens columns to 10 spaces, formats the current cell to accommodate dates, and moves the cell pointer three columns to the right.

Move to an unused portion of the worksheet such as cell AB60. In cell AB60, type an apostrophe (') followed by /WCS10 and press Enter. In this cell or segment of the macro, the apostrophe indicates a label and the slash (/) calls the Lotus 1-2-3 menu. The W chooses Worksheet, the C chooses Column, the S chooses Set-Width. The 10 indicates that the column width should be 10 spaces and the tilde ( ) is the equivalent of pressing the Enter key. Note that there are no spaces between the letters or symbols. Next, move down to cell AB61 and type an apostrophe followed by /RFD2 followed by a tilde ('/RFD2 ) and press Enter. Move to cell AB62 and type r and press Enter. Use the Copy command to copy the contents of this cell (r) to cells AB63 and AB64.

Next, return to cell AB60 and use the keystrokes / Range Name Create to name this macro \D by typing \D, then press Enter. In this macro, name the entire range by using the down arrow key to highlight all five cells that contain labels, then press Enter. Finally, to help you to remember the name of this macro, move to cell AA60 and enter \D as a label. To enter \D as a label, you must type the apostrophe first. If you forget the apostrophe, the \D will cause every space of cell AA60 to display the letter D.

You are now ready to use this macro in constructing your worksheet. Move to the first cell that to display a date. While holding Alt down, press D. The macro should widen the column, format the cell to display a date (dd-mm-yy), and then move three columns to the right, where you can again invoke the macro to continue the process.

Techniques

Macros can take up any number of adjoining cells in a column; however, macros may not contain a blank cell. A blank cell stops the macro. Consequently, all macros end with a blank cell. To separate different macros that occupy the same worksheet column, leave several blank cells between them. Often it is a good practice to use a separate column for each macro.

Each cell can hold up to 240 characters; therefore, a macro with 240 keystrokes could fit into one cell. Generally, most cells should contain no more than nine or 10 characters.

You should break up a macro into logical segments and place each segment into a separate cell. For example, in the \D macro that was illustrated earlier, you broke the macro into three logical segments. The first segment was to widen the column, the second to format the cell, and the third to move the cell pointer. This segmenting will make error locating easier and will make the macro easier to understand or edit.

Before you construct a macro, manually step through the task and make notes of every keystroke. It is particularly easy to overlook presses of the Enter key. Use your notes to construct your macro.

Editing a Macro

Additional keystrokes can be appended to an existing macro to carry out other tasks. You may add tasks by entering the keystroke(s) or special key indicator(s) in the cell(s) below the existing macro.

Example 3. The Sales macro does exactly what it is supposed to but now you decide that each time Sales is entered, you want to move two columns to the right. The Sales macro can be modified to include moving two columns to the right by adding two more cells, each containing the right pointer-movement key, i.e., RIGHT, right, R, or r. Move to cell Z61 and type r. Next, move to cell Z62 and type r.

Move to a new cell that you choose to have display the word Sales and press S while holding down Alt. The macro now enters the word Sales centered in the cell and the cell pointer moves two columns to the right.

You can edit segments of the macro from the ready or edit mode. To edit a cell from the ready mode, move to that cell and type the label as modified. This step replaces the old segment with a new segment. To edit a cell from the edit mode, move to that cell and press F2. Appropriately modify the cell and then press Enter.

A segment can be deleted by moving to the cell to be deleted and using the keystrokes / Range Erase. This command will leave the cell blank and the macro will stop at this blank cell when it is invoked. To prevent the macro from stopping, move to the cell below and use the Move command to move the rest of the column of labels up one cell each.

Using a Repetition Factor

To make it easier to enter consecutive uses of the same macro key name, a spreadsheet lets you enter a repetition factor within the key name. The repetition factor is a number that tells how many times to repeat the key name. A single space separates it from the key name. For example, D 4 is equivalent to D D D D or pressing down arrow four times. Using a repetition factor can save a lot of typing when you are entering macro instructions.

Example 4. The date macro does exactly what it is supposed to but now you decide that you would like to be more efficient in creating macros. You would like to have the three right pointer-movements occupy only one cell. Move to cell AB65 and type r 3. Ensure that you leave a space between the r and the 3. This has the same effect as pressing the right pointer-movement key three times. Next, move to cell AB64 and use the / Range Erase command to erase cells AB64..AB62. Finally, use the / Move command to move the contents of cell AB65 to cell AB62.

Creating an Interactive Macro

An interactive macro allows the user to enter variable data. During execution, this macro will temporarily pause to allow the user to enter data directly from the keyboard into the worksheet. This type of macro can be very useful when several keystroke operations are very similar except for one or two items. To create an interactive macro, the ? (pause) macro keystroke instruction is use to stop macro execution temporarily to allow user entry. Execution continues when the user presses Enter.

Example 5. This macro moves the cell-pointer to the cell selected by the user while the macro is running, formats the selected cell for currency with cents displayed and pauses for the user to enter an amount. After the user enters the amount, the cell-pointer moves down one cell. To create this macro, move to an unused portion of the worksheet, such as cell AD60. In cell AD60,

entergoto?.Next,movetocell

AD61andenter/'rfc.Movetocell

AD62 and enter ?d. Name this macro \A and enter a reminder label in cell AC60.

When you use the macro (by invoking Alt A), the first segment (cell) will execute the GOTO command and pause for you to type a cell address. Pressing Enter restarts the macro and the tilde ( ) presses Enter to execute the GOTO command. The second segment formats the cell for currency displaying the cents. The third segment in cell AD62 pauses to allow entry of an amount. After typing the amount, press Enter to restart the macro. The cell pointer will move down one cell.

The only way to stop a macro and restart that macro where it left off is by use of the ? special key indicator. Your pressing Enter restarts the macro; therefore, ensure that the data that you have typed is correct before you press Enter and restart the macro.

Using LEARN Mode

The LEARN mode is a Lotus 1-2-3 feature that records your keystrokes as a macro at the same time you are using the program to perform a task. Before using this feature, you must define a range in the worksheet where the macro commands will be recorded. This is called the LEARN range. This range is a single column range that should be large enough to contain all the keystrokes in the macro that you will create. The range is defined by the command sequence / Worksheet Learn Range Cell..Cell Enter.

After defining the range which will store the keystrokes in the macro, move to an area of the worksheet where you will use the macro. To have Lotus 1-2-3 record your keystrokes as a macro, you must turn on the LEARN mode by pressing the Learn key (Alt-F5). After turning on the LEARN mode, every keystroke that you make until you turn the LEARN mode off is recorded. To turn off the LEARN mode, press Alt-F5 again. The Alt-F5 toggles the LEARN mode on and off. After pressing Alt-F5 for the second time, move to the macro range and check the macro to ensure correctness.

Example 6. This macro automatically fills all the cells in a row with equals signs (=) to give the effect of a double line. You observe that there are eight columns in the worksheet. You want a macro that will fill a cell and the seven cells to the right with the equals sign whenever you invoke the macro.

First, move to an unused portion of the worksheet, such as cell AB70. To define the range, issue the following command sequence: /Worksheet Learn Range AB70..AB79 Enter. To have Lotus 1-2-3 record your keystrokes as a macro, you must turn on LEARN mode by pressing the LEARN key (Alt-F5) after you have moved to the cell in which you wish to begin the task. For example, move to cell A7, then press Alt-F5. Begin the recording by typing \= and press Enter. (Remember, \= causes equals signs to fill the cell.) Next, press / Copy and press Enter. With the right pointer-movement key, move to the cell immediately to the right (B7) and press period (.) Use the right pointer-movement key to highlight six more cells, then press Enter again. Finally, press Alt-F5 to turn the LEARN mode off. Go to cell AB70 to check your macro.

If the macro appears correct, move to cell AA70 and enter \L as a label. Move to cell AB70 and name the range \L using the command sequence / Range Name Create.

You are now ready to use this macro in constructing your worksheet. Move to the leftmost cell that you want to display the double line. While holding Alt down, press L. This macro automatically provides a double line eight columns wide.

Using the Step Mode

Finding errors in a macro (especially a large one) can be difficult. STEP mode helps you locate errors by slowing down the macro execution to one keystroke at a time. You can use STEP mode execution on any macro to visually examine the effect of each keystroke.

To use STEP mode, press Alt-F2. The STEP indicator appears at the bottom of the screen. To run the macro, move to an appropriate cell and invoke the macro. The bottom of the screen no longer displays the STEP indicator. The status line now displays the cell address and the cell contents of the macro being executed, and highlights the macro instruction to be executed next. To execute the first macro instruction, press any key (spacebar is recommended). Continue execution of the macro by pressing the spacebar after each keystroke executes. Upon completion of the macro, press Alt-F2 to turn off the STEP mode. The Alt-F2 key toggles the STEP function on and off.

Once you locate an error, you can stop the macro execution by pressing CTRL-BREAK followed by Esc, then edit and rerun the macro. STEP mode is still on when the macro runs again to let you continue testing your macro commands.

Accountants should find this introduction to spreadsheet macros to be a time saver for the entering data and constructing the worksheet as well as manipulating and analyzing data on the spreadsheet.