Macros are an advanced topic in Lotus 1-2-3 that let you automate many spreadsheet functions. There are two parts to the 1-2-3 macro language:

The primary skill you need to develop macros is an intimate knowledge of 1-2-3. That's because when you write a macro, you're using 1-2-3 without seeing the results of your commands. That's tricky at first, but something macro writers are used to.

In its most basic form, a macro is a set of stored keystrokes. You type these keystrokes into a series of cells in the worksheet. Then, you give a range name to the top cell of the macro so that 1-2-3 can find the macro when you want to run it.

For example, suppose you find yourself frequently formatting a single cell with the Currency format, two decimal places. This task is a good candidate for a macro--something repeated many times. Now, think about the keystrokes used in the process:

In 1-2-3 macro notation, these commands would look like this:

/rfc2~~

That, in fact, is a macro. You can recognize many of the characters in the macro as representing the keys you'd type to format the cell. The tilde (~) represents a press of the Enter key in a macro.

The most basic type of 1-2-3 macro is a series of keystrokes. You type the keystrokes as a label in a cell or cells in the worksheet, and upon command, 1-2-3 replays the keystrokes. The first step in using a macro, then, is to type the keystrokes in a cell or cells in the worksheet.

When typing macro keystrokes, pick an out-of-the way location for them. For example, you might start your macros in cell A7000--certainly a location that most worksheets won't get close to using. Since macros start with a named range, you can move to them quickly by pressing the Goto key (function key F5) and pressing the Name key (function key F3) to get a menu of range and macro names.

Remember, macros are labels, and you can manipulate them with any of the 1-2-3 commands that work with labels--copy, move, erase, whatever.

You can enter macro keystrokes in any form you like. You could type the keystrokes for the example macro discussed above in a single cell like this:

/rfc2~~

Or, you might place each keystroke in its own cell like this:

/
r
f
c
2
~
~

Both of these methods work equally well, because 1-2-3's macro processor starts with the top cell of the macro, executes the keystrokes in that cell, moves down to the next cell, executes its keystrokes, and repeats until it finds an empty cell or a cell that contains the special macro keyword {quit}.

As you gain more experience with macros, you'll find that it makes sense to group related keystrokes together in a single cell. Most people would write this simple macro in a single cell.

When typing macro keystrokes, you often must type a label prefix to force 1-2-3 to enter your typing to a cell. For example, the first keystroke in this macro is the slash key. If you move to a cell and type the slash key, 1-2-3 produces the menus. Further typing will perform menu commands or produce error beeps. To type this macro, you must start with a label prefix (most commonly the apostrophe for left alignment). Thus, to type this macro, you'd actually type '/rfc2~~ and press Enter. In general, when a macro cell starts with the slash key or a number, is is a formula or a label that the macro should type, you must start the macro cell with a label prefix.

Once you've typed the macro keystrokes, you must give a range name to the top cell of the macro. Macro range names are the backward slash followed by a single character from the alphabet. For example, you might give this macro the range name \c, the "c" standing for currency format.

Since macro names can be just a single letter from the alphabet, you can have just 26 macros in any worksheet. Later, though, we'll see that a single macro might present a menu of other macros which don't need to be names with a single character, so by using menus you can have as many macros as you want.

There is a special macro named \0 (that's the digit zero, not the letter O). This macro is called the auto-execute macro, because every time you perform the File Retrieve command, 1-2-3 scans the worksheet for a macro called \0. If 1-2-3 finds such a macro, it runs it automatically. You can't type Alternate-0 from the keyboard to run this auto-execute macro, so if you want to run the \0 macro from the keyboard, you must give it an additional macro name.

To name a macro, you can move to the top cell of a macro and issue the Range Name Create command. Another way to name a macro is to type the name of the macro in the cell to the left of the top cell of the macro. Then, use the Range Name Labels Right command to name the macro. This technique makes it easy to name the macro, and it also documents the name of the macro. We'll see an example of this in a moment.

Some macros are special purpose, and have value only in the worksheet they were created for. Others, like the date macro given as an example later on, are more general and useful in many worksheets. When developing these general macros, you might create them in a test worksheet, and when tested and perfected, saved in their own worksheet files containing just a single macro. Then, when you decide to use one of these macros in a new worksheet, you can use the /File Combine Formulas Entire-File command to bring the macro into the current worksheet. One difficulty is that range names do not come into the current worksheet, so you'll have to create the named ranges the macro uses. That's another good reason for using the macro naming technique mentioned above--it makes establishing the named ranges easier.

After you've named the macro, you can run it. Since this macro is named \c, you'd run the macro by holding down the Alternate key and pressing C. At this time, the macro processor takes over and performs the macro keystrokes, just as though you were typing them yourself.

Before running a macro, you can press the Step key, which is Alternate-F1. This action places you in STEP mode, as indicated at the bottom of the screen. Now, if you run a macro, 1-2-3 executes it one step at a time. Between each step, 1-2-3 displays a flashing SST indicator at the bottom of the screen as a reminder to press any key to proceed to the next step. To leave the single step mode and return to regular, full-speed macro execution, press Alternate-F1 again.

To stop a macro, press the Break (Control-Break) key. Most of the time, 1-2-3 will display the ERROR indicator at the top right of the screen, so press Escape or Enter to clear the error and return to READY mode.

Here's a detailed script for creating and running this macro. Start this exercise with a blank screen.

This completes the making of the macro. Let's run it now.

At this time, 1-2-3 runs the macro. You'll see the 100 change to $100.00 as evidence the macro worked. If you looked closely, you may have seen the menus flash at the top of the screen. If you want, run the macro again and watch for the control panel to operate, but if you have a very fast computer, you may not be able to see anything.

Each special key on the keyboard has a notation for use in a macro. Here's a list of keystroke notations for use in macros:

Besides the keyboard, function key, and menu commands, the 1-2-3 macro language contains many macro commands. This reference does not describe all the macro commands, as some are quite obscure and specialized.

When 1-2-3 runs a macro, it plays back the macro keystrokes just as though you were typing them yourself. The control panel will operate too, showing menus, prompts, and other information. While the operation of the control panel is harmless, it may confuse someone who's using your macros and isn't familiar with 1-2-3. Also, operating the control panel uses computer processor time. If you place the command {paneloff} at the top of the macro, 1-2-3 won't display changes in the control panel. {Panelon} restores operation of the control panel. At the end of a macro, 1-2-3 automatically turns the control panel on if it was off.

This is a cosmetic command, never absolutely necessary to use.

In a similar manner to {paneloff}, {windowsoff} freezes the screen, so that cursor movements won't cause a change in the display. This is useful because it can greatly speed macro execution. Redrawing the screen takes a lot of 1-2-3's time, so eliminating the drawing speeds the macro. {Windowson} restores screen updating.

The {beep} macro command sounds the computer's bell during a macro. You can control the pitch of the beep by using a number from 1 to 4 as an argument, as in {beep 3}. Use the {beep} command to attract someone's attention during a macro.

The {indicate} macro command lets you control the mode indicator at the top right of the screen. For example, if you put the command {indicate WAIT} in a macro, the mode indicator will display the word WAIT. Using the {indicate} command with no arguments restores 1-2-3's normal READY indicator.

If you change the mode indicator during a macro, 1-2-3 retains that mode indicator even when the macro finishes. Normally, you should use the indicate command with no argument (as in {indicate}) at the end of a macro to restore normal operation of the modi indicator.

Use {indicate} during a macro to show the user the progress of the macro. This is especially important if you've used the {paneloff} and {windowsoff} commands, because then the computer will appear to be "dead" during the macro. Using an appropriate mode indicator, and perhaps changing it throughout the macro, will let your user know the macro is working.

The question mark macro command causes macro execution to stop. You can then type whatever you like or move about the worksheet. When you press the Enter key, 1-2-3 resumes the macro. This command allows interactive macros, where the user has input during the macro.

It's important to note that pressing the enter key after moving about the during a {?} pause signifies that the user is through. If you want the macro to press the Enter key, you must include a ~ after the {?}. For example, look at this macro:

/rfc2~{?}~

In this macro, 1-2-3 issues the Range Format Currency 2 decimal places command, then stops and lets the user move about in response to the range to format question. When running the macro, the user will use cursor keys to expand the range and then press Enter. The last ~ in the macro then issues the Enter key to complete the range highlighting. What if the macro was only like this:

/rfc2~{?}

In this case, after expanding the range highlight and pressing the Enter key, 1-2-3 stops macro execution, and the control panel will still be asking for the range to format. The user will have to press the Enter key again to complete the range highlighting.

The {get} command stops the macro and waits for the user to type a single keystroke. Once the user presses a key, the {get} command stores it and continues with the rest of the macro. The {get} command looks like this:

{get location}

Location is a cell address or range name where 1-2-3 will store the keystroke. If location is a named range, 1-2-3 stores the keystroke in the top left cell of the range.

The key pressed may be an alphabet or digit key, in which case the character is stored as a label. If the user presses a special key (cursor movement, function key, or otherwise), 1-2-3 stores the macro representation of that key as a label. For example, pressing the Down arrow key in response to the {get} command produces the label '{down}.

These two commands provide a method for asking the user to type something and then accepting the input and storing it in a cell. {Getlabel} stores the input as a label, {getnumber} stores it as a number, if the user typed a valid numeric entry. The syntax of the commands looks like this:

{getlabel prompt,location}
{getnumber prompt,location}

Prompt is a character string that usually asks the user to type something--their name, the amount of a check, or whatever. Location is a cell or range name. After the user types something and presses the Enter key, 1-2-3 places their typing in that cell. For example:

{getnumber "Enter the amount of the check",a50}
{getnumber "Enter the amount of the check",check}

The first example displays the prompt Enter the amount of the check at the control panel, and then places the number the user types in cell A50. The second command performs the same action, but places the result in the cell named check.

{getlabel "Type your name :",a50}
{getlabel "Type your name :,name}

These two examples use the {getlabel} command, as the requested information is a label, not a numeric entry. Notice also that these two examples use a colon and a space after the word name. That makes the prompt easier to respond to. For example, if the command was

{getlabel "Type your name",a50}

the control panel would display

Type your name

After typing the name, the control panel looks like

Type your nameJohn Doe

Note that the first character of the name John Doe appears immediately after the prompt string. Some users may type a space before the name to make the response look better, but that action introduces a space to the name in cell A50, which probably won't be welcome. But the command

{getlabel "Type your name :",a50}

produces a result like this:

Type your name: John Doe

This looks a lot better and is less confusing to the user.

The {menubranch} and {menucall} commands let you create your own menus. These macro menus look just like 1-2-3's own menus, and work the same way.

A macro menu looks like this:

A B C D
10 fmenu Currency Fixed Percent
11 Format as Currency Format as Fixed Format as Percent
12 /rfc2~~ /rff2~~ /rfp2~~

The menu itself consists of the range B10..D11. The commands you want to appear in the menu are on the top row of the menu, row 11 in this example. This menu, then, will display the three commands Currency, Fixed, and Percent. The prompts for each of the menu items appear directly below the menu commands. Under the prompts, place the macro instructions that belong to each command. For this simple example, the macro commands format the current cell with the selected format.

Each menu has a range name so that 1-2-3 can find it. The range name can be any valid range name, and you need name only the top left cell of the menu, cell B10 in this example. (1-2-3 senses the end of a macro menu by an empty cell, so in this example, cell E10 must be blank. Otherwise, the contents of E10 would be considered part of the menu.) In this example, we typed the word fmenu in cell A10 for documentation purposes, and so we could use the Range Name Labels Right command to name cell B10.

To use this menu, you'll need to make a macro like this:

A B
1 \f {menubranch fmenu}

The {menubranch} macro command means branch to the menu whose top left corner has the range name fmenu and display the menu. After the user makes a selection from the menu, 1-2-3 executes the macro commands associated with that menu selection. When those macro commands run to completion, the \f macro is finished.

The {menucall} command works as {menubranch} does, but after 1-2-3 finishes the macro commands for a menu selection, it returns to the cell that contained the {menucall} command and continues executing macro commands.

Normally, when running a macro you can stop the macro by pressing Control-Break. But there may when you want to disable the Control-Break key action, thereby preventing someone from prematurely terminating the macro. The {breakoff} command does this. Once a macro encounters the {breakoff} command, pressing Control-Break does nothing. Placing the {breakon} command in a macro restores the operation of the Control-Break key. At the end of a macro, 1-2-3 automatically restores the operation of Control-Break.

Don't place {breakoff} in a macro until you're sure the macro is operating correctly. You'll want to retain the ability to stop your own macros if they go out of control. Once they're perfected, add the {breakoff} command.

The {branch} macro command transfers macro execution to another location and continues reading macro commands there. Examples:

{branch b45}
{branch step2}

The first example jumps to cell B45 and continues the macro. The second example uses a named range to jump to, which is the preferred practice.

Don't confuse the {branch} command with the {goto} command, which is the macro representation for function key F5, the Goto key. The {branch} command affects only where 1-2-3 looks for the next macro command, while the {goto} command moves the cell pointer to a cell. There's a big difference.

The {if} macro command lets a macro do one of two things, based upon some condition. This command works by testing a condition, and then either reads additional macro commands from the same cell, or reads them from the following cell. Here's an example:

A
1 {if b5=0}{branch ok}
2 {branch notok}

You can see that the syntax for the {if} command is {if condition}, where condition is something that evaluates to true or false. (Lotus 1-2-3 considers the numeric value zero to be false; anything else is true.) If the condition is true, the macro executor continues reading keystrokes from the same cell that the {if} command is in, in this case the command {branch ok}. If the condition is false, the macro executor reads macro commands from the following cell. In this case, it moves to cell A2 and performs the command {branch notok}.

In many programming languages, we might write this macro fragment like this:

If b5=0 then
branch to ok
else
branch to notok

We might call the {branch ok} command the "then" clause, and the {branch notok} command the "else" clause.

Be careful with the {if} command. Remember that after 1-2-3 executes the "then" clause of the macro, it will move on to the next cell and execute the "else" clause, unless the "then" clause contains a branching or quitting command.

The {quit} command indicates the end of a macro. It's not necessary to use this command to terminate a macro, because a blank cell ends a macro. But it's a good idea to use the {quit} command, as it formally marks the end of a macro. Also, if for some reason the blank cell that ends a macro gains contents of some sort, those contents are considered part of the macro.

The {for} macro command lets you repeatedly execute a macro. This command, then, provides a looping capability. The syntax of the {for} looks like this:

{for counter-location,start-number,stop-number,step-number,starting-location}

An example {for} command might look like this:

{for a50,1,10,1,printmac}

Starting-location is a range name that marks the start of the macro you'd like to repeatedly execute. This macro doesn't need to be named with the backwards slash-single letter convention. Counter-location is a cell address or named cell that the {for} command will use to hold a counter value.

The {for} command works like this, using the above example:

In our example, during step 1 1-2-3 places the value 1 in cell A50, the counter-location. In step 2, it compares 1 to 10. Since 1 is less than 10, 1-2-3 runs the macro named by printmac. Then, 1-2-3 adds the step-number of 1 to cell A50, so it becomes 2 and 1-2-3 runs printmac again, because 2 is still less than or equal to 10. Eventually cell A50 grows to contain 11, and since 11 is not less than or equal to 10, the {for} command is finished and 1-2-3 looks to the next cell for more macro commands.

It's possible that the macro named in the {for} command will never be run. A command like {for a50,11,10,1,printmac}, where the start-number is greater than the stop-number never runs the printmac macro. A {for} command like {for a50,11,10,0,printmac} runs until your computer wears out. Because the step-value is zero, the counter-location never exceeds the stop-number, so the {for} command never stops.

During the {for} command, you can reference the counter-location cell in a formula or other command. This provides a handy mechanism for counting or numbering tasks.

The {forbreak} command, when used in a macro that's run under control of the {for} command, stops execution of the {for} command. The macro executor then runs the macro commands in the cell following the {for} command, just as though the {for} command terminated normally.

If you find that a macro performs a set of keystrokes (or even a similar set of keystrokes), you might write a macro subroutine to perform these keystrokes. The macro subroutine has a name, and instead of writing the keystrokes in the macro, you can make a call to the subroutine.

Suppose you need to type your name several times during a macro. You could write a macro that looks like this:

A B
10 myname Bob Weeks~
11 {return}

Note that the subroutine starts with a named range, in this case the range name myname for cell B10 as indicated by the range naming conventions. At the end of the subroutine, use the macro command {return} to indicate the end of the subroutine. To use this subroutine, place this command in a macro:

{myname}

When 1-2-3 comes to the {myname} macro command, it will transfer control to the cell with the range name myname and will execute macro keystrokes there. When 1-2-3 comes to the {return} command in the subroutine, it transfers control back to the {myname} command in the calling macro and continues executing additional commands.

You can also pass information to macro subroutines through the use of arguments. Suppose, for example, you need to compute the area of a circle several times during a macro. This is a good task for a subroutine with an argument, so let's write it:

A B
10 area {define radius:value}
11 +@pi*radius^2
12 {return}
13 radius

The first line of the area subroutine establishes the existence of an argument for the macro. In this case, the argument is a value (instead of a string) and upon calling the subroutine, 1-2-3 will place the argument in the cell with the range name radius. Cell B11 in the subroutine types the formula to calculate the circle's area, using the radius argument. Cell B12, the {return} command, indicates the end of the subroutine. Cell A13, when used with the Range Name Labels Right command, sets up cell B13 with the range name radius.

To use this subroutine, place this command in a macro:

{area 10}

When 1-2-3 executes this statement, the argument in the calling command (the number 10) is placed in the cell with the range name radius because of the action of the {define} command. Then, the macro subroutine executes.

The {define} command can specify two types of arguments--a value or a string. A value argument is a number, cell address, or formula (which can be either a numeric or string formula). A string argument is a label. Omitting the :value or :string suffix means the argument is a string.

The {blank} macro command erases the contents of a cell or range. Typical {blank} commands look like this:

{blank A50}
{blank A50..B60}
{blank outrange}

The first command erase a single cell, the second erases a range of cells, and the third erases a named range, which could be a single cell or group of cells.

The {let} command lets a macro place a value in a cell. The syntax of the command is

{let location,number}
{let location,string}

Location is a cell address or named range (if the named range covers more than a single cell, 1-2-3 uses the top left cell). Number and string are expressions that evaluate to a number or label. The {let} command places the number or string in location.

{let A50,100}
{let title,Amount Due}
{let title, "Amount Due, Net"}

The first example places the numeric value 100 in cell A50. The second example places the label Amount Due in the cell named title. In the third example, we had to enclose the label in quotation marks because it contained a comma.

1-2-3 tries to evaluate what you're placing in a cell as a numeric value or string. If it can't evaluate it as a number or string, it creates a label. You can control whether 1-2-3 evaluates the second argument of the {let} commands as a number or a label:

{let A50,5+3:value}
{let A50,5+3:string}

In the first example, 1-2-3 evaluates the formula 5+3 and places the result (the number eight) in cell A50. In the second example, 1-2-3 places the label '5+3 into cell A50. This second example is called a string-valued expression, in that it looks like a formula, but by using the :string suffix, 1-2-3 will use the argument as a label.

You may have difficulty with commands such as {getlabel} and {getnumber} due to the way 1-2-3 updates the worksheet screen and calculates the worksheet. You might, for example, use {getlabel} to place data in a cell, but when the macro finishes, the data doesn't appear in the cell where it should be. It eventually will get there, but might not be there by the time you need it.

The difficulty arises in that there are two factors that control 1-2-3 worksheet updating and calculation. Worksheet updating is showing the result of a macro command such as {getlabel}. Calculating is updating the values of formulas. These two actions are different.

Whether 1-2-3 updates the worksheet after a command like {getlabel} depends on whether you use a ~ after the command. If you do, 1-2-3 updates the worksheet. If you don't include the ~, 1-2-3 doesn't update the worksheet. One ~ is sufficient to update the entire worksheet, so you might use several {getlabel} commands with a ~ after the last command only.

If the calculation mode is set to automatic, a ~ in a macro also causes 1-2-3 to recalculate the entire worksheet--possibly a very time-consuming action. With calculation set to manual, the ~ causes a update of the worksheet only, with no formula calculation. With calculation set to manual, use {calc} in a macro to recalculate formulas.

These macro commands are affected in this way by the inclusion or omission of the ~: {blank}, {getlabel}, {getnumber}, and {let}. Other 1-2-3 macro commands require a {calc} to update the worksheet.

A common task for a macro is to perform typing tasks, such as your name and address. Here's an example macro that types a name and address:

A B
1 \n Bob Weeks~
2 {down}
3 '4455 N. Rockwell
4 {down}
5 Chicago, IL 60625~
6

Discussion:

This macro will sum a column of numbers. It makes extensive use of cursor navigation keys, including the End key. For this macro to work properly, the cell pointer must be anywhere in the column of figures to add except for the bottom figure.

Here's a screen view of a macro to accomplish this tasks. This macro follows a common convention for macro writing. In column A (or whatever column is to the left of the macro commands), type labels that supply range names for the cells to their right. In column B, type the macro commands. In a column to the right, write a comment or note for each macro cell. This documentation, (both the range names and the comments) is important. The 1-2-3 macro language can be confusing when you're trying to figure out what the macro you wrote last month does, and any notes or comments you make will help clarify matters for you or someone else.

A B C D E F
1 \c {end}{down} Move to end of column
2 {down} Move down one more cell
3 \-~ Type a cell full of minus signs
4 {down} Move down one more cell
5 @sum( Start typing the sum function
6 {up}{end}{up} Move to top of column
7 . Anchor cell pointer with period
8 {end}{down} Move to end of column
9 {up} We moved one cell too far
10 )~ End sum function and press enter
11 {quit} Quit the macro

View of the macro from the Cambridge Spreadsheet Analyst:

B1 '{end}{down}
B2 '{down}
B3 '\-~
B4 '{down}
B5 '@sum(
B6 '{up}{end}{up}
B7 '.
B8 '{end}{down}
B9 '{up}
B10 ')~
B11 '{quit}

Discussion:

As you can see, 1-2-3 macros can be quite general if you plan them properly. To write this macro, however, you need to be very familiar with the way the End key operates--and when you're typing the macro, you don't get to see the effects of your macro commands. Many people run through the macro and write down the keys they pressed, developing a script to follow when creating the macro.

One very good use for a macro is to make date entry easier. Dates in 1-2-3 must be entered by using the @date function like this:

@date(year,month,day)

To enter September 1, 1987, you'd type @date(87,9,1). This represents quite a bit of typing and forces you to enter the elements of the calendar in an unfamiliar order. Also, after entering a date most people will format the date using one of the date formats. We'll develop a macro that relieves you from typing the @date function, lets you enter the date elements in a more familiar order, and formats the date.

Here are the steps we need to implement.

Here's a screen view of a macro to accomplish these tasks:

A B C D E F G H
1 \d {paneloff}{windowsoff} Panel & windows off
2 {getnumber "Enter Month: ",Get the month to zmonth
3 {getnumber "Enter Day: ",zdGet the day to zday
4 {getnumber "Enter Year: ",zGet the year to zyear
5 @date(zyear,zmonth,zday)~ Type the date function
6 {edit}{calc}~ Use Edit and Calc to "numberize"
7 /rfd4~ Format as Date 4
8 /rezmonth~ Erase zmonth
9 /rezday~ Erase zday
10 /rezyear~ Erase zyear
11 {panelon}{windowson} Panel & windows on
12 {quit} Quit the macro
13 zmonth Cell for month
14 zday Cell for day
15 zyear Cell for year

View of the macro from the Cambridge Spreadsheet Analyst:

B1 '{paneloff}{windowsoff}
B2 '{getnumber "Enter Month: ",zmonth}
B3 '{getnumber "Enter Day: ",zday}
B4 '{getnumber "Enter Year: ",zyear}
B5 '@date(zyear,zmonth,zday)~
B6 '{edit}{calc}~
B7 '/rfd4~
B8 '/rezmonth~
B9 '/rezday~
B10 '/rezyear~
B11 '{panelon}{windowson}
B12 '{quit}

Discussion:

When creating this macro, you can use one Range Name Labels Right command with the range A1..A15 to give all range names needed for this macro. This is a special advantage with this macro, since due to its general nature and applicability in many worksheets, you'll probably use the File Combine command to bring this macro intomany worksheets. Since 1-2-3 doesn't bring in the range names from the incoming worksheet, documenting range names is even more important.

One thing this macro doesn't do is check the validity of the date. If the user types 13 for the month, the @date function will return the error value ERR.

A variation of this macro enters today's date with the press of a single key.

A B C D E F G H
1 \t {paneloff}{windowsoff} Panel & windows off
2 @int(@now)~ Type the @int and @now functions
3 {edit}{calc}~ Use Edit and Calc to "numberize"
4 /rfd4~ Format as Date 4
5 {panelon}{windowson} Panel & windows on
6 {quit} Quit the macro

B1 '{paneloff}{windowsoff}
B2 '@int(@now)~
B3 '{edit}{calc}~
B4 '/rfd4~
B5 '{panelon}{windowson}
B6 '{quit}

This macro types the formula @int(now) to produce today's date, then "numberizes" and formats it.

This macro, which is quite simple, illustrates the use of a menu within a macro. The macro presents a menu of possible formats (Currency, Comma, Fixed, etc.) for the user to chose from. After the user selects a format, the macro applies that format (using two decimal places) to the current cell.

Screen view of the macro:

A B C D E F G H
1 \f {paneloff}{windowsoff} Turn panels & windows off
2 {menucall fmenu} Call the menu fmenu
3 {panelon}{windowson} Panel & windows on
4 {quit} Quit the macro
5
6 fmenu Currency , Fixed Percent Scientific
7 Format asFormat asFormat asFormat asFormat as Scientific,
8 /rfc2~~ /rf,2~~ /rff2~~ /rfp2~~ /rfs2~~
9 {return} {return} {return} {return} {return}

The view of the macro that the Cambridge Spreadsheet Analyst produced:

MACRO: <\F> LISTING
B1 '{paneloff}{windowsoff}
B2 '{menucall fmenu}
B3 '{panelon}{windowson}
B4 '{quit}

MACRO: <FMENU> LISTING
MENU LINE: 'Currency ', 'Fixed 'Percent 'Scientific
MENU OPTION: 'Currency
MENU PROMPT: 'Format as Currency, 2 decimal places
B8 '/rfc2~~
B9 '{return}

MENU OPTION: ',
MENU PROMPT: 'Format as Comma, 2 decimal places
C8 '/rf,2~~
C9 '{return}

MENU OPTION: 'Fixed
MENU PROMPT: 'Format as Fixed, 2 decimal places
D8 '/rff2~~
D9 '{return}

MENU OPTION: 'Percent
MENU PROMPT: 'Format as Percent, 2 decimal places
E8 '/rfp2~~
E9 '{return}

MENU OPTION: 'Scientific
MENU PROMPT: 'Format as Scientific, 2 decimal places
F8 '/rfs2~~
F9 '{return}

Discussion:

This macro asks the user to type a number for the width of the current column, then issues the command to set the width.

The trick with this macro is that we need to make the macro type a different number for the column width each time we run the macro. We do this by naming one of the cells in the macro for use with the {getlabel} command. The number (which is actually a label--macro cells must be labels) then becomes part of the macro, and the macro processor types it in turn.

Screen view of the macro:

A B C
1 \w {paneloff}{windowsoff} Panel and windows off
2 {getlabel "Enter width for column: ",width} Get the width
3 /wcs Start /wcs
4 width Number for width
5 ~ Enter key to complete
6 {panelon}{windowson} Panel and windows on

The view of the macro that the Cambridge Spreadsheet Analyst produced:

MACRO: <\W> LISTING
B1 '{paneloff}{windowsoff}
B2 '{getlabel "Enter width for column: ",width}
B3 '/wcs
B4
B5 '~
B6 '{panelon}{windowson}

Discussion:

This macro shows that at times you have to resort to tricks to make your macros work. The Lotus 1-2-3 manual won't show you how to perform tricks like this--you have to use your experience with 1-2-3 and your imagination to discover solutions to these problems.

This macro prints multiple copies of a spreadsheet, using the {for} command to control the printing. The macro consists of two macros. The first macro asks for the number of copies, the range to print, and runs a {for} command. The second macro prints one copy of the spreadsheet, and is run as many times as the {for} command dictates. This macro requires that the range of the spreadsheet to print is a named range.

Screen view of the macro:

A B C D E F G H
1 \p {paneloff}{windowsoff} Panel & windows off
2 {getlabel "Enter range to pGet print range to prntrange
3 {getnumber "Number of copieGet number of copies to copies
4 {for counter,1,copies,1,dopThe for statement to run doprint
5 {quit} Quit the macro
6
7 doprint /ppr The print printer range command
8 prntrange The range name to print
9 ~ The enter key
10 agpq Align, Go, Page advance, quit
11 {return} The end of the doprint macro
12
13 copies Cell of number of copies
14 counter Cell for counter value

The view of the macro that the Cambridge Spreadsheet Analyst produced:

MACRO: <DOPRINT> LISTING
B7 '/ppr
B8
B9 '~
B10 'agpq
B11 '{return}

MACRO: <\P> LISTING
B1 '{paneloff}{windowsoff}
B2 '{getlabel "Enter range to print: ",prntrange}
B3 '{getnumber "Number of copies: ",copies}
B4 '{for counter,1,copies,1,doprint}
B5 '{quit}

Discussion:

The \p macro runs the doprint macro however many times the user desires. Let's examine the doprint macro to see how it works.

As you can see, the function of the doprint macro is to print a single copy of a named range. The {for} command from the \p macro decides how many times to run the doprint macro.

This macro uses the File Combine command to incorporate data from three departmental worksheets (depta, deptb, and deptc) into the main, company-wide worksheet. In each of the departmental worksheets, the row of net income figures is a named range called netincome. We want to place the data from the depta worksheet starting at cell B5, deptb at B6, and deptc at B7. Row 9 of this worksheet contains @sum formulas to add the incoming data. There is a range name data that covers rows 5, 6, and 7 starting in column B.

The macro uses a subroutine called combine to perform the File Combine command for each of the three departments.

Screen view of the worksheet and macros:

A B C D E F G
1 All Departments
2
3 Jan Feb Mar Apr May Jun
4 ------- ------- ------- ------- ------- -------
5 Department A
6 Department B
7 Department C
8 ------- ------- ------- ------- ------- -------
9 Total $0.00 $0.00 $0.00 $0.00 $0.00 $0.00
10
11 \c {indicate WAIT} Mode indicator to WAIT
12 /wgrm Set calc. to manual for speed
13 /redata~ Use /Range Erase to erase
14 {goto}data~ Position cell pointer
15 {combine depta} File Combine from DeptA
16 {down} Position cell pointer
17 {combine deptb} File Combine from DeptB
18 {down} Position cell pointer
19 {combine deptc} File Combine from DeptC
20 {goto}data~ Position cell pointer
21 {down} for formatting
22 /rf,2~{end}{right}~ Format row as comma, 2 decimals
23 {down} Position cell pointer
24 /rf,2~{end}{right}~ Format row as comma, 2 decimals
25 /wgra Set calc. back to automatic
26 {calc} Press the F9 Recalculation key
27 {indicate} Restore the mode indicator
28 {quit} Quit the macro
29
30 combine {define infile,string} Location for incoming file name
31 /fcan File Combine Add Named-range
32 netincome~ The range name to combine
33 infile The worksheet to combine
34 ~ An Enter key
35 {return} The end of the subroutine

The view of the macro that the Cambridge Spreadsheet Analyst produced:

MACRO: <COMBINE> LISTING
B30 '{define infile,string}
B31 '/fcan
B32 'netincome~
B33
B34 '~
B35 '{return}

MACRO: <\C> LISTING
B11 '{indicate WAIT}
B12 '/wgrm
B13 '/redata~
B14 '{goto}data~
B15 '{combine depta}
B16 '{down}
B17 '{combine deptb}
B18 '{down}
B19 '{combine deptc}
B20 '{goto}data~
B21 '{down}
B22 '/rf,2~{end}{right}~
B23 '{down}
B24 '/rf,2~{end}{right}~
B25 '/wgra
B26 '{calc}
B27 '{indicate}
B28 '{quit}

Discussion: