LOTUS 123 MACROS
How many times have you wanted to quickly add up a column of
numbers, or determine the total width of your spreadsheet so you
know how wide to set the print margins, or enter a date as
8/15/88 instead of @DATE(88,8,15)? Perhaps you would like an
easy way to set the printer for an HP laser printer and then
switch it back to an Epson. Remember I said "easy". The way to
do these things and more is included here.
Macros to perform these and other tasks are available to all of
HCFA. They are located on the HCFA and the OMB PC bulletin
boards. Exact instructions for obtaining them and using them is
given below in "USING THE MACROS".
DESCRIPTION OF MACROS
A brief description of each Lotus 123 macro follows. For a more
detailed explanation retrieve the macro into Lotus 123. Each
macro is fully operational and contains detailed instructions on
This macro will place a line under a column of numbers and then
add them up. You do not need to use the @SUM formula, this macro
does it all for you. The only restriction is you cannot have any
gaps or spaces in the column of numbers (zeros and formulas are
This macro will display the width of a designated number of
columns on your spreadsheet, such as a print range. Place the
cursor in the left column of your print range and invoke the
macro. The total width of the column will replace READY in the
upper right corner. Press enter and the cursor will move one
column to the right and the total width of both columns will be
in the indicator. Continue until you are at the right side of
your print range or spread sheet and the total width will be in
Run this macro and use Lotus menus to set up your HP laser or
Epson dot matrix printer. Choose from either wide or compressed;
landscape or portrait, the macro will do it all for you. You
must, of course, set your own print range. Use the WIDE macro to
This will allow you to enter a date as mm/dd/yy (8/18/88). The
macro will then replace your entry with the Lotus serial number
for that day. You may then use the Lotus format command to
display the date any way you like.
Have you ever wanted a column or row of the same day of the month
for each of several succeeding months? This macro will ask you
some questions and then produce a column of dates (ie Jan 15, 88;
Feb 15, 88; Mar 15, 88; etc.). This column will contain the
Lotus serial number for the dates so you may copy them anywhere
or even transpose them into a row using Lotus Release 2's
This will hide your spreadsheet from view, "lock up your
computer", and wait until you enter your own "secret" password.
If you leave your computer it will record the time that anyone
tries to use you computer. Then when you enter your password the
time(s) that your computer was touched will be displayed and your
spreadsheet will be usable again. The only way to bypass this
is to re-boot so I suggest saving your spreadsheet before using
this macro. If someone thought your computer was broken they may
re-boot your computer in your absence.
Set the column width and use Lotus as a word processor. This
does the word wrap, allowing you to type a single long sentence.
The sentence will then be adjusted to fit into the column.
This adds as many rows as you select under the current row with
exactly the same formats as presently exist in the current row.
This is a handy addition for data entry.
For those of us without a separate number pad, entering a series
of numbers is a real pain. This very short simple macro solves
this problem by allowing you to set num-lock on and enter your
numbers. Cursor movement is controlled with the Enter key.
Place the cursor on a label, invoke this macro and the width of
the column will be adjusted to one space greater than the label.
Converts a number or a formula to a label and then converts it
back again. This is very handy when you want to copy a formula
to another location without loosing its absolute reference. If
you just copy the formula it will adjust to a relative address.
If, however, you convert the formula to a label, copy it, and
then convert it back into a formula, the absolute address of the
cell will remain be intact. This macro will do the conversion
This will allow you to underline a column of data with a symbol
of your choice. The width of the underline will be one less than
the width of the column.
Similar to adding a password to your spreadsheet. It's not as
secure as the Lotus password but effective never the less.
If your spreadsheet is very large it is often necessary to leave
your current place in the spreadsheet to get some information
from another location. To then return requires that you have
recorded in some way your present location. This macro records
your location for you. Invoke the go macro, wander around your
large spreadsheet and the return by invoking the return macro.
My all time favorite. This is the macro that Lotus forgot. All
the other major spread sheets have a macro learn mode - not
Lotus. This macro will record a series of keystrokes as you type
them. You can then use these keystrokes in your own macros as
they were created or you may edit them any way you like.
USING THE MACROS
These macros are great to examine and use by themselves. Their
real strength, however, comes when they are combined into one of
your spreadsheets for a specific function or need. To do this I
have a "secret" place in all my spreadsheets where I keep my
tools. It's way off in "never never land - HA4000". I have
never had a spread sheet extend out to column HA nor extend down
to row 4000, except for some very large data bases.
When you are working on a spreadsheet and find you have a need
for one of these macros:
1. Go to HA4000,
2. Combine the entire macro spread sheet into your current
spread sheet - Slash (/), File (F), Combine (C), Copy
(C), Entire file (E), then select the macro as you would
3. Name the ranges as instructed. (Each macro comes with
complete instructions immediately below it with the
necessary steps to follow for its use.),
4. Go back to your work and continue working, except you now
have the power of the macro or macros you have just
combined into your spread sheet.
You may of course combine several of these macros into a single
spreadsheet. I usually will combine the WIDE and PRINTER macros
into mine, plus others. I can calculate the width of my print
range and print it on my Epson prior to printing it in final on
the HP laser. In combining more than one macro, just be sure to
go down below the first before adding the second and so on, so
they do not over write each other. You may of course delete the
instructions if you do not need them but you must be sure the
range names remain unique.
If you found these Lotus macros usable and would like to be made
aware of additional ones as I develop them please register with
me. I do ask for a $10.00 contribution to help cover my time and
CITY, STATE ZIP ______________________________________________
WHERE DID YOU GET THE MACROS?
BULLETIN BOARD - WHICH ONE AND NUMBER___________________________
Please send completed form to:
4668 Clydesdale Ct.
Ellicott City, Md 21043