Copyright: ® Israel Kehaty 1993. All Rights Resereved.


Copyright © 1993 by Israel Kehaty. All Rights Reserved.

Copyright © 1993 by K.I.T.A.L. Software. All Rights Reserved.

Super Power Macros

Techniques and Secrets

for Lotus 1-2-3

Introduction:

Legal Matters

Start Here   <======= Click here now!

About the Author

Foreword

Welcome

Part One:

Macro Tutorial

Macro Listing Conventions

Advanced Macro Techniques                                                                 PgDn  ↓

Part Two:

Macros Affecting Rows, Columns and Ranges

Copy Macros

Database Macros

Date Macros

File Macros

Format Macros

Graph Macros

Mathematical Macros

Utilities for *.MLB Macros

Modifying Macros

Cells, Ranges and Cell Pointer Moving Macros

Macros Affecting Range Names

Print Macros

Miscellaneous Macros


Foreword

I discovered Lotus 1-2-3 during my two year sabbatical at Oregon State University, where I finished to develop the expert system for mechanical springs design, The ULTIMATE SPRING DESIGNER. After using the tutorial for a few hours I was hooked, and in two weeks, I finished my first version of The ORGANIZER for LOTUS 1-2-3. It was a menu driven file manager designed to take control over hundreds of worksheets and other DOS applications without leaving Lotus 1-2-3. In no time, I found myself developing macro based applications in 1-2-3, using its macro language before even using it the usual way. Lotus 1-2-3 became a hobby for me. Every day I found more exiting features and I still do. Then I found Lotus 1-2-3 for Scientists and Engineers, written by William J. Orvis and published by SYBEX Inc., which convinced me that Lotus 1-2-3 (or any spreadsheet) is an excellent tool for engineering. Since then, I have been using Lotus as the main development tool for my engineering work, instead of programming in FORTRAN, Quick Basic, etc.

I was always fascinated by Lotus macro language that could reduce tedious tasks. The macros in the literature and in commercial packages were always limited to one type of cell data and the data had to be contiguous. Also, I was disappointed with the macro library concept presented in the literature and the commercial packages, which required combining macros into one file per task or project. Why should I keep dozens of macros in the worksheet and waste memory?

My macro library is menu driven by central Macro Managers allowing the user to activate any macro using "point and shoot" while the macros are stored in the disk. The manager combines the macro, defines the range names and activates the macro. When the work is finished, the manager erases the macro and returns to the main menu. The macros should be as free from limitation as possible. My research has resulted in the SUPER MACRO LIBRARY for LOTUS 1-2-3.

Super Power is not a replacement for Lotus manuals. All of the macro keywords and functions of 1-2-3 are not included. However, Super Power does include all the techniques that are used in the SUPER MACRO LIBRARY, which are beyond the normal scope of macro books. Some of these tricks and techniques may be found scattered through the literature. In Super Power, they are organized, enhanced, improved and explained.

Super Power describes the techniques used in the macros and how they can be applied, using actual macros from the SUPER MACRO LIBRARY. Super Power can be used as an unofficial manual to the SUPER MACRO LIBRARY; however it goes far beyond that. It explains, in careful detail, every macro structure and code and presents all the techniques and tricks that were used in the macro. When an issue justifies more explanation, a special chapter is dedicated to it and cross-referenced.

When you register your copy of the SUPER MACRO LIBRARY for LOTUS 1-2-3 you are legally allowed to use its macros while studying Super Power instead of keying in the macros into Lotus 1-2-3. Occasionally you may wonder why a macro is written in a particular way? There is always more than one way to accomplish a task. In addition, the author wishes to avoid the issue of copyright infringement on other commercial macro libraries. Every effort was made to write the macros and organize so they are unique.

•	Every macro is a separate file, which gives greater flexibility and saves memory,

especially when the macros are used through the macro managers.

•	All range names associated with the macro are numbered, so there is no

possibility of two identical range names in different macros.

•	The macros are as broad as possible so they can handle ranges, regardless of

what they contain, and the data doesn't have to be contiguous. The macro always checks every cell's contents, prefix, and type, and acts accordingly.

•	The macros can be used independently, or from the macro manager.

•	The macros should work in Automatic and Manual Recalculation mode.

•	The macros check for the Lotus release being used and adapt their behavior

accordingly. For example: if the macro is used in release 3.0 and up, it will treat 3-D ranges as well as 2-D ranges. However, if the macro senses that it's being used in 2.0/2.01/2.2/2.3/2.4, it will ignore all 3-D functions and macro commands. When it is impossible to use the same macro for all the releases, a special macro is included, usually because of some incompatibility.


Welcome

This is not a standard Lotus macro book, it is a unique, one-of a-kind book. Only a few of the Lotus books include realty-based, powerful unlimited macros; and there only a few are included.

Super Power is totally different. It is based on more than 250 macros included in the SUPER MACRO LIBRARY from K.I.T.A.L. Software. These macros are commercial macros built in so that they do not suffer from the limitations of other commercial macro libraries. For example: they do not demand that the data in a range have to be the same type, contiguous or have other limitations. They handle ranges, not just columns or a single cell, but a range containing any type of data: labels, numbers, formula, blank, @MA and @ERR. If the macro is designed to change the labels to uppercase, it will ignore all the other types of data and will treat only the labels in the range. If the macro is designed to multiply all the numbers by 1.2, it will multiply only and all the numbers in the range and will ignore all the other cells. The macros handle both 2-D and 3-D ranges transparently, and automatically sense what type and version you are using and applies the correct code for every type of Lotus.

We have developed a universal range handling code which is repeatedly used as a whole or with modification in many macros. This routine prompts you to paint the range to be processed and then the routine moves the cell pointer to all the cells in the range and processes them. Even though the range handling routine is repeatedly used in many macros, we still explain the code in every macro.

Super Power is essentially a handbook designed for users of all levels of experience in Lotus 1-2-3 macro language. If you have some experience in macro writing, you can try and jump directly to macros of the fourth [4] level of difficulty. If you are a more advanced user you might start with the study of the eighth [8] level macros. If you have little or no experience, start with the Macro Tutorial and then move to macros of zero [0] level of difficulty. Super Power is flexible, which allows the reader to study macro programming by reading the analysis and explanation of actual macros, not the other way around.

Super Power offers detailed analysis and explanation for every macro code and command, and any technique or trick that is used to produce the correct results. When we analyze a macro and come to such a new or unconventional technique, it is cross-referenced to the chapter dealing with the same technique, or we explain the technique within the macro as needed. All these techniques are in Part One in the Advanced Macro Techniques chapter, so the experienced reader can study the techniques without having to read the macros. However, the techniques are simply explained so the principles are easy to understand. In Part Two of the book, the macros are divided into different group categories based on the action they perform: printing, graphics, copy, move, ranges, format etc. Each group is explained in a chapter and each macro in the chapter has a section where it is carefully explained and analyzed.

How to Use Super Power

Book Conventions


How to Use Super Power

If you read Super Power, you probably belong to one of two groups:

•	The first group of readers purchased Super Power to get the super power macros

and to key them into Lotus 1-2-3 to use them in daily work. If you belong to this group, we suggest using the discount option to purchase the complete SUPER MACRO LIBRARY for LOTUS 1-2-3, a commercial copyrighted package including all the macros in Super Power plus 220 *.MLB macros to use with the MACROMGR.ADN add-in, and six more library managers in addition to the SMALLMGR.WK1 macro manager featured in Super Power. Keying these complex macros into Lotus 1-2-3 may be a very time consuming process.

This macro code was written to make these macros as safe as possible with minimal, or no limitations, which demand more code. If you key a macro into Lotus 1-2-3, always key it into an empty worksheet, beginning from the A1 cell according to the given macro text. Then save it. When you need to use it, combine it to your current worksheet. All the cell addresses in the macros are based on the assumption that the first cell is the A1 cell. When you combine it later into your worksheet, Lotus 1-2-3 adjusts the addresses respectively to the location.

•	The second group of readers purchased Super Power to learn the ultimate

techniques to create super power macros and applications. If you belong to this group, understand that Super Power is not trivial to master; but if you want to learn macro programming at its best, you will not find a more complete source. Follow the guidelines for beginners. If Super Power proves too advanced, we suggest 1-2-3 COMMAND LANGUAGE from the QUE Corporation, 1986, an excellent book written by Darien Fenn. I gained the rest of my knowledge from experience, magazine articles, and analyzing macros that others wrote.

If after you study a specific macro command or function in the Lotus 1-2-3 manual or on-line help and it is unclear, we recommend that you copy the specific code into Lotus 1-2-3 and create a small macro, including only this code. Then execute it to see what it does. The same is true when you see a complicated string function. Break each function to its sub functions and see what every part does. In Super Power, we do not dedicate the same detailed explanation to functions in the code because Lotus users have to know how to use functions, which are the essence of the spreadsheet. You may not be familiar with a specific function, but you have to know how to write a function in Lotus 1-2-3. To fully understand the function, you have to isolate it, key it separately, and see what it does.


Book Conventions

Super Power uses the following typographic conventions:

Example of conventions                            Description

{BEEP}/RNC, labels101, LOWRCASE

Macro code listing is written in point eight type with a lighter weight. Ranges such as labels101 are written in lowercase format. Lotus 1-2-3 functions and commands are written in uppercase format. The main macro name like the LOWRCASE macro is also written in uppercase format.

{BEEP}, /RNC, @INDEX

Macro codes appearing in the book's text are written in point 10 type with lighter weight and in uppercase format.

[labels101]

When the book's text refers to range names, they are always surrounded by squared brackets.


Macro Tutorial

Beginners

Advanced Users

What Is a Macro?

How a Macro Works?

Saving a Macro to Disk for Later Use

What Is an Add-In?

Using the MACROMGR.ADN Add-In

Loading an *.MLB Macro

Executing the Macro in Memory


Beginners

If you are a beginner in the macro field, start with the What is a Macro? chapter, which will guide you to write your first macro, and give you insight into the types of macros, macro libraries, etc. After finishing this chapter, start your tour of the macros. To make it easier, each macro title is graded with a difficulty level number starting from zero and going to ten. If you are a beginner, start with the macros marked with [0] or [1] which are very simple macros mimicking the keys of the keyboard. When you gain more confidence, move to the next level and pick a macro to study. Activate every macro and see how it works.

The key is to EXPERIMENT! EXPERIMENT! EXPERIMENT! Do not be afraid to play with the macro code, which is the only way you can actually see what happens. However, remember to keep a backup copy just in case you manage to destroy the macro code.


Advanced Users

If you have some experience with macro writing, you can still get valuable information from the What is a Macro? chapter, because it reviews the types of macros, add-ins and macro library's organization. Then you can pick macros to study. If you are a really advanced user, then start to read and study the techniques in Part One. However, we recommend that Part One, which contains the techniques that have been used in the macros, should be read in order. Many techniques use other techniques as building block; therefore the basic techniques are featured first.


What Is a Macro?


How a Macro Works?


Saving a Macro to Disk for Later Use


What Is an Add-In?


Using the MACROMGR.ADN Add-In


Loading an *.MLB Macro


Executing the Macro in Memory


Macro Listing Conventions

Terminology

Macro Structure

Writing Macros to Work with the Macro Manager

Custom Menu Listing Convention

Formulas Listing Convention

Complete Cell Contents Listing Convention


Terminology:

Macro keys, macro code

When we use key sequences in the macro, representing the exact keys on the keyboard e.g. to change the column width, press the /WCS keys, we may use the phrase "macro keys", "macro code" freely.

Macro commands, commands, Routine command {routine_name}:

When we use commands which are not presented in the Lotus macro language by one key or may not be available from the keyboard, we may freely use, the "macro command/s" or "command/s". For example: "the macro issues the {EDIT}{HOME}{RETURN} macro commands".

Routines, routine names

Super Power freely uses the "routine" term interchangeably with subroutine. For example, "The macro issues the {BRANCH print121} macro command which routes the macro control to the [print121] routine".

Range names

When we write range names in Super Power, we always surround then with squared brackets. You can find phrases like: "the cell pointer moves to the B23 cell named [labe121]". In this example, the text refers to the B23 cell which also has the [label121] range name (without the squared brackets).

Section

Section refers to the sub chapter that contains the macro listing and explanation. Every macro in the second part of the book forms a section. There are sections in Super Power for each macro featured in Part Two.

The macro issues, the macro uses, the macro continues

"the macro issues" means "the macro processor issues". For example, you will see a phrase like "The macro issues the {GOTO}A1~ macro command"


Macro Structure

Because all the macros in Super Power are the actual macros from the SUPER MACRO LIBRARY commercial package, and they are designed to work independently or from The Macro Managers, they are built to insure they will work as expected. If you write macros using the same structure, you will also be able to use them with the macro manager. To make it real, we use the LOWRCASE.WK1 macro which turns any label in a selected range into a lower case form. Our example, shows the structure and other conventions used in Super Power. The macro listing for the LOWRCASE.WK1 macro is:

                A                   B                   C                  D                 E

  1 *---A macro to turn all labels in a 3-D or 2-D range to LOWERCASE form

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP

  8 !

  9 !

 10 \Z             {BREAKON}

 11 LOWRCASE       {WINDOWSOFF}{PANELOFF}/RNCWhich range ?~~/RND

                   Which range ?~/RNC{WINDOWSON}{PANELON}Which range ?~

                   {BS}{BS}{?}~{WINDOWSOFF}{GOTO}Which range ?~

                   {LET counterb410,0}~

 12 cont410        {LET hereabs410,@CELLPOINTER("address")}~

                   {LET counter410,0}

 13 !              {FOR counter410,0,@COLS(Which range ?)-1,1,labels410}

 14 !              {LET rel410,@INFO("release")}~{IF @LEFT(rel410,1)<>"@"}

                   {GOTO}{hereabs410}~{LET counterb410,counterb410+1}~

                   {IF counterb410<@SHEETS(Which range ?)}{NS}{GOTO}

                   {hereabs410}~{BRANCH cont410}

 15 !              {GOTO}Which range ?~/RNDWhich range ?~

 16 !

 17 counter410            3

 18 countera410           5

 19 labels410      {RIGHT}{LET here410,@CELLPOINTER("address")}~{LEFT}

                   {FOR countera410,0,@ROWS(Which range ?)-1,1,labelsa410}~

                   {IF counter410<@COLS(Which range ?)-1}{GOTO}{here410}~

                   {LET countera410,0}~

 20 !

 21 here410        $D$1

 22 !

 23 labelsa410     {PANELON}{RECALC prop410}{IF @CELLPOINTER("type")<>"b"

                   #AND#@CELLPOINTER("type")<>"v"#AND#@CELLPOINTER("prefix")

                   <>"\"#AND#@CELLPOINTER("prefix")<>"|"}{PANELOFF}

                   /RVprop410~~{EDIT}~

 24 !              {DOWN}

 25 !

 26 prop410        \Z

 27 !

 28 counterb410           4

 29 hereabs410     $A$1

 30 !

 31 rel410

•	The first 9 cells in the A1..A9 range of the macro are reserved for the

description and use instructions.

•	The A10 cell holds the \Z macro name and the B10 cell hold the {BREAKON}

macro command. We have chosen this macro name just in case the worksheet is in {BREAKOFF} status, but any other DO NOTHING macro name such as {},{ESC} will do.

•	The A11 cell holds the macro name (LOWRCASE in this example). This

name must be exactly as in the DOS file name of the macro, i.e. when the macro is saved, it should be saved as LOWRCASE.WK* file. This will be explained later when we will learn about the MACRO MANAGER, the last macro in Super Power.

•	The B10..I** cells are for the macro code and custom menus. The macro is

always written in the "B" column. When a custom menu is needed, it can occupy up to eight columns, the B, C ,D, E, F , G, H, and the I columns. Therefore the macro can occupy up to nine columns.

•	The cells on column "A" are used only for range names and {routine}

names. A cell not used for a range name should contain the "!" exclamation mark. This is to keep the "A" column contiguous with no blank cells. Any other character can be used instead of the "!" character; however we will use it in Super Power to be compatible with the SUPER MACRO LIBRARY. The [\Z], [LOWRCASE], [cont410], [counter410] and the rest of the labels in the "A" column are the range names of the cells to their right. For example, the [counter410] range name refers to the B17 cell. When you place the cell pointer on the A10 cell, which contains the [\Z] label and carries the instructions in the A2 cell, and press /RNLR END DOWN  ENTER, Lotus automatically assigns all the labels in the "A" column beginning with the [\Z] label down to the [rel410] label as the range names for the cells to the right.

•	Every range or routine name will be uniquely numbered (in the LOWRCASE

macro the number is 410). This way we can use a code from one macro to build another macro and then use the Search/Replace utility to replace the number with a new number.

•	If a macro creates a temporary macro/range name during its execution, it

must delete it when the macro is finished. A good macro should leave minimum traces.

Note: The "A" column of the macro is dedicated for range names, description and use instructions only. The code starts with the B10 cell. If the macro does not contain a custom menu, all the code is in the "B" column. This is important to remember when you want to key the macro code into Lotus 1-2-3. Therefore, in such macros, the code as it appears here (in the LOWRCASE.WK1 macro) is enough for you to be able to key it into Lotus 1-2-3. It occupies the "A" column and the "B" column only. When the macro uses dynamic string formulas to create its code, we show each formula's code separately and you should key the formula's code into Lotus 1-2-3 and NOT the code as it appears in the main listing (which is the result of the formula that appears in the cell). When the macro uses custom menus, which cannot be displayed on one page without overlapping, we also show every menu code separately. In both of these cases, when the macro includes complicated cell formulas and/or custom menus, we also show a full cell contents list at the end of each section to make it easier for you to key the macro into Lotus 1-2-3.


Writing Macros to Work with the Macro Manager

                A                 B                  C                  D                   E

  1 *---A macro to invert a column of numbers or text

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define

  3 *---the range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6 !

  7 !

  8 !

  9 !

 10 \Z             {BREAKON}

 11 INVRTCOL       {WINDOWSOFF}{PANELOFF}/RNCInvert column ?~~/RNDinvert...

 12 !              {WINDOWSOFF}{PANELOFF}{GOTO}Invert column ?~/WIC~/DF...

 13 !              /DSRD{BS}.{END}{DOWN}{RIGHT}~P~~G

 14 !              /WDC~

 15 !

Super Power includes the macro code of the SMALLMGR.WK1 macro manager, which allows you to use all the macros in Super Power from a menu, without the need to combine them first. The Macro Manager handles all that is needed to activate the macros. The macros in Super Power were designed to work with the macro manager unless their nature does not allow it (for example, all the toggle macros have to be part of the current worksheet; therefore there is no point in using them with a macro manager). However you can write or modify your macros or any macro that you acquire to make them compatible with the macro manager by following these rules:

•	First nine cells in the first column of the macro are for instructions or the

exclamation mark "!".

•	The 10th cell holds the \Z macro name.

•	The 11th cell holds the mnemonic macro name (must be IDENTICAL to the

macro file name).

•	Downward is for RANGE NAMES, etc.

•	There should not be any blank cell in the first column. If the cell is not used

for a range name, then it must contain the exclamation mark "!" (this also applies for the first 9 cells); so the first column is contiguous and we can use {END}{UP} or {END}{DOWN} for naming and erasing.

•	Don't use the {QUIT} macro keyword because it stops the process and exits

to the READY mode when the macro is finished, and the Manager's main menu will not re-appear. Instead, use a branch instruction to an empty routine like {BRANCH EMPTY} where EMPTY is an empty cell, with one exception: when an error routine has been activated, the macro loses track. The cell should contain {BRANCH \Y}.

•	All the macros should start with the \Z macro name and {BREAKON}.

If you look at the sample macro INVRTCOL.WK1, you can see A1 to A9 cells hold the use instructions, the A10 cell holds the '\Z label, and the A11 cell holds the INVRTCOL range name (identical to the INVRTCOL<.WK1> file name). All the other cells in the "A" column contain the exclamation mark "!" character. You can build your own macros or adapt other macros to be used with the MACRO MANAGER as long as you follow these simple rules.


Custom Menu Listing Convention

When describing menu listings separately from the general macro code (when all the menu codes cannot fit across the screen without overlapping), a code that belongs to the same cell but is too long to be displayed in the screen/page in one line is indented and written in the next line. Example:

NENU TITLE ======>  View/change

                    View or change the range area to print

                    Select the range to view and press [ENTER] {GET key1155}

                    {IF key1155="{ESC}"}{DOWN}{UP}{BRANCH end155}{ESC}

                    {IF key1155="~"}{DOWN}{UP}{LET change1155,@CELLPOINTER

INDENTED CODE ========> ("contents")}~{change155}{BRANCH end1155}

                    {key1155}{?}~{LET change1155,@CELLPOINTER("contents")}~

INDENTED CODE ========> {change155}

                    {UP}{DOWN}{MENUBRANCH menu1155}

The indented code in this example is the continuation of the code in the line above and should be written in the same cell. In the macro listing, the cells are numbered, a code that belongs to the same cell but is too long to be displayed in the screen in one line is written in the next line, but is not numbered. In the following example we placed the "Continue ====>" note to mark these lines:

                A                 B                   C                   D                  E

 19  crit2142       {WINDOWSON}{PANELON}{GETLABEL "Insert criterion string

     Continue ====> or [F]to Find or [S] to Skip: ",crit3142}~{IF @UPPER

     Continue ====> (crit3142)<>"F"#AND#@UPPER(crit3142)<>"S"}

     Continue ====> {RECALC critform142}{critform142}{WINDOWSOFF}{PANELOFF}

 20 !               {IF @UPPER(crit3142)="F"}/DQF{ESC 4}{QUERY}{?}~

     Continue ====> {WINDOWSOFF}{PANELOFF}{PGDN 2}{HOME}{RIGHT}{END}{DOWN}

     Continue ====> {DOWN}{GOTO}output142~{WINDOWSON}{PANELON}

     Continue ====> {MENUBRANCH extrct142}

 21 !               {IF @UPPER(crit3142)="S"}{RIGHT}{BRANCH crit2142}

 22 !               {IF @LENGTH(crit3142)=0}{RIGHT}{BRANCH crit2142}

The code marked with the "Continue ====>" note in this example is the continuation of the code in the line above and must be written to the same cell.

Important Note: Sometimes it may seem that there are unnecessary commands or repetitions of the same command in the code of the macros in Super Power; but from our inquiries, they are needed to cover the many versions of Lotus 1-2-3. For the macro to correctly work they are needed. In every Lotus release, there are some quirks and incompatibilities to the previous versions and different releases behave differently in the same situation. There are many examples of this. Sometimes when we look in our macros, we may forget why a specific command is there, but we are very careful not to change it before we can extensively test it in all the versions of Lotus 1-2-3.


Formulas Listing Convention

When we refer to formulas, you will often see the following notation:

        33 wid207          @STRING(@CELLPOINTER("width")-1,0)

Only the right part "@STRING(@CELLPOINTER("width")-1,0)" is the formula, "33" is the row number where the formula is (in the main macro listing) and "wid207" is the description name of the range name that was assigned to the cell containing the formula. We always place the description name to the left of the range with the same range name. Here, the B33 cell is named [wid207], the "wid207" text in the A33 cell is only a reminder to clarify the code of the macro. A full cell contents list is attached at the end of each section.


Complete Cell Contents Listing Convention

When macro is complicated, we bring a complete list of all the cell contents of the macro at the end of the section. If you use this list to key the macro into Lotus 1-2-3, you do not have to key the apostrophe. For example, if the list contains these lines:

B10: [W9] '{BREAKON}

A11: U [W15] 'FORMLETR

it means that the B10 cell contains the {BREAKON} macro command and the A11 cell contains the "FORMLETR" label. When you key the code into the B10 and the A10 cells of the macro, you do not have to type the apostrophe before the {BREAKON} macro command and before the "FORMLETR" label. Lotus adds them automatically because they are labels. The [W9] informs you that the "B" column's width is "9" characters. The [W15] informs you that the "A" column's width is "15" characters. The "U" informs you that the A11 cell is an unprotected cell. However, if the list contains lines like these:

B10: [W9] '@CODE(...

B10: [W9] '+A20.....

You have to type the apostrophe because the macro writes a formula as a label into the panel. If you omit the apostrophe Lotus writes it as an active formula.


Advanced Macro Techniques

Using the {NAME} Macro Command or the F3 Key

Moving the Cell Pointer to the Top Left Cell of the Screen

Controlling Screen and Panel Display Activity

Manipulating the Panel

Injecting Text into the Panel

Indirect Macro Command ({GOTO} for Example)

Safely Clear and Insert Data in the Panel

Insert Print Header or Footer Safely

Change Printer Setup String Safely

Combine Files Safely

Extract to File with Confidence

Save a Worksheet with Confidence

Using the F9 or the CALC Key on the Panel

Freezing and Releasing Screen and Panel Activities

Using the Pointing Method

The Enveloping Technique

Moving Between Sheets in Straight Lines   <==== Click here!

Using Lotus Menus Inside a Macro

Duplicating Lotus 1-2-3 Menus in Macros

The {MENUBRANCH} and {MENUCALL} Macro Commands

Using Custom Menus

Delete Range Names Safely Inside a Macro

Create Range Names Safely Inside a Macro

Range Name as a Prompt

To Find if a Range Name Exists

Pick a Range Name from the Screen

Using Dynamic Code

Using Formulas to Create Dynamic Code

Printing to File or Printer with Confidence

Embedding a Printing Setup String in the Worksheet

Recording Cell Pointer Position Smartly

Handling Manual and Automatic Recalculation Smartly

Copy a Cell Using Adjacent Column Length

Monitoring and Controlling User's Input

Cleaning After Macros

Quitting Without the {QUIT} Macro Command

Advanced Use of the {LET} Macro Command

Using the {INDICATE} Macro Command for Prompts

Changing a Date String into a Valid Date

Using the {FOR Loop} Macro Command

Using Matrices in Lotus 1-2-3

Using System Commands in the New Releases

Find Painted Range Address from Within a Macro

Creating Multiple Copies of a Range

2-D and 3-D Lotus Releases

Using Functions Allowed in a 3-D Release Inside a 2-D Release

Using the {CE} Macro Command in the 3-D Releases

Using Macro Commands of a 3-D Release in a 2-D Release

Insert Numbers as Labels

Finding the Release of Lotus Being Used

Advanced Use of the Response to the {GET Key} Command

Managing Macro Libraries

Change All the Labels in a Range to Lowercase Form   <=== Click here!


Using the {NAME} Macro Command or the F3 Key


Moving the Cell Pointer to the Top Left Cell of the Screen

Many times you need to move the cell pointer to the top left cell of a database or a different kind of table. At the same time you want to place the cell pointer on the upper left cell of the screen. This topic explains how to do it.


Controlling Screen and Panel Display Activity

When a macro is activated, the screen and the panel show a lot of activity which has some faults when you need to design and develop professional looking applications. This activity greatly slows the macro operation. The classic Lotus macro language has four macro commands to control the panel and screen display activity. The commands are {WINDOWSOFF}, {PANELOFF}, {WINDOWSON}, and {PANELON}. These commands do exactly what their names mean. The new releases have the {CLEARENTRY} or {CE} and the {PANELOFF CLEAR} new commands, but because we would like to keep it possible for all the macros to work in release 2.0 and up, we do not use the last three commands in our macros. You will find that we use these macro commands extensively in the macro code to control the macro appearance and to speed operation.


Manipulating the Panel

It is possible to make the macro to type text into the panel exactly as it is typed from the keyboard. This makes a very powerful tool in macro creation and programming. It can be used to display prompts for the users, to manipulate cell contents, to apply a formula on a cell content using the enveloping method, to apply a formula on adjacent cells using the pointing method, etc. This section only deals with the technique for type text into the panel. The next sections deal with more ways to manipulate the panel.

Note: In the SUBTOTAL.WK1 macro, there is an example of how to write a formula using the panel.


Injecting Text into the Panel

When we say injecting text into the panel, we refer to a unique and very powerful technique for any macro programmer. Using this technique we can write (inject) any kind of text into the panel instead of manually writing it. Other advanced techniques are based on and use this technique.


Indirect Macro Command ({GOTO} for Example)

This is a very powerful technique that highly advanced macro programmers use.


Safely Clear and Insert Data in the Panel

When changing the header or footer or inserting a new setup string in the printing process or saving a file and naming it with a new name or combining a file, there is always the possibility that Lotus will use default data that was previously used in the worksheet. The old data (if any exists) must be cleaned in a safe way, allowing you or the macro to insert the correct data, and also safely handle the process if there is no previous data.


Insert Print Header or Footer Safely

When changing the header or footer, there is always the possibility that Lotus will use the default header or footer that was previously used in the worksheet. The old data (if any exists) must be cleaned in a safe way, allowing the macro to insert the correct data, and also safely handle the process if there is no previous data. This topic explains how to do it.


Change Printer Setup String Safely

When changing the setup string, there is always the possibility that Lotus will use the default setup string that was previously used in the worksheet. The old data (if any exists) must be cleaned in a safe way, allowing the macro to insert the correct data, and also safely handle the process if there is no previous data. This topic explains how to do it.


Combine Files Safely

When combining a file, there is always the possibility that Lotus will use the name of the last combined file. The old data (if any exists) must be cleaned in a safe way, allowing the macro to insert the correct data, and also safely handle the process if there is no previous data. This topic explains how to do it.


Extract to File with Confidence

When you need to extract part or all of the worksheet into a file from inside a macro, you need to figure how many time to press the ESC key to clear the panel from the default file and the default directory. This topic explains how to solve this problem.


Save a Worksheet with Confidence

When we write a macro to save a worksheet, we have to account for two cases. First when a file with the same name exists and, second, when the file name is new. This topic shows how.


Using the F9 or the CALC Key on the Panel


Freezing and Releasing Screen and Panel Activities


Using the Pointing Method


The Enveloping Technique

This topic shows how to use a macro to apply a function on a data in the cell. For example if the cell contains -1000 and we want to overwrite it with its absolute value. Such a macro will replace the cell content with the @ABS(-1000) formula.


Moving Between Sheets in Straight Lines

In the 3-D releases of Lotus 1-2-3 (3.0/3.1/3.1+/3.4 and 123W), the cell pointer moves from one sheet to another using the CTRL-PGUP and CTRL-PGDN key combination, or in a macro using the {NS}, {NEXTSHEET}, {PREVIOUSSHEET} or the {PS} commands. However, the cell pointer doesn't move in a straight line. It can start in cell A1 in the "A" sheet and jump to the X10 cell in the "B" sheet to Z100 on the "C" sheet and so on. The 3-D releases of Lotus 1-2-3 were built so that the worksheet "remembers" the last location of the cell pointer in every sheet it passed earlier. When you page through the sheets manually or from within a macro, the cell pointer may move in a ZIGZAG path depending on the previous history of its last places in the different sheets.

This may be a bonus for you when you are using the keyboard, but it is a nightmare for macro programmers because they cannot use the {NS}, {NEXTSHEET}, {PREVIOUSSHEET} or the {PS} to move in a straight line in the Z direction. We have found that the cell pointer will move in a "straight path" when the worksheet is in the perspective mode and the syncro is ON. However, changing to this mode before a movement inside a macro can be very noisy (Lotus will beep) because the worksheet may already be in the perspective mode. Therefore, we have developed a safe and elegant way to move between sheets in our macros. We always record the cell address and then use the {NS} or {PS} to move to the other sheets and then use the indirect {GOTO} command to the recorded address. For example:

                A                 B                 C                 D                E

  1 \Z             {LET address100,@CELLPOINTER("address")

  2 !              {NS}{GOTO}{address100}~

  3 !

  4 address100     $F$5

  5

Assuming the cell pointer is located on the F5 cell in the "A" sheet and we want to page to the "B" sheet and we also want the cell pointer to be on the F5 cell. However, the last time the cell pointer was on the "B" sheet, it was placed on the A1 cell. Therefore if you issue the CTRL-PGUP from the keyboard or the {NS} or the {NEXTSHEET} in a macro, the cell pointer will land on the A1 cell in the "B" sheet.

The macro first issues the {LET address100,@CELLPOINTER("address") macro command, which stores the current cell pointer position (the $F$5 address) in the B4 cell named [address100]. Then the macro issues the {NS} macro command moving the cell pointer to the next sheet; the "B" sheet, but the cell pointer goes to the A1 cell. The macro issues the indirect {GOTO}{address100}~ macro command moving the cell pointer to the F5 cell in the "B" sheet. Although there is a separate chapter for the indirect macro command, we will also explain the technique here. To visualize the process, let's see how the command works step by step.

If the cell pointer is now on the A1 cell in the "B" sheet, when the macro issues the {GOTO} macro command, the panel displays:

          B:A1:

          Enter address to go to: B:A1

When the macro reaches the {address100} routine command the macro processor processes this command. However, the cell named [address100] contains only the $F$5 text; therefore Lotus writes (injects) the text into the panel which now displays:

          B:A1:

          Enter address to go to: $F$5

Last, the macro issues the tilde "~" macro command which is the macro equivalent of the ENTER key, and sends the cell pointer to the F5 cell in the current sheet which is the "B" sheet. Using this technique you can safely move the cell pointer in straight lines into and out of the worksheet in the Z direction.


Using Lotus Menus Inside a Macro

When a Lotus menu is activated from a macro, the menu respond differently to the user's input compared to the manual use. This topic explains the problem and how to avoid it.


Duplicating Lotus 1-2-3 Menus in Macros

This topic shows how and why?


The {MENUBRANCH} and {MENUCALL} Macro Commands

Super Power often uses the {MENUBRANCH} and the {MENUCALL} macro commands and they behave differently; therefore we will explain the difference and provide useful technique to improve and protect applications.


Using Custom Menus


Delete Range Names Safely Inside a Macro

When a macro deletes a range name, there is always the chance that the range name does not exist and an error can occur. This topic shows you how to safely delete a range name during macro execution.


Create Range Names Safely Inside a Macro

One of the most used techniques in Super Power is the assignment of a range name to the range that you paint. Let's see what happens when you try to assign a range name to a selected range. If you want to assign the [temp] range name to the A1..C24 range, you have to issue / Range Name  Create sequence: /RNCtemp, press the ENTER key, type A1..C24 and press ENTER again. This is a simple procedure with which Lotus users should be familiar. However, if you try to assign the same range name again, but this time to the F100..I150 range, the steps you use are not the same. First, start with the /RNCtemp, then press the ENTER key as before, but now Lotus volunteers and highlight the A1..C24 range and offers it as the default. Continuing as before, will result in assigning the [temp] range name again to the A1..C24 range, while you actually want to assign the range name to the F100..I150 range.

When writing a macro or a macro based application that assigns range names you have to account for this problem. This topic shows you a foolproof technique to assign a range name.


Range Name as a Prompt

Lotus 1-2-3 allows a range name of up to 15 characters long, which allows you to assign meaningful range names that can serve as helpful prompts. The sophisticated macro programmer can use the range name to display an additional helpful prompt.


To Find if a Range Name Exists

Many times we need to delete range names. We can always use the / Range Name Delete command sequence and manually delete the range names. Sometimes, we want to automatically delete all the range names in a range; therefore we need to know if the range contains cells which have range names. This is especially important when erasing part of a worksheet and there are range names in it. Trying to find which cell has a range name is a very time consuming process.

This topic shows three little known techniques to delete range names from a macro when the range contains text that may point to range names. But this topic will also show you an ultimate solution how to use a macro to find and clean range names in a selected range even if the range is empty without any text that can point to range names. We believe that this is the first time such a technique is ever published.


Pick a Range Name from the Screen

When you issue the F5 (GOTO) key or the {GOTO} macro command or the /RNC or the /RND macro keys in 1-2-3, Lotus displays a list of all the range names in the worksheet. However, Lotus does not offer a macro command or a function to "grab" a range name from the screen display which we could use to add a point and shoot options for other tasks beyond the {GOTO}, /RNC or /RND. This macro demonstrates how we can pick a range name from the screen and record it into the current cell position. This is a very powerful technique that we can use in automated macro driven applications.


Using Dynamic Code


Using Formulas to Create Dynamic Code

In the previous chapter, we showed one technique to create dynamic code in Lotus macros. Here is a second way to create dynamic code using string formulas, a much more powerful technique. This topic shows you how.


Printing to File or Printer with Confidence

When you try to print a worksheet into a file or the printer using the keyboard, you can press the first character "R" to select the [Range] menu option in the Lotus print menu or to highlight the [Range] menu option and press ENTER. If you want to print into a file you have to assign a file name. However, if the file name already exists, then you need to choose the [Replace] menu option,  pressing the "R" character the second time. If the file name is new, the [Replace] menu option does not appear so the "R" character will be pressed only once. It is not a problem when you use the keyboard but how can we make the macro know when to issue the "R" command once or twice? This topic shows you how.


Embedding a Printing Setup String in the Worksheet


Recording Cell Pointer Position Smartly

For a long time life was simple, There was only Lotus 2.0/2.01, Then came Lotus 2.2, but still was it a 2-D worksheet, Then came Lotus 3-D. To identify a cell in the 3-D releases, it is not enough to state the address, you need to state the sheet where the address is, for example C:A1..A1 is the A1..A1 address in the "C" sheet. Lotus 3.0 and up introduced the "coord" attribute so we can use a formula like @CELLPOINTER("coord") which returns the sheet and the address, such as A:B21..B21 meaning the address B21..B21 in the "A" sheet.

When we want the macro to work transparently in both types to Lotus 1-2-3, the macro has to check the type of worksheet. This topic shows how to do it.


Handling Manual and Automatic Recalculation Smartly

When we build a macro, we have to account for working in Automatic recalculation mode or Manual recalculation mode. The macro should update every formula that the macro directly affects. After every command that changes a cell content, the macro must force Lotus to update the cell or the formula. This topic shows three ways to update the cell.


Copy a Cell Using Adjacent Column Length


Monitoring and Controlling User's Input

A good macro design or a macro based application design should limit the user only to the keys that the programmer plans to allow him to use. Therefore the macro has to monitor and control every key that the user uses and respond respectively. It is possible to limit the user to use only the keys we want, or to control macro flow based on the user key presses. This topic explains how.


Cleaning After Macros

It is good practice to leave as few traces as possible after using a macro. Usually a macro comes with range names that must be defined before the macro can be used. When the macro is finished, the range names stay with the worksheet even if you erase the macro. As you use more macros, more range names are added to the worksheet making it bigger and slower. Therefore, we have tried to clean after every macro. The macros include the code to erase the temporary range names that the macro itself creates during execution. The range names that come with the macro can be deleted using the UNNAME.WK1 macro. When the macros are used from the macro manager, the macro manager does the cleaning job and even cleans itself.


Quitting Without the {QUIT} Macro Command

Lotus includes a macro command that can force a quit from a macro during macro operation. This command is the {QUIT} macro command. However this quitting technique is not always suitable. If  another macro (like a macro manager) activates this macro and this macro uses the {QUIT} macro command, Lotus will quit the macro and the macro processor will not branch back to the calling macro. Super Power uses a replacement technique. This topic shows how.


Advanced Use of the {LET} Macro Command

Often we can use the {LET} macro command to accomplish many tasks beyond the simple {LET location,entry} type of command where the entry can be a label or a formula, as shown in Lotus documentation. Where we use the {LET} macro command is equally as important as the way we use it.


Using the {INDICATE} Macro Command for Prompts


Changing a Date String into a Valid Date


Using the {FOR Loop} Macro Command

The {FOR} macro command deserves some remarks since it is in the heart of many super power macros.


Using Matrices in Lotus 1-2-3

Matrices are mathematical forms that belong to college math, which we are not going to cover; however one particular matrix is interesting because we can use it to simulate the @SUMPRODUCT function, available only in the 3-D releases of 1-2-3. This topic demonstrate the technique.


Using System Commands in the New Releases


Find Painted Range Address from Within a Macro

When you design a macro which prompts you to paint a range, you can take advantage of it and, at the same time, you can record the address of the four corners of the painted range and construct the range address of the same painted range. This topic shows how to do it.


Creating Multiple Copies of a Range


2-D and 3-D Lotus Releases

Super Power often uses the terms 3-D Lotus release or 2-D Lotus release. When we use 3-D Lotus release, we mean Lotus 3.0/3.1/3.1+/3.4 and 123W for Windows 1.0/1.0a/1.1 which have multiple sheets and three-dimension capabilities, while 2-D release refers to the 2.0/2.01/2.2/2.3 and the 2.4 Lotus releases which have one sheet only. Many of Super Power's macros automatically check what kind of spreadsheet you use and adapts themselves accordingly.


Using Functions Allowed in a 3-D Release Inside a 2-D Release

The 3-D releases of 1-2-3 include functions which are not available in the 2-D releases, therefore, if you build an application containing such an alive function, you cannot even save it as a *.WK1 file. Lotus will display a warning message and will notify you that the file cannot be saved with the *.WK1 extension without damage to the file. This topic shows how to use these function and the benefits.


Using the {CE} Macro Command in the 3-D Releases


Using Macro Commands of a 3-D Release in a 2-D Release

Why would we want to use commands that are allowed only in a 3-D release in a 2-D release? The answer is simple: we don't, but we want a macro to work in all the releases transparently; therefore, if we are using 3-D commands in the macro, we must make sure that we won't get an error when we are using the macro in a 2-D release. We have to write the macro in such a way that when you use a 3-D release the commands will be executed. However, they must be ignored if you use a 2-D release. This is also true when we use commands that are available only in new 2-D releases such as 1-2-3 2.2/2.3 and 2.4 that are not available in Lotus 2.0/2.01.

An example of such a command is the {BORDEROFF}. If we are going to use this command in a macro to hide the row and column headings while we use a new release of Lotus 1-2-3, we have to make sure that when you use Lotus 2.0/2.01, the macro will not stop with an error message. This topic shows how to do it.


Insert Numbers as Labels


Finding the Release of Lotus Being Used

When macro programmers write a macro for others to use, they have to remember that there are users still using old releases of Lotus 1-2-3. Often you own and keep previous releases for many reasons. Therefore, a good macro should be able to operate on all or many of the releases with few problems. This topic demonstrates how to determine the version you currently use. This allows the macro developer to create a foolproof macro code, which can work transparently across many versions of 1-2-3.


Advanced Use of the Response to the {GET Key} Command


Managing Macro Libraries

Grouping the Macros in One *.WK1 File

Grouping the Macros in One *.MLB File

Using Menu-Driven Macro Managers

Using the Macros Manually

Using the Macro Manager


Grouping the Macros in One *.WK1 File

Keep a group of macros in one spreadsheet file (*.WK1) and combine it to the current worksheet every time you need the macros. A spreadsheet of macros is called a library. This is a simple, often used method. However it is limited, old fashioned, and memory and time consuming. There are always some macros in the library that you do not use at your working session. Therefore they use valuable memory because they belong to the worksheet. Trying to erase these macros before you start to work demands precise preparation, and a solid knowledge of the exact actions you are going to do during the working session, which are not so simple to predict.


Grouping the Macros in One *.MLB File

This method is identical to the previous one and therefore suffers from the same limitations.


Using Menu-Driven Macro Managers

In this method, every macro is saved as an independent file. The macros are located in a sub directory. Using the menu driven manager you can:

•	See the list of all the macros in the directory

•	Activate the macros using point and shoot

•	Read the macro's operation instructions

The advantages are clear (see Appendix-A):

•	Only one macro resides in the memory at the same time

•	The manager loads the macro, defines the range names and activates the macro

without your interference. The result is memory space and preparation time saving, because there is no need to plan the work ahead.

•	The work is menu driven using a Lotus style menu bar.


Using the Macros Manually

You can combine the macros of Super Power to an empty part of the worksheet, use the /File Combine Copy Entire-file Lotus commands sequence. Then follow the built-in use instructions in the first nine rows of the macro, dedicated to macro description and use instructions.


Using the Macro Manager


Change All the Labels in a Range to Lowercase Form

Normally this macro should be in Part Two of Super Power in the Modifying Macros chapter. However, after we have covered all the advanced techniques in the previous pages, it is time to analyze at least one macro and show some of the basic techniques in action. Therefore we will take a deep look in the LOWRCASE.WK1 macro which changes all the labels in a selected range into a lowercase form. When you read the analysis, the notes refer you to the specific technique we saw earlier.

             A                  B                    C                    D                  E

  1 *---A macro to turn all the labels in a 3-D or a 2-D range to LOWERCASE

        form

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                    IT WILL WORK IN LOTUS 2.0 AND UP

  8 !

  9 !

 10 \Z             {BREAKON}

 11 LOWRCASE       {WINDOWSOFF}{PANELOFF}/RNCWhich range ?~~/RND

                   Which range ?~/RNC{WINDOWSON}{PANELON}Which range ?~

                   {BS}{BS}{?}~{WINDOWSOFF}{GOTO}Which range ?~

                   {LET counterb410,0}~

 12 cont410        {LET hereabs410,@CELLPOINTER("address")}~

                   {LET counter410,0}

 13 !              {FOR counter410,0,@COLS(Which range ?)-1,1,labels410}

 14 !              {LET rel410,@INFO("release")}~{IF @LEFT(rel410,1)<>"@"}

                   {GOTO}{hereabs410}~{LET counterb410,counterb410+1}~

                   {IF counterb410<@SHEETS(Which range ?)}{NS}{GOTO}

                   {hereabs410}~{BRANCH cont410}

 15 !              {GOTO}Which range ?~/RNDWhich range ?~

 16 !

 17 counter410            3

 18 countera410           5

 19 labels410      {RIGHT}{LET here410,@CELLPOINTER("address")}~{LEFT}

                   {FOR countera410,0,@ROWS(Which range ?)-1,1,labelsa410}~

                   {IF counter410<@COLS(Which range ?)-1}{GOTO}{here410}~

                   {LET countera410,0}~

 20 !

 21 here410        $D$1

 22 !

 23 labelsa410     {PANELON}{RECALC prop410}{IF @CELLPOINTER("type")<>"b"

                   #AND#@CELLPOINTER("type")<>"v"#AND#@CELLPOINTER("prefix")

                   <>"\"#AND#@CELLPOINTER("prefix")<>"|"}{PANELOFF}

                   /RVprop410~~{EDIT}~

 24 !              {DOWN}

 25 !

 26 prop410        \Z

 27 !

 28 counterb410           4

 29 hereabs410     $A$1

 30 !

 31 rel410

Notice that the B26 cell named [prop410] contains the following formula:

 26 prop410       @LOWER(@CELLPOINTER("contents"))

If you intend to key this macro into Lotus, you have to key the formula and not the code as it appears in the main listing.

The text in the A1 cell describes what the macro does. The text in A2 contains the instructions for how to assign the labels in the "A" column as range names to the cells to the right in the "B" column. The text in the A3 cell explains how to start the macro. The text in the A6..A7 range explains in which Lotus versions you can use the macro.

             A                  B                    C                    D                  E

 10 \Z             {BREAKON}

 11 LOWRCASE       {WINDOWSOFF}{PANELOFF}/RNCWhich range ?~~/RND

                   Which range ?~/RNC{WINDOWSON}{PANELON}Which range ?~

                   {BS}{BS}{?}~{WINDOWSOFF}{GOTO}Which range ?~

                   {LET counterb410,0}~

The macro starts in the B10 cell with the do nothing {BREAKON} macro command. The reason is connected to the way the macro manager works. It always looks for the [\Z] range name to assign the range name for the combined macro. Often the macro loops back to the beginning of the macro code. If the loop is explicitly to the [\Z] range name, you will not be able to change this name to [\A] for example. Therefore the loop is always to the macro name itself such as {BRANCH LOWRCASE} instead of {BRANCH \Z}. Sometimes you may see the {BREAKON} macro command twice and it concern the way the macro is used as an *.MLB macro in the SUPER MACRO LIBRARY.

The [LOWRCASE] name is the range name of the B11 cell, and it is also identical to the DOS name of the LOWRCASE.WK1 file. The code in B11 starts with {WINDOWSOFF} {PANELOFF} which freeze the screen and panel display activities as explained earlier.

The next piece of code is:

          /RNCWhich range ?~~/RNDWhich range ?~

              /RNC{WINDOWSON}{PANELON}Which range ?~{BS}{BS}{?}~

The macro now prompts you to paint the range to modify, while it assigns a range name to the range that you paint. The macro uses this range name later when it modifies the cells in the range. Therefore the macro starts a process which we call  the "safe technique" to assign a range name. This time the range name that the macro assigns is the [Which range ?] range name. First the macro creates this name then it deletes it and then creates it again. Why we use such an odd range name as [Which range ?]? Lotus allows us to use range names of up to 15 characters long, so we can use this property to assign range names with a meaning. Not only does the macro use the "safe technique" to assign a range name,  it combines it with a second technique which displays the [Which range ?] range name in the panel so it looks to you as a helping prompt.

Notice the exact location where the macro issues the {WINDOWSON}{PANELON} macro commands which resume panel and screen display activities just before the macro types the [Which range ?] range name. If the cell pointer is on the C10 cell for example, this results in the following display in the panel:

          Which range ?                          Enter range: C10..C10

Without this technique the panel would display:

          Enter name: Which range ?              Enter range: C10..C10

Because the macro issues the {WINDOWSON}{PANELON} macro commands after the /RNC macro keys, it suppresses the "Enter name:" text and displays the "Which range ?" text which looks like a helping prompt. The macro continues with the {BS} macro command which frees the cell pointer in the cell and the panel displays:

          Which range ?                          Enter range: C10

Note: Beginning with Lotus version 2.2 and up, the {INDICATE "string"} macro command can accept a long string and is no longer limited to only five characters; therefore an 80 character long string can be used as a prompt with the {INDICATE "Long string"}. However, because we want the macros to work with all the releases of Lotus 1-2-3, we used the previous technique instead.

Updating the LOWRCASE macro to include the {INDICATE} prompt will give:

         {WINDOWSOFF}{PANELOFF}/RNCWhich range ?~~/RNDWhich range ?~

             INDICATE "                 Paint the range to be

             processed                  "}/RNC{WINDOWSON}Which range ?

             {PANELON}~{BS}{BS}{?}~{INDICATE}{WINDOWSOFF}

Where the

              "              Paint the range to be processed                "

prompt string should be at least 80 characters long to suppress any Lotus prompt and display only the custom prompt string. The {INDICATE} command without the string clears the panel from the prompt. For now, while a large number of users still use 2.0/2.01, we will continue to use the previous prompt technique. Later, when most users will switch to the newer releases, we may prefer the {INDICATE} prompt.

Now the macro issues {?} which pauses the macro and allows you to move the cell pointer and to paint the range to modify. When you press ENTER, the macro resumes control and issues the tilde "~", the macro equivalent of the ENTER key, and assigns the [Which range ?] name to the painted range. Let's assume that you painted the C20..E22 range.

When the macro finished assigning the range name, the cell pointer moved back to the C10 cell. To move the cell pointer to the C20 cell, the macro continues with {GOTO}Which range ?~. Next it issues {LET counterb410,0}~, which sets the value in the B28 cell, [counterb410], to zero. The macro uses the B28 cell named [counterb410] as a counter. With that we conclude the definition of the [Which range ?] range. Now the macro "knows" it is going to process the [Which range ?] range.

For simplicity's sake, let's assume that you painted the C20..E22 range which looks like this:

                    C                        D                  E                  F

 20   DAVID           KING                          45

 21   ALLEN           DOYLE                         47

 22   ROBERT          DUVAL                         42

To calculate the number of rows in the [Which range ?] range, we can use the @ROWS (Which range ?) formula. To find the number of columns in the [Which range ?] range, we can use the @COLS(Which range ?) formulas, so the macro "knows" how to handle the whole range.

Now let's look on the second part of the LOWRCASE macro:

             A                  B                    C                    D                  E

 12 cont410        {LET hereabs410,@CELLPOINTER("address")}~

                   {LET counter410,0}

 13 !              {FOR counter410,0,@COLS(Which range ?)-1,1,labels410}

 14 !              {LET rel410,@INFO("release")}~{IF @LEFT(rel410,1)<>"@"}

                   {GOTO}{hereabs410}~{LET counterb410,counterb410+1}~

                   {IF counterb410<@SHEETS(Which range ?)}{NS}{GOTO}

                   {hereabs410}~{BRANCH cont410}

 15 !              {GOTO}Which range ?~/RNDWhich range ?~

The macro continues with {LET hereabs410,@CELLPOINTER("address")} which stores the current cell pointer location in the B29 cell, [hereabs410], and issues the tilde "~" to force Lotus to update the content of [hereabs410]. When we finished the definition of the [Which range ?] range name, the cell pointer was on the C20 cell (the upper left cell of [Which range ?]), so why do we "record" the cell address? When we want to return to the C20 cell, we can always use the {GOTO}Which range ?~ command. The answer is quite simple, we need the C20 address for 3-D releases where [Which range ?] may contain more than one sheet, as be explained later. Next the macro issues {LET counter410,0} which sets the value in the B17 cell, [counter410], to zero. The macro continues with:

          {FOR counter410,0,@COLS(Which range ?)-1,1,labels410}

which executes the [labels410] routine as many times as the number of columns in the [Which range ?] range. The [counter410] cell holds the count of the columns that the macro processed during it's execution. Before we continue with the code, let us look at the [labels410] routine:

             A                  B                    C                    D                  E

 19 labels410      {RIGHT}{LET here410,@CELLPOINTER("address")}~{LEFT}

                   {FOR countera410,0,@ROWS(Which range ?)-1,1,labelsa410}~

                   {IF counter410<@COLS(Which range ?)-1}{GOTO}{here410}~

                   {LET countera410,0}~

This routine starts with the {RIGHT} command which moves the cell pointer to the first cell of the next column in [Which range ?]. Then it issues {LET here410, @CELLPOINTER("address"), which stores the current cell pointer location in the B21 cell, [here410], and then {LEFT} to return. When the macro finishes processing the first column, it uses this address to return directly to the upper cell of the next column to save the movement steps all the way back to the top of the next column. Imagine how long it will take to move the cell pointer from the last cell in the current column to the first cell of the next column. If the range has 1000 rows, it will take a very long time to use the {UP @ROWS(Which range ?)}{RIGHT} commands to put the cell pointer on the first cell of the next column. Next the macro issues:

          {FOR countera410,0,@ROWS(Which range ?)-1,1,labelsa410}

which executes the [labelsa410] routine as many times as the number of rows in the [Which range ?] range. This {FOR} command processes the whole column. Before we continue let's look at the [labelsa410] routine.

             A                  B                    C                    D                  E

 23 labelsa410     {PANELON}{RECALC prop410}{IF @CELLPOINTER("type")<>"b"

                   #AND#@CELLPOINTER("type")<>"v"#AND#@CELLPOINTER("prefix")

                   <>"\"#AND#@CELLPOINTER("prefix")<>"|"}{PANELOFF}

                   /RVprop410~~{EDIT}~

 24 !              {DOWN}

 25 !

 26 prop410        \Z

Recall that the B26 cell, [prop410], contains this formula:

 26 prop410       @LOWER(@CELLPOINTER("contents"))

The [labelsa410] routine processes the whole column, cell after cell. First the routine starts with {PANELON} to allow you see some action in the panel while the routine transforms the labels into lowercase form. The routine issues {RECALC prop410}, a macro command which recalculates and updates the @LOWER(@CELLPOINTER("contents"))formula and returns the lowercase form of the current cell pointer content. For example, if the cell pointer is on the C20 cell in the sample worksheet, the formula will return the "david" string. Remember that the macro should work when the worksheet is also in manual recalculation mode. For a foolproof operation, the macro must make sure that it processes only labels. Therefore the macro issues the

         {IF @CELLPOINTER("type")<>"b"#AND#@CELLPOINTER("type")<>"v"#AND#

             @CELLPOINTER("prefix")<>"\"#AND#@CELLPOINTER("prefix")<>"|"}

compound {IF} commands, which check that the cell is not blank, that the cell does not contain a value, that the prefix is not the back slash "\" and that the prefix is not the bar "|". The back slash and the bar type prefixes have special role in Lotus 1-2-3; therefore we do not want the macro to mess up the worksheet. When all the conditions are met the macro issues /RVprop410~~, which copies the result of the formula in the B26 cell, prop410, as a value into the current cell (in our example, the "david" label).

The next {EDIT}~ commands seem to be unnecessary, however we have found that without these two commands, the macro does not work correctly in some versions of Lotus, when we use this macro from the macro manager. We added these commands to force Lotus to update the content of the current cell after the /RVprop410~~ command. Finally, the macro issues {DOWN} which moves the cell pointer to the next cell in the current column and returns the control to the {FOR} loop in the [labels410] routine.

             A                  B                    C                    D                  E

 19 labels410      {RIGHT}{LET here410,@CELLPOINTER("address")}~{LEFT}

                   {FOR countera410,0,@ROWS(Which range ?)-1,1,labelsa410}~

                   {IF counter410<@COLS(Which range ?)-1}{GOTO}{here410}~

                   {LET countera410,0}~

When the {FOR} command finishes processing all the cells in the column, the macro issues {IF counter410<@COLS(Which range ?)-1} which checks if the macro did not finish processing all the columns in the range. If so, the macro issues the {GOTO}{here410}~ indirect macro command sending the cell pointer directly to the first cell in the next column. Finally, the routine resets the value in the [countera410] counter to zero and returns the control to the {FOR} command in the [cont410] routine.

             A                  B                    C                    D                  E

 12 cont410        {LET hereabs410,@CELLPOINTER("address")}~

                   {LET counter410,0}

 13 !              {FOR counter410,0,@COLS(Which range ?)-1,1,labels410}

 14 !              {LET rel410,@INFO("release")}~{IF @LEFT(rel410,1)<>"@"}

                   {GOTO}{hereabs410}~{LET counterb410,counterb410+1}~

                   {IF counterb410<@SHEETS(Which range ?)}{NS}{GOTO}

                   {hereabs410}~{BRANCH cont410}

 15 !              {GOTO}Which range ?~/RNDWhich range ?~

When the {FOR} command finishes processing all the columns in [Which range ?], the macro issues {LET rel410,@INFO("release")}~ storing the result of the @INFO ("release") 3-D function in the B31 cell, [rel410]. Lotus uses the result of this function to find if you are using a 2-D or a 3-D Lotus release. The macro issues {IF @LEFT (rel410,1)<>"@"} to check whether the first character in [rel410] is not equal to the "@" character. If so, you are using a 3-D lotus release, therefore the [which range ?] range may contain more than one sheet to process.

The macro continues with the indirect {GOTO}{hereabs410}~ command, which moves the cell pointer to the address of the upper left cell of the current sheet of [Which range ?]. Then the macro issues {LET counterb410,counterb410+1}~ which increase the counter value in the B28 cell, [counterb410], which counts the number of sheets that the macro processes. Now it issues {IF counterb410<@SHEETS(Which range ?)} to check if all the sheets were processed. If the macro has to process more sheets, it issues the {NS} command moving the cell pointer to the next sheet to process, and then issues {GOTO}{hereabs410}~ which moves the cell pointer to the address of the upper left cell of the current sheet of [Which range ?].

To process the new sheet the macro issues {BRANCH cont410} routing the macro control to the start. When the macro finishes processing all the sheets, the macro issues {GOTO} Which range ?~, which moves the cell pointer to the upper left cell of the first sheet. Then issues /RNDWhich range ?~ to delete the [Which range ?] temporary range name to leave a clean worksheet.

Note: In this and many other Super Power's macros, we have used the two nested {FOR} commands to move the cell pointer along the column and from column to column to process all the cells in the range. The experienced user may say that we can use the @INDEX function to process the cells of the range without movement. It is true, but it has a drawback. The {PUT} command, usually associated with the @INDEX function, inserts fixed results into the cells but not formulas. In this macro, even though we used the /RV to enter fixed data into the cells, Super Power contains many macros that process the cells and leave the results as formulas. Only when we use the technique we have used here, can we edit the cell and envelop it with a formula. Therefore we have chosen to use one range handing routine for all our macros in Super Power. When we tested the two methods, there was no distinct speed advantage to the @INDEX methods, because moving one cell every time is very fast and the movement to the top of the next column is instantaneous using the indirect {GOTO} command.

We can create UPPERCASE and PROPER macros just by changing the @LOWER function to @UPPER or to @PROPER.

Till now we have seen that the macro consists of the following stages:

•	It prompts you to paint the range to be changed to lowercase format, and at the

same time the macro assigns the [Which range ?] name to the range which is also used as a supporting prompt.

•	The macro resets the counters to zero [counter410] for the number of columns,

[countera410] for the number of rows in [Which range ?], and [counterb410] for the number of sheets in case that [Which range ?] is a 3-D range (for release 3.0/3.1/3.1+/3.4 and 123W).

•	The macro starts the outer [labels410] loop routine on the number of columns

activating the inner [labelsa410] loop routine to process the rows. When an inner loop is finished, the column counter increased by 1 and the next inner loop starts. When the column counter [counter410] reaches the number of columns in the [Which range ?] range, the macro stops; but not before it deletes the [Which range ?] range name. When the [Which range ?] is a 3-D range, the macro does not stop, it steps to the next sheet in the [Which range ?] range. Now we can see the reason for recording the cell address in the [hereabs410] range.

SUMMARY

In this chapter we covered the following issues:

•	Naming a range safely using /RNC and /RND

•	Using a meaningful range name to display a prompt

•	Recalculating a formula to update the result even in manual recalculation mode

•	Testing for the release that you use

•	Using {FOR} loop macro command, and nesting {FOR} loop commands

•	Recording cell pointer address for later use

•	Using indirect macro command

•	Paging safely in a "straight path" between sheets in 3-D spreadsheet

•	Ending a routine using {RETURN} or blank cell

•	Using the {INDICATE} macro command to display long prompts

•	We used {PANELON}, {PANELOFF}, {WINDOWSON}, {WINDOWSOFF},

{NS}, {BS}, {FOR}, {EDIT}, {RECALC}, {GOTO}, {INDICATE}, {RIGHT}, {LEFT}, {IF}, {DOWN}, {RETURN}, {LET} and {PUT} macro commands

•	We used the @COLS, @ROWS, @CELLPOINTER, @SHEETS, @UPPER,

@LOWER, @PROPER, @LEFT @INFO("release") and the @INDEX functions and the #AND# logical function

•	We used "address", "type", "prefix", "contents" attributes in the @CELLPOINTER

 ("attribute") function

Not bad for one macro. For more information on these functions and macro commands, consult your Lotus manual. Super Power contains hundreds of other macros employing and using these techniques and many others. We believe reading the analysis of these macros is the best way to learn the Lotus macro language.


Macros Affecting Rows, Columns and Ranges

[5]  Transpose  Range

[7]  Absolute Transpose

[9]  Transpose a 2-D Range with Formulas Correctly

[9]  Transpose a 3-D Range with Formulas Correctly

[9]  Erase the Worksheet Except for a Specified Range

[6]  Eliminate Completely Empty Rows

[6]  Eliminate Completely Empty Columns

[7]  Erase to the End of Worksheet

[9]  Delete Partial Columns and Rows (Ranges)

[6]  Delete Rows with Zeros or Blank Cells in a Range

[3]  Delete Even Protected Rows

[3]  Delete a Selected Number of Columns

[3]  Delete a Selected Number of Rows

[9]  Insert Partial Columns and Rows (Ranges)   <=== Click here!

[3]  Insert Selected Number of Rows

[3]  Insert Selected Number of Columns

[3]  Insert Row's Duplicate

[3]  Insert a Row with Above Row's Format

[3]  Insert a Column with Same Format

[7]  Invert a Range of Data

[7]  Invert a Column of Data

[6]  Squeeze a Column of Data

[6]  Squeeze a Row of Data

[7]  Switch Range Places

[6]  Center a Column of Titles Across the Screen

[3]  Center a Title Across a Row

[3]  Continuously Hide Columns

[3]  View Hidden Columns Easily

[1]  Set the Width of a Group of Columns

[1]  Reset the Width of a Group of Columns

[3]  Adjust the Column Width to the Current Label Length

[5]  Adjust the Column Width to the Current Label/Number Length

[5]  Change Multiple Column Widths


[5]  Transpose a Range

                 A                   B                     C                      D                     E

  1 *---A macro to transpose a 3-D or 2-D column wised range to row wised

  2     range or wise versa. This macro is for a range contains labels or

  3     values but NOT formulas. To transpose ranges containing formulas

  4     see the TRANSPO2.WK1 and TRANSPO3.WK3 macros.

  5 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  6     range names in this column (starts with the \Z macro name)

  7 *---Highlight the cell to be calculated

  8 *---Hold the [ALT] key and press [Z] to activate the macro


[7]  Absolute Transpose

               A                  B                C                  D                  E

  1 *---A macro to TRANSPOSE a 3-D or 2-D range that includes formulas,

  2     numbers and labels without changing the address references.

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define

  4     the range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 !

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                     IT WILL WORK IN LOTUS 2.0 AND UP

Please refer to both the  TRANSPO2.WK1 and the TRANSPO3.WK3. This macro is for the cases where the range to be transposed contains data and formulas that refer to cells and ranges OUTSIDE of the range to be transposed. The macro turns all the cells into labels and then transposes the range and un-labels all the cells. The result is that all the formulas are unchanged and refer to the same cells and ranges as prior to the transpose action. When you transpose a range in the worksheet and the range contains formulas, the new formulas in the transposed range are changed depends how much the rows and the columns are shifted.

This is one of the beautiful and essence properties of an electronic spreadsheet. What happens if we do not want the formulas to be updated? We can use absolute addresses, but what if we want to transpose a whole range and keep the exact formulas with reference to data outside the range? The solution is to turn all the formulas to labels, and than transpose the range. But turning a 100X100 range to labels is real punishment. The TRANSPOS.WK1 macro does the job automatically. First, it turns all the values in the range into labels, then it transposes the range to the target location and last the macro UN-LABELs the two ranges.


[9]  Transpose a 2-D Range with Formulas Correctly

                 A                   B                   C                 D               E

  1 *---A macro to transpose a range in a way that preserves formulas too

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define

  3     the range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer on the upper leftmost cell of the range

  5     to be transposed, make sure there is at least one empty row

  6     above the range

  7 *---Hold the [MACRO] key and press [Z] to activate the macro

  8 *---The transposed range will begin at the same cell

As odd as it seems no spreadsheet on the market today offers the option for transposing a range also containing formulas, in such a way, that the formulas will be updated correctly. In the old releases of Lotus 1-2-3, the result of the transposition on a range containing formulas was unpredictable, and in the new releases 2.2 and up, Lotus transforms all the formulas into values. This macro is a breakthrough allowing you to transpose a range without fear of what will happen to the formulas. This macro transforms any range, and fast, and keeps all the updates of the formula references correctly.


[9]  Transpose a 3-D Range with Formulas Correctly

                 A                   B                   C                 D               E

  1 *---A macro to transpose a 2-D and 3-D range in a way that preserves

  2     formulas too, make sure there is at least one empty row above the

  3     range to be transposed

  4 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define

  5     the range names in this column (starts with the \Z macro name)

  6 *---Place the cell pointer on the upper leftmost cell

  7 *---Hold the [MACRO] key and press [Z] to activate the macro

  8         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  9                   IT WILL WORK IN RELEASE 2.0 AND UP


[9]  Erase the Worksheet Except for a Specified Range

                 A                   B                   C                 D               E

  1 *---A macro to ERASE all the worksheet EXCEPT a specified range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define

  3     the range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6 !

  7             In Release 3 use it for single sheet at a time


[6]  Eliminate Completely Empty Rows

                 A                   B                   C                 D               E

  1 *---A macro to ELIMINATE completely empty rows in a range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define

  3     the range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---In release 3 and up make sure that the GROUP option is DISABLED

  6 *---In release 2/2.01/2.2/2.3 the macro must be located ABOVE the

        range

  7 !

  8         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  9                     IT WILL WORK IN LOTUS 2.0 AND UP


[6]  Eliminate Completely Empty Columns

                 A                   B                   C                 D               E

  1 *---A macro to ELIMINATE completely empty columns in a range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define

  3     the range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---In release 3 make sure that the GROUP option is DISABLED

  6 *---In release 2/2.01/2.2 the macro must be located ABOVE the range

  7 !

  8         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  9                   IT WILL WORK IN RELEASE 2.0 AND UP


[7]  Erase to the End of Worksheet

               A             B          C        D       E         F       G         H      I

  1 *---A macro to ERASE from the cell next to the cell pointer to ends of

  2     row, columns, sheets

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 !

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                   IT WILL WORK IN RELEASE 2.0 AND UP


[9]  Delete Partial Columns and Rows (Ranges)

             A                       B                    C                    D             E

  1 *---A macro to DELETE a range, like column/row delete but limited

  2     to a specified row/column length

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 !

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                 IT WILL WORK IN RELEASE 2.0 AND UP


[6]  Delete Rows with Zeros or Blank Cells in a Range

                 A                  B                    C                  D                E

  1 *---A macro to DELETE rows containing 0 or empty cell in a range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define

  3     the range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---In release 3 make sure that the GROUP option is DISABLED

  6 *---In release 2/2.01/2.2 the macro must be located ABOVE the range

  7 !

  8         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  9                     IT WILL WORK IN LOTUS 2.0 AND UP


[3]  Delete Even Protected Rows

                A                    B                      C                     D

  1 *---A macro to DELETE rows even if the spreadsheet is protected

  2     the global protection is disabled to allow the MACRO MANAGER

  3     to work properly.

  4 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define

  5     the range names in this column (starts with the \Z macro name)

  6 *---Place the cell pointer at the row to be deleted

  7 *---Hold the [ALT] key and press [Z] to activate the macro


[3]  Delete a Selected Number of Columns

                A                   B                   C                  D                 E

  1 *---A macro to DELETE a specified number of COLUMNS

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define

  3     the range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[3]  Delete a Selected Number of Rows

                A                  B                  C                   D                  E

  1 *---A macro to DELETE a specified number of ROWS

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define

  3     the range names in this column (starts with the \Z macro name)

  4 *---Hold the [MACRO] key and press [Z] to activate the macro


[9]  Insert Partial Columns and Rows (Ranges)

                A                    B                    C                    D             E

  1 *---A macro to INSERT a range, it will push aside all the cells and

  2     will make place in the size of the chosen range

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 !

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                   IT WILL WORK IN RELEASE 2.0 AND UP

  9 !

 10 \Z              {BREAKON}

 11 RANGEINS        {WINDOWSOFF}{PANELOFF}/RNCInsert range ?~~/RND

                    Insert range ?~/RNC{WINDOWSON}{PANELON}Insert range ?~

                    {BS}{BS}{?}~{WINDOWSOFF}{GOTO}Insert range ?~

 12 !               {LET hereabs528,@CELLPOINTER("address")}~

 13 cont528         {LET col1528,@CELLPOINTER("col")}~

 14 !               {LET col2528,@CELLPOINTER("col")+@COLS(Insert range ?)

                    -1}~{LET col4528,@COLS(Insert range ?)}~

 15 !               {LET row1528,@CELLPOINTER("row")}~

 16 !               {LET row2528,@CELLPOINTER("row")+@ROWS(Insert range ?)

                    -1}~{LET row4528,@ROWS(Insert range ?)}~

 17 !               {END}{HOME}~{LET row3528,@CELLPOINTER("row")}~

                    {LET col3528,@CELLPOINTER("col")}~{GOTO}Insert range ?~

 18 !               {MENUCALL menu1528}

 19 !               {GOTO}{hereabs528}~/RNDInsert range ?~

 20 !

 21 !

 22 hereabs528      $H$63

 23 !

 24 counterb528                11

 25 !

 26 menu1528        Columnwise    Rowwise          Quit

 27 !               Push all the cPush all the cellQuit the macro

 28 !               {LET counterb5{LET counterb528,{BRANCH ret528}

 29 !

 30 !

 31 ret528

 32 !

 33 rel528          3.00.00

 34 col1528                     3

 35 col2528                     6

 36 col3528                   137

 37 col4528                     4

 38 row1528                     2

 39 row2528                    10

 40 row3528                   100

 41 row4528                     9

 42 chrz1528        C

 43 chrz2528        F

 44 chrz3528        G

 45 rightaa528      {RECALC chrz1528}{RECALC chrz2528}{RECALC chrz3528}

                    {RECALC rng1528}{RECALC rng1a528}/M

 46 rng1528         C2..EG10

 47 !               ~

 48 rng1a528        {RIGHT 4}~

 49 !               {LET rel528,@INFO("release")}~{IF @LEFT(rel528,1)<>"@"}

                    {GOTO}{hereabs528}~{LET counterb528,counterb528+1}~

                    {IF counterb528<@SHEETS(Insert range ?)}{NS}{GOTO}

                    {hereabs528}~{BRANCH rightaa528}

 50 !               {WINDOWSON}

 51 !

 52 downaa528       {RECALC chrz1528}{RECALC chrz2528}{RECALC chrz3528}

                    {RECALC rng2528}{RECALC rng2a528}/M

 53 rng2528         C2..F100

 54 !               ~

 55 rng2a528        {DOWN 9}~

 56 !               {LET rel528,@INFO("release")}~{IF @LEFT(rel528,1)<>"@"}

                    {GOTO}{hereabs528}~{LET counterb528,counterb528+1}~

                    {IF counterb528<@SHEETS(Insert range ?)}{NS}{GOTO}

                    {hereabs528}~{BRANCH downaa528}

 57 !               {WINDOWSON}

 58 !

 59 outaa528        {}

This macro fills a gap. In Lotus you can insert only a complete column, row or range, but you cannot insert a partial row, column or range. For example, if you are using a complicated worksheet with tables positioned one over the other, and you want to insert a column in a table, you have a problem because you may also insert a column in other tables. This macro allows you to insert partial rows and columns. To better understand this macro, let's look at the following table:

          A               B             C             D               E            F       G

  1         21         41         61         81

  2         22         42         62         82

  3         23         43         63         83

  4         24         44         64         84

  6         26         46         65         85

  7         27         47         67         87

  8         28         48         68         88

  9         29         49         69         89

 10         20         40         60         80

Let's assume that you started the macro and highlighted the B4..C7 range (three rows and two columns) to insert. You can insert in the rowwise direction and the result will be:

          A               B             C             D               E            F       G

  1         21         41         61         81

  2         22         42         62         82

  3         23         43         63         83

  4         24                               84

  6         26                               85

  7         27                               87

  8         28         44         64         88

  9         29         46         65         89

 10         30         47         67         80

 11                    48         68

 12                    49         69

 13                    40         60

The macro pushed the rest of the two columns beneath the B4..C7 range down to insert an empty range with the size of the B4..C7 range. If you choose to insert in the columnwise direction, the result is:

          A               B             C             D               E            F       G

  1         21         41         61         81

  2         22         42         62         82

  3         23         43         63         83

  4         24                               44        64        84

  6         26                               46        65        85

  7         27                               47        67        87

  8         28         48         68         88

  9         29         49         69         89

 10         20         40         60         80

We can see that the macro pushed all the rows right of the B4..C7 range to the right and inserted an empty range with the size of the B4..C7 range. See the RANGDEL1.WK1 macro, which does the opposite. It allows you to delete partial rows and columns. The macro uses a custom menu with three menu options which cannot fit clearly on one page without overlapping. Here is the code of every menu option separately. If you intend to key this macro into Lotus, you should use the code as it appears here, NOT the code as it appears in the main listing.

          Columnwise

          Push all the cells to the right to make space for the new range

          {LET counterb528,0}~{rightaa528}

          Rowwise

          Push all the cells downward to make space for the new range

          {LET counterb528,0}~{downaa528}

          Quit

          Quit the macro

          {BRANCH ret528}

Notice, that the code in the B42, B43, B44, B46, B48, B53 and B55 cells of the macro is the result of the following dynamic string formulas. If you intend to key in the code, you should use the formulas, NOT the code as it appears in the main listing.

 42 chrz1528        @IF(@MOD(B34,26)=0,"Z",@CHAR(@MOD(B34,26)+64))

 43 chrz2528        @IF(@MOD(B35,26)=0,"Z",@CHAR(@MOD(B35,26)+64))

 44 chrz3528        @IF(@MOD(B36,26)=0,"Z",@CHAR(@MOD(B36,26)+64))

 46 rng1528         @IF(B34<27,@CHAR(B34+64),@CHAR(@INT(B34/26)+64)&B42)&

                    @STRING(B38,0)&".."&@IF(B36<27,@CHAR(B36+64),@CHAR

                    (@INT(B36/26)+64)&B44)&@STRING(B39,0)

 48 rng1a528        +"{RIGHT "&@STRING(B37,0)&"}~"

 53 rng2528         @IF(B34<27,@CHAR(B34+64),@CHAR(@INT(B34/26)+64)&B42)&

                    @STRING(B38,0)&".."&@IF(B35<27,@CHAR(B35+64),@CHAR

                    (@INT(B35/26)+64)&B43)&@STRING(B40,0)

 55 rng2a528        +"{DOWN "&@STRING(B41,0)&"}~"

                A                    B                    C                    D             E

 11 RANGEINS        {WINDOWSOFF}{PANELOFF}/RNCInsert range ?~~/RND

                    Insert range ?~/RNC{WINDOWSON}{PANELON}Insert range ?~

                    {BS}{BS}{?}~{WINDOWSOFF}{GOTO}Insert range ?~

The macro issues the {WINDOWSOFF}{PANELOFF} commands to freeze the screen and panel display activity. Then the macro uses the "safe technique" to prompt you to paint the range to delete and simultaneously assigns the [Insert range ?] range name to the same range and uses the range name as a prompt.

                A                    B                    C                    D             E

 12 !               {LET hereabs528,@CELLPOINTER("address")}~

 13 cont528         {LET col1528,@CELLPOINTER("col")}~

 14 !               {LET col2528,@CELLPOINTER("col")+@COLS(Insert range ?)

                    -1}~{LET col4528,@COLS(Insert range ?)}~

 15 !               {LET row1528,@CELLPOINTER("row")}~

 16 !               {LET row2528,@CELLPOINTER("row")+@ROWS(Insert range ?)

                    -1}~{LET row4528,@ROWS(Insert range ?)}~

 17 !               {END}{HOME}~{LET row3528,@CELLPOINTER("row")}~

                    {LET col3528,@CELLPOINTER("col")}~{GOTO}Insert range ?~

 18 !               {MENUCALL menu1528}

 19 !               {GOTO}{hereabs528}~/RNDInsert range ?~

Now the macro stores the current address in cell [hereabs528]. Later, the macro will use it to return to this address. The macro continues with a series of {LET} macro commands which store information the macro will use later. The {LET col1528,@CELLPOINTER("col")}~ macro command stores the column number of the current column in cell [col1528] which is the first column of the [Insert range ?] range.

The {LET col2528,@CELLPOINTER("col")+@COLS(Insert range ?)-1}~ commands store the column number of the last column of the [Insert range ?] range in cell [col2528]. The {LET col4528,@COLS(Insert range ?)}~ store the number of columns of the [Insert range ?] range in cell [col4528]. The {LET row1528,@CELLPOINTER ("row")}~ store the row number of the current row (the first row of the [Insert range ?] range) in cell [row1528]. The {LET row2528,@CELLPOINTER("row")+@ROWS(Insert range ?)-1}~ commands store the number of the last row of the [Insert range ?] range in [row2528], and {LET row4528,@ROWS(Insert range ?)}~ store the number of rows of the [Insert range ?] range in cell [row4528].

Next, the macro issues the {END}{HOME}~ commands, which move the cell pointer to the last occupied cell in the worksheet, and issues {LET row3528,@CELLPOINTER("row")}~, which store the row number of the last cell of the worksheet in the cell [row3528] and continues with {LET col3528,@CELLPOINTER("col")}~, which store the column number of the last cell of the worksheet in cell [col3528]. Now that all the properties of the range to delete and the size of the worksheet are known, the macro issues {GOTO}Insert range ?~, which moves the cell pointer back to the upper left cell of the [Insert range ?] range, and issues {MENUCALL menu1528} activating the [menu1528] custom menu.

The first menu option is [Rowwise] :

          Rowwise

          Push all the cells downward to make space for the new range

          {LET counterb528,0}~{downaa528}

The routine starts with {LET counterb528,0}, which sets the value in the cell [counterb528] to zero. Next, the macro issues the {downaa528} routine command which starts the [downaa528] routine.

                A                    B                    C                    D             E

 45 rightaa528      {RECALC chrz1528}{RECALC chrz2528}{RECALC chrz3528}

                    {RECALC rng1528}{RECALC rng1a528}/M

 46 rng1528         C2..EG10

 47 !               ~

 48 rng1a528        {RIGHT 4}~

 49 !               {LET rel528,@INFO("release")}~{IF @LEFT(rel528,1)<>"@"}

                    {GOTO}{hereabs528}~{LET counterb528,counterb528+1}~

                    {IF counterb528<@SHEETS(Insert range ?)}{NS}{GOTO}

                    {hereabs528}~{BRANCH rightaa528}

 50 !               {WINDOWSON}

The routine issues the five {RECALL} macro commands to update the formulas in the [chrz1528], [chrz2528], [chrz3528], [rng1528], [rng1a528] cells. Let's look at the formulas:

The first formula is in cell [chrz1528]:

 42 chrz1528        @IF(@MOD(B34,26)=0,"Z",@CHAR(@MOD(B34,26)+64))

This formula calculates the letter of the first column of the [Insert range ?] range. If the column is designated by two letters, this formula calculates only the second letter.

The second formula is in cell [chrz2528]:

 43 chrz2528        @IF(@MOD(B35,26)=0,"Z",@CHAR(@MOD(B35,26)+64))

This formula calculates the letter of the last column of the [Insert range ?] range. If the column is designated by two letters, this formula calculates only the second letter.

The third formula is in cell [chrz3528]:

 44 chrz3528        @IF(@MOD(B36,26)=0,"Z",@CHAR(@MOD(B36,26)+64))

This formula calculates the letter of the last occupied column of the worksheet. If the column is designated by two letters, this formula calculates only the second letter.

The fourth formula is in cell [rng1528]:

 46 rng1528         @IF(B34<27,@CHAR(B34+64),@CHAR(@INT(B34/26)+64)&B42)&

                    @STRING(B38,0)&".."&@IF(B36<27,@CHAR(B36+64),@CHAR

                    (@INT(B36/26)+64)&B44)&@STRING(B39,0)

This formula calculates the range to move to the right to insert an empty range with the same size as that of the [Insert range ?] range. For example, if the range named [Insert range ?] is the C2..F10 range and the last occupied column of the worksheet is the EG column, the result will be the C2..EG10 range, which contains all the data in the worksheet to the right including the [Insert range ?] range.

The fifth formula is in cell [rng1a528]:

 48 rng1a528        +"{RIGHT "&@STRING(B37,0)&"}~"

This formula calculates how many columns to move the C2..EG10 range to the right.

Next, the macro issues /MC2..EG10~{RIGHT 4}~ to move the C2..EG10 range four columns to the right.

                A                    B                    C                    D             E

 49 !               {LET rel528,@INFO("release")}~{IF @LEFT(rel528,1)<>"@"}

                    {GOTO}{hereabs528}~{LET counterb528,counterb528+1}~

                    {IF counterb528<@SHEETS(Insert range ?)}{NS}{GOTO}

                    {hereabs528}~{BRANCH rightaa528}

 50 !               {WINDOWSON}

Because a 3-D Lotus worksheet may have more than one sheet, the macro issues the {LET rel528,@INFO("release")}~ code, which stores the result of the @INFO("release") function in the cell [rel528]. Then the macro issues {IF @LEFT(rel528,1)<>"@"} to check if you are using a 2-D or a 3-D Lotus release. If the first character of the content of the cell [rel527] is the "@" character, then you are using a 2-D Lotus release, otherwise you are using a 3-D release. If you are using a 3-D release, the macro issues {GOTO}{hereabs528}~ indirect macro command to move to the first cell in the current sheet range, and then issues {LET counterb528,counterb528+1}~ to increase the counter in cell [counterb528] by one.

Next, the macro issues {IF counterb528<@SHEETS(Insert range ?)} to compare the counter value in cell [counterb528] to the number of sheets in the [Insert range ?] range. If the counter value is less than the number of sheets in [Insert range ?], the macro has to process more sheets before it can quit. Therefore the macro issues {NS}{GOTO}{hereabs528}~ to move the cell pointer to the upper left cell of the [Insert range ?] range in the new sheet, and issues {BRANCH rightaa528} to loop back to the beginning of the [rightaa528] routine. The routine is finished only when the value in [counterb528] is greater than the number of sheets in the [Insert range ?] range.

The second menu option is [Columnwise]:

          Columnwise

          Push all the cells to the right to make space for the new range

          {LET counterb528,0}~{rightaa528}

The routine starts with {LET counterb528,0}, which sets the value in cell [counterb528] to zero. Next the macro issues {rightaa528} which starts the [rightaa528] routine.

                A                    B                    C                    D             E

 52 downaa528       {RECALC chrz1528}{RECALC chrz2528}{RECALC chrz3528}

                    {RECALC rng2528}{RECALC rng2a528}/M

 53 rng2528         C2..F100

 54 !               ~

 55 rng2a528        {DOWN 9}~

 56 !               {LET rel528,@INFO("release")}~{IF @LEFT(rel528,1)<>"@"}

                    {GOTO}{hereabs528}~{LET counterb528,counterb528+1}~

                    {IF counterb528<@SHEETS(Insert range ?)}{NS}{GOTO}

                    {hereabs528}~{BRANCH downaa528}

The routine issues the five {RECALC} macro commands to update the formulas in the [chrz1528], [chrz2528], [chrz3528], [rng2528], [rng2a528] cells. The first three are the same formulas as we have seen in the previous menu option. Therefore, let us look at the last two formulas:

The first formula is in cell [rng2528]:

 53 rng2528         @IF(B34<27,@CHAR(B34+64),@CHAR(@INT(B34/26)+64)&B42)&

                    @STRING(B38,0)&".."&@IF(B35<27,@CHAR(B35+64),@CHAR

                    (@INT(B35/26)+64)&B43)&@STRING(B40,0)

This formula calculates the range to move down to insert an empty range with the same size as that of the [Insert range ?] range. For example, if the range named [Insert range ?] is the C2..F10 range and the last occupied row of the worksheet is the 100 row, the result of this formula will be the C2..EG100 which contains all the data in the worksheet below including the [Insert range ?] range.

The fifth formula is in cell [rng2a528]:

 55 rng2a528        +"{DOWN "&@STRING(B41,0)&"}~"

This formula calculates how many rows down to move the C2..F100 range. Next the macro issues /MC2..F100~{DOWN 9}~ to move the C2..EG10 range four columns to the right.

                A                    B                    C                    D             E

 56 !               {LET rel528,@INFO("release")}~{IF @LEFT(rel528,1)<>"@"}

                    {GOTO}{hereabs528}~{LET counterb528,counterb528+1}~

                    {IF counterb528<@SHEETS(Insert range ?)}{NS}{GOTO}

                    {hereabs528}~{BRANCH downaa528}

 57 !               {WINDOWSON}

The rest of the code is identical to the code of the [rightaa528] except that the macro loops back to the [downaa528] routine instead. The third menu option is [Quit]:

          Quit

          Quit the macro

          {BRANCH ret528}

When you choose this option, the macro issues {BRANCH ret527}, which routes macro control to the empty routine [ret527] and quits. Now the macro returns control to the line after the {MENUCALL menu1528} in the main code, which issues {GOTO}{hereabs528}~/RND Insert range ?~, to move the cell pointer back to the origin cell and then deletes the temporary [Insert range ?] range name to leave a clean worksheet.

Here is the list of all the cell contents to help you to key this macro into 1-2-3.

A1: U [W14] '*---A macro to INSERT a range, it will push aside all the

            cells and

A2: U [W14] '    will make place in the size of the chosen range

A3: [W14] '*---Use the /Range Name Label Right [End] [Down] [ENTER] to

          define the

A4: [W14] '    range names in this column (starts with the \Z macro name)

A5: [W14] '*---Hold the [ALT] key and press [Z] to activate the macro

A6: [W14] '!

A7: U [W14] '        THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3

            RELEASE

A8: U [W14] '                  IT WILL WORK IN RELEASE 2.0 AND UP

A9: [W14] '!

A10: U [W14] '\Z

B10: '{BREAKON}

A11: U [W14] 'RANGEINS

B11: '{WINDOWSOFF}{PANELOFF}/RNCInsert range ?~~/RNDInsert range ?~

     /RNC{WINDOWSON}{PANELON}Insert range ?~{BS}{BS}{?}~{WINDOWSOFF}

     {GOTO}Insert range ?~

A12: [W14] '!

B12: '{LET hereabs528,@CELLPOINTER("address")}~

A13: [W14] 'cont528

B13: '{LET col1528,@CELLPOINTER("col")}~

A14: [W14] '!

B14: '{LET col2528,@CELLPOINTER("col")+@COLS(Insert range ?)-1}~

     {LET col4528,@COLS(Insert range ?)}~

A15: [W14] '!

B15: '{LET row1528,@CELLPOINTER("row")}~

A16: [W14] '!

B16: '{LET row2528,@CELLPOINTER("row")+@ROWS(Insert range ?)-1}~

     {LET row4528,@ROWS(Insert range ?)}~

A17: [W14] '!

B17: '{END}{HOME}~{LET row3528,@CELLPOINTER("row")}~{LET col3528,@CELLPOINTER("col")}~

     {GOTO}Insert range ?~

A18: [W14] '!

B18: '{menucall menu1528}

A19: [W14] '!

B19: '{GOTO}{hereabs528}~/RNDInsert range ?~

A20: [W14] '!

A21: [W14] '!

A22: [W14] 'hereabs528

B22: '$H$63

A23: [W14] '!

A24: [W14] 'counterb528

B24: 11

A25: [W14] '!

A26: [W14] 'menu1528

B26: 'Columnwise

C26: 'Rowwise

D26: 'Quit

A27: [W14] '!

B27: 'Push all the cells to the right to make space for the new range

C27: 'Push all the cells downward to make space for the new range

D27: 'Quit the macro

A28: [W14] '!

B28: '{LET counterb528,0}~{rightaa528}

C28: '{LET counterb528,0}~{downaa528}

D28: '{BRANCH ret528}

A29: [W14] '!

A30: [W14] '!

A31: [W14] 'ret528

A32: [W14] '!

A33: [W14] 'rel528

B33: '3.00.00

A34: [W14] 'col1528

B34: 8

A35: [W14] 'col2528

B35: 12

A36: [W14] 'col3528

B36: 13

A37: [W14] 'col4528

B37: 5

A38: [W14] 'row1528

B38: 63

A39: [W14] 'row2528

B39: 70

A40: [W14] 'row3528

B40: 80

A41: [W14] 'row4528

B41: 8

A42: [W14] 'chrz1528

B42: U @IF(@MOD(B34,26)=0,"Z",@CHAR(@MOD(B34,26)+64))

A43: [W14] 'chrz2528

B43: U @IF(@MOD(B35,26)=0,"Z",@CHAR(@MOD(B35,26)+64))

A44: [W14] 'chrz3528

B44: U @IF(@MOD(B36,26)=0,"Z",@CHAR(@MOD(B36,26)+64))

A45: [W14] 'rightaa528

B45: '{RECALC chrz1528}{RECALC chrz2528}{RECALC chrz3528}{RECALC rng1528}

     {RECALC rng1a528}/M

A46: [W14] 'rng1528

B46: U @IF(B34<27,@CHAR(B34+64),@CHAR(@INT(B34/26)+64)&B42)&@STRING(B38,0)

       &".."&@IF(B36<27,@CHAR(B36+64),@CHAR(@INT(B36/26)+64)&B44)&@STRING

       (B39,0)

A47: [W14] '!

B47: '~

A48: [W14] 'rng1a528

B48: U +"{RIGHT "&@STRING(B37,0)&"}~"

A49: [W14] '!

B49: '{LET rel528,@INFO("release")}~{IF @LEFT(rel528,1)<>"@"}{GOTO}

     {hereabs528}~{LET counterb528,counterb528+1}~{IF counterb528<@SHEETS

     (Insert range ?)}{NS}{GOTO}{hereabs528}~{BRANCH rightaa528}

A50: [W14] '!

B50: '{WINDOWSON}

A51: [W14] '!

A52: [W14] 'downaa528

B52: '{RECALC chrz1528}{RECALC chrz2528}{RECALC chrz3528}{RECALC rng2528}

     {RECALC rng2a528}/M

A53: [W14] 'rng2528

B53: U @IF(B34<27,@CHAR(B34+64),@CHAR(@INT(B34/26)+64)&B42)&@STRING(B38,0)

       &".."&@IF(B35<27,@CHAR(B35+64),@CHAR(@INT(B35/26)+64)&B43)&@STRING

       (B40,0)

A54: [W14] '!

B54: '~

A55: [W14] 'rng2a528

B55: U +"{DOWN "&@STRING(B41,0)&"}~"

A56: [W14] '!

B56: '{LET rel528,@INFO("release")}~{IF @LEFT(rel528,1)<>"@"}{GOTO}

     {hereabs528}~{LET counterb528,counterb528+1}~{IF counterb528<@SHEETS

     (Insert range ?)}{NS}{GOTO}{hereabs528}~{BRANCH downaa528}

A57: [W14] '!

B57: '{WINDOWSON}

A58: [W14] '!

A59: [W14] 'outaa528

B59: '{}


[3]  Insert Selected Number of Rows

               A                 B                  C                   D                  E

  1 *---A macro to insert a specified number of rows

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define

  3     the range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[3]  Insert Selected Number of Columns

                A                   B                  C                  D                  E

  1 *---A macro to INSERT a specified number of COLUMNS

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define

  3     therange names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[3]  Insert Row's Duplicate

                 A                 B                   C                  D                E

  1 *---A macro to INSERT a duplicate of the row above

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define

  3     the range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer under the row to be duplicate

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[3]  Insert a Row with Above Row's Format

                 A                 B                   C                   D                 E

  1 *---A macro to INSERT a row with the above row's format

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define

  3     the range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer under the row to be copied (format only)

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[3]  Insert a Column with Same Format

                 A                  B                  C                  D                 E

  1 *---A macro to INSERT a column to the left with the current column

  2     format

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define

  4     the range names in this column (starts with the \Z macro name)

  5 *---Place the cell pointer on the column to be copied (format only)

  6 *---Hold the [ALT] key and press [Z] to activate the macro


[7]  Invert a Range of Data

                A                  B                    C                  D                 E

  1 *---A macro to invert a range of numbers or text

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define

  3     the range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP

This macro allows you to invert a range of data so that the first row of data becomes the last row of data and the second becomes the row before the last row, etc. This macro uses five dynamic string formulas to create the correct code for the macro.


[7]  Invert a Column of Data

                A                  B                    C                  D                 E

  1 *---A macro to invert a column of numbers or text

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP

See Invert a Range of Data in the previous section.


[6]  Squeeze a Column of Data

                A                  B                    C                  D                 E

  1 *---A macro to SQUEEZE a list of column entries (take out blank cells)

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer at the upper most cell of the list

  5 *---Hold the [MACRO] key and press [Z] to activate the macro


[6]  Squeeze a Row of Data

                A                  B                    C                  D                 E

  1 *---A macro to SQUEEZE a list of row entries (take out blank cells)

  2 *---Use the /Range Name Label Right [END] [RIGHT] [ENTER] to define

  3     the range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer at the left most cell of the list

  5 *---Hold the [MACRO] key and press [Z] to activate the macro

  6 *---The most right cell of the highlighted row must not be empty


[7]  Switch Range Places

                A                  B                    C                  D                 E

  1 *---A macro to switch 3-D and 2-D ranges places

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP

  8 !

  9 !

 10 \Z              {BREAKON}

 11 SWITCH          {LET rel209,@INFO("release")}~{RECALC loc209}

Click with the mouse on the SWITCH text to launch Lotus 1-2-3 for Windows or on the \Z to start a DOS version of Lotus 1-2-3. See the Start Here topic for details.


[6]  Center a Column of Titles Across the Screen

                A                  B                    C                  D                 E

  1 *---A macro to CENTER a column of titles in middle of screen

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---The labels to be centered must be on the most left column on screen

  6 !

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                     IT WILL WORK IN LOTUS 2.0 AND UP


[3]  Center a Title Across a Row

                A                  B                    C                  D                 E

  1 *---A macro to CENTER a TITLE across the row

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define

  3     the range names in this column (starts with the \Z macro name)

  4 *---Move the cell pointer to the first column

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[3]  Continuously Hide Columns

                A                  B                    C                  D                 E

  1 *---A macro to contiguously HIDE columns

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---Place the cell pointer at least one column apart of the columns

  6     you plan to hide. When the macro is activated the cell pointer

  7     position is recorded, hiding the column cause the managers to

  8     loose track and beep few times.

This macro allows you to continuously hide contiguous on non-contiguous columns easily.


[3]  View Hidden Columns Easily

                A                  B                   C                   D                 E

  1 *---A macro to VIEW and/or UNHIDE ALL the hidden columns

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[1]  Set the Width of a Group of Columns

                A                  B                    C                  D                 E

  1 *---A macro to change the width of group of adjacent columns

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6 !

  7 !

  8              * * *     FOR LOTUS 2.2 AND UP    * * *


[1]  Reset the Width of a Group of Columns

                A                  B                    C                  D                 E

  1 *---A macro to reset the width of group of adjacent columns

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6 !

  7 !

  8            * * *     A LOTUS 2.2 AND UP MACRO     * * *


[3]  Adjust the Column Width to the Current Label Length

                A                  B                    C                  D                 E

  1 *---A macro to adjust the column width to the length of the label in

  2     the current cell (1 space longer).

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[5]  Adjust the Column Width to the Current Label/Number Length

                A                  B                    C                  D                 E

  1 *---A macro to SET the column width to one more than the LABEL/NUMBER

  2     length in the current cell.

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP


[5]  Change Multiple Column Widths

                A                  B                    C                D                E

  1 *---A macro to change multiple column width

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

This macro allows you to simultaneously set the same column width to a specified number of columns, or set the width for a column at a time, each with a different column width. The macro uses a custom menu with three menu options and because one page cannot display all the menu options side by side without overlapping; we show here each menu option separately.


Copy Macros

[3]  Copy Adjacent Cell's Format

[7]  The Absolute Copy Macro

[6]  Copy a Cell Every Other Specified No. of Rows or Columns

[6]  Copy a Range Every Other Specified No. of Rows or Columns

[6]  Special Copy Options


[3]  Copy Adjacent Cell's Format

              A             B     C       D        E           F               G            H

  1 *---A macro to copy the adjacent cell's format

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP


[7]  The Absolute Copy Macro

               A                    B                   C                  D                 E

  1 *---A macro to copy any 3-D or 2-D range and keep ABSOLUTE references

  2     even though the range includes relative references/addresses.

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 !

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                     IT WILL WORK IN LOTUS 2.0 AND UP

This macro solves a common issue that most Lotus 1-2-3 users face. When you copy a cell in the worksheet and the cell contains a formula, the new formula is changed depending on the number of rows and columns shifted. This is a beautiful property of the electronic spreadsheet, but what do we do if we do not want the formulas updated? We can use absolute addresses, but what if we want to copy a whole range and have two identical copies of the range, which contain non-absolute formulas, to a data outside the range? The solution is to turn all the formulas to labels, and then copy the range. Turning a 100X100 range to labels is a real punishment. The ABS-COPY.WK1 macro does the job automatically by first turning all the values in the range into labels, then copying the range to the target location and last UN-LABELING the two ranges.


[6]  Copy a Cell Every Other Specified No. of Rows or Columns

                 A                      B                          C                         D

  1 *---A macro to COPY a cell every other specified rows or columns

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[6]  Copy a Range Every Other Specified No. of Rows or Columns

               A                        B                           C                       D

  1 *---A macro to create multiple copies of a range with specified

  2     number of rows or columns apart

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[6]  Special Copy Options

                A                  B                C             D            E           F

  1 *---A macro with standard and extra SPECIAL COPY options :

  2     1) Standard copy  2) Copy and move to target cell  3) Copy from

  3     there to here  4) Repeat last copy

  4 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  5     range names in this column (starts with the \Z macro name)

  6 *---Hold the [ALT] key and press [Z] to activate the macro


Database Macros

[7]  Sub-Total and Grand-Total Macro

[6]  Query and Extract Macro

[6]  Add Data to Database

[3]  Sort Descending by One Key

[3]  Sort Ascending by One Key

[3]  Sort Descending by Two Keys

[3]  Sort Ascending by Two Keys

[6]  Sort by Multiple Keys


[7]  Sub-Total and Grand-Total Macro

               A                  B                   C                   D                  E

  1 *---A SUBTOTAL and GRAND TOTAL macro for a database table

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Sort the database with respect to the items column to subtotal

  5 *---There should be at least two empty rows above the database to

  6     accommodate for the criterion area.

  7 *---Place the cell pointer at the upper left cell of the database

  8 *---Hold the [ALT] key and press [Z] to activate the macro

To understand what this macro does, let's look at the following table:

     Code No.    Name             Sales $

      001       Camera           10000.00

      001       Film              5000.00

      001       Lens              6000.00

      002       CD Player        20000.00

      002       Amplifier        15000.00

      002       Speaker          16000.00

      003       Laser Paper       4000.00

      003       Copy paper        3000.00

      003       Toner             2000.00

The macro will extract the sub-totals and the grand-total for every code item (must be sorted). The result will show:

     Code No.    Name             Sales $

      001       Camera           10000.00

      001       Film              5000.00

      001       Lens              6000.00

Sub-Total of 001 =               21000.00

     Code No.    Name             Sales $

      002       CD Player        20000.00

      002       Amplifier        15000.00

      002       Speaker          16000.00

Sub-Total of 002 =               51000.00

     Code No.    Name             Sales $

      003       Laser Paper       4000.00

      003       Copy paper        3000.00

      003       Toner             2000.00

Sub-Total of 003 =                9000.00

GRAND TOTAL =                    81000.00


[6]  Query and Extract Macro

                A                  B                 C                    D                  E

  1 *---A QUERY macro with EXTRACT, the macro assumes two empty lines above

  2     the database fields for the criterion range. There is no need

  3     to add "*" etc. just type the searched string. The query is case

  4     insensitive.

  5 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  6     range names in this column (starts with the \Z macro name)

  7 *---Place the highlight on the upper left cell of the database

  8 *---Hold the [ALT] key and press [Z] to activate the macro

  9 *---Leave two empty rows above database and one empty column to the left


[6]  Add Data to Database

               A                    B                    C                   D               E

  1 *---A macro to add data to a database. If you type a number the macro

  2     changes it to a label.

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Place the cell pointer on the upper left cell of your database

  6     field names. The data base should have at least two records.

  7 *---Hold the [ALT] key and press [Z] to activate the macro


[3]  Sort Descending by One Key

               A                    B                    C                   D               E

  1 *---A macro to descending sort by primary-key only

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[3]  Sort Ascending by One Key

               A                    B                    C                   D               E

  1 *---A macro to ascending sort by primary-key only

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[3]  Sort Descending by Two Keys

               A                    B                    C                   D               E

  1 *---A macro to descending sort by primary-key and secondary key

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[3]  Sort Ascending by Two Keys

               A                    B                    C                   D               E

  1 *---A macro to ascending sort by primary-key and secondary key

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[6]  Sort by Multiple Keys

               A                    B                    C                   D               E

  1 *---A macro to SORT by MULTIPLE KEYS

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---Highlight the data range PLUS one more empty column to the right,

  6     the macro uses the extra column for the sorting process.

  7 *---Works with labels only. Example: zip codes must be entered as labels

  8                      WILL NOT WORK WITH RELEASE 3

One of the limitations imposed by Lotus 1-2-3 is the number of sort keys available. In Lotus 2.0/2.01/2.2/2.3/2.4 you can use only two sort keys, in the 3-D releases more keys can be used. The macro displayed here can sort a database of virtually unlimited sort keys; the only limitation is that Lotus can accept up to 240 characters in a cell, which puts the limit on the number of sort keys.


Date Macros

[4]  Number of Days Between Two Dates

[4]  Set the System Time and Date from Lotus (2.0/2.01)

[4]  Set The System Time and Date from Lotus (2.2 and Up)

[4]  Set the Time and Date from Lotus (3.0 and Up)

[5]  Insert Dates Easily

[5]  Same Day for List of Months

[1]  Time Stamp Macro

[6]  Turn Date Labels Into Date Values

[6]  Turn Date Values into Date Labels

[5]  Create a List of Dates

[4]  Past Date and Future Date

[4]  Date Stamp with Extras


[4]  Number of Days Between Two Dates

               A                    B                    C                   D               E

  1 *---A macro to calculate the number of days between two dates

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---The macro prompts for the first date and then the second date

  6 *---Make sure that the keyboard is in [INSERT] mode and not [OVR]


[4]  Set the System Time and Date from Lotus (2.0/2.01)

               A                    B                    C                   D               E

  1 *---A macro to set the system time and date

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---The macro creates a batch file C.BAT to update the date and time

  6 *---When the DOS prompt appears press [C] and [ENTER] to run C.BAT

  7     the macro will automatically EXIT to 1-2-3


[4]  Set The System Time and Date from Lotus (2.2 and Up)

               A                    B                    C                   D               E

  1 *---A macro to set the system time and date

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---The macro creates a batch file C.BAT to update the date and time

  6 *---When the DOS prompt appears press [C] and [ENTER] to run C.BAT

  7     the macro will automatically EXIT to 1-2-3

  8                    * * *  LOTUS 2.2 AND UP * * *


[4]  Set the Time and Date from Lotus (3.0 and Up)

               A                    B                    C                   D               E

  1 *---A macro to set the system time and date

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---The macro creates a batch file TEMP.BAT to update the date and time

  6     and deletes it later


[5]  Insert Dates Easily

               A                          B                            C                    D

  1 *---A macro to insert a DATE function (see the MACROHLP macro too)

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---The @DATE(Yr,Mo,Dy prompt will appear. Change the [INS] to [OVR]

  6     and type the year, month and day and press [ENTER]. The macro will

  7     add the closing bracket ")".


[5]  Same Day for List of Months

             A                     B              C           D            E             F

  1 *---A macro to insert the same day for list of months (column, row or

  2     sheet)

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 !

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                     IT WILL WORK IN LOTUS 2.0 AND UP


[1]  Time Stamp Macro

               A                   B               C             D             E             F

  1 *---A macro to enter the current time to the cell

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[6]  Turn Date Labels Into Date Values

               A                    B              C           D            E             F

  1  *---A macro to turn a 3-D OR 2-D range of DATE LABELS into DATE VALUES.

  2      This macro creates format 4 dates (mm/dd/yy), to change to other

  3      formats change to /RFD[Format Mo.] in the second line of

         labels1a402.

  4  *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  5      range names in this column (starts with the \Z macro name)

  6  *---Hold the [ALT] key and press [Z] to activate the macro

  7  *---In release 3 the date label can be any of the 5 dates formats

  8          THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  9                       IT WILL WORK IN RELEASE 2.0 AND UP


[6]  Turn Date Values into Date Labels

               A                    B              C           D            E             F

  1 *---A macro to turn a 3-D or 2-D range of DATE VALUES into DATE LABELS.

  2     This macro accepts and creates format 4 dates (mm/dd/yy).

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 !

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                  IT WILL WORK IN RELEASE 2.0 AND UP


[5]  Create a List of Dates

              A               B            C             D            E             F        G

  1 *---A macro to enter a series of DATES

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[4]  Past Date and Future Date

              A               B            C             D            E             F        G

  1 *---A macro to insert dates for the future or the past. You can

  2     find the date several days ago (-) or ahead (+).

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 *---When you are prompted for the date format use the direction keys

  7     and press ENTER. Do not use the first character option.


[4]  Date Stamp with Extras

              A               B            C             D            E             F        G

  1 *---A macro to INSERT the current TIME or DATE in the current cell

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


File Macros

[1]  Change the Default Directory

[1]  Change the Default Drive

[1]  Save a File for Lotus 2.0 and Up

[1]  Save a File for Lotus 2.2 and Up

[1]  Save File to the A: Drive

[1]  Save a File to the A: Drive for Lotus 2.2 and Up

[5]  Use DOS Commands and Execute DOS Files from 1-2-3

[1]  Import Text Files

[5]  Create Files Table

[4]  Print a Worksheet as an ASCII File

[5]  Combine Files and Ranges

[3]  Retrieve a File from Four Drives (A:, B:, C: or D:)

[1]  Retrieve a File from the Default Directory

[3]  Extract a File to Four Drives (A:, B:, C:,  or D:)

[4]  Improved File Extract to Four Drives (A:, B:, C:,  or D:)

[4]  Check File Existence from Inside a Macro

[1]  List All the Files in the Default Directory

[3]  Use Full Screen Display and Point and Shoot to Delete Files


[1]  Change the Default Directory

                A                 B                   C                  D                  E

  1 *---A macro to change the DEFAULT FILE/DIRECTORY

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[1]  Change the Default Drive

                A                 B                   C                  D                  E

  1 *---A macro to change and update the DEFAULT DRIVE/DIRECTORY

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[1]  Save a File for Lotus 2.0 and Up

                A                 B                   C                  D                  E

  1 *---A macro to SAVE A FILE with the current name or a new name

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---If the file is the current file just press [ENTER] otherwise

  6     print the new name and press [ENTER]


[1]  Save a File for Lotus 2.2 and Up

                A                 B                   C                  D                  E

  1 *---A macro to SAVE A FILE with the current name or a new name

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---After the file is saved press [ENTER] if the [CMD] indicator is

  6     highlighted

  7 !

  8                * * *     FOR LOTUS 2.2 AND UP    * * *


[1]  Save File to the A: Drive

                A                 B                   C                  D                  E

  1 *---A macro to save file with a new name to the A: drive

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[1]  Save a File to the A: Drive for Lotus 2.2 and Up

                A                 B                   C                  D                  E

  1 *---A macro to save file with a new name to the A: drive

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---After the file is saved press [ENTER] if the [CMD] indicator is

  6     highlighted

  7 !

  8           THIS MACRO WORKS IN LOTUS 2.2 AND UP


[5]  Use DOS Commands and Execute DOS Files from 1-2-3

                A                      B                            C                       D

  1 *---A file and application manager macro that allows activating any

  2     executable file without quitting Lotus application. When using

  3     batch files the options are unlimited. To add or edit the command

  4     list add to/or change the list starting at the LIST522 range name.

  5 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  6     range names in this column (starts with the \Z macro name)

  7 *---Hold the [ALT] key and press [Z] to activate the macro

  8                  THIS MACRO WORKS IN LOTUS 2.2 AND UP


[1]  Import Text Files

                A                 B                   C                  D                  E

  1 *---A macro to IMPORT a file from the default directory, with a full

  2     screen of files list.

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 *---Highlight the file name and press the [ENTER] key


[5]  Create Files Table

                A                 B                   C                  D                  E

  1 *---A macro to create file names table

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer where you want the names table, the table

  5     occupies 4 columns and as many rows as necessary

  6 *---Hold the [ALT] key and press [Z] to activate the macro

  7 !

  8                       FOR LOTUS 2.2 AND UP


[4]  Print a Worksheet as an ASCII File

                A                   B                C                D                E

  1 *---A macro to save a range as unformatted ASCII file

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer at the leftmost cell of the range to file

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 *---Paint the range to be printed to file and press [ENTER]

  7 *---The macro saves the file with .PRN extension


[5]  Combine Files and Ranges

                A                   B                      C                D                E

  1 *---A macro to COMBINE a file from the default directory, with a full

  2     screen list of files.

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 *---Use the arrow keys to point where to combine the file

  7 *---Highlight the file name and press the [ENTER] key


[3]  Retrieve a File from Four Drives (A:, B:, C: or D:)

                A                   B                 C                 D                E

  1 *---A macro to RETRIEVE a file from the A:, B:, C: and D: drives

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[1]  Retrieve a File from the Default Directory

                A                   B                C                D                E

  1 *---A macro to RETRIEVE a file from the default directory, with a full

  2     screen list

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 *---Highlight the file name and press the [ENTER] key


[3]  Extract a File to Four Drives (A:, B:, C:,  or D:)

                A                   B                C                D                E

  1 *---A macro to EXTRACT a file to A:, B:, C: and D: to REDUCE it's size

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[4]  Improved File Extract to Four Drives (A:, B:, C:,  or D:)

                A                  B                C            D              E            F

  1 *---A macro to EXTRACT a file to A:, B:, C: and D: to REDUCE it's size

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6 !

  7 !           THIS MACRO WORKS ONLY IN LOTUS 2.2/2.3/2.4


[4]  Check File Existence from Inside a Macro

                A                   B                C                D                E

  1 *---A macro to check for file existance

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[1]  List All the Files in the Default Directory

                A                   B                C                D                E

  1 *---A macro to LIST files and view SIZE, creation DATE and TIME

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---Press [ENTER] to quit the file list display


[3]  Use Full Screen Display and Point and Shoot to Delete Files

                A                   B                C                D                E

  1 *---A macro to DELETE files from the default directory, with a full

  2     screen of files list.

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 *---Highlight the file name and press the [ENTER] key


Format Macros

[6]  Center Titles in the Middle of the Screen

[7]  Split Full Name

[3]  Underline a Column

[1]  Set Titles

[0]  Clear Titles

[3]  Toggle Titles

[3]  Highlight a Range of Text

[7]  Fill All the Empty Cells with Zeros

[3]  Set Time and File Indicator

[3]  Left Align Labels

[3]  Parse Data

[3]  Center All the Labels in a Range

[3]  Right Align All the Labels in a Range

[7]  Underline All the Labels in a Range

[9]  Search and Replace for Release 2.0/2.01 and Up

[2]  Control Negative Numbers Appearance

[7]  Annotate Cell Content

[6]  Turn All the Formulas and the Values into Labels

[6]  Turn All the Labels Back into Values and Formulas

[4]  Display a Flashing Message in the Panel

[4]  Insert a Phrase into a Cell

[6]  Pad Cells Content with Trailing Periods

[2]  Search and Replace for Release 2.2 and Up

[2]  Disable the Undo Feature

[2]  Enable the Undo Feature

[3]  Toggle the Undo Feature

[2]  Disable the Autoexec Macro from Running

[2]  Enable the Autoexec Macro from Running

[3]  Toggle Autoexec Macro Execution Mode

[2]  Disable the Beep Tone

[2]  Enable the Beep Tone

[3]  Toggle the Beep Tone Mode

[4]  Transform All the Formulas in a Worksheet into Values

[4]  Transform the Formulas in a Selected Range into Values

[6]  Strip Leading and Trailing Spaces from Strings

[6]  Add Zero Leads to All Numbers

[3]  Underline a Label

[6]  Un-Indent Label

[6]  Indent Text in a Range


[6]  Center Titles in the Middle of the Screen

                A                   B                C                D                E

  1 *---A macro to CENTER a column of titles in middle of screen

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---The labels to be centered must be on the most left column on screen

  6 !

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                     IT WILL WORK IN LOTUS 2.0 AND UP


  Split Full Name

                A                   B                C                D                E

  1 *---A macro to EXTRACT and SPLIT a full name to its components, i.e.

  2     FIRST NAME, M.I., LAST NAME, FIRST+M.I., and ALL THREE TOGETHER

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Point to the cell right to the upper cell of the NAMES list

  6 *---Hold the [ALT] key and press [Z] to activate the macro


[3]  Underline a Column

                A                   B                C                D                E

  1 *---A macro to UNDERLINE the current column to one character less

  2     than the column width

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Place the cell pointer just under the column to underline

  6 *---Hold the [ALT] key and press [Z] to activate the macro


[1]  Set Titles

                A                   B                C                D                E

  1 *---A macro to SET TITLES ON

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer at the upper left corner of the titles

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[0]  Clear Titles

                A                   B                C                D                E

  1 *---A macro to CLEAR TITLES

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer at the upper left corner of the titles

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[3]  Toggle Titles

                A                   B                C                D                E

  1 *---A macro to TOGGLE TITLES i.e. to switch between locked and unlocked

  2     titles

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Locate the cell pointer at the upper left corner of the titles

  6 *---Hold the [ALT] key and press [Z] to activate the macro


[3]  Highlight a Range of Text

                A                   B                C                D                E

  1 *---A macro to HIGHLIGHT and UN-HIGHLIGHT a range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[7]  Fill All the Empty Cells with Zeros

                A                   B                C                D                E

  1 *---A macro to REPLACE all BLANKS with ZEROS in a 3-D or 2-D range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP


[3]  Set Time and File Indicator

                A                   B                C                D                E

  1 *---A macro to set the date/time/file indicator in the lower left corner

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[3]  Left Align Labels

                A                   B                C                D                E

  1 *---A macro to LEFT ALIGN all the labels in a range. If a cell

  2     contains a number it WILL NOT be changed

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 *---Highlight the range to ALIGN, use the direction keys or type the

  7     range address or the range name and press [ENTER]


[3]  Parse Data

                A                   B                       C                          D

  1 *---A macro to PARSE a range of long labels

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer at the leftmost cell of the range to PARSE

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[3]  Center All the Labels in a Range

                 A                   B                  C                  D                 E

  1 *---A macro to CENTER all labels in a range. If the cell contains a

  2     number it WILL NOT be changed

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 *---Highlight the range to ALIGN, use the direction keys or type the

  7     range address or the range name and press [ENTER]


[3]  Right Align All the Labels in a Range

                 A                   B                  C                  D                 E

  1 *---A macro to RIGHT ALIGN all labels in a range. Values WILL NOT be

  2     changed.

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the <MACRO> key and press Z to activate the macro

  6 *---Highlight the range to RIGHT ALIGN, use the direction keys

  7     or type the range address or the range name.


[7]  Underline All the Labels in a Range

                 A                   B                  C                  D                 E

  1 *---A macro to underline a range. Usually is used for underlining rows

  2     to the column's width minus one, or to the up label/number width -1

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Place the cell pointer under the cell/range/row to be underlined

  6 *---Hold the [ALT] key and press [Z] to activate the macro

  7 *---Highlight the cells where you want the underline (under the range)

  8         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  9                     IT WILL WORK IN LOTUS 2.0 AND UP


[9]  Search and Replace for Release 2.0/2.01 and Up

                 A                   B                  C                  D                 E

  1 *---A macro to SEARCH and REPLACE labels or values in a range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define all

  3     the range names at the first column (starts with the \Z macro name)

  4 *---Place the cell pointer at the upper leftmost cell of the range to be

  5     searched

  6 *---Hold the [ALT] key and press [Z] to activate the macro

This macro was written to allow users of Lotus 2.0/2.01 to perform search and replace in formulas, values and labels, but it will work in all the Lotus releases from 2.0 and up. You can replace all occurrences in one operation or you can select what to replace and what to skip, or the macro allows you to perform find only.


[2]  Control Negative Numbers Appearance

                 A                                B                                    C

  1 *---A macro to control the negative values display

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[7]  Annotate Cell Content

             A                   B              C              D              E             F

  1 *---A macro to ANNOTATE cells containing formulas, In release 2.0-2.4

  2     it uses the @IF(1,[cell contents],"Note: [note]") formula that can

  3     annotate both labels and values, limited to 240 characters in

        release

  4     2.0-2.4 or 512 in release 3.0 and up

  5 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  6     range names in this column (starts with the \Z macro name)

  7 *---Hold the [ALT] key and press [Z] to activate the macro

  8         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  9                    IT WILL WORK IN RELEASE 2.0 AND UP


[6]  Turn All the Formulas and the Values into Labels

                A                   B                C                D                E

  1 *---A macro to turn all formulas and/or numbers in a 3-D or 2-D range

  2     into labels, labels and blank cells will not be changed.

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 !

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                    IT WILL WORK IN RELEASE 2.0 AND UP


[6]  Turn All the Labels Back into Values and Formulas

                A                   B                C                D                E

  1 *---A macro to turn all labels in a 3-D or 2-D range to formulas

  2     and/or numbers, values and blank cells will not be changed.

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 !

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                    IT WILL WORK IN RELEASE 2.0 AND UP


[4]  Display a Flashing Message in the Panel

                A                   B                C                D                E

  1 *---A macro to present a flashing message in the panel area

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---Type your message right to the cell MESSAGE

  6 !

  7                  THIS MACRO WORKS WITH LOTUS 2.2 AND UP


[4]  Insert a Phrase into a Cell

                A                   B                C                D                E

  1 *---A macro to ENTER a phrase into a cell

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[6]  Pad Cells Content with Trailing Periods

                A                   B                C                D                E

  1 *---A macro to PAD a cell content with trailing periods [.] to improve

  2     appearance and adjust descriptive cells to column width

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 !

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                    IT WILL WORK IN RELEASE 2.0 AND UP


[2]  Search and Replace for Release 2.2 and Up

                A                   B                C                D                E

  1 *---A macro to replace string occurrences by new string

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6                   THIS MACRO WORKS IN LOTUS 2.2 AND UP


[0]  Disable the Undo Feature
[2]  Disable the Undo Feature

                A                   B                C                D                E

  1 *---A macro to disable the UNDO feature

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6 !

  7                   THIS MACRO WORKS IN LOTUS 2.2 AND UP


[0]  Enable the Undo Feature
[2]  Enable the Undo Feature

                A                   B                C                D                E

  1 *---A macro to enable the UNDO feature

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6 !

  7                   THIS MACRO WORKS IN LOTUS 2.2 AND UP


[3]  Toggle the Undo Feature

                A                   B                C                D                E

  1 *---A macro to TOGGLE UNDO i.e. between UNDO ENABLE and DISABLE

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6 !

  7                   THIS MACRO WORKS IN LOTUS 2.2 AND UP


[0]  Disable the Autoexec Macro from Running
[2]  Disable the Autoexec Macro from Running

                A                   B                C                D                E

  1 *---A macro to disable the AUTOEXEC \0 macros

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6 !

  7                   THIS MACRO WORKS IN LOTUS 2.2 AND UP


[0]  Enable the Autoexec Macro from Running
[2]  Enable the Autoexec Macro from Running

                A                   B                C                D                E

  1 *---A macro to enable the AUTOEXEC \0 macros

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6 !

  7                   THIS MACRO WORKS IN LOTUS 2.2 AND UP


[3]  Toggle Autoexec Macro Execution Mode

                A                   B                C                D                E

  1 *---A macro to TOGGLE AUTOEXEC \0 macros between YES or NO

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6 !

  7                   THIS MACRO WORKS IN LOTUS 2.2 AND UP


[0]  Disable the Beep Tone
[2]  Disable the Beep Tone

                A                   B                C                D                E

  1 *---A macro to disable the BEEP bell in case of error

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6 !

  7                   THIS MACRO WORKS IN LOTUS 2.2 AND UP


[0]  Enable the Beep Tone
[2]  Enable the Beep Tone

                A                   B                C                D                E

  1 *---A macro to enable the BEEP bell in case of error

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6 !

  7                   THIS MACRO WORKS IN LOTUS 2.2 AND UP


[3]  Toggle the Beep Tone Mode

                A                   B                C                D                E

  1 *---A macro to TOGGLE BEEP i.e. to switch between BEEP ON and OFF

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[4]  Transform All the Formulas in a Worksheet into Values

                A                   B                C                D                E

  1 *---A macro to change all formulas in 3-D or 2-D worksheet into values.

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  6                   IT WILL WORK IN RELEASE 2.0 AND UP

  7 !

  8      ATTENTION: This macro changes the whole nature of the worksheet

  9                 and all formulas will be turned into values


[4]  Transform the Formulas in a Selected Range into Values

                A                   B                C                D                E

  1 *---A macro to REPLACE a range with formulas to values

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[6]  Strip Leading and Trailing Spaces from Strings

                A                   B                C                D                E

  1 *---A macro to STRIP leading and trailing spaces from all strings

  2     in a range, can be used to UNDO the CENTER2.WK1 macro operation

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 !

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                 IT WILL WORK IN RELEASE 2 AND UP


[6]  Add Zero Leads to All Numbers

                A                   B                C                D                E

  1 *---A macro to add zero leads to all numbers in a 3-D or 2-D range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                    IT WILL WORK IN RELEASE 2.0 AND UP


[3]  Underline a Label

                A                   B                   C                  D                E

  1 *---A macro to UNDERLINE the current label, it will underline a

  2     label with leading spaces too

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Place the cell pointer just under the label to underline

  6 *---Hold the [ALT] key and press [Z] to activate the macro


[6]  Un-Indent Label

                A                   B                   C                  D                E

  1 *---A macro to UN-INDENT all LABELS in a 3-D or 2-D range a

  2     specified No. of spaces

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 !

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                     IT WILL WORK IN LOTUS 2.0 AND UP


[6]  Indent Text in a Range

                A                   B                   C                  D                E

  1 *---A macro to INDENT all LABELS in a 3-D or 2-D range a specified

  2     No. of spaces

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 !

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                     IT WILL WORK IN LOTUS 2.0 AND UP


Graph Macros

[7]  Assign Graph Ranges to All the Graphs in One Step

[7]  Assign Graph Labels to All the Graphs in One Step

[7]  Assign Graph Legends to All the Graphs in One Step

[1]  Create a Named Graph

[3]  Delete Named Graphs

[1]  Use a Named Graph

[0]  Reset All the Named Graphs

[4]  Create a Slide Show

[5]  Graph Group Data Macro

[5]  Graph Group Labels Macro

[4]  Graph Group Legends Macro

[0]  Create Graph Names Table

[5]  Display the ASCII Code of a Character


[7]  Assign Graph Ranges to All the Graphs in One Step

                A                      B                        C                       D

  1 *---A macro to assign all graph data ranges (X and A-F) simultaneously.

  2     Simulates the /Graph Group in Lotus 2.2, the first column/row will

  3     become X and the rest A-F.

  4 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  5     range names in this column (starts with the \Z macro name)

  6 *---Hold the [ALT] key and press [Z] to activate the macro

  7 *---Expand and highlight the data range and press [ENTER]

The new releases of Lotus 1-2-3 (2.2 and up) include a new graph option, called /Graph Group, which will automatically assign up to seven adjacent data columns or rows to six graphs. The first column or row of data will be the X data range, the second the A data range, the third the B data range, and so on. This macro allows users of Lotus 2.0 and 2.01 to do the same.


[7]  Assign Graph Labels to All the Graphs in One Step

                A                      B                        C                       D

  1 *---A macro to assign all graph data labels (A-F) in one step

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---Expand and highlight the data range and press [ENTER]

  6 *---When you see the prompt menu bar use the  cell pointer to highlight

  7     your choice and press [return] to make your choice

  8             DO NOT USE THE CAPITAL LETTERS TO MAKE YOUR CHOICE!!

The new releases of Lotus 1-2-3 (2.2 and up) include a new graph option, called / Graph Option Data-labels Group, which will automatically assigns up to six data labels to six graphs. This macro allows the users of Lotus 2.0 and 2.01 to do the same. The macro should be used after the graphs have been created using the previous macro GRAPHGRP.WK1 or manually. The range that contains the text for the data labels is usually the same range you used as the A through F data ranges. This range must have the same size as the range containing the A, B, C, D, E and F data range that you label. For example, if the graph has A to F data ranges, each of which has four values, the data label range must have six rows or columns of four cells each.

Because the data range for labels has the same size as the data range for the graphs except the X data range we can use almost the same code as in the previous macro GRAPHGRP.WK1. Therefore we will not repeat the discussion from the pervious macro except the differences.


[7]  Assign Graph Legends to All the Graphs in One Step

                A                      B                        C                       D

  1 *---A macro to assign all graph legends (A-F) in one step

  2     Simulates the /Graph Option Legend Group in Lotus 2.2, the first

  3     column/row will become A and rest B-F.

  4 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  5     range names in this column (starts with the \Z macro name)

  6 *---Hold the [ALT] key and press [Z] to activate the macro

  7 *---Expand and highlight the data range and press [ENTER]

The new releases of Lotus 1-2-3 (2.2 and up) include a new graph option, that is called / Graph Options Legend Range. This option will automatically assign up to six data ranges simultaneously.

The text for the legends must be in a group of adjacent cells. The text in the first cell will become the legend for the A graph and so on. This macro allows the users of Lotus 2.0 and 2.01 to do the same.


[1]  Create a Named Graph

                 A                 B                   C                  D                  E

  1 *---A macro to CREATE a named graph

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[3]  Delete Named Graphs

                 A                 B                   C                  D                  E

  1 *---A macro to DELETE named graphs, with full screen graphs list.

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---Highlight the graph name and press the [ENTER] key


[1]  Use a Named Graph

                 A                 B                   C                  D                  E

  1 *---A macro to make a named graph the current graph

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---Highlight the graph name and press the [ENTER] key


[0]  Reset All the Named Graphs

                 A                 B                   C                  D                  E

  1 *---A macro to DELETE (RESET) all named graphs.

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[4]  Create a Slide Show

                 A                 B                   C                  D                  E

  1 *---A macro to show graphics slide show, the macro includes 5 sample

  2     graphs for demonstration. When you are finished you can reset all

  3     graphs and erase the range of numbers in the next page.

  4 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  5     range names in this column (starts with the \Z macro name)

  6 *---Hold the [ALT] key and press [Z] to activate the macro


[5]  Graph Group Data Macro

                 A                 B                   C                  D                  E

  1 *---A macro to assign all graph data ranges (X and A-F) simultaneously.

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---Expand and highlight the Data range ?and press [ENTER]

  6 !

  7                 THIS MACRO WORKS IN LOTUS 2.2 AND UP


[5]  Graph Group Labels Macro

                 A                  B                   C              D               E

  1 *---A macro to assign all graph data labels (for A-F) simultaneously.

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---Expand and highlight the Data range ?and press [ENTER]

  6 !

  7                 THIS MACRO WORKS IN LOTUS 2.2 AND UP


[4]  Graph Group Legends Macro

                 A                 B                   C                  D                  E

  1 *---A macro to assign all graph legends (A-F) simultaneously.

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---Expand and highlight the Data range ?and press [ENTER]

  6 !

  7 !

  8                 THIS MACRO WORKS IN LOTUS 2.2 AND UP


[0]  Create Graph Names Table

                 A                 B                   C                  D                  E

  1 *---A macro to create graph names table

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer where you want the names table, the table

  5     occupies 3 columns and as many rows as necessary

  6 *---Hold the [ALT] key and press [Z] to activate the macro


[5]  Display the ASCII Code of a Character

                 A                 B                   C                  D                  E

  1 *---A macro to display the ASCII code for a given character

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


Mathematical Macros

[6]  Cross Multiply Two Ranges

[6]  Cross Multiply Two Columns

[6]  Add to Left Column

[7]  Sum Multiple Cells

[7]  Sum Multiple Ranges

[3]  Automatically Sum and Underline a Column of Numbers

[3]  Semi Automatically Sum and Underline a Column of Numbers

[3]  Round a Value in a Cell

[7]  Round All the Values in a Range

[7]  Round All the Values in a Range (2)

[7]  Change the Sign of All the Values in a Range

[7]  Modify All the Values in a Range

[7]  Calculate the @ABS of All the Values in a Range

[7]  Calculate the @ACOS of All the Values In a Range

[7]  Calculate the @ASIN of All the Values In a Range

[7]  Calculate the @ATAN of All the Values In a Range

[7]  Calculate the @COS of All the Values In a Range

[7]  Calculate the @EXP of All the Values In a Range

[7]  Calculate the @INT of All the Values In a Range

[7]  Calculate the @LN of All the Values In a Range

[7]  Calculate the @LOG of All the Values In a Range

[7]  Calculate the @SIN of All the Values In a Range

[7]  Calculate the @SQRT of All the Values In a Range

[7]  Calculate the @TAN of All the Values In a Range

[3]  Calculate the @ABS of a Value In a Cell

[3]  Calculate the @ACOS of a Value In a Cell

[3]  Calculate the @ASIN of a Value In a Cell

[3]  Calculate the @ATAN of a Value In a Cell

[3]  Calculate the @COS of a Value In a Cell

[3]  Calculate the @EXP of a Value In a Cell

[3]  Calculate the @INT of a Value In a Cell

[3]  Calculate the @LN of a Value In a Cell

[3]  Calculate the @LOG of a Value In a Cell

[3]  Calculate the @SIN of a Value In a Cell

[3]  Calculate the @SQRT of a Value In a Cell

[3]  Calculate the @TAN of a Value In a Cell


[6]  Cross Multiply Two Ranges

                 A                 B                   C                  D                  E

  1 *---A macro to cross multiply (sum products) of two ranges

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer where you want the multiplication result

  5 *---Hold the [ALT] key and press [Z] to activate the macro

While writing Super Power, I have decided to expand the CROSMLT2.WK1 macro to multiple column ranges and replace it with this macro from the SUPER MACRO LIBRARY. In this macro we cannot use the Matrix mathematics of Lotus 1-2-3, instead we will make use of the @INDEX function. This macro is more general and can also sum products of multi column ranges, however it is slower than the CROSMLT2.WK1 macro when summing products of one column ranges.


[6]  Cross Multiply Two Columns

                 A                 B                   C                  D                  E

  1 *---A macro to cross multiply two columns (the first column can be

  2     the quantities, and the second can be the prices).

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Place the cell pointer where you want the multiplication result

  6     should be above the column ranges

  7 *---Hold the [ALT] key and press [Z] to activate the macro

This macro fills the gap in Lotus 2/2.01/2.2/2.3/2.4 for a missing CROSS MULTIPLY function like the @SUMPRODUCT in release 3/3.1/3.1+/123w, which sums the products of two column cells. The program uses the Data Matrix built-in options in Lotus 1-2-3 but is limited to the sum products of two columns. For a more general macro which can sum two general ranges with more than one column each, see the previous CROSMULT.WK1 macro. This macro was replaced in the SUPER MACRO LIBRARY by the CROSMULT.WK1 macro during the writing process of Super Power.


[6]  Add to Left Column

                 A                 B                   C                  D                  E

  1 *---A macro to add the column's values to the left column's values. The

  2     macro assumes two columns of values and adds the right one to the

  3     left one. Can be used for year to date calculations.

  4 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  5     range names in this column (starts with the \Z macro name)

  6 *---Place the cell pointer on the top cell of the right column

  7 *---Hold the [ALT] key and press [Z] to activate the macro

  8 *---The macro adds the right values to the left and erases the right.

This interesting macro adds one column's values to the column to the left of it and clears the column. For example if we have the following two columns of data:

                 A                 B                   C                  D                  E

  1            100          250

  2            200          100

  3            300          300

  4            450          200

  5            600          100

  6

after using the macro the result will be:

                 A                 B                   C                  D                  E

  1            350

  2            300

  3            600

  4            650

  5            700

  6

This macro can be used when you need to collect data and only the total interests you.


[7]  Sum Multiple Cells

                 A                 B                   C                  D                  E

  1 *---A macro to @SUM MULTIPLE cells and stay where the formula is

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer where you want the @SUM formula to appear

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 *---Use Ctrl-Break to stop the macro

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                     IT WILL WORK IN LOTUS 2.0 AND UP

Every Lotus 1-2-3 user writes formulas to sum data from scattered cells using the basic arithmetic operation "+" either by typing the cell addresses or more likely by pointing to the cells using the cell pointer. This macro makes the task easier and clearer. You are only prompted to point to the cells and press ENTER, no need to type "+" signs and/or commas "," the macro does the job.


[7]  Sum Multiple Ranges

                 A                 B                   C                  D                  E

  1 *---A macro to @SUM MULTIPLE ranges

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer where you want the @SUM formula to appear

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 *---Use Ctrl-Break to stop the macro

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                     IT WILL WORK IN LOTUS 2.0 AND UP

This macro is basically the same as the previous @SUMCELS.WK1 macro, but here we can easily sum scattered ranges not just cells. The macro uses the same techniques as the previous @SUMCELS.WK1 macro, except that here the range names are enveloped by the @SUM() function.


[3]  Automatically Sum and Underline a Column of Numbers

                 A                 B                   C                  D                  E

  1 *---An AUTOMATIC macro to SUM and UNDERLINE a column of numbers

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Highlight the cell just beneath the column of numbers

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 *---The column of number must be contiguous


[3]  Semi Automatically Sum and Underline a Column of Numbers

                 A                 B                   C                  D                  E

  1 *---A SEMIAUTOMATIC macro to SUM and UNDERLINE a column of numbers

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Highlight the cell just beneath the column of numbers

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 *---If the column of number is contiguous the whole column will be

  7     highlighted otherwise expand the highlight and press [ENTER]


[3]  Round a Value in a Cell

                 A                 B                   C                  D                  E

  1 *---A macro to @ROUND the value in a cell

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Highlight the cell to be rounded

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[7]  Round All the Values in a Range

                 A                 B                   C                  D                  E

  1 *---A macro to @ROUND all values in a 3-D or 2-D range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP

This macro uses the @ROUND function to round off all the values in the range to a specified number of decimal places. The macro prompts you to highlight (paint) the range to process and then it rounds all the numbers and formulas in this range into fixed values. The macro ignores the labels and the empty cells. This macro is a little bit different than the ROUNDVAL.WK1 macro, it turns all the rounded formulas and values into fixed values and it uses a different technique to round the values.


[7]  Round All the Values in a Range (2)

                 A                 B                   C                  D                  E

  1 *---A macro to ROUND all values in a 3-D or 2-D range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP

This macro uses the @ROUND function to round off all the values in the range to a specified number of decimal places. The macro prompts you to highlight the range to process and then the macro rounds all the values in this range. The macro ignores the labels and the empty cells.


[7]  Change the Sign of All the Values in a Range

                 A                 B                   C                  D                  E

  1 *---A macro to CHANGE THE SIGN of all values in a 3-D or 2-D range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP

This macro changes the sign of all the values in a range; the macro ignores labels and blanks. Then the macro prompts you to paint the range to process and then the macro envelopes every value with the -() function. For example: if a cell contains the -1000 number, after the macro is finished, the cell will contain the -(-1000) formula.


[7]  Modify All the Values in a Range

                 A                 B                   C                  D                  E

  1 *---A macro to MODIFY all values in a 3-D or 2-D range. For example:

  2     multiply values by 5 or add 9 to all values in the range. Blanks or

  3     labels will not be altered.

  4 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  5     range names in this column (starts with the \Z macro name)

  6 *---Hold the [ALT] key and press [Z] to activate the macro

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                     IT WILL WORK IN LOTUS 2.0 AND UP

This macro allows you to MODIFY all the values in a 3-D or 2-D range. For example: you can multiply all the values by 5 or add 9 to all values in the range. Blanks cell or cells which contain labels are ignored.


[7]  Calculate the @ABS of All the Values in a Range

                 A                 B                   C                  D                  E

  1 *---A macro to CALCULATE the @ABS of all values in a 3-D range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP

This macro applies the @ABS function on all the values in a range. It ignores labels and blanks. The macro prompts you to paint the range to process, and then it envelopes every value with the @ABS function. For example: if a cell contains the -1000 number, after the macro is finished the cell will contain the @ABS(-1000) formula.


[7]  Calculate the @ACOS of All the Values In a Range

                 A                 B                   C                  D                  E

  1 *---A macro to CALCULATE the @ACOS of all values in a 3-D range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP

See Calculate the @ABS of All the Numbers In a Range.


[7]  Calculate the @ASIN of All the Values In a Range

                 A                 B                   C                  D                  E

  1 *---A macro to CALCULATE the @ASIN of all values in a 3-D or 2-D range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP

See Calculate the @ABS of All the Numbers In a Range.


[7]  Calculate the @ATAN of All the Values In a Range

                 A                 B                   C                  D                  E

  1 *---A macro to CALCULATE the @ATAN of all values in a 3-D or 2-D range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP

See Calculate the @ABS of All the Numbers In a Range.


[7]  Calculate the @COS of All the Values In a Range

                 A                 B                   C                  D                  E

  1 *---A macro to CALCULATE the @COS of all values in a 3-D or 2-D range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP

See Calculate the @ABS of All the Numbers In a Range.


[7]  Calculate the @EXP of All the Values In a Range

                 A                 B                   C                  D                  E

  1 *---A macro to CALCULATE the @EXP of all values in a 3-D or 2-D range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP

See Calculate the @ABS of All the Numbers In a Range.


[7]  Calculate the @INT of All the Values In a Range

                 A                 B                   C                  D                  E

  1 *---A macro to CALCULATE the @INT of all values in a 3-D or 2-D range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP

See Calculate the @ABS of All the Numbers In a Range.


[7]  Calculate the @LN of All the Values In a Range

                 A                 B                   C                  D                  E

  1 *---A macro to CALCULATE the @LN of all values in a 3-D or 2-D range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP

See Calculate the @ABS of All the Numbers In a Range.


[7]  Calculate the @LOG of All the Values In a Range

                 A                 B                   C                  D                  E

  1 *---A macro to CALCULATE the @LOG of all values in a 3-D or 2-D range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP

See Calculate the @ABS of All the Numbers In a Range.


[7]  Calculate the @SIN of All the Values In a Range

                 A                 B                   C                  D                  E

  1 *---A macro to CALCULATE the @SIN of all values in a 3-D or 2-D range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP

See Calculate the @ABS of All the Numbers In a Range.


[7]  Calculate the @SQRT of All the Values In a Range

                 A                 B                   C                  D                  E

  1 *---A macro to CALCULATE the @SQRT of all values in a 3-D or 2-D range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP

See Calculate the @ABS of All the Numbers In a Range.


[7]  Calculate the @TAN of All the Values In a Range

                 A                 B                   C                  D                  E

  1 *---A macro to CALCULATE the @TAN of all values in a 3-D or 2-D range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP

See Calculate the @ABS of All the Numbers In a Range.


[3]  Calculate the @ABS of a Value In a Cell

                 A                 B                   C                  D                  E

  1 *---A macro to CALCULATE the @ABS of the value in a cell

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer on the cell to be calculated

  5 *---Hold the [ALT] key and press [Z] to activate the macro

This macro applies the @ABS function to the value in the current cell. For example, if the cell contains the -1000 value, the result of the macro is the @ABS(-1000) which returns the 1000 value. The macro leaves the formula in the cell so that you can see what the it did.


[3]  Calculate the @ACOS of a Value In a Cell

                 A                 B                   C                  D                  E

  1 *---A macro to CALCULATE the @ACOS of the value in a cell

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Highlight the cell to be calculated

  5 *---Hold the [ALT] key and press [Z] to activate the macro

See Calculate the @ABS of a Value in a Cell.


[3]  Calculate the @ASIN of a Value In a Cell

                 A                 B                   C                  D                  E

  1 *---A macro to CALCULATE the @ASIN of the value in a cell

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Highlight the cell to be calculated

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[3]  Calculate the @ATAN of a Value In a Cell

                 A                 B                   C                  D                  E

  1 *---A macro to CALCULATE the @ATAN of the value in a cell

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Highlight the cell to be calculated

  5 *---Hold the [ALT] key and press [Z] to activate the macro

See Calculate the @ABS of a Value in a Cell.


[3]  Calculate the @COS of a Value In a Cell

                 A                 B                   C                  D                  E

  1 *---A macro to CALCULATE the @COS of the value in a cell

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Highlight the cell to be calculated

  5 *---Hold the [ALT] key and press [Z] to activate the macro

See Calculate the @ABS of a Value in a Cell.


[3]  Calculate the @EXP of a Value In a Cell

                 A                 B                   C                  D                  E

  1 *---A macro to CALCULATE the @EXP of the value in a cell

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Highlight the cell to be calculated

  5 *---Hold the [ALT] key and press [Z] to activate the macro

See Calculate the @ABS of a Value in a Cell.


[3]  Calculate the @INT of a Value In a Cell

                 A                 B                   C                  D                  E

  1 *---A macro to CALCULATE the @INT of the value in a cell

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Highlight the cell to be calculated

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[3]  Calculate the @LN of a Value In a Cell

                 A                 B                   C                  D                  E

  1 *---A macro to CALCULATE the @LN of the value in a cell

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Highlight the cell to be calculated

  5 *---Hold the [ALT] key and press [Z] to activate the macro

See Calculate the @ABS of a Value in a Cell.


[3]  Calculate the @LOG of a Value In a Cell

                 A                 B                   C                  D                  E

  1 *---A macro to CALCULATE the @LOG of the value in a cell

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Highlight the cell to be calculated

  5 *---Hold the [ALT] key and press [Z] to activate the macro

See Calculate the @ABS of a Value in a Cell.


[3]  Calculate the @SIN of a Value In a Cell

                 A                 B                   C                  D                  E

  1 *---a macro to calculate the @SIN of the value in a cell

  2 *---use the /range name label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \z macro name)

  4 *---highlight the cell to be calculated

  5 *---hold the [ALT] key and press [Z] to activate the macro

See Calculate the @ABS of a Value in a Cell.


[3]  Calculate the @SQRT of a Value In a Cell

                 A                 B                   C                  D                  E

  1 *---A macro to CALCULATE the @SQRT of the value in a cell

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Highlight the cell to be calculated

  5 *---Hold the [ALT] key and press [Z] to activate the macro

See Calculate the @ABS of a Value in a Cell.


[3]  Calculate the @TAN of a Value In a Cell

                 A                 B                   C                  D                  E

  1 *---A macro to CALCULATE the @TAN of the value in a cell

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Highlight the cell to be calculated

  5 *---Hold the [ALT] key and press [Z] to activate the macro

See Calculate the @ABS of a Value in a Cell.


Utilities for *.MLB Macros

[4]  Add-In Attach Macro

[1]  Add-In Detach Macro

[1]  Create *.MLB Macros from *.WK1 Macros

[4]  Edit and Save an *.MLB Library

[2]  Load *.MLB Macros to the Memory

[4]  Load *.MLB Macros to the Memory (2)

[2]  Remove *.MLB Macros from the Memory

[4]  Remove *.MLB Macros from the Memory (2)

[8]  Create *.MLB Macro Libraries from *.WK1 and *.MLB Macros


[4]  Add-In Attach Macro

                 A                 B                   C                  D                  E

  1 *---A macro to attach add-ins, The macro assumes that the DOS default

  2     directory is the directory where LOTUS and the add-ins are located

  3     the ALT-F10 key combination is assigned to the attached add-in

  4 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  5     range names in this column (starts with the \Z macro name)

  6 *---Hold the [ALT] key and press [Z] to activate the macro

  7 !

  8                 THIS MACRO WORKS ONLY WITH LOTUS 2.2 to 2.4


[1]  Add-In Detach Macro

                 A                 B                   C                  D                  E

  1 *---A macro to Detach add-ins

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---Use Ctrl-Break to quit the macro


[1]  Create *.MLB Macros from *.WK1 Macros

                 A                 B                   C                  D                  E

  1 *---A macro to create library macros (*.MLB) from a (*.WK1) macro/file

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6 !

  7                 THIS MACRO WORKS ONLY WITH LOTUS 2.2 to 2.4


[4]  Edit and Save an *.MLB Library

                 A                   B                      C                          D

  1 *---A macro to edit and save a library macro (*.MLB)

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the ,ALT] key and press [Z] to activate the macro

  5 !

  6 !

  7                 THIS MACRO WORKS ONLY WITH LOTUS 2.2 to 2.4


[2]  Load *.MLB Macros to the Memory

                 A                 B                   C                  D                  E

  1 *---A macro to continuously load a library macros (*.MLB) from the disk

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press <[Z] to activate the macro

  5 *---Use the Ctrl-Break to Quit

  6 !

  7                 THIS MACRO WORKS ONLY WITH LOTUS 2.2 to 2.4


[4]  Load *.MLB Macros to the Memory (2)

                 A                 B                   C                  D                  E

  1 *---A macro to load *.MLB macros to memory

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---Use Ctrl-Break to quit

  6 !

  7                 THIS MACRO WORKS ONLY WITH LOTUS 2.2 to 2.4


[2]  Remove *.MLB Macros from the Memory

                 A                 B                   C                  D                  E

  1 *---A macro to remove *.MLB macros from memory

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6 !

  7 !

  8                 THIS MACRO WORKS ONLY WITH LOTUS 2.2 to 2.4


[4]  Remove *.MLB Macros from the Memory (2)

                 A                 B                   C                  D                  E

  1 *---A macro to remove library macros (*.MLB) from the memory

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6 !

  7                 THIS MACRO WORKS ONLY WITH LOTUS 2.2 to 2.4


[8]  Create *.MLB Macro Libraries from *.WK1 and *.MLB Macros

                 A              B             C                 D                 E

  1 *---A macro to create library macros (*.MLB) from the macros (*.WK1) in

  2     the default directory to be used with the MACROMGR.ADN add-in. The

  3     macros must follow the rules given in Super Power for creating macros

  4     compatible with the SUPER MACROS LIBRARY.

  5 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  6     range names in this column (starts with the \X macro name)

  7 *---Hold the [ALT] key and press [X] to activate the macro

  8                 THIS MACRO WORKS ONLY WITH LOTUS 2.2 to 2.4


Modifying Macros

[6]  Replace All ERR Values or NA Values with Zeros

[3]  RECALC a Range

[6]  Erase Only the Numbers in a Range

[6]  Erase Only the Labels Inside a Range

[6]  Erase Only the Zeros in a Range

[6]  Change All the Labels in a Range to UPPERCASE Form

[6]  Change All the Labels in a Range to Proper Form


[6]  Replace All ERR Values or NA Values with Zeros

                 A                 B                   C                  D                  E

  1 *---A macro to REPLACE all ERR or NA in a 3-D or 2-D range with ZEROS

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                    IT WILL WORK IN RELEASE 2.0 AND UP


[3]  RECALC a Range

                 A                 B                   C                  D                  E

  1 *---A macro to RECALC a range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[6]  Erase Only the Numbers in a Range

                 A                 B                   C                  D                  E

  1 *---A macro to ERASE all NUMBERS in a 3-D or 2-D range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP


[6]  Erase Only the Labels Inside a Range

                 A                 B                   C                  D                  E

  1 *---A macro to ERASE all LABELS in a 3-D or 2-D range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP


[6]  Erase Only the Zeros in a Range

                 A                 B                   C                  D                  E

  1 *---A macro to ERASE all ZEROS in a 3-D or 2-D range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP


[6]  Change All the Labels in a Range to UPPERCASE Form

                 A                 B                   C                  D                  E

  1 *---A macro to turn all labels in a 3-D or 2-D range to UPPERCASE form

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP

See Change All the Labels in a Range to LOWERCASE Form.


[6]  Change All the Labels in a Range to Proper Form

                 A                 B                   C                  D                  E

  1 *---A macro to turn all labels in a 3-D or 2-D range to PROPER form

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP

See Change All the Labels in a Range to LOWERCASE Form.


Cells, Ranges and Cell Pointer Moving Macros

[4]  Jump to Any Column and Stay at the Same Screen Position

[1]  Scroll the Cell to the Top of the Screen

[5]  Visit a Set of Addresses/Stations in Order

[7]  Go to the Last Cell Pointer Location

[6]  Jump to the End of a Row, a Column or a Sheet

[6]  Jump to the End of the Worksheet

[6]  Extra Cell and Range Move Options

[1]  Use Point and Shoot to Jump to a Range

[6]  Move the Cell Pointer Between the Worksheet Corners


[4]  Jump to Any Column and Stay at the Same Screen Position

                 A                 B                   C                  D                  E

  1 *---A macro to jump to other column and stay at the same screen position

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[1]  Scroll the Cell to the Top of the Screen

                 A                 B                   C                  D                  E

  1 *---A macro to move the current cell to the top of the screen

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[5]  Visit a Set of Addresses/Stations in Order

                 A                 B                   C                  D                  E

  1 *---A macro to move the cell pointer around the worksheet to predefined

  2     set of locations. Every time the macro is activated the cell pointer

  3     moves to the next location (station). The macro already includes a

  4     list of stations A1, B1. . . G1, insert your stations instead. The

  5     list is not limited in size but has to be contiguous.

  6 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  7     range names in this column (starts with the \Z macro name)

  8 *---Hold the [ALT] key and press [Z] to activate the macro


[7]  Go to the Last Cell Pointer Location

                 A                 B                   C                  D                  E

  1 *---A macro that REMEMBERS last pointer position including the sheet

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP


[6]  Jump to the End of a Row, a Column or a Sheet

                 A             B          C           D           E          F         G    H

  1 *---A macro to jump with the cell pointer to ends of row, columns, sheets

  2     even if they are not empty

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 !

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                     IT WILL WORK IN LOTUS 2.0 AND UP


[6]  Jump to the End of the Worksheet

                 A               B           C        D         E        F       G     H

  1 *---A macro to JUMP to the end of rows, columns, sheets (last occupied

    cell)

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                    IT WILL WORK IN RELEASE 2.0 AND UP


[6]  Extra Cell and Range Move Options

                 A                  B                   C             D              E       F

  1 *---A macro with standard and extra SPECIAL MOVE options :

  2     1) Standard move  2) Move to target cell and stay there 3) Move from

  3     there to here  4) Repeat last move

  4 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  5     range names in this column (starts with the \Z macro name)

  6 *---Hold the [ALT] key and press [Z] to activate the macro


[1]  Use Point and Shoot to Jump to a Range

                 A                 B                   C                  D                  E

  1 *---A macro to GO TO any range in the worksheet

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[6]  Move the Cell Pointer Between the Worksheet Corners

                 A                 B                   C                  D                  E

  1 *---A macro to move the cell pointer around the worksheet corners

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP


Macros Affecting Range Names

[0]  Name Cells to the Right

[0]  Name Cells to the Left

[0]  Name Cells Down

[0]  Name Cells Up

[4]  The Range Name Create Macro

[4]  Verify Range Name Existence

[6]  Edit Formulas with Range Names

[5]  Create Range Names Table

[4]  Restore Range Names

[6]  Delete a Group of Range Names

[6]  Delete All the Range Names in a Range

[3]  Delete Range Names Easily

[3]  The Range Name Delete Macro


[0]  Name Cells to the Right

                 A                 B                   C                  D                  E

  1 *---A macro to name the cells RIGHT to the current cells

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer on the uppermost name

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 *---If there is ONLY one name just press [ENTER] otherwise "PAINT"

  7     the NAMES range using the direction keys and press [ENTER]


[0]  Name Cells to the Left

                 A                 B                   C                  D                  E

  1 *---A macro to name the cell LEFT to the current cell

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer on the upper most name

  5 *---Hold the [ALT] key and press Z to activate the macro

  6 *---If there is ONLY one name just press [ENTER] otherwise "PAINT"

  7     the NAMES range using the direction keys and press [ENTER]


[0]  Name Cells Down

                 A                 B                   C                  D                  E

  1 *---A macro to name the cells DOWN to the current cells

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer on the left most name

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 *---If there is ONLY one name just press [ENTER] otherwise "PAINT"

  7     the NAMES range using the direction keys and press [ENTER]


[0]  Name Cells Up

                 A                 B                   C                  D                  E

  1 *---A macro to name the cells UP to the current cells

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer on the leftmost name

  5 *---Hold the [ALT] key and press Z to activate the macro

  6 *---If there is ONLY one name just press [ENTER] otherwise "PAINT"

  7     the NAMES range using the direction keys and press [ENTER]


[4]  The Range Name Create Macro

                 A                 B                   C                  D                  E

  1 *---A macro to CREATE range names

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer at the cell to be named

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[4]  Verify Range Name Existence

                 A                 B                   C                  D                  E

  1 *---A macro to VERIFY a range name existence, list all range names

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---When you get the list of the range names use the direction keys to

  6     page through the range names and press [ENTER] to finish.


[6]  Edit Formulas with Range Names

                 A                 B                   C                  D                  E

  1 *---A macro to EDIT a formula using the range names instead of addresses

  2     when the addresses has names but are not shown in the formula

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Place the cell pointer on the formula to be edited

  6 *---When you get the formula as literal, edit it and press [ENTER]

  7 *---Hold the [ALT] key and press [Z] to activate the macro

  8         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  9                    IT WILL WORK IN RELEASE 2.0 AND UP


[5]  Create Range Names Table

                 A                 B                   C                  D                  E

  1 *---A macro to create a TABLE of all range names, the table is created

  2     outside the worksheet area using the {END}{HOME} key combination.

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[4]  Restore Range Names

                 A                 B                   C                  D                  E

  1 *---A macro to RESTORE all range names after issuing /RNR accidentally.

  2     The worksheet must contain a range names table previously created

  3     using /RNT. It is a good practice to set aside a place for range

  4     names table and using the /RNT to update the table any time new

  5     range names are created.

  6 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  7     range names in this column (starts with the \Z macro name)

  8 *---Place the cell pointer on the upper left cell of the names table.

  9 *---Hold the [ALT] key and press [Z] to activate the macro


[6]  Delete a Group of Range Names

                 A                 B                   C                  D                  E

  1 *---A macro to DELETE a LISTED GROUP of range names, the macro checks

  2     the validity of the range names too

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 !

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                     IT WILL WORK IN LOTUS 2.0 AND UP


[6]  Delete All the Range Names in a Range

                 A                 B                   C                  D                  E

  1 *---A macro to DELETE all range names in a specified 3-D or 2-D range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP


[3]  Delete Range Names Easily

                 A                 B                   C                  D                  E

  1 *---A macro to DELETE range names

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---Use Ctrl-Break to quit


[3]  The Range Name Delete Macro

                 A                 B                   C                  D                  E

  1 *---A macro to DELETE range names one by one

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---Highlight the range name and press the [ENTER] key


Print Macros

[6]  Print Ranges

[0]  Advance the Paper One Line

[0]  Advance the Paper one Page

[0]  Set Margin to None

[3]  Set Margins to None (Improved)

[4]  Insert a Ruler for Printing Width Measurement

[6]  Global Setup String for Printing

[6]  Print a Range with Row and Column Headings

[7]  Print a List of Ranges Picked Using Point and Shoot

[7]  Print Every Other Column in a Range

[6]  Print a Worksheet

[6]  Print a Worksheet and Headings

[6]  Measure Range Width before Printing

[1]  Insert a Print Footer

[1]  Insert a Print Header

[1]  Print Cells Content as Displayed

[1]  Print Cells Content as Formulas

[1]  Print a Range

[0]  Page Align

[7]  Print Form Letters from an Address Database

[7]  Create One Across Mailing Labels from an Address Database


[6]  Print Ranges

                 A                 B                   C                  D                  E

  1 *---A macro to print the FIRST GROUP OF SPECIFIED number of RANGES,

  2     you can print all ranges in the worksheet in ascending order.

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[0]  Advance the Paper One Line

                 A                 B                   C                  D                  E

  1 *---A macro to the advance the paper in the printer one line.

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[0]  Advance the Paper one Page

                 A                 B                   C                  D                  E

  1 *---A macro to advance the paper in the printer one page

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[0]  Set Margin to None

                 A                 B                   C                  D                  E

  1 *---A macro to clear and reset print margin

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6 !

  7                  THIS MACRO WORKS WITH LOTUS 2.2 AND UP


[3]  Set Margins to None (Improved)

                 A                 B                   C                  D                  E

  1 *---A macro to set the margins to NONE. In release 2/2.01 the left,

  2     bottom and top margins are set to "0" and the right margin to

  3     "240"

  4 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  5     range names in this column (starts with the \Z macro name)

  6 *---Hold the [ALT] key and press [Z] to activate the macro

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                   IT WILL WORK IN RELEASE 2.0 AND UP


[4]  Insert a Ruler for Printing Width Measurement

                 A                 B                   C                  D                  E

  1 *---A macro to insert a ruler for printing measurement. The ruler lines

  2     will be ignored when included in the print range. The ruler takes

  3     two rows, insert or clean two empty rows for the ruler.

  4 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  5     range names in this column (starts with the \Z macro name)

  6 *---Place the cell pointer where you want the ruler

  7 *---Hold the [ALT] key and press [Z] to activate the macro


[6]  Global Setup String for Printing

                 A               B           C           D          E       F   G    H    I

  1 *---A macro to create a new GLOBAL printing setup string, or insert

  2     a setup string into a cell, can be activated in the middle of Lotus

  3     printing menu too.

  4 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  5     range names in this column (starts with the \Z macro name)

  6 *---Place the cell pointer where you want the setup string to be

  7 *---Hold the [ALT] key and press [Z] to activate the macro

  8         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  9                     IT WILL WORK IN LOTUS 2.0 AND UP


[6]  Print a Range with Row and Column Headings

                 A                 B                   C                  D                  E

  1 *---A macro to print any range including the column and row headings

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6 !

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                    IT WILL WORK IN RELEASE 2.0 AND UP


[7]  Print a List of Ranges Picked Using Point and Shoot

                 A               B            C          D           E        F            G

  1 *---A macro to print a LIST of ranges picked from the screen or

  2     entered manually

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 !

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                     IT WILL WORK IN LOTUS 2.0 AND UP


[7]  Print Every Other Column in a Range

                 A                 B                   C                  D                  E

  1 *---A macro to print only every other specified number of columns. The

  2     macro prompts the user for the number of columns to skip. Example:

  3     To print only every 4th column, answer 3 to the "How many columns to

  4     skip ? " prompt.

  5 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  6     range names in this column (starts with the \Z macro name).

  7 *---Hold the [ALT] key and press [Z] to activate the macro

  8         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  9                     IT WILL WORK IN LOTUS 2.0 AND UP


[6]  Print a Worksheet

                 A                  B                 C               D           E          F

  1 *---A macro to PRINT a worksheet

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer where you want the setup string to be

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[6]  Print a Worksheet and Headings

                 A                 B                   C                  D                  E

  1 *---A macro to print the worksheet including the columns and rows

  2     headings.

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[6]  Measure Range Width before Printing

                 A                 B                   C                  D                  E

  1 *---A macro to measure range width ( before printing for example )

  2 *---Use the /Range Name Label Down [ENTER] to define the \Z macro name

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column ( starts with RANGWIDE )

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[1]  Insert a Print Footer

                 A                 B                   C                  D                  E

  1 *---A macro to prompt the user for a PRINT FOOTER

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[1]  Insert a Print Header

                 A                 B                   C                  D                  E

  1 *---A macro to prompt the user for a PRINT HEADER

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro


[1]  Print Cells Content as Displayed

                 A                 B                   C                  D                  E

  1 *---A macro to print cells as displayed to the printer

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer at the upper leftmost cell of the print range

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[1]  Print Cells Content as Formulas

                 A                 B                   C                  D                  E

  1 *---A macro to print cell formulas to the printer

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer at the upper leftmost cell of the print range

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[1]  Print a Range

                 A                 B                   C                  D                  E

  1 *---A macro to print SELECTED RANGE

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer at the upper leftmost cell of the range

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[0]  Page Align

                 A                 B                   C                  D                  E

  1 *---A macro to ALIGN the paper before printing

  2 *---Use the /Range Name Label Down [ENTER] to define the \Z macro name

  3 *---Hold the [ALT] key and press [Z] to activate the macro


[7]  Print Form Letters from an Address Database

                 A                 B                   C                  D                  E

  1 *---A macro to print FORM LETTERS from an address database. The macro

  2     compensates for M.I. and missing COMPANY NAME.

  3 *---Type your form letter right to the LETTER range name, the macro

  4     will take care of the date, address, etc.

  5 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  6     range names in this column (starts with the \Z macro name)

  7 *---Place the highlight on the upper left cell of the labels list

  8 *---Hold the [ALT] key and press [Z] to activate the macro


[7]  Create One Across Mailing Labels from an Address Database

                 A                 B                   C                  D                  E

  1 *---A macro to print MAILING labels from an address database. The macro

  2     compensates for M.I. and missing COMPANY NAME.

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Place the highlight on the upper left cell of the labels list

  6 *---Hold the [ALT] key and press [Z] to activate the macro


Miscellaneous Macros

[5]  View the Range Inside the @AVG Formula

[5]  View the Range Inside the @COUNT Formula

[5]  View the Range Inside the @MAX Formula

[5]  View the Range Inside the @MIN Formula

[5]  View the Range Inside the @STD Formula

[5]  View the Range Inside the @SUM Formula

[5]  View the Range Inside the @VAR Formula

[5]  Grade Student's Scores

[8]  Link Cells to External Worksheets for Release 2.0/2.01

[4]  Link a Range (2.2 and Up)

[7]  Help Macro

[9]  Create a Menu Range

[5]  Notepad Macro

[4]  Make Formulas Error Proof   <==== Click here!

[4]  Display a Flashing Message in the Panel

[4]  Find Adjacent Duplicate Entries in a Column

[4]  Trapping an Error in a Loop

[8]  Key Pressed Recorder (Learn Macro for Lotus 1-2-3 2.0/2.01)

[9]  Word Processor with Search and Replace (1-2-3 2.0/2.01)

[9]  Calendar

[4]  Learn Macro

[6]  Document the Worksheet

[0]  View Data in the Worksheet

[1]  Use the Numeric Key Pad to Insert Data Along a Column

[1]  Use the Numeric Pad to Insert Data Across a Row

[6]  Labels Entry Macro

[6]  Combine Two Range in the Same Worksheet

[5]  Runkey Macro for Lotus 2.0/2.01

[7]  Write Check Amount in  Words

[10]  The Macro Manager


[5]  View the Range Inside the @AVG Formula

                 A                 B                   C                  D                  E

  1 *---A macro to VIEW the range to be @AVGed, the range is highlighted

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer on the cell containing the @AVG formula

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[5]  View the Range Inside the @COUNT Formula

                 A                 B                   C                  D                  E

  1 *---A macro to VIEW the range to be @COUNTed, the range is highlighted

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer on the cell containing the @COUNT formula

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[5]  View the Range Inside the @MAX Formula

                 A                 B                   C                  D                  E

  1 *---A macro to VIEW the range to be @MAXed, the range is highlighted

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer on the cell containing the @MAX formula

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[5]  View the Range Inside the @MIN Formula

                 A                 B                   C                  D                  E

  1 *---A macro to VIEW the range to be @MINed, the range is highlighted

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer on the cell containing the @MIN formula

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[5]  View the Range Inside the @STD Formula

                 A                 B                   C                  D                  E

  1 *---A macro to VIEW the range to be @STDrd, the range is highlighted

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer on the cell containing the @STD formula

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[5]  View the Range Inside the @SUM Formula

                 A                 B                   C                  D                  E

  1 *---A macro to VIEW the range to be @SUMmed, the range is highlighted

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer on the cell containing the @SUM formula

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[5]  View the Range Inside the @VAR Formula

                 A                 B                   C                  D                  E

  1 *---A macro to VIEW the range to be @VARianced, the range is highlighted

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Place the cell pointer on the cell containing the @VAR formula

  5 *---Hold the [ALT] key and press [Z] to activate the macro


[5]  Grade Student's Scores

                 A                 B                   C                  D                  E

  1 *---A macro to GRADE students scores (F = 0-55, D = 56-74, C = 75-84

  2     B = 85-94 and A = 95-100). To change it change the numbers in the

  3     gradesa111 range.

  4 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  5     range names in this column (starts with the \Z macro name)

  6 *---Point to the cell right to the upper cell of the grades list

  7 *---Hold the [ALT] key and press [Z] to activate the macro

  8         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  9                     IT WILL WORK IN LOTUS 2.0 AND UP


[8]  Link Cells to External Worksheets for Release 2.0/2.01

                A                   B                     C                       D

  1 *---LINK cells in worksheet to cells/ranges in other worksheets

  2 *---Combine this macro to any worksheet that needs to be linked to

  3     other worksheets. Combine it to an empty area of your

  4     worksheet and make sure that there will be enough empty rows to

  5     accommodate for the linking information table.

  6 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  7     range names in this column (starts with the \Z range name)

  8 *---Use the [ALT] key and press [Y] to ESTABLISH linking between the

  9     current cells to a cells in other worksheets.

 10 *---Use the [ALT] key and press [Z] to UPDATE all the linked cells/ranges

 11 *---After the worksheet containing this macro is saved the next

 12     time it will be retrieved the AUTO macro \0 will UPDATE all

 13     linked cell automatically.


[4]  Link a Range (2.2 and Up)

                 A                 B                   C                  D                  E

  1 *---A macro to LINK a range. Lotus 1-2-3 allows only cells linking, this

  2     macro links a whole range, it creates a range of cell linking

  3     formulas.

  4 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  5     range names in this column (starts with the \Z macro name)

  6 *---Place the cell pointer where you want the linked range

  7 *---Hold the [ALT] key and press [Z] to activate the macro

  8 *---Ranges should be entered as address ONLY!  ( For example: A1..D5 )

  9              * * *     A LOTUS 2.2 AND UP MACRO   * * *


[7]  Help Macro

                 A                 B                   C                  D                  E

  1 *---A macro to HELP WRITING MACROS and FORMULAS, allows you to

  2     CONTINUOUSLY write every Lotus function or command by POINTING to

  3     the syntax on display and SHOOTING (press RETURN). There is no more

  4     typing errors or a need to remember the exact syntax.

  5 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  6     range names in this column (starts with the \Z macro name)

  7 *---Hold the [ALT] key and press [Z] to activate the macro

  8                    THIS MACRO WORKS IN LOTUS 2.0 AND UP


[9]  Create a Menu Range

                 A                 B                   C                  D                  E

  1 *---A macro to TURN any 2-D or 3-D range of cells into an active

  2     MENU-RANGE

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Y macro name)

  5 *---Hold the [ALT] key and press [Y] to define the MENU RANGE. The MENU

  6     RANGE needs to be defined ONLY once.

  7 *---Hold the [ALT] key and press [Z] to USE the MENU-RANGE, or use

  8     {BRANCH MENURANG} inside or from a macro.


[5]  Notepad Macro

                 A                 B                   C                  D                  E

  1 *---A NOTEPAD MACRO that updates itself at end of work. Best utilized

  2     with the MACRO MANAGER macro (the MACROMGR.WK1 or the SMALLMGR.WK1

  3     macro library managers)


[4]  Make Formulas Error Proof

                 A                 B                   C                  D                  E

  1 *---A macro to make a formula ERROR PROOF. The macro alters the formula

  2     in the cell to return ZERO instead of ERR in case of error condition

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Place the cell pointer on the formula to be altered

  6 *---Hold the [ALT] key and press [Z] to activate the macro

  7 !

  8 !

  9 !

 10 \Z           {BREAKON}

 11 ERRPROOF     {WINDOWSOFF}{PANELOFF}{EDIT}{HOME}'~{LET temp016,@CELLPOINTER

                 ("contents")}~{EDIT}{HOME}{RIGHT}@IF(@ISERR({END}),0,

                 {temp016}){HOME}{DEL}~

 12 !

 13 temp016      @SIN(AA1)/@TAN(AB1)

This macro turns any formula to an immune error proof formula, in such a way, that the formula can never produce an @ERR, it will produce a "0" zero value instead. This macro uses the enveloping method to change the formula so it will return the correct answer as long as the formula is legal, where as when the original formula returned ERR, the upgraded formula returns zero. For example: if the current cell contains the @SIN(AA1)/@TAN(AB1) formula, which should return an ERR if the AB1 cell contains a zero value, the macro changes it to the @IF(@ISERR(@SIN(AA1)/@TAN(AB1)),0,@SIN(AA1)/@TAN(AB1) formula, which returns a zero instead.

The macro starts with the {WINDOWSOFF}{PANELOFF} commands which freeze the screen and panel display activity. Then the macro issues {LET temp016,@CELLPOINTER ("contents")}~ to copy the content of the current cell as a label to the B13 cell named [temp016]. Next it issues {EDIT} to enter to the EDIT mode and {HOME} to move the cursor to the beginning of the formula in the panel, which now displays:

          @SIN(AA1)/@TAN(AB1)

          ^

The "^" represents the cursor position. Then the macro writes the apostrophe "'"  to turn the formula into a label, issues the tilde "~" command, which is the same as ENTER, and writes it back to the current cell, which now contains a label instead of the formula. Next, the macro copies the current cell's content (the label) to the B13 cell named [temp016]. Now the macro again issues {EDIT}, {HOME} and {RIGHT} to enter the EDIT mode, and move the cursor to the second character of the label in the panel, which now displays:

          '@SIN(AA1)/@TAN(AB1)

          ^

Next the macro writes the "@IF(@ISERR(" text into the panel ,which shows:

          '@IF(@ISERR(@SIN(AA1)/@TAN(AB1)

Next the macro issues {END} moving the cursor to the end of the panel and then writes the "),0," text to the panel, which displays:

          '@IF(@ISERR(@SIN(AA1)/@TAN(AB1)),0,

Now the macro needs to insert the original formula after the comma, but how does the macro know what the original formula was? The macro copied the original formula to cell [temp016], therefore the macro issues the {temp016} routine command which injects the content of [temp016] into the panel right where the cursor is. Therefore the panel displays:

          '@IF(@ISERR(@SIN(AA1)/@TAN(AB1)),0,@SIN(AA1)/@TAN(AB1)

All that left to complete the formula is to write the closing parenthesis ")", delete the apostrophe and press ENTER. Therefore the macro issues {HOME}{DEL}, which delete the apostrophe "'" and turn the label back into a formula, which is now immune. Even if the original @SIN(A1)/@TAN(B1) formula had returned an ERR, the new formula returns zero

"0" instead. Note how the macro wrote the original formula twice to get a correct formula. We recorded the formula in [temp016] as a label, and then injected the formula into the panel using the {temp016} routine command. This macro is one of the finest examples of panel manipulating techniques that stretch Lotus's macro language to the limit.


[4]  Display a Flashing Message in the Panel

                 A                 B                   C                  D                  E

  1 *---A macro to present a flashing message in the panel area

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---Type your message right to the cell MESSAGE


[4]  Find Adjacent Duplicate Entries in a Column

                 A                 B                   C                  D                  E

  1 *---A macro to find DUPLICATE entries in a column. The macro handles

  2     numbers and labels.

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Point to the first entry cell in the column

  6 *---Hold the [ALT] key and press [Z] to activate the macro


[4]  Trapping an Error in a Loop

                 A                 B                   C                  D                  E

  1 *---A macro to TRAP errors in a loop WITHOUT breaking the loop.

  2     Let's say you want to combine 10 files to a worksheet, therefore

  3     endloop=10. If the drive door is open a message will appear.

  4     Close the drive door and try again. You can use this macro for other

  5     than file combining. Use your own ERROR ROUTINE and prompt message.

  6 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  7     range names in this column (starts with the \Z macro name)

  8 *---Hold the [ALT] key and press [Z] to activate the macro

  9 !


[8]  Key Pressed Recorder (Learn Macro for Lotus 1-2-3 2.0/2.01)

                 A                 B                   C                  D                  E

  1 *---A macro to RECORD and EXECUTE keystrokes as they are typed. The

  2     macro allows using LOTUS 1-2-3 and simultaneously to record

  3     your keystrokes into a macro that can be used later again.

  4 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  5     range names in this column (starts with the \Z macro name)

  6 *---Hold the [ALT] key and press [Z] to activate the macro. Start

  7     working. Your keystrokes will be recorded and placed out off the

  8     work area in the range macro1. Use Ctrl-Break to Quit.


[9]  Word Processor with Search and Replace (1-2-3 2.0/2.01)

                A                 B            C                    D                     E

  1 *---A WORD PROCESSOR macro with a full featured SEARCH & REPLACE

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---Press {ESC} to return to main menu

  6 !


[9]  Calendar

                 A                 B               C              D              E           F

  1 *---A Calendar macro. The screen displays four months at a time

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

         A    B    C   D    E    F    G    H    I    J     K    L    M     N    O    P     Q

 29 !    Sun MonTue Wed Thu Fri Sat     Sun Mon Tue Wed Thu Fri Sat

 30 !

 31 !           January 1993                    February 1993

 32 !   ~~~~~~~~~~~~~~~~~~~~~~~~~~~    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 33 !                         1   2           1   2   3   4   5   6

 34 !      3  4   5   6   7   8   9       7   8   9  10  11  12  13

 35 !     10 11  12  13  14  15  16      14  15  16  17  18  19  20

 36 !     17 18  19  20  21  22  23      21  22  23  24  25  26  27

 37 !     24 25  26  27  28  29  30      28

 38 !     31

 39 !

 40 !           March 1993                      April 1993

 41 !    ~~~~~~~~~~~~~~~~~~~~~~~~~~~    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 42 !         1   2   3   4   5   6                       1   2   3

 43 !      7  8   9  10  11  12  13       4   5   6   7   8   9  10

 44 !     14 15  16  17  18  19  20      11  12  13  14  15  16  17

 45 !     21 22  23  24  25  26  27      18  19  20  21  22  23  24

 46 !     28 29  30  31                  25  26  27  28  29  30

 47 !

 48 !    __________________________     ____________________________


[4]  Learn Macro

                 A                 B                   C                  D                  E

  1 *---A macro to start LEARN mode and range

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---Expand and highlight the LEARN range and press [ENTER]

  6 *---Press [ALT-F5] and press [RETURN] to end LEARN

  7 !

  8          * * *     A LOTUS 2.2/2.3/2.4 MACRO ONLY    * * *

  9 !


[6]  Document the Worksheet

                 A                 B                   C                  D                  E

  1 *---A macro to DOCUMENT the spreadsheet

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !


[0]  View Data in the Worksheet

                 A                 B                   C                  D                  E

  1 *---A macro to let you move the pointer across the worksheet to view

  2     your data and then come back to the current cell

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to start the macro

  6 *---Move around the worksheet to watch your data, when you are

  7     finished press the [ESC] key TWICE

  8 !


[1]  Use the Numeric Key Pad to Insert Data Along a Column

                 A                 B                   C                  D                  E

  1 *---Use the numeric key pad to enter data in a column.

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---Press the NUMLOCK key then place the cell pointer at the first

  6     cell and start data entry, every time you press the ENTER key the

  7     cell pointer moves down one cell. Use [Ctrl Break] to quit.

  8 !


[1]  Use the Numeric Pad to Insert Data Across a Row

                 A                 B                   C                  D                  E

  1 *---Use the numeric key pad to enter data in a row.

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 *---Press the NUMLOCK key then place the cell pointer at the first

  6     cell and start data entry, every time you press the ENTER key the

  7     cell pointer moves right one cell. Use [Ctrl Break] to quit.

  8 !


[6]  Labels Entry Macro

                 A                 B                   C                  D                  E

  1 *---A macro to always accept data as a label. If you type a number the

  2     macro automatically adds "'" to change it into a label.

  3 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  4     range names in this column (starts with the \Z macro name)

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 *---The Next_sheet and Previous_sheet are only for RELEASE 3 and up

  7 !

  8         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  9                     IT WILL WORK IN LOTUS 2.0 AND UP


[6]  Combine Two Range in the Same Worksheet

                 A                B           C            D           E           F       G

  1 *---A macro to COMBINE two ranges inside the worksheet. This macro

  2     works exactly as file extract and combined together. This macro uses

  3     the disk, therefore the disk should not be write protected.

  4 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  5     range names in this column (starts with the \Z macro name)

  6 *---Place the cell pointer on the upper left cell of the range to

  7     changed.

  8 *---Hold the [ALT] key and press [Z] to activate the macro

  9                     IT WILL WORK IN LOTUS 2.0 AND UP


[5]  Runkey Macro for Lotus 2.0/2.01

                 A                 B                   C                  D                  E

  1 *---A macro to ACTIVATE any macro/routine without using the [ALT] key,

  2     all range names are displayed on screen and by pointing and pressing

  3     the [ENTER] key the macro is activated (like RUNKEY in symphony)

  4 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  5     range names in this column (starts with the \Z macro name)

  6 *---Hold the [ALT] key and press [Z] to activate the macro

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                     IT WILL WORK IN LOTUS 2.0 AND UP

  9 !


[7]  Write Check Amount in  Words

                 A                 B                   C                  D                  E

  1 *---A macro to write CHECK'S amount in WORDS up to $99,999,999

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Position the cell pointer where you want the amount to be written

  5 *---Hold the [ALT] key and press [Z] to activate the macro

  6 !

  7         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  8                     IT WILL WORK IN LOTUS 2.0 AND UP

  9 !


[10]  The Macro Manager

                 A                 B                   C                  D                  E

  1 *---A MACRO MANAGER to activate any macro without first combining it

  2     to the worksheet. The macro manager combines the macro, defines

  3     the range names and activates the macro. When the macro is done

  4     the MACRO MANAGER deletes the range names and erases the macro.

  5 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  6     range names in this column (starts with the \Y name)

  7 *---Place the cell pointer at upper left cell of the range to be

  8     processed

  9 *---Hold the [ALT] key and press [Y] to activate the macro


Appendix A - The Super Macro Library for Lotus 1-2-3

How to Order the SUPER MACRO LIBRARY for LOTUS 1-2-3?

The SUPER MACRO LIBRARY for LOTUS 1-2-3 is a large collection of more than 250 *.WK* macros that enhance and increase the power and efficiency of Lotus 1-2-3 versions 2.0 and up, and an equally large collection of over 220 *.MLB library macros for Lotus 2.2/2.3/2.4. Most of the macros are far more powerful and flexible than those found in other books, magazines and commercially available packages which you already know it if you looked at the macros in Super Power. All these macros are just few key strokes away using our revolutionary Macro Library Managers (macro themselves). You need to combine only the Macro Library Manager to the worksheet, and then you use the main menu of the macro manager to start the macros directly from the disk using Point and Shoot.

The Macro Library Manager features a Quick Run mode, which displays a full screen list of all the macros in the default directory. You point to the macro name and press ENTER to start it. A Search by Keyword query allows you to find all the macros matching a keyword. When you press ENTER to start the macro, the manager automatically combines the macro, defines all the necessary range names and starts the macro. When you finish working with the macro, the manager erases it and deletes all its range names and returns to the main menu to allow you to start another macro. Of course, you can still use all the macros manually without the macro managers.

The *.MLB macros support the MACROMGR.ADN macro library manager add-in, which comes with Lotus 1-2-3 versions 2.2/2.3/2.4 and the third dimension of Lotus 1-2-3 versions 3.0/3.1/3.1+ /3.4 and Lotus 123W version 1.0/1.0a/1.1 for Windows 3/3.1. Some of the macros in this package contain new features which become available with the introduction of the new releases 2.2/2.3/2.4 to the users of Lotus 1-2-3 versions 2.0/2.01 which can now enjoy, full featured Search and Replace, and pseudo Linking.

This package contains seven Macro Managers for different cases:

•	The SMALLMGR.WK1, the last macro in Super Power is about 6-7 Kbytes in

size and is intended for use in memory shortage situations. This macro manager displays the full files directory list, and allows you to use point and shoot to activate the macro you need.

•	The MACROMGR.WK1 file is almost 26 Kbytes in size. This manager has a

Quick-Run mode, like the SMALLMGR.WK1, as well as a full featured Search by Keyword working mode, which performs a query on the files list database to look for the matched macro description. Just enter the desired string and the manager will find all the macro descriptions containing this string anywhere in the description. To move, you use the UP and DOWN direction keys. To activate the matched macro, you press ENTER. This special way to use the Lotus query to activate a macro is based on a discovery made by the developer of this package and was published in the PC-MAGAZINE (SPREADSHEET CLINICS) July 1988 Vol 13 page 414.

•	The SMALLMLB.WK1 and the MACROMLB.WK1 are equivalent to the previous

two managers but activate the *.MLB macros instead of the *.WK1 macros, and make use of the MACROMGR.ADN add-in which you received with Lotus 1-2-3 version 2.2/2.3/2.4.

•	The SMALLMG3.WK3 and the MACROMG3.WK3 are equivalent to the previous

managers and activate the *.WK* macros, but they use the third dimension of the 3-D releases to keep the activated macro in a separate sheet.

•	The MEW_MNGR.MLB manager is a completely new type of manager, which

resides in the memory (as do all *.MLB macros) and allows you to activate the *.MLB macros in the disk using point and shoot. This is a real breakthrough in the field of *.MLB macro managers and only K.I.T.A.L. Software offers this unique approach for macro library handling. In this package we include a "compiler" macro, COMPILE.MLB that can create a manager from a simple column list of macro names which you prepare. Create as many managers as you like, it's easy. You can always use the NEW_MNGR.MLB manager, which allows you to activate all the *.MLB macros listed in the NEW_MNGR.PRN ASCII file. Or use partial lists to create smaller managers and save memory when you run short.

Unfortunately, the MACROMGR.ADN add-in which comes with LOTUS 2.2/2.3/2.4 is limited, and does not allow the use of all the commands and techniques that are allowed in the worksheet. Therefore the number of *.MLB macros is lower than the *.WK1 macros, and you still need to use some of the *.WK* macros that we could not make work as *.MLB macros.

All *.WK1 macros have been checked and adapted to work with the 3-D releases of Lotus 1-2-3. Few *.WK3 macros are included in the package and can be used only with the 3-D releases of Lotus 1-2-3. Many macros specifically state that they detect which Lotus release you use. These macros use the @INFO("release") function to check if you are using a 3-D release or a 2-D release of Lotus 1-2-3. If you are using a 3-D release, the macro also exploits the third dimension. Many other macros even if it is not specifically noted, also support the third dimension when they use the Lotus natural functions (like: copy, delete, move etc.). Because the 3-D releases do not support the MACROMGR.ADN add-in supplied with Lotus 2.2/2.3 and 2.4, the *.MLB macros cannot be used with a 3-D release. However, the 3-D releases can have multiple sheets and multiple files in memory, which allows us to keep macros and data in separate sheets.

In the Super Macro Library, we have minimized the risk that two or more macros may contain the same range names. Every macro containing more than one range name was numbered by a three digit number attached to the range name. For example, a previously "loop" range name now may be "loop001" in one macro but "loop123" in other macros. The result is a foolproof package, which allows you to create large macro libraries without the danger of a range names conflict.

If you already collected macros or own another macro library, you can change them fairly simply to work with the macro managers and enjoy the new possibilities.


Appendix B - The Scientific and Engineering Tool for Lotus 1-2-3

The SCIENTIFIC and ENGINEERING TOOL for LOTUS 1-2-3 is a completely menu driven solver and calculator which is included in the SUPER MACRO LIBRARY for LOTUS 1-2-3 as a bonus pack. It features:

1.   simple arithmetic

2.   functions editing, programming and calculations

3.   physical units definition and conversion

4.   integration and differentiation of analytic functions and data tables

5.   editing and calculating statistic functions on lists of numbers

6.   frequency distribution and normal distribution of data

7.   curve fitting (Polynomial, Exponent, Powers, Linear)

8.   root finding of non-linear one dimension equations

9.   matrix and vector operations

10. simultaneous linear equation solver of up to 70 unknowns

This version works correctly inside all versions of Lotus 1-2-3, 2.0 and up, it does not work in Lotus 1-2-3 version 1.0 and 1.0a for Windows 3.0/3.1. The two previous Lotus releases for windows lost the Horizontal and the Vertical division of the screen each time the /GV or the F10 was pressed. However it works fine with Lotus 1-2-3 version 1.1 for Windows 3.0/3.1.

Statistical functions and any other functions and units are user programmable. This program can become your custom scientific handbook. You can program thousands of functions: from one variable functions to seven variable functions. If you split functions of higher numbers of variables to groups of functions of seven variables and less, you can solve more complicated problems.

Integration and differentiation is done on functions inserted from the keyboard or on data tables imported as text files. The program enables a graph of the function before and after integration or differentiation and allows you to save the numeric data tables and graphs for later use.

The distribution functions are build-in with the program and work on a list of numbers inserted from the keyboard or imported as a text file. These function produces a graph of the distribution and allows you to save the graph as a .PIC file and the data as a text file for later use.

The program already includes a large number of commonly used functions and units. You can edit them or add as much as  you like. You can use this program to keep track of your worksheets, and by using a keyword instead of a function, you can activate a whole worksheet not just a function.


How to Order the Super Macro Library for Lotus 1-2-3?

Save Time and Ensure Accuracy!

Super Macro Library for Lotus 1-2-3

$70 per copy without a proof of purchase of Super Power

($55 per copy with a proof of purchase of Super Power)

Contains the latest version of the Super Macro Library for Lotus 1-2-3 which includes all the macros in Super Power  plus over 220 *.MLB macros, six other macro managers and the Scientific & Engineering Tool for Lotus 1-2-3, a menu driven solver and calculator, total of 1.6 Mbyte of pure macro code .

Please send _____ copy(ies) of the Super Macro Library for Lotus 1-2-3.

Check disk format: 360K  5.25" (X 3) __ , 1.2M 5.25" __ , 720K 3.5" (X2) __ ,

                             1.44M 3.5" __

Subtotal   $ _______

Shipping & Handling ($5 per item)   $ _______

TOTAL    $ _______

Attach your check (US currency only) and mail to K.I.T.A.L. Software. P.O. Box 748 Karmiel 20100  ISRAEL.

For fast response call (972)-4-987255 and order TODAY.

Please call at the evenings ISRAEL time.

All prices subject to change without notice


Important Note

Sometimes it may seem that there are unnecessary commands or repetitions of the same command in the code of the macros in Super Power; but from our inquiries, they are needed to cover the many versions of Lotus 1-2-3. For the macro to correctly work they are needed. In every Lotus release, there are some quirks and incompatibilities to the previous versions and different releases behave differently in the same situation. There are many examples of this. Sometimes when we look in our macros, we may forget why a specific command is there, but we are very careful not to change it before we can extensively test it in all the versions of Lotus 1-2-3.


Start Here

A Word for Shareware Users

This message is not part of the commercial book

We want to make it clear that this file is only a demo of the full featured book Super Power Macros, Techniques and Secret for Lotus 1-2-3. At the same time we want to make it absolutely clear that the SUPER MACRO LIBRARY for LOTUS 1-2-3 that you received as a shareware package IS NOT A DEMO it is a full featured package that is identical to the commercial package and it is also identical to the registered version. This is why we do not send a registered version when you register, we trust you to pay for it if you are using it.

However the full featured book, Super Power Macros, Techniques and Secret for Lotus 1-2-3, is a COMMERCIAL product.

Even if you are not a registered user you can purchase Super Power, but as you will later see, you will receive the semiautomatic book, which does not allow you to launch Lotus 1-2-3 and the macro you currently study from inside Super Power. The reason is simple: you do not legally own the SUPER MACRO LIBRARY for LOTUS 1-2-3, so legally you cannot read a commercial book and use the macros from an unregistered program, because then you are using it instead of evaluating it.

The macro library is an application with many more macros and managers than Super Power features, it is not a tutorial aid, and it was in the market long before Super Power. Super Power contains the complete list of every macro, so you can always key it into Lotus 1-2-3 to experiment with it.

When an owner of the semiautomatic book registers or purchases the SUPER MACRO LIBRARY for LOTUS 1-2-3, we send him an automatic book that allows him to launch Lotus 1-2-3 and the macro that he currently studies from inside Super Power using a click of the mouse on the macro name. The automatic book IS NOT FOR SALE it is our way to say thank you!

If you order the book when you send your registration, you are also entitled to a discount on the price of Super Power. See the ORDER.TXT and the INVOICE.TXT files for prices and shipping rates.

The rest of the book refers to commercial users that nornally pay more for the (identical) commercial version of the SUPER MACRO LIBRARY for LOTUS 1-2-3 because it is more expensive to market.

This package already includes the AUTO123.WK1 and the SUPER.BAT files mentioned in the next pages. Edit the SUPER.BAT according to your default directory.

Because you have the shareware version of the SUPER MACRO LIBRARY for LOTUS 1-2-3, you can use the SWITCH.WK1 macro from the library, see details in the next pages.

This demo is a skeleton of Super Power that contains all the headings of the topics in Super Power except a few complete topics to allow you to see the spectrum of macros and techniques featured in Super Power. The first is Make Formulas Error Proof, a fourth [4] level of difficulty macro. The second is Insert Partial Columns and Rows (Ranges), a ninth [9] level of difficulty macro. The third is Change All the Labels in a Range to Lowercase Form, a ninth [9] level of difficulty macro. The last is Moving Between Sheets in Straight Lines, one of dozens of advanced techniques included and explained in Super Power.

These topics contain the complete analysis and explanation of the macro codes which will allow you to understand how Super Power can help you master the Lotus macro language.

Before jumping to these topics we suggest that you finish reading this topic.

End of -- A Word for Shareware Users

Super Power contains the equivalent of 1000 pages of a printed paper book. It is not just another book about the Lotus macro language it is completely different. See the Forward and Welcome for details.

This electronic book named SUPER.HLP comes in the form of a Windows 3.1 help file. It was written using Word for Windows 2.0c and compiled with the Windows Help Compiler version 3.1 from Microsoft.

For clarity, we used different fonts and different colors for macro codes and written text. We used only fonts that come with Windows 3.1 to assure that any one who has Windows 3.1 can view Super Power correctly. For text we used Times New Roman size 11 and for macro code with used Courier New sizes 8 and 10. For headings we used Univers 11,14,18.

Use the maximum window size to correctly view Super Power.

Because this file is a Windows help file you can use and read it as any other help file under Windows. Therefore a green colored and underlined text indicates a jump to another topic. Just click on it with the mouse to jump to the relevant topic. The Content, Search, Back, History, Browse buttons, etc. are fully operational.

For beginners, the Search button can be very useful to locate all the macros with the same level of difficulty for studying. For example, if you click on the Search button on the top of the screen and type [1] as the search string (including the brackets), windows will list all the macros of [1] level of difficulty as displayed here:

Super Power comes in two forms:

1.   The first, the standard form, is a semiautomatic version of Super Power just as a

      paper bound book. You cannot activate a macro from inside a paper bound book, can

      you?

2.   The second is a dynamic version of Super Power which allows you to start Lotus

      and the macros that you currently read just by clicking with the mouse on the green

      underlined macro name.

The second form IS NOT FOR SALE it is a bonus for readers that also purchased the SUPER MACRO LIBRARY for LOTUS 1-2-3, which allows them to use and enjoy the macros featured in Super Power and included in the SUPER MACRO LIBRARY for LOTUS 1-2-3, which also saves them many hours of keying the code into Lotus 1-2-3.

If you decide to purchase the SUPER MACRO LIBRARY for LOTUS 1-2-3 then you will also receive the dynamic version of Super Power that allows you to launch Lotus and the macro you currently read just by clicking with the mouse on the macro name, as you will see in a moment. The following is part of the code of the SWITCH.WK1 macro

                A                  B                    C                  D                 E

  1 *---A macro to switch 3-D and 2-D ranges places

  2 *---Use the /Range Name Label Right [End] [Down] [ENTER] to define the

  3     range names in this column (starts with the \Z macro name)

  4 *---Hold the [ALT] key and press [Z] to activate the macro

  5 !

  6         THIS MACRO AUTOMATICALLY DETECTS THE LOTUS 1-2-3 RELEASE

  7                     IT WILL WORK IN LOTUS 2.0 AND UP

  8 !

  9 !

 10 \Z              {BREAKON}

 11 SWITCH          {LET rel209,@INFO("release")}~{RECALC loc209}

                    {RECALC form209}

 12 form209         {LET here1209,@CELLPOINTER("coord")}~

 13 !               {WINDOWSOFF}{PANELOFF}/RNCFirst range ?~~/RND

                    First range ?~/RNC{PANELON}First range ?~{WINDOWSON}

When you click on the green underlined SWITCH text, windows starts Lotus 123W for Windows and retrieves the SWITCH.WK1 file if it is located in the Lotus default directory, so you can experiment with the actual file while you are reading the book.

To try this now:

1.   Quit Super Power.

2.   Copy or move the SUPER.HLP file to the Lotus 123W directory.

3.   Create any file named SWITCH.WK1 and place it in the Lotus default directory. If

      you are not sure which is the default directory, start 123W and press / Worksheet

      Global Default Directory ... to see the current default directory.

4.   Start Super Power again (double click on the SUPER.HLP file name).

5.   Click on the green underlined SWITCH text.

In no time you will see Lotus 123W with the SWITCH.WK1 file loaded. When you quit Lotus 1-2-3 you return to Super Power.

If you do not own Lotus 123W but own a DOS version of Lotus 1-2-3 you can still launch the macros but it demands more steps:

1.   Quit Super Power.

2.   Copy or move the SUPER.HLP file to the Lotus 123 directory.

3.   Create any file named SWITCH.WK1 and place it in the Lotus default directory. If

      you are not sure which is the default directory, start 123W and press / Worksheet

      Global Default Directory ... to see the current default directory.

4.   Use a pure ASCII editor such as EDLIN to create a batch file, which contains the

      following command line:

               ECHO %1>C:\MACROS\SUPER.PRN

      The C:\MACROS is the Lotus default directory. If your default directory is different,

      types it instead of the C:\MACROS. For example, if your default directory is

      C:\123\DATA, the batch file should include:

               ECHO %1>C:\123\DATA\SUPER.PRN

5.   Save the file with the SUPER.BAT file name in the Lotus 1-2-3 directory.

6.   Create the following Lotus macro:

         A              B              C              D

               1      \0      {GOTO}B3~/FITSUPER~

               2              /FR

               3

               4              ~

7.   Type the macro in the A1..B4 range as you see here.

8.   Place the cell pointer on the A1 cell which contains the \0 (backslash and zero) and

      issue: / Range Name Label Right and press ENTER to assign the [\0] range name to

      the B1 cell.

9.   Save it to the Lotus default directory and name it as AUTO123.WK1.

10. Start Super Power again (from Windows, double click on the SUPER.HLP file

      name).

11. Click on the green underlined \Z text.

In no time you will see Lotus 1-2-3 with the SWITCH.WK1 file loaded. When you quit Lotus 1-2-3 you return to Super Power.

The automatic book comes with the AUTO123.WK1 and the SUPER.BAT files. You need to edit the SUPER.BAT according to your default directory.


About The Author

Israel Kehaty received his M.Sc. in aeronautical engineering from the Technion Institute in ISRAEL in 1972 cum-laude. He is currently working as a Senior Engineer for one of the largest companies in ISRAEL. Israel has 20 years of experience working with computers for scientific and engineering applications. Since 1986 he has developed applications for IBM PC's and he is the sole developer of THE ULTIMATE SPRING DESIGNER, an expert system for mechanical spring design. In 1986 during his sabbatical leave in the Oregon state university, Israel has gotten acquainted with Lotus 1-2-3. Since then he is using 1-2-3 as a scientific and engineering tool for his engineering work and develops general macros as a hobby. Israel is currently a Lotus Authorized Consultant (LAC) for the Israeli branch of Lotus Company. He has developed the SUPER MACRO LIBRARY for LOTUS 1-2-3 on which Super Power is based.


Legal Matters

Copyright © 1993 by Israel Kehaty. All Rights Reserved.

Copyright © 1993 by K.I.T.A.L. Software. All Rights Reserved.

All Rights Reserved. Printed in the United States of America and ISRAEL. No part of Super Power may be used or reproduced in any form or by any means, or stored as a database or retrieval system, without the prior written permission of Israel Kehaty, except in the case of brief quotation embodied in critical articles and reviews. Making copies of any part of Super Power for any purpose other than your own personal use is a violation of United States and ISRAEL copyright laws and the international treaties.

Important: Because in general (but not guarantied) these macros should work in other Lotus 1-2-3 compatible spreadsheets such as QUATTRO and QUATTRO PRO from Borland International Company but not limited to, trying to modify these macros and distribute then as originals using minor changes like using QUATTRO-PRO's MENU EQUIVALENT commands instead of the keyboard macro keys, is an infringement of K.I.T.A.L. Software's copyright. K.I.T.A.L. Software already created and reserves the copyrights to such versions of the macros listed in Super Power which will be available to customers in the future.

SUPER POWER (THIS BOOK) AND THE ACCOMPANYING SOFTWARE IS WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, RESPECTING THE CONTENTS OF SUPER POWER AND THE ACCOMPANYING SOFTWARE, INCLUDING BUT NOT LIMITED TO IMPLIED WARRANTIES FOR THE BOOK'S QUALITY, PERFORMANCE, MERCHANABILITY, OR FITNESS FOR ANY PARTICULAR PURPOSE. NEITHER ISRAEL KEHATY NOR K.I.T.A.L. SOFTWARE NOR ITS DEALERS OR DISTRIBUTORS SHALL BE LIABLE TO THE PURCHASER OR ANY OTHER PERSON OR ENTITY WITH RESPECT TO ANY LIABILITY, LOSS, OR DAMAGE CAUSED OR ALLEGED TO BE CAUSED DIRECTLY OR INDIRECTLY BY SUPER POWER AND/OR ITS ACCOMPANYING SOFTWARE.

If you purchased Super Power you are allowed to key the macro codes from Super Power into Lotus 1-2-3 and save them as Lotus files for your personal use ONLY. You are NOT ALLOWED to distribute them or use them in commercial applications that you develop and sell. The macros listed in Super Power are commercial macros copyrighted by K.I.T.A.L. Software and used in Super Power with K.I.T.A.L.'s permission.

You MUST treat this electronic book the same way you treat a paper bound book. You can use it only on ONE CPU and on ONE VIEWING MACHINE at the same time. Therefore you cannot install it on ANY KIND of a network without purchasing a site license and you cannot install it on more than one personal computer.

If you value your time and want the full power of K.I.T.A.L.'s macro library, you can use the order form for the SUPER MACRO LIBRARY for LOTUS 1-2-3 to order it directly from K.I.T.A.L. Software, instead of investing your valuable time keying the macros into Lotus 1-2-3.

Trademark Acknowledgment

Lotus and 1-2-3 are registered trademarks of Lotus Development Corporation.

QUATTRO and QUATTRO PRO are registered trademarks of Borland International Company. Windows and Help Compiler are registered trademarks of Microsoft Corporation.


{BREAKON}

Almost all the macros in Super Power start with the do nothing {BREAKON} macro command in the B10 cell. The reason is connected to the way the macro manager works. It always looks for the [\Z] range name to assign the range name for the combined macro. Often the macro loops back to the beginning of the macro code. If the loop is explicitly to the [\Z] range name, you will not be able to change this name to [\A] for example. Therefore the loop is always to the macro name itself such as {BRANCH LOWRCASE} instead of {BRANCH \Z}. Sometimes you may see the {BREAKON} macro command twice and it concern the way the macro is used as an *.MLB macro in the SUPER MACRO LIBRARY.