In this article, we'll take a look at a few simple macros that you can create. All you'll need is your 95LX and a few minutes.
To show you how easy it is to create a Lotus macro, let's suppose that you wanted to change the format for a range of numbers to the currency format, with two numbers after the decimal point. To do this in 1-2-3, you'd press the following keys: (MENU) Range Format Currency 2 (ENTER), and then type in the range (e.g. A1..D5), or use the arrow keys to highlight the range. Finally you would press (ENTER). You can just as easily create a Lotus macro for this keystroke sequence.
How to Create Lotus Keystroke Macros
The process for creating a Lotus keystroke macro is easier to do than it is to explain.
Lotus stores its macros in empty cells, so the first thing you have to do is move the cursor to a spot in your worksheet that won't be used for anything else. Then type in an apostrophe ('), the keystrokes you want, and then press (ENTER). For example, to create a macro to format a range of cells to display two places after the decimal point in currency (e.g. 25.98), you would move to an empty cell (say, D1), type in '/rfc2~ and press (ENTER). You've created a keystroke macro -- that's all there is to it. (The keystrokes '/rfc2~ are called the "label." Note the leading apostrophe. Without this, you'd activate the 1-2-3 menu instead of writing a keystroke macro. Also note that the symbol in the macro language for (MENU) is the foreslash (/) and the symbol for (ENTER) is the tilde (~).)
There is also a macro Learn mode that lets Lotus record your keystrokes into macro instructions. For more on this feature, see page 13-27 of the HP 95LX User's Guide.
You can access this macro by pressing (ALT)-(F3) (Macro to run:), type in the cell where the macro is located (i.e. D1), and press (ENTER). 1-2-3 will read the macro in D1 and play back the keystrokes as if you had just typed them in. The message "Range to format: A1..A1" will appear on the prompt line. (The A1..A1 part of the message will vary depending on the current location of the cursor.) You would then type in the range and press (ENTER).
You can run a keystroke macro more easily by doing the following. First, give the cell in which the macro is located a special name. To do this, press (MENU) Range Name Create \F (ENTER) D1 (ENTER), where \F is the name you've assigned to the cell, and D1 is the location of the macro. Note that the backslash (\) key on the 95LX is the Shifted ()), not (MENU). The backslash stands for (ALT) in 1-2-3 macro language. So to activate the macro located in D1, all you do is press (ALT)-(F).
It's kind of magical to press two keys and watch a large series of keystrokes executed automatically. It's this magic that has hooked a lot of good people into creating large libraries of Lotus keystroke macros.
Creating a Lotus Macro Library
Here's another time and keystroke saver that will keep you from having to type your macros into the next spreadsheet you develop.
The procedure for doing this is a little more complicated than creating a single macro. It assumes that you've created several such macros and want to gather them together so that you can easily retrieve them.
The process involves creating a separate spreadsheet with all of your keystroke macros in it. After opening a new, blank worksheet, use the following procedure to copy individual keystroke macros to it.
Press (MENU) File Combine Copy Named/Specified-Range. You're then asked to key in the "Range name or address:". Reply by typing in the name you've given to your keystroke macro (\F in the above example) and press (ENTER). Then you're asked to supply the name of the worksheet file that contains this macro. Highlight the filename and press (ENTER). The macro appears in the new worksheet.
You'll repeat this procedure to copy your other macros to this worksheet. Before you do, here's another tip to organize your macro library and make it look like it was done by a pro.
Copy each of your macros to a separate cell in column B of the spreadsheet. You must leave at least one blank cell between each macro.
In the cell to the left of the macro, type in the name of macro. For example, suppose you have '/rfc2 in cell B1. Then in cell A1, you might type '\F which is the name of the macro. (Don't forget the leading apostrophe.) Do the same for any other macros that you want to put in your macro library.
When you've got all you macros lined up, along with their names, put your cursor on the cell containing the first macro name (A1 contains '\F in this example) and press (MENU) Range Name Labels Right. Move the cursor down to the last macro name in the list (A5 or whatever and press (ENTER). This associates the names you've listed in the A column with the macros in the B column.
1-2-3 Screen With Macro: Graphic
Save this new worksheet to a file called MACRLIB.WK1 (press (MENU) File Save macrlib (ENTER)). This is your "Macro Library Worksheet."
You can copy individual keystroke macros over from this worksheet to any worksheet you are working on. For example, from within a new worksheet you would put your cursor in an unused cell and press (MENU) File Combine Copy Named/Specified-Range. Next you would type in the name of the macro \F or whatever) and press (ENTER). Finally, you would select MACRLIB.WK1 (your macro library file) and press (ENTER). The macro would be copied to the cell.
I'll give some specific examples of Lotus keystroke macros later in the article. All these tips and techniques will work on any PC that runs Lotus 1-2-3.
The 95LX's User Defined Keys
The 95LX's User Keys (system macros) have several advantages over 1-2-3 macros; they don't take up any space in a worksheet, they're easier to launch, and you don't have to search for a blank spot in the worksheet to put the macro. In fact, you can replace many Lotus keystroke macros with user Keys.
Let me give you and example of how to do this and then discuss some of the advantages and limitations of this approach. We'll create a User Key to perform the same currency formatting operation as we did with the '/RFC2~ keystroke macro.
There are two ways to create User Keys. The Learn Mode lets you enter the macro on the fly, recording into a user key each keystroke as you make it. The User Mode lets you enter and edit user key commands individually, much like writing a line of code in any programming language. For more on the Learn Mode, see page 21-17 of the HP 95LX User's Guide. I'll use the User Mode for this example.
Press (<Shift>)-(FILER) (CHAR) to enter the User Key Setup screen. Move your cursor down to an empty User Key slot and press (ENTER) (I'll use F7 in this example). In the Comment field, type in 123 Currency and press (ENTER). In the Contents field type in {MENU}rfc2{ENTER}. (You can key in {MENU} and {ENTER} by pressing (F6) first and then pressing (MENU) or (ENTER).) Press (F10) to leave the macro input screen and press (ESC) to return to the main SET UP screen.
Note the difference between the System Macro and the 1-2-3 macro. {MENU} replaces the foreslash (/) and {ENTER} replaces the tilde (~).
Now press (123) to activate Lotus. Activate the User Key by pressing (CHAR)-(F7) (or whatever F-key you've chosen). The "Range to format:" message will appear on the prompt line. Use the arrow keys to highlight a range and press (ENTER). (If needed, press (ESC) prior to using the arrow keys to move the cursor to a new location and then press (.) (period key) to start highlighting.)
Transferring Keystroke Macros to User Keys
Here's a process for transferring your favorite Lotus keystroke macros to User Keys.
Open the worksheet that has the keystroke macro you want to transfer. Press (CTRL)-(F2), specify the range that contains the macro and press (ENTER) to copy the macro to the clipboard. The range may be one cell (Z1..Z1) for simple macros, or a large block of cells (Q1..Z10) for complex macros.
Activate MEMO and press (F4) to Paste the macro there. Change all foreslashs (/) to {MENU} and replace tildes (~) with {ENTER}. In addition if you use the DEL or ESC commands in your macro, you'll have to replace the left and right French brackets ({ and }) with {LBRACE} and {RBRACE}, respectively.
When you have made the necessary corrections to your macro, press (F9) (Mark), highlight the macro and press (F2) to copy it to the 95LX's clipboard again. Finally, press (<Shift>)-(FILER) Char, move the cursor to a blank macro, and press (ENTER) (ENTER) (F4) to paste the macro into the Contents field.
What Does All This Get You?
As mentioned, User Keys are easy to find and use, and don't take up space in a worksheet. However, they do have a drawback -- you're limited to ten of them at any one time. A previous Looking Glass column showed how to do this by swapping SETUP.ENV type files (see page 39-40, Mar/Apr 93 issue). However, to use this procedure, you have to leave 1-2-3, swap the SETUP.ENV files and then return to 1-2-3. The macro library file approach described earlier would be better if you have more than 10 Lotus macros you need to use.
Until next time, Happy Porting.