Printer Friendly

How macro menus enhance Lotus 1-2-3.

How macro menus enhance Lotus 1-2-3

At our lab, we have used Lotus macros to simplify the evaluation of data obtained from our laboratory information system. In a recent article in this department, (1) I showed how to design a worksheet template using Lotus macros. The useful macro is a mini-program in which to store keystrokes and Lotus commands for speedy playback. Attaining proficiency in writing them, however, is just the beginning. Stringing them together in menus accelerates the process even more.

Retrieving the TAT template file described in the previous article automatically invokes a macro. This auto-executing macro displays our menu, of which the setup will be explained later in this article. The main menu screen display is shown in Figure I.

The presence of the CMD status indicator in the center along the bottom of the screen tells you that a macro is working. The MENU indicator at upper right indicates menu mode. Individual menu items are listed across the top.

Pressing the right or left arrow key moves the cursor to the next or previous item. Just as in Lotus command menus, descriptions are displayed below the menu items. The user selects an item by one of two methods: placing the cursor over it and pressing <ENTER> or typing the first letter of the word that defines the item.

Figure II represents a portion of the worksheet macro section. Column A is reserved for descriptive information. The first column shown, column B, lists macro names. The macros themselves begin in column C. For example, the name of the first macro, \O, is in cell B1; its macro, {BRANCH \M}, is in cell C1. * Magic macro. Assigning the special name \O to the auto-executing macro commands Lotus to start it when the worksheet is retrieved. This macro is useful for one's coworkers because the selection choices are automatically displayed. Another advantage is that unlike the names of regular macros, which consist of only one letter, the descriptive names used for macro menus identify the task to be performed.

The auto-executing macro can be programmed to do anything you wish. After the template has been invoked, the auto-executing macro directly branches to another macro by means of the following command: {BRANCH \M}. The macro name is \M. The \M macro starts in cell C3. The macro is:




This macro has several consecutive functions. It clears previously set titles (titles set in stationary rows, columns, or both), goes to the cell named START (a cell in the data section of the worksheet, described in the aforementioned "Computer Dialog" article), moves down one cell, sets the titles there, and branches back to the main macro menu at the cell location named MAINMENU. * Constructing the menu. The special macro command to start a menu is {MENUBRANCH menuname}. This command - which must include the cell coordinates or range name where the menu is located in the worksheet - executes the menu command at the named location.

The items from which to select are placed in one to eight consecutive columns. Each column corresponds to one item of the menu except the last column, which remains empty. Upon identifying the following macro command - {MENUBRANCH menuname} - Lotus finds "menuname" and reads across until it locates the empty column. In Figure II, column I is the last item. Column J, not shown in the figure, is empty.

Each menu item occupies at least three vertical rows. The first row contains the item name. Each item within the menu should begin with a different letter of the alphabet.

The description, in the second vertical row of each entry block, can consist of no more than 80 characters (the width of the screen). It is displayed under each item when that item is selected. The third row starts the program command sequence. As with all Lotus macros, this macro continues down the column until it reaches an empty cell or a "go to" command (either BRANCH or MENUBRANCH).

When naming macro menus, I use range names rather than cell coordinates. The first selection item, in the upper left cell of the menu, is given the range name for the entire menu. It is not necessary to assign a range name to the entire block of information. Therefore, cell C7, containing "Import," is given the range name MAINMENU.

This macro can also be called up by pressing <ALT>M or by selecting the menu item. To keep my worksheets consistent, I name all main macro menus \M. * Main menu options. From the main menu, you may select one of the following commands:

* Import (import ASCII file);

* ModTime (modify time format);

* CalcTime (copy calculations);

* DataSort (sort data);

* Summary (display or print report);

* View (display documentation); and

* Quit (quit macro menu).

The first selection item, in cell C7, is Import.

The description in cell C8 is "Import ASCII file." Cell C9 starts the macro command sequence. The command /FDA: changes from the current file directory to that of the A: drive. The next command - /FINfilename.prn| -imports the file filename.prn. The last command, {BRANCH \M}, returns to the main menu.

After importing the file, you will have to modify the raw data. Macros for the selection items ModTime, CalcTime, and Summary are explained in the September MLO article.

A menu item - the DataSort selection item, for example - can be set up to reference another menu. For our turaround time worksheet, we classify the data many times on either one or two fields.

The DataSort item "Select sort level" starts in cell F7. The command, occupying cell F9, is:


SORTMENU, which begins in cell C15, contains four selection items:

* Primary (sort on one field);

* Secondary (sort on two fields);

* Menu (return to main menu); and

* Quit (quit menus).

The primary and secondary sort macros reduce the number of keystrokes required to perform a sort from 24 to 4.

Adding Menu and Quit to all menus is a useful technique. After defining them in the main menu, copy them to the last columns of each menu. The Menu description is Return to MainMenu. The macro command is {BRANCH \M}. The Quit description is Quit the menu; the macro command is {QUIT}. * Documentation is key. I have learned from experience to document macros. If I don't, sometimes I forget how they work!

The "View" selection item is used as a help screen. This item clears the titles, goes to home (cell A1), goes to the cell named View (A5), and sets the horizontal title at this point. In this macro, the command {?} after /WTH (in H10) waits for <ENTER> before calling up the main meny again.

This article, in conjunction with the one published in MLO in September 1989, is a good start for your macro library. You'll find that learning the capabilities of this application never ends. The combinations are challenging, rewarding, and limited only by your imagination. (1.) Scheele, K. Evaluating LIS turnaround times with Lotus 1-2-3. MLO 21(9): 73-75, September 1989.

General references:

Cobb, D. "Douglas Cobb's 1-2-3 Handbook: The Complete Guide for Power Users." New York, Bantam Books, December 1986.

"References Manual, Release 2.01." Cambridge, Mass., Lotus Development Corp., 1986.

"Lotus Computing for Managers and Professionals." Cambridge, Mass., Lotus Development Corp., published monthly.

"Using 1-2-3 Special Edition." Carmel, Ind., QUE Corp., 1987.

PHOTO : Figure I Main menu screen display Macro menu items, which run across the top of the screen work like Lotus menus.

PHOTO : Figure II Worksheet layout for macro menu
COPYRIGHT 1990 Nelson Publishing
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 1990 Gale, Cengage Learning. All rights reserved.

Article Details
Printer friendly Cite/link Email Feedback
Author:Scheele, Kathleen
Publication:Medical Laboratory Observer
Date:Jan 1, 1990
Previous Article:Student health lab: a different MT experience.
Next Article:Bush budget for '91 again hits clinical labs.

Related Articles
Workload recording with Lotus 1-2-3.
Saving time with combined microcomputer applications.
Using spreadsheet macros to minimize labor-intensive analysis.
Saving time with combined microcomputer applications.
Impress your inspectors with linearity graphs.
A simple workload recording system that your entire staff can use.
Creating Levey-Jennings charts on a PC.
A spreadsheet system for managing workload data.
Templates for generating Levey-Jennings and Westgard QC charts.
Lotus 1-2-3 data entry made easy with a macro.

Terms of use | Copyright © 2017 Farlex, Inc. | Feedback | For webmasters