In addition to using and identifying named ranges, the author of _EXPENSE.WK1 uses another very good macro programming technique. His code is well organized in that each line generally performs a relatively discreet operation and each line of code is kept relatively short. However, he is not perfect in this area. It would be better if the lines of code had been made even shorter so each line would be completely visible on the screen. Cells B65 and B70 are examples of code that could be improved by displaying it on more than one line. Cell B70 is a very good example of another flaw in the author's techniques. It is difficult to tell where the slash command ends and the range name begins. One should be typed in upper case and the other in lower case (/REsetstring or /REsetstring1). It does not much matter which is which, as long as you're consistent. I prefer typing slash commands in upper case.
Remarks describing code are another good programming technique. Shorter lines of code make placing meaningful remarks in an adjacent cell easier. The down side of remarks is they make the spreadsheet larger. This can become serious on a computer as small as the 95LX.
Let's examine another part of the spreadsheet and see if we can find some interesting code. Press F5 (GOTO) and when asked for an address, respond setstring followed by <ENTER>. Since the range, SETSTRING, is in cell A1, the cell pointer should be at cell A1 along with some of the descriptive information that should be visible just below it in cell A4--A8. If you entered data when you executed the <ALT>-A macro, this data should be displayed in column B. Move the cell pointer to cell B4 and note the formula, @S(EMP_NAME), displayed in the control panel at the top of the screen. Press F2 (EDIT) and the formula changes to @S(R4..R4). If a formula references a named range, the named range appears on the control panel when 1-2-3 is in READY mode. In EDIT mode the absolute address appears. Press <ENTER> to get out of EDIT mode. Note the use of the underscore in the range name. 1-2-3 will not accept spaces in range names. Substituting an underscore for a desired space is a common programming technique used when the application will not accept a space.
You may wonder why the <ALT>-A data entry macro entered the data in another part of the spreadsheet instead of in this area. Having the data entered in a remote part of the spreadsheet provides greater flexibility in data entry. For example, Employee#, Dept, and Tel. Ext could be entered either as labels or values. Cell B5, B6, and B8 contain formulas to insure these items are displayed correctly no matter how they are entered. This is a good technique.
The author's code may not be entirely bulletproof. If a value is entered in the name input cell, the @S function will return a blank. It might be a good idea to check for this condition or the failure to enter a name and return an error message if necessary. The formula could look something like this:
Let me quickly mention the other place messed up by changing the width of column A to 8 characters. Look at the signature line, A58...B58, and the approval line, A60...B60. This problem is easily fixed. Allow the descriptions to overlap two columns and replace the repeating labels, \_, with sufficient underscores to catch a signature. The problems in A5, A7, and A8 mentioned earlier can be solved by abbreviating or modifying the description. For example, replace Location with City. Another solution that may be acceptable and could even be useful would be to replace column B with two or more narrower columns and adjust the descriptions as required.
Before ending the examination of _EXPENSE.WK1 let me identify one small glitch that crept into this spreadsheet. Press F5 (GOTO) and the press F3 (NAME) twice. Move the cursor to the right column and down to \B. This range begins in cell A8192, an obviously "orphaned" (unused) range name. Orphaned range names are not an uncommon occurrence and are not seriously detrimental. Each one just wastes a little memory and adds unnecessary clutter.
_EXPENSE.WK1 was fairly easy to explore because the author used range names and maintained generally tight control over the construction of the spreadsheet. Unfortunately all spreadsheets are not designed as well. This sometimes makes it difficult to determine the active range of a spreadsheet. One of the best ways to see what is in a spreadsheet is to print it from corner to corner by pressing the following string of commands: <Shift> <Left Arrow> <MENU> Print Printer Clear All Align Range <period> <Shift> <Right Arrow> <Shift> <Left Arrow> <ENTER> Go.
It's much better to have a wide carriage printer rather than a LaserJet. It will save cutting and pasting horizontally. Printing the entire spreadsheet will not show formulas and hidden cells. The top of a spreadsheet is fairly level and easy enough to find, but the bottom edge of a spreadsheet can be very ragged and difficult to find.
[Tom works for a natural gas pipeline company in Houston, Texas, and was one of the first to use PC's in the natural gas industry. He has been a regular contributor to support publications of Hewlett-Packard computers for many years. Tom's CompuServe ID# is [76011,3655].]
* Available on The HP Palmtop Paper ON DISK Jan/Feb 92.