Category : Lotus and other Spreadsheets
Archive   : LTSTIPS1.ZIP
Filename : DATASORT.123

A 1-2-3 Tutorial
Organizing Information With Data Sort
(PC Magazine Vol 3 No 18 Sept 18, 1984 by E. Baras)

A macro is a column of label cells that store the keystrokes of
123 commands and data entries exactly as you would have typed them
yourself. You assign the macro a name. When that name is invoked, the
contents of the macro are executed. By storing a batch of 123 commands
and entries in a range of cells, you can instruct 123 to execute the
batch simply by entering its name.
The Play-Rite Merriment Co. employs a marketing force of 200
persons. We want to rank the sales of each person on a quarterly basis
and identify the top salespeople in each quarter for incentive awards.
Assume a database of six people. Make sure the CAPS indicator is on.
Do not type spaces between 123 commands.
1. Set column A's width to 10 characters by pressing the Home key
and typing /Worksheet Column-Width Set 10 .
2. Move to cell B1 and set the column-width for column B to 15 by
typing /Worksheet Column-Width Set 15 .
3. Enter the column headings and salespeople's data as they are
reflected in the file MACRO1.
4. Type /Range Label-Prefix Center to enter the column titles.
5. Type the titles PLAY-RITE MERRIMENT COMPANY in cell C2 and
SALES RANKING SYSTEM in cell C3. Press Enter. The second title is not
centered beneath the main title. Shifting it over three spaces will do
the trick.
6. With your pointer on cell C3, enter Edit mode by pressing the
F2 (Edit) key. To displace the label toward the right, you must insert
spaces on the left of the label. The spaces must be placed between the
label-prefix (apostrophe) and the first character of the label, the
initial S in SALES.
7. Press the Home key to move the edit pointer to the left side of
the label, the apostrophe.
8. Press the right-arrow key once, press the space bar three
times, and press Enter. The control panel should now show the cell's
contents centered below the title. Because the sales figures in this
worksheet will be in thousands of dollars,
9. Go to cell F1 and type the label '($ IN THOUSANDS). The last
column, YTD sales, is the sum of the quarterly amounts. At the
beginning of the year the worksheet is blank. As each quarter passes
the quarterly data is input. Thus, after any given quarter the YTD
column equals the year-to-date sales for each Play-Rite salesperson.
We will use the @SUM function to set YTD equal to the sum of the four
quarters contained in columns C through F.
10. Go to cell G7 to enter the equation for year-to-date sales for
A. Miller.
11. Type @SUM( to begin the formula.
12. Move the pointer to fourth-quarter sales at F7. (Why do we
point to the fourth quarter as the beginning of the range? Doesn't 123
require us to specify the range as first quarter (C7) through fourth
quarter? 123 is very flexible when it comes to ranges. It even lets
us point "backward."
13. Type a period to anchor the range.
14. Move to first-quarter sales at C7. Notice that the pointer
expands normally, encompassing the range that we want.
15. Type the ) key and press Enter. 123 uses the endpoints of the
range to determine where the range is. It makes no difference to 123
in what order the endpoints are specified, as long as they are
endpoints. Copy the YTD formula for the rest of the data records:
16. Type /Copy G8..G12 word . Your results should
look like the YTD column in the file MACRO1.
Now that we have set up the worksheet (MACRO1.WKS), we can begin
to manipulate its contents. The first step in organizing the data is
to sort it in descending order of year-to-date sales. This is easily
accomplished with the /DS (Data Sort) command.
1. Type /Data Sort.
2. Choose the Data-Range option in order to tell 123 where to find
the records to be sorted.
3. Move the pointer to cell A7, the first field of the first
record. (You do not include the field names in a Data Sort range
because you do not want them involved in the sort. You want them to
stay at the top.
4. Type the . key and press the End and Home keys to move to the
bottom right of the database, cell G12.
5. Press Enter. The range of records to be sorted is thus
A7..G12. The Sort menu reappears.
6. Choose the Primary-Key option by typing P. You must now
indicate which column should be the basis of the sort and in what order
the sort is to be arranged.
7. Move the pointer to any cell in the YTD field (that is, column
G).
8. Press Enter. Now 123 will request the sort order, with a
recommendation of A (ascending). Our interest is in seeing the top
salespeople on top of the list, so we will reject this suggestion.
9. Type D to choose the descending order and press Enter. The
Sort menu returns, and we are given the option of selecting a Secondary
key on which to sort. If two or more salespeople have the exact same
year-to-date sales total, you can sort those people in alphabetical
order:
10. Choose the Secondary-Key option.
11. Move the pointer to any cell in the NAME field.
12. Press Enter. In response to the sort-order prompt,
13. Type A to choose ascending (alphabetical) order and press
Enter. Now begin the sort:
14. Choose the Go option of the Sort menu.
As the file MACRO2 shows, Albee is the top Play-Rite Merriment
salesperson, with midyear sales of $171,000.
One of the primary reasons for developing this worksheet is to
rank salespeople, so it would be useful to add a column that numbers
the year-to-date sales performances from top (1) to bottom (6). When
we resort the file, we can use this column to compare other types of
ranking to the year-to-date ranking.
We can assign rankings to the sales force by entering a sequence
of numbers, starting with 1, in the column to the right of YTD.
1. Move to cell H4 and enter the label ^YTD; then move to cell H5
and enter ^RANK. (The label-prefix ^ serves to center the label.)
2. Fill cell H6 with a line of hyphens using \-. Rankings
can be attached to the sorted records using the /DF (Data Fill)
command. Data Fill creates a sequential list of numbers in a range of
consecutive cells. In this instance, the sequence of numbers would be
placed in column H, beginning with 1 in row 7 and increasing in steps
of 1 through the rest of the range.
3. Move to cell H7.
4. Type /Data Fill. Now 123 will ask for the fill range, the range
of cells that will contain the sequence. It suggests the current
position, H7, as the beginning of the range. This is desired, so:
5. Type a period to anchor the range.
6. Move down to the end of the field to the cell corresponding to
the record of Miller (cell H12).
7. Press Enter. Now you are asked to supply the beginning of the
sequence. In response to the "Start" prompt,
8. Type 1. This will give Albee a ranking of 1.
9. Press Enter. The next prompt asks for "Step", the value of the
increment. Because this sequence should increase by 1, accept 123's
recommendation:
10. Press Enter. The "Stop" prompt gives you the opportunity to
specify a maximum value for the sequence. If the fill range were so
long that it exceeded the maximum value that could be filled, then the
sequence would be abbreviated before reaching the end of the fill
range. For the current example, the suggested 2047 is more than enough.
11. Press Enter to accept the recommended stop value. Instantly,
the sequential rankings 1 through 6 will fill the RANK column of the
worksheet. Before we forget, it is necessary to update the addresses
of the Sort data range to include the RANK column.
12. Type /Data Sort Data-Range. The control panel displays the
active data range of A7..G12, the corners of the highlighted area.
13. Press the right-arrow key once to expand the data range toward
the right to include column H.
14. Press Enter.
Although the company's quarterly incentives are based on year-to-
date sales, it is also of interest to sort the salespeople by the
sales of the most recent period, the second quarter. This simply
means changing the Primary key and resorting.
1. Select the Primay-Key option. The pointer will move to the
current Primary key in the YTD column. To use the second quarter as the
basis for sorting,
2. Move the pointer to any cell in the QTR2 column.
3. Press Enter. The suggestion of descending sort order is
acceptable, so
4. Press Enter again. The Secondary key, which is the NAME field,
can remain in effect, so
5. Select the Go option. Notice that the old year-to-date
rankings were rearranged when the file was resorted. Albee is still
in first place, but Ibsen, who ranked fifth for YTD, is second for the
quarter.
Now that the data range has been established, it need not be
repeated until it changes. Sorting on a different sort key requires
specifying the new Primary key and the new Secondary key (if desired)
and issuing the Go command. Play-Rite will be doing a lot of sorting
with this worksheet, so it will be worthwhile to develop macros that
automate and simplify the sorting task.
Recall that a macro is a vertical range of cells containing 123
keystrokes that can either invoke commands or enter information. The
keystrokes are stored as labels -- that is, each is preceded by a
label-prefix, such as an apostrophe. The first cell of a macro is given
a special range name consisting of a backward slash followed by a
single letter (for instance, \A). You invoke the macro by typing its
name. This is done by pressing the Alt key in combination with the
letter assigned to the macro's name. The macro \A would be invoked by
pressing the Alt key while typing the A key.
Here is a list of the commands and responses to prompts that we
used to rearrange our file in order of year-to-date sales:
/Data Sort Primary-Key G1Descending
Secondary-Key B1AscendingGo
(We will present the macro translation shortly.)
If we resort the file by year-to-date sales, it must be ranked
again with the Data Fill command. As we have seen, Data Fill requires
four pieces of information: the fill range, the starting value of the
sequence, the step (increment) and the stop, or maximum, value. If you
already issued a Data Fill during the current session, 123 will
remember all four of these settings and will recommend them the next
time you issue the command
Note, however, that if you save the worksheet and retrieve it
later, 123 will remember only the fill range. The start, step, and
stop will revert back to their initial default values of 0, 1, and
2047, respectively. For our purposes, the step and stop can assume the
default values. But the starting value of the sequence must be set to
1, not 0, in order to assign the highest ranking salesperson a rating
of 1, not 0. So when the Data Fill command is issued, the entries
should be as follows:
/Data Fill to initiate the command and invoke the Fill
range prompt
Enter to accept the previous range (H7..H12)
1 Enter to begin the sequence with 1
Enter to accept the "Step" recommendation of 1
Enter to accept the "Stop" recommendation of 2047.
In summary, then, the commands and responses to prompts that are
necessary to sort and rank the file on the basis of year-to-date sales
are:
/Data Sort Primary-Key G1Descending
Secondary-Key B1AscendingGo
/Data Fill1
To translate these steps into a macro, you must convert the commands
and prompts to labels, and then you must convert any special keystrokes
(virtually every non-typewriter keystroke) to the special macro
keystroke indicators (see table, "Macro Codes for Special Key Names").
The only special key used in the sequence above is the Enter key. Any
reference to Enter in a macro must be replaced by the tilde (~)
character. Thus, the sequence of keystrokes that actually goes into
the macro would be
/DSPG1~D~SB1~A~G/DF~1~~~
A macro need not reside entirely in one cell. In fact, creating
long macro labels by chaining keystrokes together is not a good idea.
In the first place, macros are difficult enough to understand by
looking at them. If you do not remember exactly what a macro label
does, it is hard to figure out the macro by looking at a long, coded
label. (It is a good idea to document your macros in plain English,
storing this documentation either on the worksheet or on paper.)
Shorter macro labels are more easily deciphered. In addition, macros
are extremely susceptible to errors. Even a misplaced comma can cause
ruin.
Therefore, we will divide the commands of the macro into segments,
stored in cells J5 through J7, as shown in Figure 4. 123 will read the
first segment and then examine the next cell below. If that cell
contains a label, it is interpreted as a continuation of the macro.
123 will interpret and execute any labels it sees as if they were
together in a single cell. Processing stops when the next cell in the
macro is a blank or when a macro is explicitly ended by a command.
Follow these steps to store the macro:
1. Move to J5, an unused cell.
2. Type '/DSPG1~D~ and Enter.
3. In cell J6, enter the label SB1~A~G.
4. In cell J7, enter the label 'DF~1~~~. Merely entering a label
into the cell does not tell 123 that cell J5 is a macro. We must
assign the macro a name and, in the process, let 123 know where to find
the macro when we invoke it. The /RNC (Range Name Create) command
allows you to assign a name to a cell or to a range of cells.
5. With the pointer on the first cell of the macro, J5, type
/Range Name Create. Assign a special name to the cell:
6. Type \A and press Enter. Now 123 will recommend the current
cell, J5, as the named range. This is acceptable, so
7. Press Enter. Before trying out the macro, you should protect
yourself against zapping by saving the worksheet:
8. Type /File Save MACRO3 and then press Enter.
Now test the macro. Press the Alt key, holding it down as you type A.
Very swiftly, 123 will execute the macro as if you were actually typing
the keystrokes. When the word READY appears in the mode indicator of
the control panel, check the results. The names and ranks should be
rearranged as they were when we first sorted them according to YTD
sales.
If there is an error in the macro, 123 will sound a beep.
Depending on the problem, the macro may be interrupted or it may
execute completely but erroneously. If the results of the macro are
not what they should be, or if a beep was emitted, there is probably a
typing error in the macro.
How would the operation of \A be affected if we decided, at some
point, to insert a column on the left side of the worksheet? Recall
that the Primary and Secondary keys, YTD and NAME, were given as cells
G1 and B1 in the command
/Data Sort Primary-Key G1Descending
Secondary-Key B1AscendingGo
If a column is inserted to the left of column B, then the fields to the
right of the inserted column are displaced to the right. The YTD
column will be shifted from column G to column H, while the NAME column
will move from column B to column C. The sort command would have to be
adjusted to:
/Data Sort Primary-Key H1Descending
Secondary-Key C1AscendingGo
But the macro is not aware of the inserted column. It knows only to
refer to cells G1 and B1, no matter what, because a macro is comprised
of labels, and labels are not altered by rearranging cells in the
worksheet. The inserted column renders the macro useless until the
macro is corrected.
This is an instance where the range-naming capability is a
blessing, for a named range of cells is automatically adjusted when the
worksheet is altered. No matter where a named range is moved to, its
name is still assigned to it at its new location. By assigning range
names to the G1 and B1 cells and by referring to those range names
rather than the cell coordinates, the macro will use the new addresses
for the ranking procedure. Because this chapter will ultimately
involve macros that refer to all of the columns, we will give range
names to all of them.
When 123 prompts for the location of a sort key, it requests the
address of a cell within the column that is to be sorted. To be able
to sort any column in the worksheet, we should assign a unique name to
one cell in each of the eight columns. The eight names could be
assigned with eight Range Name Create commands. However, it is
possible to assign all eight names at once with the Range Name Labels
command.
Suppose there is a range of cells with unique labels in the
worksheet. The range of field names in cells A5..H5 is an example.
With the Range Name Labels command, use these labels as names to be
assigned to cells bordering the range of labels.
1. Move the pointer to cell A5.
2. Type /Range Name Labels. The control panel displays a menu
with the options Right, Down, Left and Up. If you choose Down, then
the cells below the range of labels (which you will soon specify) will
be given names.
3. Choose the Down option. Next, 123 wants to know the label
range, that is, the range of coordinates containing labels that will be
used as names. Because the pointer is on A5, 123 recommends the range
A5..A5.
4. Move the pointer to the right, to H5, to expand the label range
to A5..H5.
5. Press Enter. The READY mode indicator returns. To find out
whether the cells were named, we could use the F5 (GoTo) key to move to
the named cells and see if the GoTo works. To do this, it would be
convenient to see a list of all the names currently active in the
worksheet.
6. Press the F5 (GoTo) key. Now 123 enters Point mode, and it
wants you to tell it where to go. If you press the F3 (Name) key in
Point mode, you will retrieve a list of active range names.
7. Press the F3 (Name) key. The names are alphabetized. The list
includes NAME, QTR1, QTR2, QTR3, QTR4, RANK, REGION, YTD and \A (the
name of our macro). There is not enough room in the control panel to
display all of the names, but you can move the menu pointer across with
the right arrow key to view the rest of the list. Placing the menu
pointer on a particular range name and pressing the Enter key will
select that name for the current command, which is GoTo.
8. Move the menu pointer to YTD.
9. Press Enter. The pointer jumps to cell G6, which is the cell
below the YTD field name. If you test the GoTo command using one of
the other field names, the pointer will move to the cell beneath that
field name. That is because the Range Name Labels Down command uses
the contents of the labels range to name the row of cells immediately
below the labels range.
The other Range Name Label commands work in a similar manner.
The Range Name Labels Up command names the bordering cells above the
labels. If the labels were contained in a vertical range of cells, you
could use the Right or Left options to name the bordering cells on the
left or right of the labels.
The F3 (Name) key can be used to obtain a list of range names
whenever 123 is in Point mode. Thus, it can be used with the Move,
Copy, Data, Print, File, and Graph commands and in a formula entry.
The F3 (Name) key is especially convenient if you have forgotten the
name of a range while you are entering a command or formula.
Now that the Range Name Labels command has been issued, let us
return to the \A macro. Instead of using Data Sort Primary-Key G1 to
sort the records in order of year-to-date sales, we will use the named
cell, entering Data Sort Primary-Key YTD. This command is flexible.
If the YTD column is removed for any reason, the name YTD will be
automatically reassigned to the new address, and the macro will
function correctly.
1. Press the F5 (GoTo) key and type \A to move the pointer
to the \A macro.
2. Press the F2 (Edit) key to enter Edit mode. The current macro
label will appear as '/DSPG1~D~.
3. Replace the characters G1 with YTD and press Enter.
4. Similarly, in cell J6, replace the characters B1 with NAME, so
that the Secondary key will refer to the NAME cell, not the B1
coordinate.
5. Press Enter. Make certain that you entered the macro label
correctly, then hold down the Alt key while typing A to see if the
macro works. The results should be the same as before.
Now we will create a second macro, \B, to sort the second-quarter
sales records in decreasing order. Observe that the commands to
reorder the file should be identical, except that the Primary key
should be designated as QTR2. Therefore, as a shortcut to entering
the macro, we will copy the sorting commands (the first two cells) of
\A to another column and change the Primary Key from YTD to QTR2.
We will put this new macro right next to \A in column K; the two
macros must be "neighbors" in order to be part of a user-defined menu.
Otherwise, we could have put the second macro somewhere else.
1. Use the F5 (GoTo) key to go to the named range \A (press F5 key
and type \A).
2. Type /Copy, type a period, and then press the down arrow key to
mvoe to the last cell of \A. Next, press Enter.
3. Move to cell K5, the target cell for the copy.
4. Press Enter.
5. Move to cell K5.
6. Press the F2 (Edit) key to edit the cell's contents.
7. Replace the characters YTD in the label with the characters
QTR2.
8. Press Enter. Next name the macro.
9. Type /Range Name Create \B. The width of column
J is not sufficient to display the macros clearly. We will take care
of this by widening column J.
10. Move to cell J5 and type /Worksheet Column-Width Set 12
. Macro \B should be located directly next to the revised macro
\A, like this:

J K
5 /DSPYTD~D~ /DSPQTR2~D~
6 SNAME~A~G SNAME~A~G
7 /DF~1~~~ /DF~1~~~
^
MACRO\B
Scroll the display so that you can see the QTR2 column; then
invoke the macro by holding down Alt and typing B. The results should
be the same as those we saw when we did the same sort without a macro
earlier. The completed worksheet is stored in file MACRO4.

MACRO CODES FOR SPECIAL KEY NAMES

Key Name Macro Code Name*

ENTER key ~
Up arrow key {UP}
Down arrow key {DOWN}
Right arrow key {RIGHT}
Left arrow key {LEFT}
HOME key {HOME}
END key {END}
PgUp key {PGUP}
PgDn key {PGDN}
ESC key {ESC}
DEL key {DEL}
BACKSPACE key {BS}
F2 (Edit) key {EDIT}
F3 (Name) key {NAME}
F4 (Abs) key {ABS}
F5 (GoTo) key {GOTO}
F6 (Window) key {WINDOW}
F7 (Query) key {QUERY}
F8 (Table) key {TABLE}
F9 (Calc) key {CALC}
F10 (Graph) key {GRAPH}
Pause for manual input {?}
until user presses ENTER

*Macro code names may be entered in uppercase or lowercase.

(The files MACRO1, MACRO2, MACRO3 and MACRO4 named in this article are
contained on the LOTUS DATA FILES disk.)

-----------------------------------------------------------------
A 1-2-3 Tutorial
A Guide to Making Macros More Flexible
(PC Magazine Vol 3 No 19 Oct 2, 1984 by E. Baras)

(Steps for Creating a Flexible Macro)
The macro \B sorts the worksheet according to second-quarter sales
of the fictional Play-Rite Merriment Company. If you wish to perform
the same sort of operation for first-, third-, or fourth-quarter sales,
it is not necessary to write three additional macros. Instead, \B can
be revised to make it more flexible. These steps show how to revise
\B and similar macros.
1. Store the value of the current quarter (1, 2, 3, or 4) in a
cell that is separate from the macro. In this case, we will name the
cell QTRX.
2. If QTRX contains a 1, then the first-quarter sales should be
sorted. The macro will assign the cell named QTR1 to the Primary key,
assign NAME to the Secondary key, execute the sort, and then skip to
Step 6. If QTRX does not contain a 1, the macro will proceed to Step
3.
3. If QTRX contains a 2, the macro will assign QTR2 to the Primary
key and NAME to the Secondary key, then execute the sort and skip to
Step 6. If QTRX does not contain a 2, the macro proceeds to Step 4.
4. If QTRX contains a 3, the macro will assign QTR3 to the Primary
key and NAME to the Secondary key, then execute the sort and skip to
Step 6. If QTRX does not contain a 3, the macro then proceeds forward
to Step 5.
5. If QTRX contains a 4, the macro will assign QTR$ to the Primary
key and NAME to the Secondary key. It then executes the final sort and
proceeds to Step 6.
6. Stop.
- - - - - - - - - -
Before you create a flexible macro, you must know how to endow a
macro with the intelligence needed to distinguish between quarters and
how to instruct a macro to issue sequences of keystrokes conditionally,
depending on which quarter is the current quarter.
The macros developed up to this point have been exact duplicates
of keystrokes that could have been typed manually by the user. These
macros contained no internal intelligence or logic, just plain 123
keystrokes. However, there exists a class of commands unique to the
macro environment, commands that cannot be used outside of a macro.
These are the /X commands. The /X commands control the sequence of
execution of a macro. With the /X commands, the macro becomes an
intelligent "automatic pilot."
The /XI command directs 123 to perform tasks only if a certain
condition has been met. The format of the command is:
/XI conditional formula~...keystrokes...
The /XI command has the following meaning: If the result of the
conditional formula is true, then read any keystrokes that follow the ~
(tilde) in this cell; otherwise, skip to the next macro cell below this
and continue execution. The I in /XI stands for if, and the ~
represents then, so that the anglicized version of the command is, "IF
the condition is satisfied, THEN execute the keystrokes that follow."
Whatever comes between the I of /XI and the following ~ is the
conditional formula. For example, consider the macro label
/XIQTRX=1~/DSPQTR1~
The condition if QTRX=1. If this condition is true (i.e., if the cell
named QTRX contains a 1), then the macro will execute the remaining
keystrokes in the cell, which represent the command:
/Data Sort Primary-Key QTR1
In other words, if the current quarter is the first quarter, then the
macro sets the Primary key to QTR1 and then continues with the next
macro label. Otherwise, 1-2-3 skips to the next macro label without
executing the /DSPQTR1~ keystrokes.
Observe that the tilde following the conditional formula stands
for then, but the tilde at the end of the Data command represents an
Enter keystroke. Whatever follows that first tilde, whether a single
command or several commands, will be performed only if QTRX=1.
Otherwise, the flow of command proceeds to the next cell down.
Another useful command is /XG. The G stands for "go to,"
instructing 123 to skip to some other macro cell to obtain the next
macro command. For example, /XGJ9~ would direct 123 to stop whatever
it is doing, go to cell J9 (a macro cell), and follow whatever
instructions are contained there. The destination of the /XG command
can be an actual cell reference or a cell name. The tilde following
the coordinate is essential. If the destination coordinate is not
followed by a tilde, the macro won't work.
Together, /XI and /XG permit us to translate Steps 2 through 6 of
the procedure above into the language of macros. Assume that cell QTRX
already contains the value of the current quarter. Begin entering \B
with the following steps:
1. Name the QTRX cell by moving to J1 and typing /Range Name
Create QTRX .
2. Move to \B (cell K5).
The first task for the macro is to examine the contents of QTRX to see
whether the file should be sorted by first-quarter sales. If so, we
want it to issue the appropriate Sort command. The macro label to do
this would be:
/XI(QTRX=1)~/DSPQTR1~D~SNAME~A~G
Here we have enclosed the condition in parentheses, which contributes
to clarity. To the right of the first ~ are the keystrokes to be
executed for the first quarter, which translate as follows:
/Data Sort Primary-Key QTR1Descending
Secondary-Key NAMEAscendingGo
In this case, we must violate our convention of keeping the labels
short, because all keystrokes that are subject to the /XI condition
must be stored in the same label as the /XI command.
Begin by entering the label in cell K5. (We will be replacing
the old contents of K5 with a new entry.)
3. Type '/XI(QTRX=1)~/DSPQTR1~D~SNAME~A~G
Do not press Enter yet. If you did, press the F2 (Edit) key to
continue entry. So far, we have entered only part of Step 2 of our
planned procedure, which was to sort the file based on QTR1. If we
were to end the label here and if QTRX were 1, then 123 would execute
the keystrokes of the rest of the cell and then proceed to the next
cell of the macro.
Instead of proceeding to the next label, however, we want to stop
processing the macro. One way of stopping macro execution is to
instruct 123 to go to a blank cell. When 123 goes to the blank cell,
it stops processing the macro because only nonblank cells are valid
macro cells.
We will use a cell at the end of the macro we are currently
developing, but we have not yet finished the macro, so we do not know
where the end is -- we know only that we want to skip to that location.
Wherever that cell may be, let us plan now to give it the name
CONTINUE1. We will create CONTINUE1 with the Range Name Create command
later, as soon as we can ascertain the address of CONTINUE1. For now,
we need to tell the macro that, if QTRX=1 is true, it should sort the
worksheet and skip to CONTINUE1. We use the /XG command:
4. Type /XGCONTINUE1~.
The first \B cell, cell K5, should now contain the label:
/XG(QTRX=1)~/DSPQTR1~D~SNAME~A~G/XGCONTINUE1~
The next cell will represent Step 3 of the planned procedure. It is
almost exactly like Step 2, except that QTRX=1 is replaced by QTRX=2,
and QTR1 is replaced by QTR2. Instead of typing the entire label
again into the next cell, K6, we will just copy K5 and edit its
contents.
5. With the pointer on cell K5, type /Copy.
6. Move down once, to cell K6.
7. Press Enter.
8. Move down to cell K6. Now enter Edit mode.
9. Press the F2(Edit) key.
10. Change the characters QTRX=1 to QTRX=2, and change QTR1 to
QTR2.
11. Press Enter. The next cell, K7, will perform Step 4.
12. Repeat the procedure of instructions 5 through 11, but
substitute QTRX=3 for QTRX=2 and QTR3 for QTR2. Do the same for the
next cell, K8, replacing QTRX=3 with QTRX=4 and QTR3 with QTR4. Now
Steps 2 through 5 of the plan are done.
The next macro cell, K9, represents Step 6. It is the blank cell
that ends the macro, the elusive CONTINUE1 that we have been waiting to
define. Before continuing, we will name cell K9 CONTINUE1.
13. With the pointer at cell K9, type /Range Name Create
CONTINUE1. When the macro is done, 123 will resume the
Ready mode. The updated version of macro \B is:

K L M N O
5 /XI(QTRX=1)~/DSPQTR1~D~SNAME~A~G/XGCONTINUE1~
6 /XI(QTRX=2)~/DSPQTR2~D~SNAME~A~G/XGCONTINUE1~
7 /XI(QTRX=3)~/DSPQTR3~D~SNAME~A~G/XGCONTINUE1~
8 /XI(QTRX=4)~/DSPQTR4~D~SNAME~A~G/XGCONTINUE1~

To put \B to the test, we will load QTRX with the value for the second
quarter.
14. Go to QTRX (cell J1) and set it equal to 2. The invoke the
macro.
15. Hold down the Alt key and press B.
Your results should coincide with those of the second-quarter sort that
we did earlier.
With a macro such as \B, it is easy to make logical or
typographical errors. Under normal conditions, 123 executes a macro as
fast as it can, often faster than the user can follow. It is difficult
to detect exactly which command the macro is executing when an error
occurs? How can you find the error?
Version 1A of 123 introduced a useful facility for testing
macros: the Single-Step mode of macro execution. Whether or not you
were successful with macro \B, it is important to understand this
facility. Holding down the Alt key while pressing the F1(Help) key
will cause 123 to enter Single-Step mode.
1. Hold down the Alt key while pressing the F1(Help) key. The
word STEP is displayed in the lower right-hand portion of the screen;
from here on, the macro will execute only one step at a time. A step
is defined as a single keystroke or a single /X command. Before each
step, 123 waits until you press any key; then it will execute the step.
It is possible to speed up the process by holding down a key -- in
effect, repeating the keystroke. Release the key when you reach the
steps that you would like to follow closely. Begin the single-step
execution of \B.
2. Hold down the Alt key while typing B. Notice that an SST
(Single-Step) indicator appears at the top right, a signal to press any
key. Press a key several times and watch the control panel. As 123
executes each keystroke of /X command, the control panel displays what
is being typed or performed by 123. Hold down the key, and the macro
will execute quickly. Because QTRX=2 is true, the macro ends after it
does the processing for the second quarter. You will not see the
keystrokes related to the third and fourth quarters; these keystrokes
are skipped over, thanks to the /XG command. To disable Single-Step
mode,
3. Press the Alt key while pressing the F1(Help) key. The STEP
indicator will disappear from the lower right-hand portion of the
screen.
The quarterly sort can be automated even more. As it stands, the
user must remember to put the value of the quarter into QTRX;
otherwise, the macro will malfuntion.
There is another /X command called /XN, which prompts the user for
a number, retrieves the user's responses, and stores the response in a
designated cell. The format of the command is:
/XNmessage~location~
Here message is the prompt that will be displayed to the user in the
control panel, and location is the cell address or name where the
number is stored. If the \B macro began with the label
/XNI BESEECH THEE, ENTER THE QUARTER:~QTRX~
then as soon as Alt-B was pressed, the user would be asked to enter the
quarter. If the user then pressed 2 followed by Enter, the number 2
would be stored in QTRX (cell J1).
The \B macro currently begins in cell K5. When inserting a
command into a macro, you can manipulate the macro's cells just like
any other cells in the worksheet. However, after any changes to the
positions of the cells, it is essential that the range name for the
macro (in this case, \B) be attached to the first cell. For future
purposes, it is necessary to have this macro begin in cell K5.
Therefore, to insert the /XN command at the beginning of \B, the rest
of the macro must be moved down one cell:
1. Press the F5(GoTo) key; then type \B to move to the cell
named \B.
2. Type /Move to invoke the Move command.
3. Press the End key and then press the down arrow key twice to
highlight the entire macro including the blank cell CONTINUE1.
4. Press Enter. The pointer returns to its original position,
and 123 requests the destination of the move.
5. Move down one cell to K6.
6. Press Enter. The macro is shifted down, and the pointer
returns to the blank cell above the macro. Enter the /XN command
beginning with a label prefix:
7. Type '/XNI BESEECH THEE,ENTER THE QUARTER:~QTRX~.
8. Press Enter. Next check on the location of \B using the
F5(GoTo) key. Type F5\B. You will find that \B is attached to
its old assignment, which is now the second cell of the macro, so it
must be reassigned.
9. Type /Range Name Create \B. Now 123 will recommend K6,
the current assignment of the \B name.
10. Move the pointer up once to cell K5.
11. Press Enter. We are back in business.
12. Test the macro by holding down the Alt key while typing B. In
the control panel, 123 will beseech you to tell it which quarter you
wish to use for the sort.
13. Respond with a 1 to sort first-quarter sales.
Thus far, we have built macros to sort and rank by year-to-date
sales and to sort by quarterly sales. Next we will create three more
macros to sort by name, region and to print the sheet.
When the entire sales force of 200 is eventually incorporated
into the database, it would be easier to locate particular individuals
in the file it were sorted alphabetically. All that is required is to
point the Primary key to the Name field and Go. The data range is
already in 123's memory. The command is:
Data Sort Primary-Key NAMEAscendingGo
If we put this into a macro called \C, the procedure will be that much
easier to use.
1. Move to cell L5.
2. Type /Range Name Create \C to name the macro.
3. Enter the label '/DSPNAME~A~G and press Enter. Hold down the
Alt key and press C to execute the macro. To rank the file on YTD
sales and then alphabetize it, invoke macro \A and then invoke macro
\C. Because the label in the previous column (beginning in cell K5)
was so long, the \C label interrupts it. Let us move back to column K
and widen the column so that we can see the entire macro.
4. Move to cell K5 and type /Worksheet Column-Width Set 48
. Before we continue, let us also widen the columns for the
other macros in advance.
5. Move to cell L5 and type /Worksheet Column-Width Set 14
.
6. Move to cell M5 and type /Worksheet Column-Width Set 24
.
7. Move to cell N5 and type /Worksheet Column-Width Set 50
.
Another useful arrangement would be one with records alphabetized
by region and, within each region, by name. This is accomplished by
the following commands:
Data Sort Primary-Key REGION Ascending
Secondary-key NAMEAscendingGo
Hence, to enter macro \D,
1. Move to cell M5.
2. Type /Range Name Create \D to name the macro.
3. Type '/DSPREGION~A~SNAME~A~G.
4. Press Enter.
Invoke the \D macro. Observe that the two records of the Western
region, Ibsen and Shaw, are arranged alphabetically within their
region.
Now that the macros have been developed to sort the file in
various way, a macro should be written to output the file to a printer.
When developing a macro, the initial step is to figure out how the
task might be accomplished interactively, without a macro. Implement
the steps to report the file, record the steps as they are executed,
and then translate them into a macro.
The report should start from the top-left corner of the worksheet,
which is where you should transfer the pointer:
1. Press the Home key.
2. Type /Print to enter the Print menu.
3. Select the Printer option.
4. Select Clear Range to clear any previous print ranges.
5. Select the Range option to enter a print range.
6. Type a period to anchor the range.
Now 123 request the lower right-hand corner of the range to be printed.
Normally, it would be sufficient to use the down-arrow and right-arrow
keys to move the pointer to the bottom right. However, we are going
through this exercise for the sake of writing a macro, and a macro
executes automatically; it is not aware of the bottom right of the
worksheet. It must be instructed exactly how many times to move right
and how many times to move down, which is not readily available
information. By using the End key, we can cleverly circumvent the
problem.
If the pointer is on a blank cell, then pressing the End key
followed by an arrow key transplants the pointer to the next nonblank
cell in the direction of the arrow. If the pointer is on a nonblank
cell, pressing the End key and an arrow key moves the pointer to the
last contiguous nonblank cell before a blank cell.
With the proper combination of keys, the pointer can be shifted
down to the Region field name (cell A5), over to the Rank field name
(H5), and down to the bottom right-hand corner of the worksheet.
7. Press the End key, followed by the down arrow key. The range
A1..A5 will be highlighted, but this is not yet the entire range
desired.
8. Press the End key once again, followed by the right arrow key.
Now the range A1..H5 is highlighted.
9. Press End and then the down arrow key again. The entire range,
A1..H12, is now designated in the control panel, so
10. Press Enter to accept the range.
If you were to add the column widths of the print range, you would find
that our report will be 79 characters wide. Because 123's defaults are
a left margin at 5 and a right margin at 76, the report will be too
wide to print. This can be fixed by changing the left and right
margins to 0 and 79, respectively.
11. Type Options Margins Left 0 and Enter.
12. Type Margins Right 79 and Enter.
13. Select Quit to return to the Print menu. Make sure the paper
in the printer is aligned and that the printer is on.
14. Type G to select the Go option. The report should print out.
To skip to the next page and exit the Print menu,
15. Select Page and Quit.
The commands used to print the report were:
/Print Printer Clear Range
Range
Options Margins Left 00
Margins Right 79
Quit Go Page Quit
The special keys that must be translated to the macro are Home, End,
down arrow, right arrow, and Enter. Translating these keystrokes, we
create the macro to print the report:
{HOME}/PPCRR.{END}{DOWN}{END}{RIGHT}{END}{DOWN}~
OML0~MR79~Q
GPQ
We will split this macro into two labels:
16. In cell N5, enter the label
{HOME}/PPCRR.{END}{DOWN}{END}{RIGHT}{END}{DOWN}~
17. In cell N6, enter the label
OML0~MR79~QGPQ
18. Press Enter. To name the macro \E,
19. Move to cell N5, the beginning of the macro, and type:
/Range Name Create \E
Now align the printer paper and invoke macro \E.
The worksheet now has a collection of five macros. To invoke
them, the user must know how to execute a macro and must also remember
which macro is which. The names that the macros have been given are
not particularly descriptive.
The /XM command lets you create your own 123 menus. You can
choose your own command names and assign these names to macros that you
have created. Each menu can contain up to eight commands. Like
regular 123 menus, the user-defined menu displays the command names on
the second line of the control panel. The menu pointer highlights one
of these commands, and it can be moved to another command by using the
pointer movement keys. On the third line of the control panel, a
descriptive phrase (designed by the user) explains what will occur if
the command highlighted by the menu pointer is selected.
A user-defined menu consists of up to eight commands, which are
incorporated into a menu range. The menu range includes one column for
each of the menu commands and at least three rows of each. The first
column of the menu range relates to the first command of the menu. Its
uppermost cell contains the command name, which should begin with an
uppercase letter. The second cell in the column is a label containing
an explanation of the command. The third cell, and any cells below it,
store a macro that is executed when the associated command is chosen.
Now it is clear why all of the macros of this worksheet are
located side by side in consecutive columns and why they all start in
row 5. We plan to integrate the macros into a menu. We can use row 3
for command names and row 4 for command descriptions, thus completing
the menu range. With the menu range constructed, we need only inform
123 that a menu exists and tell 123 where it is located in the
worksheet.
In choosing command names for a menu, make sure that no two
commands begin with the same letter. Then there will be no ambiquity
when a command is chosen by typing the first letter of its name. Here
are names for the five commands that will invoke macros \A through \E:

MACRO COMMAND NAME
\A YTD
\B QTR
\C NAME
\D REGION
\E PRINT

With this information, we can enter the menu.
1. Use the command names just given to complete the row of command
names (row 3) for the menu range. For example, the label YTD should be
entered into cell J3, QTR into cell K3, etc.
2. Press the CapsLock key to allow for lowercase typing.
3. Use the table of macro description to enter descriptive labels
into row 4 of the worksheet. For example, "Rank salespeople by year-
to-date sales" is the label to be entered into J4, "Rank salespeople by
quarter sales" into K4, and so on.
4. Press the CapsLock key to restore uppercase typing.
Now to enter the /XM command which informs 123 of a menu's existence
and location, in cell I1. The structure of the command is
/XMlocation~, where location is the top-left corner of the menu range
-- cell J3. All 123 needs is the location of the top-left cell. It
can deduce where the rest of the menu is, because it knows that the
macros that form the menu must be next to each other.
Instead of referring to cell J3 in the /XM command, let us give J3
the name of MENUPLACE, just in case the menu gets moved away from cell
J3.
1. Move to cell J3.
2. Type /Range Name Create MENUPLACE. The /XM
command will be /XMMENUPLACE~. Like other /X commands, /XM may be used
only within a macro. Therefore, we must create a new macro whose only
entry is '/XMMENUPLACE~. For example, a macro \M could be defined,
containing the /XM command. Invoking the \M macro will activate the
user-defined menu.
3. Move to cell I1 and enter '/XMMENUPLACE~.
4. Type /Range Name Create \M. The label was cut
off by the entry in cell J1, so widen column I:
5. With the pointer on cell I1, type Worksheet Column-Width Set
13.
Now hold down the Alt key while typing the letter M to invoke the
macro. The moment the letter M is pressed, the following user-defined
menu will appear on the second line of the control panel:
YTD QTR NAME REGION PRINT
and the menu pointer highlights the first option, YTD. The mode
indicator on the top right shows CMD MENU, because 123 is executing a
menu under the command of a macro. The bottom line of the control
panel displays the description for the YTD command, which is stored in
cell J4.
When we move the pointer to the right, another command is
highlighted, and its associated description appears below it. Pressing
the Enter key will execute the macro pertaining to whatever command is
highlighted by the menu pointer, and typing the first letter of a menu
option has the same effect. Pressing the Home key transfers the
pointer to the first command in the menu, whereas the End key shifts it
to the last command.
Select the QTR command, and the prompt for the quarter appears.
Respond by entering 1 . The file is now in descending order of
first-quarter sales. The menu disappears, the macro is completed, and
Ready mode is resumed. To invoke the menu again, just invoke the \M
macro.
If you invoke the menu but then decide you do not want to use the
macro, how do you abort the menu? There are three ways: 1) press the
Esc key; 2) hold the Ctrl key down while pressing Scroll Lock; and 3)
mimic other 123 menus by including an Exit command within the menu.
We can add a command (i.e., Exit) to the menu range by entering
it into the next column of the menu.
1. Move to cell O3.
2. Enter the label EXIT, which serves as the command name.
3. Move down once to cell O4.
4. Enter the description Exit from the menu. How can you make the
Exit command work? There is no way of instructing the macro to press
the Ctrl Scroll Lock key because these two special keys have no macro
translations. However, there is another /X command, /XQ (Q for Quit),
that has precisely the same function as this keystroke sequence.
5. Move down once to cell O5.
6. Enter '/XQ.
7. Press Enter.
Now see it if works. Invoke the macro \M and choose Exit.
Consider the design of our menu. Whenever a user selects one of
the options we have provided, 123 will execute the appropriate macro
and then relinquish control to Ready mode. It is like, though, that
the user would rather be returned to the user-defined menu than to
Ready mode. For example, the user might want to sort the worksheet and
then print it, two separate commands of our menu.
Using the /XG command, we instruct each command of the menu
(except for Exit) to return to the menu when it is done. In the last
cell of each of macros \A through \E, we will enter the label '/XG\M~.
This will send 123 to the menu macro, which invokes the menu anew.
In each of cells J8, K10, L6, M6 and N7, enter the label '/XG\M~.
Invoke the menu by holding down the Alt key while typing M, and
then execute a command. You will be returned to the menu when the
selected macro finishes executing. We have succeeded in creating a
fairly closed environment.
In this worksheet, we have automated five of the principal tasks
of Play-Rite Merriment's ranking system by incorporating them into
macros. We made the macros themselves easier to use by integrating
them into a menu. Now to execute any of the principal tasks, a use
need only hold down the Alt key while typing M. From that point on,
the system almost runs itself.