1. Line by line macro execution or the most useful 123 macros ever!
2. Macro layout convention or what is the recommended layout for writing 123 macros?
3. How to use a common Macro sheet for my projects or can I make my own macro add-ins.
4. Iterate over all cells in a range a very useful macro that scans a range.
5. Calculate the total columns and rows of a range useful when you need the total rows and columns in a range.

1. Line by line macro execution or one of the most useful 123 macros ever!
To test a macro line by line enter the macro below then the key sequence 'Ctrl X' will  execute the macro line at the cellpointer.

 
2. Macro layout convention are best done in three columns as in example 1. The first column contains all the necessary range names. The range names are then easily set using the "/RNLRrange~" key sequence where 'range' is marked out to be the entire column. The next column contains the actual macro commands. The third column contains comments.

A development of this idea is to include 2 columns to the left (5 in all) to hold the dynamic variables as below:

123 Release 4 and 5 can work with all macros collected in a separate worksheet file. The file can then be loaded with another worksheet files when the macros contained are needed. However, to do this you need to translate your existing macros to follow these basic rules:

• all range references to the macros in the macro file need to be in the form <<?>>range (<<?>> is the 'look for in memory' specifier) as 123 assumes range references are limited to the file on which they are made. For example if you call a macro on your macro file say "{MYMACRO SALARY,OUT}" 123 will only look in the current file for "MYMACRO", "SALARY" and "OUT". The correct call would be "{<<?>>MYMACRO SALARY,OUT}" NB: you could include the full file specifier. in front of MYMACRO e.g. "{<<C:\123\WORK\MACRO.WK5>>MYMACRO SALARY,OUT}" but "<<?>>" works just as well providing that there are no name conflicts.
• within the macro file all range references need to be in <<?>> form since 123 will assume that a range without the "<<?>>" specifier to be in the calling file instead of the macro file and consequently return errors. So instead of macro lines like:

you need to have in the macro file:

• finally the macro file needs to ensure that the range names do not conflict with range names in the calling file. Hence the convention where all range names are given a 3 character tail e.g. "1D6" in "NAME1D6" so the macro file writer can invent the tails on the fly with little chance of conflict within the macro file or the calling file.
• you can use the LAPSOFT™ 123Macro Convention for writing macroroutines which basically allows the macros to be used in a macro file loaded in memory. The convention allows for two versions of each macroroutine: one {macroname@ ...} with calculated arguments (i.e. ":V" qualifier) and one {macroname ...} with straight arguments (no ":V" qualifier). The '@' suffix referring to the indirect calculated argument version.
  
  e.g. the {COLS_ROWS ..} macro of Item5  has two versions:
  {COLS_ROWS range,output_cols,output_rows} and
  {COLS_ROWS@ range:V,output_cols:V,output_rows:V}
  
  The first version takes arguments as ranges e.g. {COLS_ROWS USA1,A:B132,A:B133} calculates the column count of range USA1 and outputs to A:B132 and likewise row count to A:B133.
  
  The second version takes all string arguments e.g. {COLS_ROWS@ +"USA1",+"A:B132",+"A:B133"} which are de-referenced before use, allowing the arguments to be calculated values e.g. :
  {COLS_ROWS@ @VLOOKUP(COUNT,STATES,1),+"A:B"&@STRING(132+COUNT),+"A:C"&"&@STRING(132+COUNT)}
  Here the first argument is looked up from the STATES table, the second argument is an address "A:B10" where the row (10 here) changes with the value of COUNT, the third argument likewise is calculated with varying row values.
  
  Once the indirect '@' version is defined, there is little overhead in defining the straight version as it is a one line reuse of the already defined straight version. E.g.:

4. Iterate over all cells within a range.

This WITH@ macro is useful for iterating over all cells of a range, the 2 arguments are: the range to iterate over, and the macro to apply. Both arguments are input as strings (which may be formulae) as the {DEFINE statement uses the 'arg:V'  convention which means 'calculate this first'. So to iterate over a range with name SALARY and apply a macro {CHECK ERR_OUT} you would write : {<<?>>WITH@ +"SALARY",+"{CHECK ERR_OUT}"} so in this case the arguments did not need to be calculated and are input as strings. Re-writing the macro without the arg:V inputs in the {DEFINE..} statement would allow : {<<?>>WITH@ SALARY,"{CHECK ERR_OUT}"} but SALARY could not be a calculated argument e.g. @vlookup(21,TABLE,0).

5. Calculate the total columns and rows of a range

Listed in item 4 above COLS_ROWS  COLS_ROWS@  these macros are useful when you need the total rows and columns in a range. The direct version { COLS_ROWS..} takes all range arguments: the input range to calculate col and row count and output ranges for cols and rows. The indirect version { COLS_ROWS@ ..} takes all strings as input so that the user can formulae which are calculated for the input arguments. Note the use of @@(<<?>>RANGE0F3) the @@() converts the string input value to a range. Using the :V on input arguments enables calculated input arguments but means that arguments have to be input as calculated strings e.g. {COLS_ROWS@ +"MYRANGE",+"ROWS",+"COLS"} where MYRANGE is the range to get cols and rows for, ROWS is the name of a cell to place the row count in and COLS is the name of a cell to place the column count in. Names have been suffixed with "0F3" to prevent accidental duplication of range names. Use of "<<?>>" qualifiers for range names enables the macro to be placed in a separate macro worksheet file and loaded into memory with the calling spreadsheet file.

 
[HOME]   [Products]  [Download]   [FAQ]   [Contact]   [Links]

"ENHANCE23 ™" is a trademark of LAPSOFT. "Lotus", "Lotus123" and related trademarks are trademarks of Lotus Development Corp. www.lotus.com All other products mentioned are registered trademarks or trademarks of their respective companies.

Questions or problems regarding this web site should be directed to contact
Copyright © 2001 LAPSOFT. All rights reserved.
Last modified: Sunday August 12, 2001.