DP Tool Club 8

home *** CD-ROM | disk | FTP | other *** search

/ DP Tool Club 8 / CDASC08.ISO / VRAC / SUPER_M3.ZIP / MANUAL.DOC < prev next >

Wrap

Text File | 1993-06-14 | 60KB | 1,210 lines

+----------------------------------------------------------------+ |(C)Copyright K.I.T.A.L. Software 1989-1992. All rights reserved.| | (C)Copyright by Haruyasu Yoshizaki, 1991. All Rights Reserved. | | | | | | SUPER MACRO LIBRARY For LOTUS(R) 1-2-3 | | | | | | K.I.T.A.L. Software | | P.O. Box 748 | | Karmiel 20100 ISRAEL | | (972)-4-987255 | | | | | | U S E R ' S M A N U A L | | | | | | SHAREWARE Version 3.4 | | | | For registration fee see ORDER.TXT and INVOICE.TXT | | | | Developed and Written by Israel Kehaty | +----------------------------------------------------------------+ _______ ____|__ | (R) --| |o |------------------- | o___||_ | Association of | | |_| Shareware |__| o | Professionals -----| | |--------------------- |___|___| MEMBER Member of the ASP Since 1993 - 2 - Table Of Contents 1. Welcome. . . . . . . . . . . . . . . . . . . . . . . . . 3 2. General. . . . . . . . . . . . . . . . . . . . . . . . . 6 2.1 Macro format and rules to add macros to the package 7 3. The macro library manager. . . . . . . . . . . . . . . . 8 3.1 General . . . . . . . . . . . . . . . . . . . . . . 8 3.2 Using the macro managers (MACROMGR.WK1 & MACROMLB.WK1) 9 3.3 Using the small macro library managers. . . . . . . 11 3.4 Using the NEW_MNGR.MLB manager and the COMPILE.MLB. 11 4. The macro writer (MACROHLP.WK1). . . . . . . . . . . . . 13 4.1 Using the macro writer. . . . . . . . . . . . . . . 13 5. The link macro (LINK.WK1). . . . . . . . . . . . . . . . 14 5.1 Using the link macro. . . . . . . . . . . . . . . . 14 5.2 File linking information table. . . . . . . . . . . 15 5.3 Establishing or edition a link. . . . . . . . . . . 15 6. The word processor macro (WORDPROC.WK1). . . . . . . . . 16 6.1 Using the word processor macro. . . . . . . . . . . 16 7. The search & replace macro (SCRHREPL.WK1). . . . . . . . 17 7.1 Using the search & replace macro. . . . . . . . . . 17 8. The run key macro (RUNKEY2.WK1). . . . . . . . . . . . . 17 8.1 Using the run key macro . . . . . . . . . . . . . . 17 9. The menu range macro (MENURANG.WK1). . . . . . . . . . . 18 9.1 Using the menu range macro. . . . . . . . . . . . . 18 10. The macro recorder (RECORDER.WK1). . . . . . . . . . . . 18 10.1 Using the macro recorder. . . . . . . . . . . . . . 19 11. Range processing macros. . . . . . . . . . . . . . . . . 19 12. Database macros. . . . . . . . . . . . . . . . . . . . . 19 13. Add-in MACROMGR.ADN macro utilities. . . . . . . . . . . 19 13.1 The library make macros (LIBMAKE.WK1...). . . . . . 20 14. All other macros . . . . . . . . . . . . . . . . . . . . 21 15. System requirements. . . . . . . . . . . . . . . . . . . 21 - 3 - 1. WELCOME Thank you for taking the time to try our new productivity tools for LOTUS 1-2-3, release 2 and up. Version 3.3 now supports the macro library manager add-in MACROMGR.ADN of LOTUS 1-2-3 Versions 2.2/2.3/2.4 and the third dimension of versions 3.0/3.1/3.1+/3.4 and Lotus 123W 1.0/1.0a/1.1 for Windows 3/3.1. 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 release 2 and up, and an equally large collection of 220 *.MLB library macros for LOTUS 2.2/2.3/2.4 created from all the *.WK1 macros that were suitable to work as *.MLB library macros. Most of the macros are far more powerful and flexible than those found in books, magazines and commercially available packages. All these macros are just a few key strokes away using our revolutionary MACRO LIBRARY MANAGERS (macros, themselves). Only the MACRO LIBRARY MANAGER needs to be combined to the worksheet, the other macros can be directly activated from the disk. The MACRO LIBRARY MANAGER features a QUICK RUN mode that uses the disk directory list display to POINT and SHOOT, and a SEARCH by KEYWORD query to POINT to the matched macro description and SHOOT. Added to the MACRO LIBRARY MANAGERS, the package features the following unique macros: a. Full featured SEARCH & REPLACE SCRHREPL.WK1), which allows you to search and replace any set of characters through any defined range, including multiple occurrences in one cell. The macro features: Case sensitive, Case insensitive, Replace all, or Find and replace or skip. This macro is especially useful for release 2/2.01 users. b. LINK (LINK.WK1), which allows you to establish a link between any cells/ranges of the worksheet to cells/ranges of OTHER worksheets in the disk. Any time the worksheet is retrieved, the macro updates the linked cells/ranges. This macro is especially designed for release 2/2.01 users. c. RUN KEY (RUNKEY2.*), which allows you to activate any routine in the worksheet using POINT and SHOOT to the range/routine names mnemonics display list, thereby eliminating the need to use the [Alt] + key combination to run a macro for release 2/2.01 users. d. MACRO WRITER (MACROHLP.WK1), which allows you to CONTINUOUSLY write macros, text and formulas using pre-written syntax picked from the screen display for EVERY LOTUS macro keyword or function. The MACRO WRITER automatically "knows" when a formula is written, and inserts it to the cell as a formula. e. WORD PROCESSOR (WORDPROC.WK1) with an adjustable line width and SEARCH and REPLACE. - 4 - f. MENU RANGE (MENURANG.WK1), which allows you to define any range as MENU RANGE and thereby overcome the eight menu title limitation in Lotus 1-2-3 menu bar. g. MACRO RECORDER (RECORDER.*), which allows you to work normally, but simultaneously records every keyboard entry in versions 2/2.01. h. ERROR PROOF (ERRPROOF.*), which automatically turns any formula to an ERR proof, i.e. to display ZEROs instead of ERRs. i. RANGE COMBINE (RANGCOMB.*), which allows you to combine two ranges in the same worksheet. j. GRAPH GROUP (GRAPHGRP.*) for release 2, which simulates the / Graph Group new options in Lotus 1-2-3 versions 2.2/2.3/2.4. k. INSERT RANGE (RANGEINS.*), which allows you to open an empty range in the middle of the worksheet and keeps all the formulas intact, permitting partial column or row insert. l. DELETE RANGE (RANGDEL1.*), which allows you to delete a range in the middle of the worksheet and keeps all formulas intact, permitting partial column or row delete. m. KEEP RANGE (RANGKEEP.*), which allows you to erase the whole worksheet EXCEPT a selected range in one operation. n. TRANSPOSE (TRANSPO2.* and TRANSPO3.WK3), which allows you to transpose a range and handle all the formula references correctly. Users of 1-2-3 release 2 can now enjoy most of the new features and power of versions 2.2/2.3/2.4. For the LOTUS 2.2/2.3/2.4 user, this package contains the following special macros that support the MACROMGR.ADN add-in macro library manager supplied with the LOTUS 1-2-3 versions 2.2/2.3/2.4: a. Two macro managers (MACROMLB.WK1 and SMALLMLB.WK1) allowing you to use all the *.MLB macros directly from the disk, only the macros library manager needs to be combined to the worksheet. A new manager type has been added to this package which no longer needs to be combined to the worksheet. The NEW_MNGR.MLB macro allows you to use over 200 MLB macros using point and shoot. This is a real breakthrough in the field of *.MLB macro managers. Only K.I.T.A.L. Software is offering this unique approach for macro library handling. We are the first and only programmers to have managed to create such a manager. We also include a "compiler" macro COMPILE.MLB that can create such a manager from a simple column list of macro names, which are included in this package. You are easily create as many managers as you like (Paragraph 3.4.1). - 5 - You can always use the NEW_MNGR.MLB manager, which allows you to operate all the *.MLB macros listed in the NEW_MNGR.PRN ASCII file. Or you can use partial lists to save memory when your computer is limited in memory. The MACROMLB.WK1 and the SMALLMLB.WK1 macros are not obsolete, they still need less memory than the NEW_MNGR.MLB. Remember that these two managers must be combined to the worksheet exactly as the managers (MACORMGR.WK1 and SMALLMGR.WK1) for the *.WK1 macros intended for users of LOTUS 2/2.01. The first advantage from using *.MLB macros, which can be activated by the MACROMGR.ADN manager, is that they reside in the memory and do not interfere with the worksheet. The second advantage is that, if you use small library macros, you save memory. Compared to the *.WK1 macros, the disadvantage is that some sophisticated macros that stretch LOTUS 1-2-3 to the limit and use special tricks to get the most out of the LOTUS 1-2-3 macro language, cannot be used inside the MACROMGR.ADN add-in. You still need to rely on some of the *.WK1 macros. b. A special library creator (LIBMAKE.WK1), which allows you to automatically create *.MLB library macros from any *.WK1 macro, which conforms to the simple rules in section 2.2. You can use point and shoot at the macros directory list on screen, to combine *.WK1 macros into one *.MLB library or to combine *.MLB macros into one large *.MLB macro and more. If you are using a 3-D release of LOTUS 1-2-3, this package also supports the third dimension, in fact, all the range process macros handle 2-D and 3-D r*anges. The macros automatically detect which Lotus release you are using and adapt themselves. This package also contains the following special utilities: a. Two macro managers (MACORMG3.WK3 and SMALLMG3.WK3), which allow you to use all the *.WK* macros directly from the disk, only the macro library manager needs to be combined to the worksheet. b. A special library creator (LIBMAKE3.WK1), which allows you to automatically create *.WK3 macro libraries from the *.WK* macros. It allows you to use point and shoot at the macros directory list on-screen, and to combine *.WK* macros into one *.WK3 library. Two options can be used: create a one sheet macro library or a multiple sheet macro library. All *.WK1 macros have been checked and have been adapted to work with 3-D releases of Lotus 1-2-3. A few *.WK3 macros were added to the package and can be used only with 3-D releases of Lotus 1-2-3. Mmany macros specifically state that they detect which Lotus release you are using. 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. - 6 - Many other macros also support the third dimension when they use the natural Lotus functions (such as: 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 in separate sheets and the data in separate sheets. In the SUPER MACRO LIBRARY, we have minimized the common risk that two macros may have the same range names in between themselves and between the macros and your worksheet file. Every macro containing more than one range name is 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 another macro. The result is a foolproof package that allows you to create large macro libraries without the danger of a range name conflict. Bundled with this package is the completely menu driven solver and calculator The SCIENTIFIC and ENGINEERING TOOL for LOTUS 1-2-3 as a BONUS pack, which features: Simple arithmetic, Functions programming, Physical units definition & conversion, Integration and Differentiation of functions and data tables, Statistics functions, Frequency and normal distributions, Curve fitting (Polynomial, Exponent, Powers, Linear), Root finding of non-linear equations, Matrix operations, Simultaneous linear equations solver. Use the macro library managers with your already existing macro libraries, and enjoy the new possibilities it can afford. NOTE: Due to a serious bug in the Lotus 123W version 1.0/1.0a for Windows 3/3.1 the SCIENTIFIC and ENGINEERING TOOL for LOTUS will not work correctly in 123W 1.0/1.0a. This bug was fixed in 123W 1.1. 2. GENERAL To make macro handling easier every macro is a separate file. Because we use the MACRO LIBRARY MANAGERS (macros) developed especially by us, there is no need to combine most of the macros, except those macros that by nature CANNOT be used from the manager and must be combined directly to the mother worksheet (see the LIST2.WK1 file). Every macro contains the instructions for how to use it WITHOUT the MANAGER. Additional explanation and use instructions have been supplied for some macros, along with a few sample files allowing you to experiment, before getting down to serious business. We recommend that every time you use a new macro, you experiment on a sample or backup file. You can use all the macros without the macro manager, just use the COMBINE option of LOTUS 1-2-3, and the built-in instructions. - 7 - From your worksheet, place the cell pointer where you want the macro, and use / File Combine Copy Entire-file to combine the macro to your worksheet. Then use the built-in instructions to define the range names and activate the macro. If you use Version 2.2, 2.3 or 2.3 you can use the *.MLB macros. Again, every macro is a separate file and can you can activate it directly from the disk, if you use the MACROMLB.WK1 or the SMALLMLB.WK1 macro managers, which automatically load the desired macro into memory and use the MACROMGR.ADN add-in to activate the macro. You need to combine only the macro manager to the worksheet. When you start the macro manager, you can use the custom menu bar to activate the *.MLB macro using point and shoot. IMPORTANT: Make sure that no range name in the macro is identical to one of the range names in your worksheet. If this happens, change the range name in your worksheet. In this version, we attached a serial number to every macro range name, which insures there is virtually no possibility of identical range names in the macros in this package. Only the main (first) macro name in the macro is not numbered because it has to be identical to the macro file name which is limited by DOS to 8 characters only. All the macros have been designed to run under the macro managers (except those listed in the LIST2.WK1 and LIST4.WK1 files) in MANUAL and AUTOMATIC GLOBAL RECALCULATION modes. However, if you find that a macro does not function properly, please check the macro when the WORKSHEET GLOBAL RECALCULATION is in the AUTOMATIC mode. 2.1 MACRO FORMAT AND RULES TO ADD MACROS TO THE PACKAGE All macros are built this way: - The first nine cells in the first column are for use instructions. - The 10th cell holds the \Z macro name. - The 11th cell holds the mnemonic macro name, which must be IDENTICAL to the macro file name. - Downward is for RANGE NAMES, etc. - No blank cell is allowed in the first column. If the cell is not used for a range name, then it must contain the exclamation mark !. This also applies to the first nine cells. This way the first column is always contiguous and we can use {END}{UP} or {END}{DOWN} to assign or delete the range names. - Add the macro name and description to the files list inside the MACROMGR.WK1 file. Start with the DATABASE@xxx range name. - Do not use the {QUIT} macro keyword because it stops the process and exits to the READY mode, 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. There is one exception to this: when an error routine is activated the macro loses track. Instead of an empty cell, the cell should contain the {BRANCH \Y} command. - All the macros must start with the \Z macro name and {BREAKON}. - 8 - EXAMPLE: The macro INVRTCOL.WK1 is shown here: A B C D E F 1 *---A macro to invert a column of numbers or text 2 *---Use the / Range Name Label Right [End] [Down] [Return] to 3 *---define the range names in this column (starts with the \Z 4 *---macro name) 5 *---Hold the [Alt] key and press [Z] to activate the macro 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 16 The A1 to A9 cells hold use instructions. The A10 cell contains the \Z, and the A11 cell holds the INVRTCOL range name (identical to the INVRTCOL[.WK1] file name). All the cells in the "A" column which do contain a label name should contain the ! character. You can build your own macros or adapt other macros to use with the MACRO LIBRARY MANAGER as long as you follow these simple rules. 3. THE MACRO LIBRARY MANAGERS 3.1 GENERAL This package contains seven MACRO MANAGERS (in Disk_3): the SMALLMGR.WK1 and the MACROMGR.WK1 files are for 2-D releases, while the SMALLMG3.WK3 and the MACROMG3.WK3 files are for 3-D releases. The SMALLMLB.WK1 and MACROMLB.WK1 and the NEW_MGR.MLB files are for 2.2/2.3/2.4. The SMALLMGR.WK1 is about 6-7 KBYTE 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. REMEMBER that this list will show all the files in the directory unless your macros occupy a separate sub-directory. The MACROMGR.WK1 file is almost 26 KBYTE. 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, use the {UP} and {DOWN} direction keys. To activate the matched macro just press the [Return] key. - 9 - This special way of using 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 supplied with version 2.2. The SMALLMG3.WK3 and the MACROMG3.WK3 are equivalent to the previous managers and activate the *.WK* macros using 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 activates the *.MLB macros using point and shoot. IMPORTANT: 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 that are allowed in the worksheet. Therefore, the number of *.MLB macros is lower than the *.WK1 macros, and you still need to use the macros that we could not make to work as *.MLB macros. The LIST1.WK1 and the LIST2.WK1 files list the *.WK1 files and the LIST3.WK1 and LIST4.WK1 files list the *.MLB files. 3.2 USING THE MACRO LIBRARY MANAGERS (MACROMGR.WK1 & MACROMLB.WK1) The MACROMGR.WK1 activates the *.WK1 macros in the disk that can be used with all versions of Lotus 1-2-3 from release 2 and up. The MACROMG3.WK3 activates the *.WK* macros in the disk, and the MACROMLB.WK1 activates the *.MLB macros in the disk that can only be used with versions 2.2/2.3/2.4. We recommend that the you combine the MANAGER to an area outside the work area (or a separate sheet in a 3-D release) to insure that all the cells down to the manager are empty. It is best if the macro manager code is below and left or below and right of the working area, this assures that when you insert or delete rows, it will not affect the macro manager. To get there quickly, use the [End] [Home] [Right] [PgDn] keys or insert nine new columns left of your data and use [End] [Home] [PgDn] [Left] to move the cell pointer to the desired location of the macro. In both cases, if you insert or delete rows in your data, the macro is not affected. Combine the MACRO LIBRARY MANAGER to an empty area (empty sheet in a 3-D release) of your worksheet. Press / [F]ile [C]ombine [C]opy [E]ntire-file, type MACROMGR or MACROMLB and press [Return]. Place the cell pointer on the cell that contains the \Y macro name and press: / [R]ange [N]ame [L]abel [R]ight [End] [Down] [Return] to define all the range names. Press and hold the [MACRO] key ( [Alt] for most computers [Ctrl] in 1-2-3 for Windows) and press [Y]. The macro will start and the next menu will appear. - 10 - Default-dir Search-by-keyword Run_quick View List Erase Quit [D]efault-dir Choose this menu option to set the default directory to the same directory where the macros are. If you try to run a macro which is not in the default directory, it will result in an error message. [S]earch-by-Keyword Choose this menu option to look for the matched MACRO DESCRIPTION. The macro will prompt you to insert the keyword to look for. Enter the keyword (string) and press [Return] or just press [Return]. If you press ONLY the [Return] key without a string, the manager will look for the NULL string and allow you to use the [UP] and [Down] keys to scroll through the description list. If you insert a string and press the [Return] key, the manager starts a QUERY for all the matched descriptions, use the [UP] and [Down] keys to page through the matched descriptions. When the desired macro is highlighted, press the [Return] key to start it. After a short delay the macro is operative. When the macro is completed the manager deletes all the macro's range names and erases the macro. Then the main menu re-appears. [R]un_quick A quick mode to start the macro, which is the same as in the SMALLMGR.WK1. When you choose this menu option, the macro displays the directory list. Highlight the macro to run and press [Return]. After a short delay the macro is operative. When the macro is completed, the manager deletes all the macro's range names and erases the macro. Then the main menu re-appears. NOTE: if the directory holds files other than the macros, all the files are displayed. We recommend that you place the macros in a separate directory. [V]iew Choose this menu option to display the directory list. Highlight the macro to VIEW and press [Return]. After a short delay, the macro code is displayed on the screen. Use ONLY the direction keys to scroll through the macro. To execute the macro press [E]. If you press the [Return] key, the manager erases the macro and the main menu re-appears. [L]ist Choose this option to list all the files in the current directory. This option allows you to verify the existence of a macro in the current directory/disk. This is especially useful for 360K floppy diskettes systems, because the macros are divided over many diskettes. Press [Return] to quit the session. - 11 - [E]rase Choose this option to remove the MANAGER from the worksheet. All the range names associated with the manager are deleted and the manager text is erased from the worksheet, leaving no trace of the manager. Because the manager erases itself, the process ends with an ERROR. Therefore press [Return] to return to READY mode. NOTES: a. If you press [Ctrl Break] during the MANAGER SESSION, the error handling routine is activated and prompts you to press the [Return] key to return to the main menu. However, there is one problem: the manager will not be able to erase the macro's range names, and they will be added to the worksheet. To erase them, QUIT the manager and then place the cell pointer on an empty cell/area, activate the manager and activate the same macro again. When the macro is completed, the manager also erases the macro and all the range names. b. To use the [V]iew and [R]un_quick or [M]acro_run options in the main menus of ALL the managers, the *.WK1 files must also be included in the macros directory. Therefore, even if you use one of the 2.2/2.3/2.4 versions and the *.MLB macros, keep the *.WK1 in the same directory. 3.3 USING THE SMALL MACRO MANAGERS (SMALLMGR.WK1 & SMALLMLB.WK1) The SMALLMGR.WK1 can activate all the *.WK1 macros in the disk that can be used with all versions of Lotus 1-2-3 from release 2 and up. The SMALLMLB.WK1 can activate all the *.MLB macros in the disk that can be used only with version 2.2/2.3/2.4. However, for these managers to work, the equivalent *.WK1 macro MUST be included in the same directory. The SMALLMLB uses the first range name (cell A11) in the *.WK1 macro file to activate the *.MLB macros. The small manager contains the same options as the full size manager except for the "Search by Keyword" menu option. The [M]acro_run option is the same as the [R]un_quick option in the MACROMLB.WK1 manager. NOTE: To use the [V]iew and [R]un_quick or [M]acro_run options in the main menus of ALL the managers, the *.WK1 files must also be included in the default directory. Even if you use one of the 2.2/2.3/2.4 versions and the *.MLB macros, keep the *.WK1 in the same directory. 3.4 USING THE NEW_MNGR.MLB Load the NEW_MNGR.MLB into memory the same way you load any *.MLB macro. Make sure that no other *.MLB macros are loaded. (See the README.TXT file for basic macro information and/or Lotus 1-2-3 documentation. Make sure that you define the Default directory to be the same directory that holds the *.MLB macros. Press the ALT-F3 combination keys and point to the macro name. Press [Return] to start the macro. The macro manager will load the macro that you chose into memory and activate it. When you are finished with the macro, the manager automatically removes it from the memory. - 12 - This package includes an exiting utility, COMPILE.MLB, which allows you to create your own managers like the NEW_MNGR.MLB. We created the NEW_MNGR.MLB manager from the NEW_MNGR.PRN text (ASCII) file using this (COMPILE.MLB) macro. 3.4.1 USING THE COMPILE.MLB COMPILER TO CREATE NEW MANAGERS K.I.T.A.L. Software developed an *.MLB macro that can compile a XXXXXXXX.PRN text file which contains a list of macro names from the SUPER MACRO LIBRARY, into a macro manager with the same name, but with the MLB extension ( XXXXXXXX.MLB ). When you use the compiled MLB manager, the *.MLB macros in the default directory can be activated using point and shoot. This package already contains the NEW_MNGR.MLB manager which can activate over 200 macros, therefore its size is 32K. If you have memory shortage problems you can create smaller managers if you use partial lists of the NEW_MNGR.PRN text file. Remember to give the new list a new name and make sure the extension is ".PRN". 1. Attach the Lotus 2.2/2.3/2.4 MACROMGR.ADN using /AAMACROMGR and press [Return]. 2. Invoke the MACROMGR.ADN using /AIMACROMGR and press [Return]. NOTE: If the MACROMGR add-in is already in memory, remove all the *.MLB macros from the memory or detach it using /ADMACROMGR [Return] and start from step 1. 3. Define the global default directory to the same directory where you keep the macros of the SUPER MACRO LIBRARY. NOTE: Erase the content of the worksheet using /FE... ; the worksheet must be empty for the compiler to work correctly. 4. Load the COMPILE.MLB macro using /AIMACROMGR [Return] and choose Load and highlight the COMPILE name and press [Return]. 5. Press ALT-F3 and point to the @COMPILE name and press ENTER, then type the name of the text file that holds the list of themacro names and press [Return]. DO NOT type the PRN The macro creates a file with the same name, but with the .MLB extension. This file is the newborn manager. 6. Load the new file into memory and press ALT-F3, then point to the macro to start and press [Return]. SUMMARY: Prepare a *.PRN ASCII file which contains the list of the *.MLB macros from the SUPER MACRO LIBRARY. The COMPILE.MLB macro builds a new MLB macro file using this list. The new file is a macro manager that can activate all the files in the list using point and shoot. You can create as many managers as you like using partial lists. The ASCII file must have the .PRN extension, but do not type the .PRN extension when the COMPILE macro asks for the name of the ASCII file. - 13 - 4. THE MACRO WRITER (MACROHLP.WK1) This macro allows you to CONTINUOUSLY write every Lotus function or macro command or ASCII/LICS character, including the extended characters (above ASCII/LICS 128), using POINT and SHOOT to the syntax on display. There are no more typing errors and need to remember the exact syntax. This macro doesn't work with the MANAGER and needs to be manually combined to the worksheet. 4.1 USING THE MACRO WRITER (MACROHLP.WK1) Combine the MACROHLP.WK1 to the worksheet using /FCCEMACROHLP and press [Return]. Use the /Range Name Label Right [End] [Down] [Return] to define the range names in the first column (starting with the \Z macro name). Place the cell pointer where you want to start writing. Hold the [Alt] key and press [Z] to activate the macro. The following list of all macro {COMMANDS} and @FUNCTIONS "TYPES" will show up. "ABORT "ADDRESS" "ASCII "CONTENTS" "FORMAT" "PREFIX" "ROW" "TYPE" "WIDTH" @ABS @ACOS @ASIN @ATAN @ATAN2 @CELL @CELLPOINTER@CHAR @CHOOSE @CODE @COLS @COS @COUNT @DATE @DAVG @DAY @DCOUNT @DDB @DMAX @DMIN @DSTD @DSUM @DTVALUE @DVAR @ERR @EXACT @EXP @FALSE @FIND @FV @HLOOKUP @HOUR @IF @INDEX @INT @IRR @ISERR @ISNA @ISNUMBER @ISSTRING @LEFT @LENGTH @LN @LOG @LOWER @MAX @MID @MIN @MINUTE @MOD @MONTH @N @NA @NOW @NPV @PI @PMT @PROPER @PV @RAND @RATE @REPEAT @REPLACE @RIGHT @ROUND @ROWS @S @SECOND @SIN @SLN @SQRT @STD @STRING @SUM @SYD @TAN @TERM @TIME @TIMEVALUE @TRIM @TRUE @UPPER @VALUE @VAR @VLOOKUP @YEAR {BREAKOFF} {BREAKON} {CALC} {CONTENTS} {DEFINE} {DISPATCH} {DOWN} {EDIT} {END} {ESC} {FILESIZE} {FORBREAK} {FOR} {GETLABEL} {GETNUMBER} {GETPOS} {GET} {GOTO} {GRAPH} {HOME} {IF} {INDICATE} {LEFT} {LET} {LOOK} {MENUBRANCH}{MENUCALL} {NAME} {ONERROR} {OPEN} {PANELOFF} {PANELON} {PGDN} {PGUP} {PUT} {QUERY} {QUIT} {READLN} {READ} {RECALCOL} {RECALC} {RESTART} {RIGHT} {UP} {WAIT} {WINDOWSOFF}{WINDOWSON} {WINDOW} {WRITELN} {WRITE} Move to one of them and press [Return]. The macro writes the text to the anel and waits for you to edit and press [Return]. Press [Return] to accept. When the list shows up again, choose again and press [Return]. The macro adds the new text to the previous one and displays the result in the panel. You can continuously write MACROS, FUNCTIONs, etc. - 14 - For example, let's write the @SIN(0.5)+@COS(0.5)+@TAN(0.5) formula to the K1..K1 cell. - Place the cell pointer on the K1..K1 cell. - Activate the macro using ALT + Z. - Move to the @SIN in the display and press [Return]. The panel will show: '@SIN(X) with the cursor on the X. Replace the X with 0.5 and add + to get '@SIN(0.5)+ and press [Return]. The full list appears again. - Move to the @COS in the display and press [Return]. The panel will show: '@SIN(0.5)+@COS(X) with the cursor on the X. replace the X with 0.5 and add + to get '@SIN(0.5)+@COS(0.5)+ and press [Return]. - Repeat with the @TAN, but don't add the + and press [Return]. The full list appears again. - Choose "ABORT (probably the first) and press [Return]. The result, of 1.903310, will show up in cell K1. If you have followed this procedure, you have noticed that the macro took care of the apostrophe ' at the beginning of the formula. In he same way, you can build macros using the building blocks in the screen display. However, remember that LOTUS accepts 240 characters (512 in the 3-D releases) maximum in a line and formulas must be legal. Use this macro to incorporate all the ASCII/LICS characters set in your text. Choose the "ASCII option from the display, the full character set table appears. Move to the character to print and press [Return]. The macro adds the character to the text in the panel. REMEMBER that the content of the table depends on the LOTUS installation, and if you use the ANSI.SYS driver or not. For more information, see your DOS and LOTUS manuals. 5. THE LINK MACRO (LINK.WK1) This macro links cells in the current worksheet to cells/ranges in other external worksheets on disk. Combine this macro to any worksheet that needs to be linked. Combine it to an empty area of your worksheet and make sure that there will be enough empty rows to accommodate the linking information table. 5.1 USING THE LINK MACRO (LINK.WK1) The LINK.WK1 file is already linked to the TEST1.WK1 file. Set the default directory to the directory occupied by the macros and Retrieve the LINK.WK1. Notice how the file collects the information from the TEST1.WK1 file during the RETRIEVE process. The link information table appears for few seconds: ----------------------------------------------------------------- EXTERNAL-FILE EXTERNAL-RANGE LINKED-CELL-NAME LINKED-CELL-ADDRESS ----------------------------------------------------------------- TEST1 B2 LINK1 $A$71 TEST1 A1 LINK2 $A$72 TEST1 C1 LINK3 $A$73 TEST1 C2 LINK4 $A$74 TEST1 D1 LINK5 $A$95 - 15 - Then the cell pointer is placed on A1..A1. When you COMBINE the LINK.WK1 file to your worksheet, ERASE the contents of the table. Otherwise, when the LINK macro is activated, an error message will appear because all range names are stripped when a worksheet is combined. 5.2 FILE LINKING INFORMATION TABLE The table holds information concerning the linked cells (for example LINK1 at $A$71) and the external file TEST1.WK1 (cell B2..B2). The \0 macro uses the TABLE to update the linked cell from the data in the external file TEST1.WK1. 5.3 ESTABLISHING OR EDITING A LINK Place the cell pointer on the cell to be linked. Use the [Alt] key and press [Y] to ESTABLISH linking between the cell and cells/ranges in the other worksheet. The panel displays: " Point to the CELL to be linked and press [Return] or press [Esc] to Quit " Now you can move to another location and press [Return] or just press return to continue. The link table appears and the next prompt is displayed in the panel: " Move up to edit and press [Return] or just press [Return] " --------------------------------------------------------------- EXTERNAL-FILE EXTERNAL-RANGE LINKED-CELL-NAME LINKED-CELL-ADDRS --------------------------------------------------------------- TEST1 B2 LINK1 $A$71 TEST1 A1 LINK2 $A$72 TEST1 C1 LINK3 $A$73 TEST1 C2 LINK4 $A$74 TEST1 D1 LINK5 $A$95 The cell pointer is located at the table. If you want to edit a link, use the direction keys to move upward and press [Return]. If you want to add linked cells, press [Return]. The next prompt is: " WHICH FILE TO LINK: " Enter the external file name and press [Return]. The cell pointer moves one cell to the right and the next prompt appears: " INSERT RANGE NAME OR RANGE ADDRESS TO LINK TO THIS CELL: " Enter the external cell/range name or address and press [Return]. The cell pointer moves one cell to the right and the next prompt appears: - 16 - " ASSIGN A RANGE NAME TO THIS CELL: " Enter a name to be assigned to the linked cell in the current worksheet and press [Return]. The cell pointer moves one cell to the right and also inserts the cell address. The first prompt appears again: " Point to the CELL to be linked and press [Return] or press [Esc] to Quit " Repeat this procedure again to link more cells or press [Esc]. To update the worksheet, hold the [Alt] key and press [Z]. After te worksheet containing this macro is saved, the next time it is retrieved the AUTO macro \0 will UPDATE all the linked cells automatically. 6. THE WORD PROCESSOR MACRO (WORDPROC.WK1) A WORD PROCESSOR macro with a full featured SEARCH & REPLACE and an adjustable line width. 6.1 USING THE WORD PROCESSOR MACRO (WORDPROC.WK1) Combine the WORDPROC.WK1 file to the worksheet and use /Range Name Label Right [End] [Down] [Return] to define the range names in the first column (starting with the \Z macro name). Hold the [Alt] key and press [Z] to activate the macro, the following prompt appears: " Number of characters in line ? " Enter the line width and press [Return], the macro will adjust the column width and the following menu will appear: Typing Re-justify Search & Replace Quit [T]yping When you choose this option, the macro waits for you to begin to type. Press the [Return] key and the macro justifies the whole paragraph and moves to the bottom of the paragraph and waits for you to continue typing. Be careful to separate paragraphs by an EMPTY cell\line, otherwise when you press [Return], the whole document will be justified as a single paragraph. Press [Esc] to return to the main menu. [R]e-justify Use this option to change the line width. When you choose this option, the following prompt appears: " Number of characters in line ? " - 17 - Insert the new line width and press [Return]. The macro will justify the whole paragraph to the new line width. [S]earch & Replace This option gives you the same features as the file SCRHREPL.WK1. See Chapter 7. 7. THE SEARCH & REPLACE MACRO (SCRHREPL.WK1) A macro to SEARCH and REPLACE labels or values in a range. The macro features: - Exact match (case sensitive) a. Replace All b. Find and Replace, Skip or Quit - Inexact match (case insensitive) a. Replace All b. Find and Replace, Skip or Quit 7.1 USING THE SEARCH & REPLACE MACRO (SCRHREPL.WK1) Combine the SCRHREPL.WK1 file to the worksheet and use /Range Name Label Right [End] [Down] [Return] to define the range names in the first column (starting with the \Z macro name). Place the cell pointer at the upper left cell (A10..A10 for example) of the range to be searched. Hold the [Alt] key and press [Z] to activate the macro, the following prompt appears: Which range ? Enter range: A10 Press the PERIOD . to anchor the cell and "paint" the range to be searched. Press [Return] and follow the on screen/panel prompts and instructions. 8. THE RUN KEY MACRO (RUNKEY2.WK1) A macro that ACTIVATES any macro/routine without the use of the Alt] key. When you start this macro, all the range names in your worksheet are displayed on screen. POINT to the macro/routine name and press [Return] to start the macro/routine. 8.1 USING THE RUN KEY MACRO (RUNKEY2.WK1) Use /Range Name Label Right [End] [Down] [Return] to define the range names in the first column (starting with the \Z macro name). Hold the [Alt] key and press [Z] to activate the macro. A full screen list of all the range names/routines in your worksheet will show up. Point to the routine name and press [Return]. The routine will be activated. This macro eliminates using the ALT+key combination to activate a macro and overcome the limitation of maximum 26 characters in the English alphabet. - 18 - 9. THE MENU RANGE MACRO (MENURANG.WK1) This macro gives you the ability to define/turn any rectangular range of cells into an active MENU RANGE. Every item in a cell is a menu title (routine name). When you place the cell pointer on one of the titles, the description line appears in the panel exactly as in the 1-2-3 menu bar. When you press the [Return] key the routine is activated. This macro is mainly for application developers to avoid nested menus. 9.1 USING THE MENU RANGE MACRO (MENURANG.WK1) Use /Range Name Label Right [End] [Down] [Return] to define the range names in the first column (starting with the \Y macro name). Hold the [Alt] key and press [Y] to define the MENU RANGE, highlight the MENU RANGE and press [Return]. The MENU RANGE needs to be defined ONLY once. The menu range can look like this: Routine1 routine2 routine3 Routine4 routine5 routine6 Routine7 routine8 routine9 Routine10 routine11 routine12 Routine13 routine14 routine15 For every routine name in the MENU RANGE, there should be a routine with the same name in the worksheet. A routine should always start with the description line immediately followed by the macro name "{exec283}" as follows: routine1 This is the description line: {exec283} {GOTO}EMPTYAREA~/FCCE{?}~ {BRANCH MENURANG} This is an example of a COMBINING routine. The first line is composed of the description line "This is the description line: " and the macro routine {exec283}. The {exec283} routine executes your keyboard entries (direction keys only) and executes the main routine when you press the RETURN key. The last line can hold the {BRANCH MENURANG} to insure that the menu range will be activated when the routine is finished. Hold the [Alt] key and press [Z] to USE the MENU-RANGE, or use {BRANCH MENURANG} inside or from another macro. Use the direction keys to move between the menu items (notice the description line in the panel) press RETURN to activate the routine. 10. THE MACRO RECORDER (RECORDER.WK1) A macro to RECORD and EXECUTE keystrokes as they are typed. The macro allows you to use LOTUS 1-2-3 and simultaneously records your keystrokes in the background into a macro that can be used again. - 19 - 10.1 USING THE MACRO RECORDER (RECORDER.WK1) Use /Range Name Label Right [End] [Down] [Return] to define the range names in the first column (starting with the \Z macro name). Hold the [Alt] key and press [Z] to activate the macro. Your keystrokes will be recorded and placed out of the working area. Use Ctrl-Break to Quit. To locate the recorded keystrokes, press [End] [Home] [Right] [End] [Up] [Left]. 11. RANGE PROCESSING MACROS This package contains a large number of macros to process ranges. Unless otherwise specified, place the cell pointer at the upper left cell of the range to be processed and activate the macro. A prompt like the following usually appears: Which range ? Enter range A1 Move and "paint" the range using the arrow keys. However to "paint" the range you need to ANCHOR the cell pointer, therefore press the PERIOD . key and the prompt should look like: Which range ? Enter range A1..A1 Now use the arrow keys to expand and highlight ("paint") the range and press [Return]. 11.1 THE POWERFUL PERIOD . This tiny PERIOD . is one of the most effective and powerful keys in LOTUS 1-2-3. It ANCHORs and FREEs the cell pointer and allows you to visit all corners of the highlighted range just by repeatedly pressing the PERIOD . key, so you can expand a range into all directions simultaneously. When you will press the PERIOD . you will notice that each time the . is pressed, the cursor jumps to the next corner of the highlighted range. 12. DATABASE MACROS When you are using the database macros, ALWAYS make sure that the database range and the output range have at least two empty rows above to accommodate for the criterion range. 13. ADD-IN MACROMGR.ADN MACRO UTILITIES In this package, we have included a few macros (LIBMAKE*.WK1) to help you work with the MACROMGR.ADN add-in, supplied with Lotus 1-2-3 version 2.2/2.3 or 2.4. This manager can save *.WK1 files as *.MLB files. The MACROMGR.ADN add-in loads the *.MLB macros into the memory instead of the worksheet. As explained in the Lotus manual, to activate an *.MLB macro the MACROMGR.ADN you first must attach it to the worksheet using the / Add-in Attach ... sequence. Then you have to INVOKE it using / Add-in Invoke ... sequence, or the ALT-F* assigned function key. Then choose the [L]oad menu option to load the *.MLB macros. - 20 - To activate the macro, press ALT-F3 and point to the macro/routine name that you want to start and press [Return]. When you press ALT-F3, all the range/routine names in the worksheet file and the macro are displayed on screen. This makes it difficult to locate the macro/routine name to activate. To make it easy to locate the main routines, the LIBMAKE.WK1 macro adds the ASCII character no. 2 [@CHAR(2)] to the main routine name of the macro. This character appears on the panel as - preceding the main routine name. For example if the macro name is "CENTER" the main routine name will be "-CENTER" this makes it much easier to locate it on a screen filled with range names. NOTE: Due to a serious bug in the new Lotus 2.4, the "-CENTER" name does not appear at all; however Lotus recognize it when it is called from a macro. Lotus 2.4 users should look for the "CENTER" name to start the macro. Because of this nasty bug, we had to update more than 200 *.MLB macro. The MACROMLB.WK1 and SMALLMLB.WK1 macro managers templates look for the @CHAR(2) character. Therefore, if you create macros and save them as *.MLB library macros without using the LIBMAKE.WK1 macro, you have to add the @CHAR(2) character to the macro's main routine. For example: if the macro's main routine name is CENTER' press the F2 key to switch to EDIT mode and delete the ', then enter @CHAR(2)&"CENTER". Now press CALC (the F9 key). The result is [ CENTER]. The space before the CENTER is the @CHAR(2), which appears as -CENTER on screen when you press ALT-F3. NOTE: The @CHAR(2)&"CENTER" formula should appear exactly as you see it, including the double quotes"". The CALC or F9 key turns the formula into a label. You can also use /Range Value ... 13.1 LIBRARY MAKE MACROS (LIBMAKE.WK1, LIBMAKE2.WK1& LIBMAKE3.WK3) LIBMAKE.WK1 and LIBMAKE2.WK1 were designed for 2.2/2.3/2.4. The LIBMAKE.WK1 macro is especially built to be used with *.WK1 macros that follow the simple rules in paragraph 2.1 of this document. This macro allows you to easily create separate library macros from the *.WK1 macros. We have used this macro to create all the *.MLB macros in this package. For macros that don't follow these rules, use the LIBMAKE2.WK1 macro, but remember that you will not be able to enjoy the following benefits. You can create macros, one at a time by point and shoot to a macro name in the screen display, or let the macro create *.MLB library macros from all the *.WK1 macros in the default directory in one step. You can combine any number of *.WK1 files to one *.MLB library or combine any number of the *.MLB library macros in this package into one *.MLB library macro. IMPORTANT: Before you use the LIBMAKE.WK1, make sure that there are no *.MLB macros in the directory by the same names as the macros you plan to create. Remove all the *.MLB macros from the memory using the LIBRMOVE.WK1 macro or the /Add-in Invoke ... sequence. - 21 - The LIBMAKE3.WK3 is for 3-D releases of Lotus 1-2-3. It allows you to create macro libraries by combining separate macros into one file. You can combine the macros into one sheet, one after another or stacked together in a multiple sheet file. A multiple sheet library is preferred because it allows you to delete a macro from the library if you delete the sheet. Before you combine a multiple sheet library into your worksheet, make sure to add enough empty sheets to accommodate the library. Use the / Worksheet Insert Sheet ... sequence. When you create a library, the LIBMAKE3.WK3 macro adds an opening sheet to the macro sheets, which states the number of macros in the sheet and instructions for how to use the new library. 14. ALL OTHER MACROS Unless otherwise specified, place the cell pointer at the upper left cell of the range to be processed and start the macro manually or use one the appropriate MANAGERs. 15. SYSTEM REQUIREMENTS IBM(R) PC, XT, AT, PS2 or compatibles, DOS 2.0 or higher LOTUS 1-2-3 Version 2.0/2.01 and up or 100% compatible spreadsheets. (C)Copyright K.I.T.A.L. Software, 1989-1992. All rights reserved. Please send your comments about the software to K.I.T.A.L. Software. LHA is a registered trademark of (C)Yoshi 1988-1991. All rights reserved. Lotus and 1-2-3 are registered trademarks of Lotus Development Corporation. IBM is a registered trademark of International Business Machines Corporation.