home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Simtel MSDOS 1992 June
/
SIMTEL_0692.cdr
/
msdos
/
lotus123
/
lotushlp.arc
/
LOTUS1.DOC
< prev
next >
Wrap
Text File
|
1985-05-31
|
24KB
|
661 lines
1-2-3 Macro Tips
A Collection of Illustrative Macros
The Macro facility (The Typing Alternative) is a powerful tool;
it can allow you to get the most out of 1-2-3. However, it can be
a little intimidating.
The basic concepts are simple. When you use 1-2-3 without Macros,
you press keys to enter numbers and labels, to move the cell
pointer, and to issue commands. Macros are labels containing just
those keystrokes you would normally type into 1-2-3. There are
some keys that you can't type directly into a label, like the
pointer movement keys and the function keys. In Macros, these are
represented by words in braces, like {left} or {graph}.
Of course, it can get more complicated. There are special 1-2-3
commands, the /X commands, that can only be used in Macros. These
determine which Macro instructions are actually executed; they
allow you to write Macros that are computer programs.
(If you haven't yet done so, read the 1-2-3 manual section on
Macros before going on.)
A good time to use a Macro is when you find yourself typing the
same keystrokes again and again. Just remember what keys you are
striking, and type the same strokes into a label, using the
bracketed instructions when necessary.
Macros are also problem-solvers. One of the best times to write a
Macro is when you are trying to perform some task, and none of
the 1-2-3 commands seems to do exactly what you want. Using a
Macro, you can create your own commands!
To help you get started, we've assembled some examples of Macros
that we have found useful. These examples are presented in terms
of problems and solutions, because that's the way Macros tend to
get developed. However, even if you don't think you will ever
face such a problem, it's probably worth reading the example
anyway; they can teach you some useful general techniques.
We've adopted a Macro-writing style that might be helpful. The
Macro name, and other cells with range names in the Macro, are
labeled in the column to the left of the Macro. This way, you can
assign all the range names at one time, using /Range Name Label
Right. Also, we capitalize range names within Macros, and use
lower case for the commands. This makes the Macros easier to
read.
Here are some problems, and their Macro solutions.
1
Editing Labels
Problem: To indent some labels.
There are times when you've typed a column full of labels, and
you want to indent some of them. You could insert a column, and
move over the labels to be indented, but this might be easier.
Solution: The indent (\I) Macro.
Place the cell pointer on the label cell you want indented. press
ALT-I.
Version 1:
\I {edit} Go into Edit Mode.
{home} Put cursor at the beginning of Edit line.
{right} Move past the Label-Prefix.
~ Type two spaces and [Return] to leave Edit
Mode.
This demonstrates the utility of using Macros to edit labels.
With the {home}, {end}, {right} and {left} notations in your
Macro, you can move around on the edit line. {del} and {bs}
remove characters. To insert characters, simply type them into
the Macro string. If you want to leave Edit Mode include a tilde
(~) to represent an [Return].
To use this Macro to indent a series of labels in a column, you
must position the cell pointer, press ALT-I, move the cell
pointer down, press Alt-I, and so on. We can make the Macro more
useful by adding the {down} key.
\I {edit}{home} | This is Version 1.
{right} ~ |
{down} Move the cell pointer to the
next label.
To use it, simply press ALT-I repeatedly. You can manually skip
over labels you don't want to indent.
Adding the {down} keystroke to the Macro makes it a lot easier to
use. Whenever a Macro will be used repeatedly, on different
cells in a particular order, it's a good idea to include arrow
keys at the end of the Macro, to move the cell pointer to what
will probably be the next cell.
In fact, some useful Macros consist of just arrow keys.
2
Movement Macros
Problem: To move the cell pointer more than one cell at a time.
You can create Macros that move the cell pointer in big jumps.
These can be for special purposes, as when working on data where
corresponding numbers are six columns apart.
Or you can create a set of general purpose big step Macros.
Solution: Big step Macros, Left (\L), Right (\R), Up (\U), Down
(\D)
You could also choose to give these Macros names of keys that
form a diamond pattern: Up (\I), Left (\J), Right (\K), and Down
(\M).
\L {left}{left}{left}{left}
\R {right}{right}{right}{right}
\U {up}{up}{up}{up}
\D {down}{down}{down}{down}
Now, to move in bigger steps, just press ALT-L, R, U or D.
Here's one last motion-key Macro that lets you use the numeric
keypad.
Using the Keypad
Problem: To use the numeric keypad without having to switch off
the Num-Lock to move the cell pointer.
The problem with the numeric keypad is that serves two purposes:
movement keys and numbers. If you want to use the keypad as
numbers, you can use the [Shift] key to temporarily turn them
back into arrows.
This Macro, however, moves the cell pointer each time you enter a
number, so you don't have to use any keys except the number and
[Return] keys.
Solution: The move Macro (\M).
Use this when you want to enter a column or row of numbers using
the keypad. This example moves the cell pointer down after you
press [Return].
Use the {?} to make the Macro stop to get input before moving
down.
3
\M {?} Wait for input; resume when [Return] is pressed.
{down} Move down.
After you press ALT-M, 1-2-3 will wait until you press [Return]
before continuing with the execution of the Macro. You can tell
that you are in a Macro by the little CMD indicator next to the
Mode Indicator (upper right corner of the screen). And you can
tell that it's waiting for input because the Mode is READY.
So far, this isn't very useful. It will move the cell pointer
down, but to use it again, you must press ALT-M, which is about
as much trouble as Shift-[Down arrow].
What we can do is make the Macro "loop," repeat itself. To do
this, use the /XG command, which tells the Macro where to go to
get its next instruction. Note that this is different from the
{goto} function keystroke, which moves the cell pointer, but does
not affect the steps in the execution of the Macro.
Place the cell pointer at the top of the column in which you want
to type numbers. Press Alt-M and press the Num Lock key (or vice
versa). Type the numbers you want to input, and press [Return]
after each entry; the cell pointer will move down automatically.
When you are finished, press Ctrl-Break to stop the Macro (and
turn off the [Num Lock] so you can use the arrow keys).
This Macro was discovered by Rich Landsman at Lotus.
\M {?} | This is version 1.
{down} |
/xg\M~ Go back to the beginning of the Macro.
This is an "infinite" loop; it keeps going around in circles
until you stop it. After you have finished putting your numbers
in the column, press Ctrl-Break to stop the Macro.
Later we will see how to get a Macro loop to stop itself, but for
now, let's look at a different type of problem.
Putting a Value in a Cell
Problem: To record the date of the last update.
Many people have found the @today function and the related Date
formats to be a useful way of dating printed material. However,
when you use @today to put the date on a worksheet, and save it,
the date will change when you read in the worksheet and
recalculate it. This is fine for some purposes, but doesn't give
you a secure record of the last day a worksheet was revised.
Solution: The Update (\U) Macro.
4
Name the cell in which you want the date "DATE," and give it a
date format. After you have made your revisions, press ALT-U, and
then save the file.
Version 1:
\U {goto}DATE~ Go to the date cell.
@today Type in the function.
{calc}~ Replace the function with its present
value.
This works, but it has one problem. It takes you from wherever
you are on the worksheet, and leaves you at "DATE."
Version 2:
\U /rncHERE~ Give the name "HERE"
~ to the current cell pointer cell.
{goto}DATE~ |
@today | This is Version 1.
{calc}~ |
{goto}HERE~ Return to your original position.
/rndHERE~ Delete the name.
Delete the name at the end of the Macro so that when you use it
again, you don't end up the last place it was created. Remember:
when you try to create a named range for a name that already
exists, the cell pointer goes back to the named range's last
position.
You could insert a {bs} to bring it back to the position when the
Macro was invoked: /rncHERE~{bs}~, but DON'T. If HERE's old
position was referred to in a formula (e.g. +C5+HERE+E5), then
after you moved it, the formula would include HERE, meaning its
new position.
D5 the name HERE, the formula automatically became +C5+HERE+E5.
And it would stay that way, wherever you put HERE, until you
/Range Name Delete HERE.
Later on, we'll demonstrate Macros that depend heavily on range
names, names that are applied to first one cell, then another. If
you don't delete the range name before applying the old range
name to a new range, you can end up with all your formulas and
range names referring to the same cell.
A good general rule is: whenever you create a range name within a
Macro, delete it before you exit, or before you change the
location(s) to be referred to by the name.
Okay, one more version. We don't really need it, but Version 2
does fail if you've created another range named HERE. Anyway,
this is a chance to introduce a nice technique (spelled "trick")
that one of the Lotus staff discovered.
5
\U /dfDATE~@today~~~ Put the value of today's date in
the cell named "DATE."
That's it! For this gem, we can all thank Bill Liles, of Product
Development (this isn't the kind of development we expected, but
don't get in the way of the freight train of creativity).
Bill found that you could use the /Data Fill command to plug
values into cells. In fact, in the next example, we'll see how it
can be used to increment, decrement or otherwise operate on the
value currently in a cell.
Let's string that same Macro (Version 3) down a column so we can
more easily explain what is happening.
\U /df Execute the /Data Fill command.
DATE~ on the range (one cell) named DATE.
@today~ Make the Start value @today.
~~ Accept the defaults for Step and Stop
(they won't be used in a one-cell range).
What is now in DATE is the VALUE of @today, not the function.
The next example will show how to use this technique to count how
many times you loop, and stop you when you're done. It also
demonstrates how to use range names to find out something about
the current cell.
Stopping a Loop
Problem: To make some cells blank.
When 1-2-3 evaluates a formula it treats empty cells as having a
value a value of zero. This can create confusion when you have
missing data. You can substitute @NA for missing cells, but it
doesn't look very neat.
Here is a listing of salaries for two years. The user wanted to
create a column with the percent change in salary, but some
people weren't employed at this company for both years. For them,
he wanted just blank cells.
Salary 81 Salary 82
$15,000 $16,500
$36,000
$13,000 $14,500
$35,000
$19,500 $21,000
Solution: A Macro and a formula.
6
A standard formula for percent change in salary would be:
(Sal82-Sal81)/Sal81
But this gives a value of @ERR when Salary 81 is missing, and a
value of -1 when Salary 82 is missing. Let's embed our standard
formula in an @if function formula.
@IF((Sal81=0)#OR#(Sal82=0),999,(Sal82-Sal81)/Sal81)
This takes on a value of 999 when either figure is missing;
otherwise it gives the correct value. Here is how the figures
look now.
Salary 81 Salary 82 Percent change
$15,000 $16,500 10.00%
$36,000 99900.00%
$13,000 $14,500 11.54%
$35,000 99900.00%
$19,500 $21,000 7.69%
Version 1:
Next, place the cell pointer at the top of the "Percent Change"
column and press ALT-B. Repeat this until the entire column is
cleaned up.
\B /rncHERE~ Create the one-cell range, HERE.
~ at the present position.
/xi(HERE=999)~/re~ If HERE equals 999, erase the cell.
/rndHERE~ Delete the cell name (!)
{down} And move down.
And this is the result:
Salary 81 Salary 82 Percent change
$15,000 $16,500 10.00%
$36,000
$13,000 $14,500 11.54%
$35,000
$19,500 $21,000 7.69%
Finally, we can put the above Macro in a loop that stops itself.
To do this, we will create ahead of time two one- cell named
ranges, NCELLS and CNUMBER. First, we'll count the number of
cells in the column, and put the number in NCELLS. Then, each
time we check (and perhaps erase) a cell, we'll add one to
CNUMBER. When CNUMBER is greater than NCELLS, we're finished.
7
Version 2:
\B /rncHERE~ Create a range named HERE.
{end}{down}~ Make it the whole column.
/dfNCELLS~ Put in NCELLS the
@count(HERE)~~~ number of cells in the column.
/rndHERE~ Delete HERE.
/dfCNUMBER~0~~~ Start counting cells with 0.
LOOP /rncHERE~ | ]<-- This cell is named "LOOP."
~ |
/xi(HERE=999)~/re~ | This is Version 1.
/rndHERE~ |
{down} |
/dfCNUMBER~ Increase the number in CNUMBER
CNUMBER+1~~~ by one (increment).
/xi(CNUMBER<=NCELLS)~/xgLOOP~
This last line has the Macro resume execution at
the cell named LOOP, until the number in CNUMBER
is equal to the number in NCELLS.
One last example. This one demonstrates how you can turn a number
into a formula, and back to a number, using {Edit}.
Accumulating Numbers
Problem: To Update a value.
The user was maintaining a database of sales people and their
current monthly sales and sales year-to-date. He wanted to be
able to enter the monthly figures and update the year-to- date.
Salesperson Year-to-date Current
DiAngelo $83,000 $6,000
Gottfried $56,000 $8,000
Jones $48,000 $5,000
Washington $77,000 $9,000
These were the figures after the previous month's entries. They
are numbers, not formulas.
Solution: The accumulate Macro (\A).
This Macro takes advantage of the fact that while editing a
formula, you can point to cells, just as when you are first
entering formulas. You must be at the end of the edit line and
the last character must be one that could be followed by a cell
or range such as an operator (e.g. +, -, #AND#) or an open
parenthesis. To start pointing to cells, rather than move along
the edit line, press the {edit} key again to put you in VALUE
mode. When you then press the pointer-movement key you will be in
POINT mode, as when entering a formula.
Place the cell pointer on the first "current" cell. Press ALT-A.
Type the new current value and press [Return]. Press ALT-A again
to enter the value for the next salesperson.
8
Version 1:
\A /re~ Erase the old current value.
{?}~ Wait for user to put in new value.
{left} Go to the year-to-date cell.
{edit} Edit it.
+ Adding the plus turns it into a formula.
{edit} The {edit} key puts you in VALUE Mode.
{right}~ Add the value in the "Current" cell.
{edit}{calc}~ Turn the formula back into a number.
{right}{down} Move to next current cell.
Remember that when the Macro is waiting for input ({?}) the CMD
indicator is next to the READY Mode indicator.
Next, let's put the Macro in a loop, so that it will keep asking
for this month's figures, and stop when it reaches the end of the
column. The only requirement is that there be no empty cells in
the year-to-date column (except at the end), so put zeros in any
empty cells.
This version uses a different technique to stop at the end of the
column; it checks to see if the next cell is blank, and if it is,
it stops.
Actually, there is a problem finding out if a single cell is
blank. Labels, blank cells and zeros all have the value of zero,
so you can't use that. And @count always has a value of one if
its' argument is a one-cell range.
This Macro uses @count and a two-cell range, named TEST. The
range consists of the potential next "Current" cell and the one
cell above it. The value of @count(TEST) will be 2, until it hits
the end of the column, when it will be 1.
Version 2:
\A /re~ |
{?}~ |
{left} |
{edit} |
+ | This is Version 1.
{edit} |
{right}~ |
{edit}{calc}~ |
{right}{down} |
/rncTEST~{up}~
/xi(@count(TEST)=2)~/rndTEST~/xg\A~
/rndTEST~
It's hard to put comments next to those long Macro lines, so
let's take the last three lines one at a time:
/rncTEST~{up}~
9
Creates a two-cell range consisting of the potential next
"Current" cell and the one above it.
/xi(@count(TEST)=2)~/rndTEST~/xg\A~
This checks to see if the value of @count(TEST) is still 2. If it
is, it deletes TEST (important!) and tells the Macro to start
again from the beginning.
This Macro label had to be long, because the /xi command tells
the Macro to do the rest of the Macro label, if the condition is
true.
/rndTEST~
And finally, to clean things up, we delete TEST at the end of the
whole Macro. We had to do this because when @count(TEST) is NOT
2, the rest of that line doesn't get executed, and TEST still
exists.
Well, that should be enough to help you get started with Macros.
Give them a try, and write some of your own.
10
