Spreadsheet macro programming: A critique with emphasis on Lotus 1-2-3

3. SYSTEMS SOFTWARE 1990: 13:197-200

197

Spreadsheet Macro Programming: A Critique with Emphasis on Lotus l-2-3 Chuck Litecky Associate Professor, wahoos of Acco~nfa~cy College qf Busyness and Public Admin., university of ~i~ouri-Columbia, Coiumb~a, Missouri

While many users are developing applications utilizing spreadsheet software, few computer professionals are involved. This paper analyzes the programming facilities in the popular Lotus l-2-3 spreadsheet software to interest software engineers in improvement of spreadsheet programming languages and users’ applications.

Older Keyboard Based Command Symbols: Slash

/

Command

rnlr

indicates the beginning of a command example command to define a range name causes the enter key to be received indicates a predefined function, e.g., present value

Tilde

1. INTRODUCTION

Users of both micro- and mainframe computers have increasingly begun to develop their own systems [4, 81. The long lead times, large backlogs, and high costs associated with traditional applications development by the professions programing staff provide impetus for end user development [4]. Substantial computing power is controlled by users and the potential effects of applications developed by users are large. Surveys of end users’ application development practices indicate many applications are developed in spreadsheets regardless of the ava~abili~ of more conventional programming tools. The facilities of spreadsheet packages, especially those of Lotus l-2-3, appear to be widely used in end user computing [2, 61. Over two million copies of l-2-3 have been purchased [3]. l-2-3 is irn~~nt in end user computing because of its wide use. However, computer scientists and software engineers have given relatively little attention to the spreadsheet packages and their programming languages [5]. This paper may help to arouse interest in this important class of software. 2. PROGRAMMING AND LOTUS l-2-3

Table 1. Syntax of Lotus l-2-3 Commands

LANGUAGE MACROS

FUNCTIONS

The original spreadsheet functions of l-2-3 and similar spreadsheet packages have been supplemented by genAddress correspondence to Chuck Litecky, College of Business and Public Admin., University of Missouri, Columbia, MO 65211.

0 ElsevierScience~biis~g Co.. Inc. 655 Avenueof the Americas,New York, NY 10010

Function

Q

Newer Macro Command Symbols: Braces

l

Command Range names

Let

t

Identifies the beginning and end of a command example of command similar to variables in other languages

eral programming functions. Table 1 shows the general syntacticar conventions in this progression and Table 2 provides a sample of programming language facilities of Lotus l-2-3. 3. PROGRAMMING LANGUAGE WITHIN LOTUS l-2-3

FUNCTIONS

As shown in Table 2, the programming language functions of l-2-3 parallel conventional, procedural programming languages with some non-procedural facilities. For example, the traditional assignment statement in l-2-3 is: (Let X, Y) . This statement replaces the contents of range X with the contents of range Y. l-2-3 implements the replacement statement in a conventional manner and without default definition of variables which is an aid to structured programming. l-2-3 supplements common programming language unctions with extensive template ma~pulation facilities. The facilities available to the user for building templates are also available as programming language commands. For example, the facilities for accounting

198

C. Litecky

J . SYSTEMS SOFTWARE 1990;13:197-200

Table 2. Summary of Programming Language Features in Lotus l-Z-3 FUNCTION

EXPLANATION

Assignment (Let X,Yl

Replace the contents of Y with Y Copy the contents of Y into X

/cY-XComputation (Let X,@Sum(Fixed Costs)}

Compu~tion is embedded in other statements

File Input /Output (sequential only) {Write DataRecord}

Writes sequential record to disk

Keyboard Input/Output ~~tnurn~r “Please enter cost of unit”, UCoSt)

User enters single data item

Spreadsheet Oriented Input /Output /fsIncState -

Saves the current spreadsheet

Transfer of Controt {Branch Loop) {For Count,l,lO,l,DataRtn) {MainRtn}

Goto Loop Do DataRtn while Count < = 10 by 1 Calls the subroutine named MainRtn

Screen Display and Control ~Goto~~nding~r~n {WindowsOff)

Shows the E~ngSc~n range on the display Freeze the contents of the display

Logic {If X = Y}{EqualRtn)

Simple IF statement logic with a call

eon-Procedural /riInputScreen /dsCityTable - pCity - a - g /dqiDatabase - oGutput - ccriteria - eq

and financial mathematics are numerous and can be included in l-2-3 programs (Table 2 computation example). These supplements aid the applications development process and are one of the strengths of l-2-3. 3.1 Input /Output

Functions

Early versions of l-Z-3 restricted the disk input/output functions to the spreadsheet oriented commands, e.g., file input and output was restricted to retrieving and saving spreadsheets. Later versions added commands to enable simple input and output of records for sequentially organized files. Unfortunately, 1-2-3’s newer commands only support sequential file organization. Data intensive applications are difficult to develop in l-2-3 and the file oriented capabilities of l-2-3 are discouragingly primitive for data base oriented applications development. New data base facilities are needed. The Lotus Development Corporation appears to be rightly giving priority to the enhancement of its data base facilities. l-2-3 also supports a variety of commands for I/O at the keyboard, e.g., the (Getlabel} and ~Getn~~r~ commands for low volume input. More important for screen display in applications development, l-2-3 provides a high level, non-procedural command called the range input command. A typical range input command is: /riInputScreen - . This command displays the range

Shows screen for high volume input Sorts a table of cities on the city field Retrieves records matching criteria into Output range from Database

named InputScreen on the display and monitors the user’s entering of the data into that screen. It controls the cursor, prevents entry in unwanted areas, etc. Figure 1 is a typical screen. Data input screens shown by range input commands offer facilities to the applications developer which lead to better design and control of input for applications. Computer professionals should encourage its use. The Lotus Development Corporation should add further screen control features such as color, cursor control, etc. 3.2 Logic Functions Logic unctions in l-2-3 are contained in an IF statement. For example: {If Cost < 0) (Down}Err - , Figure 1. A sample input screen displayed with the range

input. Inventory Data Input Screen Directions: Enter one inventory item at a time. Use the arrow keys to lmove to the next column. Press enter twice to finish along a~

( Item Item Number 1 Description 23345

T-Bar, yellow

I Number I Cost per f Selling 1 onHand 1 Item t Price 500

$100.00

$131.00

3. SYSTEMS SOFTWARE 1990;13: 197-200

Spreadsheet Macro Programming which means, if the contents of Cost are less than zero, then the cursor is moved down one cell and the word “Err” is typed in that cell. This statement might be used for logic and display in an edit routine. The IF statement does not provide an explicit “else” facility to specify actions for the not true case. In l-2-3, when a logic statement is not true, the next program line is simply executed. The lack of an “else” facility enforces use of the Branch command (l-2-3 GOT0 verb) to avoid the “not true” command. Use of the GOT0 too easily leads to misuse and progr~ing logic errors. Lack of an “else” and forced use of a GOT0 is an unfortunate consequence of the l-2-3 language design. To avoid this problem, other programming languages use an IF. .THEN.. .ELSE format using the THEN to explicitly designate the true path and the ELSE to explicitly designate the path for the “not true” situation. These changes could be made to l-2-3 as follows: {Down} (If cost c 0) {Then) Err{Else) OK.( Endif} These changes would add au explicit IF-THEN-ELSE format to l-2-3 and improve the syntax of l-2-3 to add indenting, significant blanks and an explicit ending to the IF statement. Indenting and significant blanks add to the readability of a program but are not allowed in l-2-3. The explicit ending to the IF statement is also missing. These are design weaknesses of l-2-3 which have been overcome in modem programming languages. For the end users, these are design weaknesses which decrease program quality in sensitive logic functions. l-2-3 should be provided with a better syntax for logic functions. 3.3 Definition Functions for Data and Variables The data characteristics of variables (called ranges) are taken from the l-2-3 conventions for spreadsheet data. The details of these conventions are too numerous to review here. In general, the data definition facilities are unrelated to those of major programming languages and a deliberate effort must be made to document and structure the definitions. For this purpose l-2-3 provides a rudimentary range name table. The table only identifies named memory locations. Facilities such as cross-referencing of program variables, characteristics of data, etc., are not available. At a minimum, the end user developer should be encouraged to maintain a

199

current range name table in each application and the Lotus Development Corporation should add more analytical facilities to support analysis of data and variable definitions. 3.4 Transfer of Control Functions 1-2-3’s transfer of control commands are illustrated in

Table 2. The commands are conventional and support modem structured programming reasonably well except the DO-WHILE command. The l-2-3 DO-WHILE (written as “For”) does a subroutine instead of in-line code. The lack of a “Do in-place” makes the programs harder to read and more difficult to debug and maintain The Lotus Development Corporation should support a Do in-place and add an explicit ending to help in stocking programs. l-2-3 contains another transfer of control command which is not needed and should be dropped, the {Dispatch}. This statement modifies a GOT0 statement to proceed to a new location without a trace of the change. Use of similar commands in other languages has been widely conde~ed for creating difficulties in maintenance and debugging, e.g., the Alter in COBOL. End user developers should avoid the use of (Dispatch} in l-2-3 applications. The Lotus Development Corporation should remove the (Dispatch] from the language. l-2-3 also provides a strong non-procedural transfer of control for menus. l-2-3 menus are abbreviated displays in the top rows of screens used to solicit a choice of several possible actions from the user of an application. Table 2 illustrates the Menucall command used for display of menus, and Figure 2 shows a resulting menu. Figure 2 illustrates a menu displayed with the l-2-3 menu facilities. After the user chooses one of the actions, the macro then transfers control to the appropriate code or subroutine for that choice. This command eases the burden of writing interactive menus. Use of the menu co-eds help structure applications and should be encouraged. This feature is a strength of l-2-3. 3.5 Table Handling and Data Base Functions In l-2-3, a data base is a simple internal flat file consisting of rows of records sequentially arranged in Figure 2. A menu choice screen from execution of a typical l-2-3 menu (cursor is on the first choice). File Sort wu Input data for inventory

Report

Quit

(main part of screen with supplementary information for the menu)

200

J

C. Litecky

SYSTEMS SOFTWARE 1990; 13:197-200

the spreadsheet. Each row is a record; each column in every record is a field or data item. Many other languages would treat such a data base as an array accessed through subscripted variables. However, in 1-23, subscripting is not well developed. Primitive subscripting facilities are only available in the (Put} statement. Subscripting could be made more powerful if tabular data could be explicitly defined as an array with subscripts controlling array access. For specific data base manipulation, l-Z-3 has high level, non-procedural commands to sort find, extract, delete data, etc. Examples are shown in Table 2. The commands are unsophisticated. For example, the user with a data query must build a table of conditions expressed in formulae. The natural language queries proven in fourth generation languages and data base packages are lacking. This lack forces developers to build custom assists for users’ data maintenance. This is a weakness of the l-2-3 data base facilities. Better, more user friendly, data base facilities should be added.

4. EXPERIENCE WITH APPLICATIONS DEVELOPMENT AND TEACHING IN LOTUS l-2-3 The author has been writing l-2-3 applications and teaching systems development in l-2-3 for about three years. Over 200 students in eight classes have used l-2-3 to develop typical end user applications. These three years of writing and teaching l-2-3 have been a refreshing contrast to the preceding 15 years of writing and teaching in conventional third generation programming languages. Lotus l-2-3 deserves much of the credit. Credit is due l-2-3 because applications which would have taken months of effort in conventional programming languages generally took weeks or less and were implemen~ble within the bounds of a 15-week class. For example: one application was a departmental inventory of personal computers and software. The application contained two input screens, a flat file data base of several hundred records, four reports, and simple data base main~n~ce functions. Most students developed the application in about 100 hours (including analysis, design, testing, and documentation). In l-2-3, students can write reasonable code and complete useful applica-

equal sign in the assignment statement made worse by a misleading error message). There is a need to improve this language, but, overall, the experience is positive. Teaching and applications development in l-2-3 is expected to continue and grow. 5. CONCLUSIONS

While many of the design characteristics of l-2-3 support structured applications, such as subroutines, DOWhile, etc., many others are missing or partially supported such as the IF. .THEN.. .ELSE and explicit ends, indenting to show structure, a DO in-place s~tement, etc. More capability for structured programming is needed in the l-2-3 language. l-2-3 also has non-procedural facilities which support high quality applications development, e.g., the range input and the menu commands support interactive, user friendly systems. The design of l-2-3 is too likely to result in applications which compromise application quality. Computer professionals can insist on better macro programming language support for their end users. The Lotus Development Corporation should respond with facilities that better aid structured development of applications.

REFERENCES 1. G. B. Davis, Caution: User developed systems can be

2.

3. 4. 5.

6.

tions .

There are negatives. l-2-3 applications run very slowly and lack of structured progra~ng facilities cause problems, e.g., annoyingly and error-prone circumIocutions in logic. The syntax has many small glitches that cause the inexperienced hours of difficulty (such as the comma instead of a more conventional

AND RECOMMENDATIONS

7.

8.

dangerous to your organi~tion, in Proceedings of the Joint Znternationa~ Symposium: Znformution Systems, Australian Computer Society, Sydney, 1986. R. Klepper and M. Sumner, End user application characteristics and end-user technology, in Proceedings Southwest Decision Sciences Institule Conference, Houston, Texas, March, 1987. P. H. Lewis, Lotus i-2-3 faces up to the upstarts, The New York Times, March 13, 1988, page 8. J. Martin, Applications Development without Programmers, Prentice Hall, 1983. B. Shneiderman, Empirical studies of programmers: The territory, paths and destinations, in E. Solloway and S. Iyengar, eds., Empirical S&dies of Programmers, Ablex Publishing Corp., 1986, pp. 1-12. M. Sumner and R. Klepper, Information systems strategy and end-user applications development, Data Base, Summer, 1987, pp. 19-30. M. Van Name and W. Catchings, New language-linked developments are closing the mainfr~e-~ gap, PC’ W&XX,November, 1986, pp. 184-185. R. Weber, Planning and control issues in end-user computing, The Australian Computer J., 18, pp. 159- 165, 1986.