1-2-3 Macro Tips The /XI Command The following is a set of tips on the use of the 1-2-3 macro command /XI, and some hints on the practical application of conditional statements. An example of a useful looping macro is explained that lets you use 1-2-3 as a simple word processor. Designing a Cleaner Printout An easy way to get cleaner looking printouts from spreadsheets is to cause all cells with a value of zero to appear blank. To do this we can use the /XI or If command. First we'll write a macro that checks to see if the value of a cell is zero, and, if it is, erases it. Then we'll expand the macro to include a loop so that it checks a cell, blanks it if it`s zero, moves down and does the same thing to the next cell, and so on. Finally, we'll make the macro stop itself. Introducing the If Command The format of the If command, /XI is: /XIcondition~action if condition is true This command lets the macro make a decision. If a certain condition is true, then do a specified action. The condition is stated as an expression and written between the "/XI" and the tilde (~). Because cells in 1-2-3 can have a true or false value, the condition is stated using the cell coordinates or, preferably, the name of a cell. Typically, the condition looks something like the following three examples: NUMBER=5 @sum(AMOUNTS)>10000 @today>DUEDATE#and#AMOUNTDUE>0 The action is defined by macro instructions written after the tilde. If the condition is true, these instructions are executed. The macro then continues on to the next cell down, if there is one, unless the action includes an /XG, (goto), an /XM (menu), an /XC (subroutine call) or an /XQ (quit). If the condition is false, the action is not executed, and the cell below is read. Creating the "Blank" Macro For our macro, the action is to make the current cell blank, so the instruction is /RE~ (/Range Erase [Return]) The condition is "the current cell equals zero." How do we find out the value of the current cell? Give it a range name and proceed as shown in this \B (for Blank) macro: \B /rncHERE~~ Create the range HERE. /xi(HERE=0)~/re~ If it's "0", make it blank. By the way, any text, i.e. alpha characters, in "HERE" will also be evaluated as equal to zero and erased by this macro. Make sure your spreadsheet is suitable for the \B macro. NOTE: (To help make macros easier to read, capitalize range names, leaving all other letters lower case and putting the conditions in parenthesis.) Notice the two tildes in the first line of this macro. When you name a range, you press [Return] to end the name. Then you specify the range. Pressing [Return] again indicates the range is simply the current cell. The tilde after the "/re" does the same thing. Adding the Loop Next, we want the macro to move down one cell and check again for a value of zero. However, if we try to do this the way we did in the last "Tips" column, by adding {down}/xg\B~, there will be a problem: The next time we try to create the range name "HERE", it already will exist. 1-2-3 will show us where it is by moving the cell pointer back to its old location and we won`t go anywhere. Therefore, we need to delete the range name. Add this line to the macro, and it will work: /rndHERE~{down}/xg\B~ Delete "HERE", move down and run \B again. A word of caution: You might be tempted to try another approach, adding a {bs}, (backspace) between the two tildes in /rncHERE~~. This puts the cell pointer back where it was when the command began. Sometimes this won't affect your worksheet, but other times it can cause problems. Why? Because when you redefine a name, i.e, change the cells it refers to, all references to that cell are redefined as well. Let's say you have a formula that adds two entries in a row -- D5+E5 -- and that our macro "passes through" D5 on its way down to D6, D7, etc. When the macro is on D5, the formula reads +HERE+E5. But when it moves on, the formula continues to read +HERE+E5, even when HERE is D2048. Moral:In macros, always delete range names before you move them. Stopping the Loop The easiest way to stop a loop is to choose a number that you know isn't in the column and place that number in the cell where you want the macro to stop. Often any negative number will do; we've used "-1." And now, the completed macro: \B /rncHERE~~ /xi(HERE)=-1~/rndHERE~/re~/xq /xi(HERE=0)~/re~ /rndHERE~{down}/xg\B~ Before testing for the zero value, our \B macro first checks for the last cell. If it finds "-1", the macro thoughtfully deletes HERE (so you can use the macro again), erases the -1, and stops. A bit more computer jargon: the "-1" (or any special value that a program looks for) is a "flag." In this case, it flags down the macro to stop the loop. Creating a Simple Word Processor This little gem uses /Range Justify, a handy command that rearranges a column of long labels so that they fit within a specified width. See your manual or HELP screen if you're not familiar with /Range Justify. In /Range Justify you tell 1-2-3 how wide the text can be by pointing out a range. The words stay in the first column, but they extend out to the column you point to. In our example, the text will be two columns wide, though you can choose any number you like. By the way, don`t use this macro above tables. If you do, you won't lose any data or change any values, but your columns will become misaligned because the justification affects everything, all the way down the columns in question. Here's the macro \E for edit: \E {edit}{?}~ Edit the cell until [Return] /rj{right}~ Justify over two columns {end}{down} Go to the bottom of the column /xg\E~ And start again The number of {right}s following the /rj determines how many columns wide the text will be. To use this macro, put the cell pointer where you want to type and press [Alt] [E]. Type away, occasionally pressing [Return] when you want to cause justification. To stop, press [Ctrl-Break]. By the way, if you press [Return] before you've typed enough to go over the end of the second column, you'll {end} up at the bottom of the worksheet. Just press [Up] to get out of Edit Mode, [End][Up] to get back to the last line of the text, and [Edit] (F2) to put you back in Edit. You will still be in the macro. While this macro alone is no match for the features of the modern word processor, it's handy for short memos and letters. You can use it to create a "template" worksheet with a standard heading for your letters, another for your memos, including a cell with @today and a date format. In Closing We designed the \B macro to demonstrate general macro techniques. You can modify it to do almost anything you want to a group of cells in the worksheet. The \E macro is likely to be handier, and it suggests the potential of the {end} key. Experiment. ¿  €Û¿  (ÿ Bð €à¿ €ä¿ €ã¿  €Û¿  (ÿ Bð €à¿ €ä¿ €ã¿  €Û¿