home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Crawly Crypt Collection 1
/
crawlyvol1.bin
/
apps
/
spread
/
sheet_20
/
shtdoc20
/
shtdoc20.man
< prev
Wrap
Text File
|
1988-10-10
|
231KB
|
6,073 lines
SHEET -- FOR ATARI ST
A BASIC interpreter in a spreadsheet environment
Atari, 520 ST and ST are trademarks of Atari Corporation
GEM is trademark of Digital Research, Inc.
Lotus 123 is trademark of Lotus Development Corporation
Copyright (C) 1988, Chor-ming Lung. All rights reserved.
Table of Content ________________
Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Disclaimer . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
PART I -- TUTORIAL . . . . . . . . . . . 3
Example 1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Example 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Example 3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Example 4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Example 5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Example 6 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Example 7 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Example 8 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Example 9 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
PART II -- Spreadsheet operations . . . . . . . . 22
MENU SELECTIONS . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Desk . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
About SHEET . . . . . . . . . . . . . . . . . . . . . . . 23
File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
New N . . . . . . . . . . . . . . . . . . . . . . . . 23
Load L . . . . . . . . . . . . . . . . . . . . . . . . 23
Load WKS . . . . . . . . . . . . . . . . . . . . . . . . 23
Load PART . . . . . . . . . . . . . . . . . . . . . . . . 23
Save S . . . . . . . . . . . . . . . . . . . . . . . . 23
Save WKS . . . . . . . . . . . . . . . . . . . . . . . . 23
Save PART . . . . . . . . . . . . . . . . . . . . . . . . 23
Print P . . . . . . . . . . . . . . . . . . . . . . . . 23
Quit Q . . . . . . . . . . . . . . . . . . . . . . . . 23
Sheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Erase . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Defaults . . . . . . . . . . . . . . . . . . . . . . . . 23
Options . . . . . . . . . . . . . . . . . . . . . . . . . 24
Dates . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Row/Col . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Insert I . . . . . . . . . . . . . . . . . . . . . . . . 25
Delete D . . . . . . . . . . . . . . . . . . . . . . . . 25
Col widths . . . . . . . . . . . . . . . . . . . . . . . 25
Cells . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Reformat R . . . . . . . . . . . . . . . . . . . . . . . 25
Erase E . . . . . . . . . . . . . . . . . . . . . . . 25
Copy C . . . . . . . . . . . . . . . . . . . . . . . 25
Move M . . . . . . . . . . . . . . . . . . . . . . . 25
Adjust A . . . . . . . . . . . . . . . . . . . . . . . 25
Graph . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
- i -
MOVING AROUND . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
EDITING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Types of cell entry . . . . . . . . . . . . . . . . . . . . . 28
Formula . . . . . . . . . . . . . . . . . . . . . . . . . 28
Constant . . . . . . . . . . . . . . . . . . . . . . . . 28
Statement . . . . . . . . . . . . . . . . . . . . . . . . 28
Command . . . . . . . . . . . . . . . . . . . . . . . . . 28
Text . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Rules for constructing formula . . . . . . . . . . . . . . . . 29
Cell input . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Move the editing cursor . . . . . . . . . . . . . . . . . 30
Modify your input . . . . . . . . . . . . . . . . . . . . 30
Terminate your input . . . . . . . . . . . . . . . . . . 30
Miscellaneous operations . . . . . . . . . . . . . . . . 30
Modify a cell . . . . . . . . . . . . . . . . . . . . . . . . 31
Editing direction . . . . . . . . . . . . . . . . . . . . . . 31
CUSTOMIZED DISPLAY . . . . . . . . . . . . . . . . . . . . . . . . 32
Change column width . . . . . . . . . . . . . . . . . . . . . 32
Default display format . . . . . . . . . . . . . . . . . . . . 33
Display format: . . . . . . . . . . . . . . . . . . . . . . . 33
Block . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
De-select blocks . . . . . . . . . . . . . . . . . . . . . . . 34
Change display format . . . . . . . . . . . . . . . . . . . . 35
COPY AND MOVE . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Relative and Absolue cell reference . . . . . . . . . . . . . 37
ERASE CELLS . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
INSERT AND DELETE . . . . . . . . . . . . . . . . . . . . . . . . . 39
Row/Column selection . . . . . . . . . . . . . . . . . . . . . 39
LOAD, SAVE AND PRINT . . . . . . . . . . . . . . . . . . . . . . . 40
File extension . . . . . . . . . . . . . . . . . . . . . . . . 40
Load . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Save . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Load PART . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Save Part . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Lotus files . . . . . . . . . . . . . . . . . . . . . . . . . 41
Print . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
PART III -- SHEET BASIC . . . . . . . . . . 43
Cells and Variables . . . . . . . . . . . . . . . . . . . . . . . . 44
Cell reference with dimension . . . . . . . . . . . . . . . . 44
Variables . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Cell reference with variables . . . . . . . . . . . . . . . . 44
String . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
String operators String operators . . . . . . . . . . . . . . . . . . . . . . . 45
Flow control Statements . . . . . . . . . . . . . . . . . . . . . . 47
IF .. THEN .. ELSE .. . . . . . . . . . . . . . . . . . . . . 47
GOTO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
GOSUB .. RETURN . . . . . . . . . . . . . . . . . . . . . . . 47
- ii -
LOOP .. EXIT IF .. ENDLOOP . . . . . . . . . . . . . . . . . . 47
FOR .. NEXT . . . . . . . . . . . . . . . . . . . . . . . . . 48
RUN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
I/O Statements . . . . . . . . . . . . . . . . . . . . . . . . . . 49
REDIRECT TO . . . . . . . . . . . . . . . . . . . . . . . . . 49
PRINT statement . . . . . . . . . . . . . . . . . . . . . . . 49
Miscellaneous statements . . . . . . . . . . . . . . . . . . . . . 51
SAVE SCREEN . . . . . . . . . . . . . . . . . . . . . . . . . 51
RESTORE SCREEN . . . . . . . . . . . . . . . . . . . . . . . . 51
WAIT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
DEFINE . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
MESSAGE . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Text files . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
GET_FILE . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
OPEN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
CLOSE . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
INPUT #n . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
PRINT #n . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
Worksheet commands . . . . . . . . . . . . . . . . . . . . . . . . 55
COPY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
MOVE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
ERASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
REFORMAT . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
SORT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
SWAP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
Console commands . . . . . . . . . . . . . . . . . . . . . . . . . 57
CLS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
CURSORON . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
CURSOROFF . . . . . . . . . . . . . . . . . . . . . . . . . . 57
VIDEOHIGH . . . . . . . . . . . . . . . . . . . . . . . . . . 57
VIDEONORM . . . . . . . . . . . . . . . . . . . . . . . . . . 57
GOTOXY . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
INKEY . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
UNGET . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
INPUT . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
Graphic commands . . . . . . . . . . . . . . . . . . . . . . . . . 59
Setup commands . . . . . . . . . . . . . . . . . . . . . . . . 60
WINDOW . . . . . . . . . . . . . . . . . . . . . . . . . 60
VIEWPORT . . . . . . . . . . . . . . . . . . . . . . . . 61
SCALE . . . . . . . . . . . . . . . . . . . . . . . . . . 61
CLEAR . . . . . . . . . . . . . . . . . . . . . . . . . . 61
EXITGRAPH . . . . . . . . . . . . . . . . . . . . . . . . 61
Line/Dot commands . . . . . . . . . . . . . . . . . . . . . . 62
ATTRIBUTE . . . . . . . . . . . . . . . . . . . . . . . . 62
LINE . . . . . . . . . . . . . . . . . . . . . . . . . . 62
LINE_TO . . . . . . . . . . . . . . . . . . . . . . . . . 62
PLOT . . . . . . . . . . . . . . . . . . . . . . . . . . 63
PLOT DATA . . . . . . . . . . . . . . . . . . . . . . . . 63
SPLINE . . . . . . . . . . . . . . . . . . . . . . . . . 63
Turtlegraphics . . . . . . . . . . . . . . . . . . . . . . . . 64
- iii -
FORWARD . . . . . . . . . . . . . . . . . . . . . . . . . 64
TURN . . . . . . . . . . . . . . . . . . . . . . . . . . 64
TURN_TO . . . . . . . . . . . . . . . . . . . . . . . . . 64
Axis command . . . . . . . . . . . . . . . . . . . . . . . . . 65
MARKX . . . . . . . . . . . . . . . . . . . . . . . . . . 65
MARKY . . . . . . . . . . . . . . . . . . . . . . . . . . 65
LABELX . . . . . . . . . . . . . . . . . . . . . . . . . 65
LABELY . . . . . . . . . . . . . . . . . . . . . . . . . 65
Text command . . . . . . . . . . . . . . . . . . . . . . . . . 66
PTEXT . . . . . . . . . . . . . . . . . . . . . . . . . . 66
Miscellaneous . . . . . . . . . . . . . . . . . . . . . . . . 67
MOUSE_POS . . . . . . . . . . . . . . . . . . . . . . . . 67
Tips on using SHEET BASIC . . . . . . . . . . . . . . . . . . . . . 68
Interrupt . . . . . . . . . . . . . . . . . . . . . . . . . . 68
Using previous row format . . . . . . . . . . . . . . . . . . 68
Error Error . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
Multiple statements . . . . . . . . . . . . . . . . . . . . . 68
Garbled screen . . . . . . . . . . . . . . . . . . . . . . . . 68
Adjust . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
Appendix A BUILT-IN FUNCTIONS . . . . . . . . . . . . . . . . . . . 69
Math . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
General functions . . . . . . . . . . . . . . . . . . . . 69
ABS . . . . . . . . . . . . . . . . . . . . . . . . 69
INT . . . . . . . . . . . . . . . . . . . . . . . . 69
FRAC . . . . . . . . . . . . . . . . . . . . . . . . 69
FACT . . . . . . . . . . . . . . . . . . . . . . . . 69
SQR . . . . . . . . . . . . . . . . . . . . . . . . 69
SQRT . . . . . . . . . . . . . . . . . . . . . . . . 69
Trigo & logarithm functions . . . . . . . . . . . . . . . 69
SIN . . . . . . . . . . . . . . . . . . . . . . . . 69
COS . . . . . . . . . . . . . . . . . . . . . . . . 69
TAN . . . . . . . . . . . . . . . . . . . . . . . . 69
ASIN . . . . . . . . . . . . . . . . . . . . . . . . 69
ACOS . . . . . . . . . . . . . . . . . . . . . . . . 69
ATAN . . . . . . . . . . . . . . . . . . . . . . . . 69
LOG . . . . . . . . . . . . . . . . . . . . . . . . 69
LN . . . . . . . . . . . . . . . . . . . . . . . . . 69
EXP . . . . . . . . . . . . . . . . . . . . . . . . 69
RAD_DEG . . . . . . . . . . . . . . . . . . . . . . 69
DEG_RAD . . . . . . . . . . . . . . . . . . . . . . 69
DATE functions . . . . . . . . . . . . . . . . . . . . . 69
DATE . . . . . . . . . . . . . . . . . . . . . . . . 69
DAY . . . . . . . . . . . . . . . . . . . . . . . . 69
MONTH . . . . . . . . . . . . . . . . . . . . . . . 69
YEAR . . . . . . . . . . . . . . . . . . . . . . . . 69
Matrix functions . . . . . . . . . . . . . . . . . . . . 69
INVERSE . . . . . . . . . . . . . . . . . . . . . . 69
TRANSPOSE . . . . . . . . . . . . . . . . . . . . . 69
Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . 70
AVERAGE . . . . . . . . . . . . . . . . . . . . . . . . . 70
MEDIAN . . . . . . . . . . . . . . . . . . . . . . . . . 70
STD . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
VAR . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
SQR_SUM . . . . . . . . . . . . . . . . . . . . . . . . . 70
- iv -
Miscellaneous . . . . . . . . . . . . . . . . . . . . . . . . 70
MAX . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
MIN . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
SUM . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
RAND . . . . . . . . . . . . . . . . . . . . . . . . . . 70
IFF . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
ISEMPTY . . . . . . . . . . . . . . . . . . . . . . . . . 70
TRUE . . . . . . . . . . . . . . . . . . . . . . . . . . 70
FALSE . . . . . . . . . . . . . . . . . . . . . . . . . . 70
ALERT . . . . . . . . . . . . . . . . . . . . . . . . . . 70
COL_WIDTH . . . . . . . . . . . . . . . . . . . . . . . . 71
LAST_ROW . . . . . . . . . . . . . . . . . . . . . . . . 71
LAST_COL . . . . . . . . . . . . . . . . . . . . . . . . 71
SEARCH . . . . . . . . . . . . . . . . . . . . . . . . . 71
Finance . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
Future growth . . . . . . . . . . . . . . . . . . . . . . 71
GRATE . . . . . . . . . . . . . . . . . . . . . . . 71
GBASE . . . . . . . . . . . . . . . . . . . . . . . 71
GFUTURE . . . . . . . . . . . . . . . . . . . . . . 71
Investment . . . . . . . . . . . . . . . . . . . . . . . 71
INVEST_PV . . . . . . . . . . . . . . . . . . . . . 71
INVEST_FV . . . . . . . . . . . . . . . . . . . . . 72
INVEST_INTEREST . . . . . . . . . . . . . . . . . . 72
INVEST_TERM . . . . . . . . . . . . . . . . . . . . 72
Loan . . . . . . . . . . . . . . . . . . . . . . . . . . 72
LOAN_PAY . . . . . . . . . . . . . . . . . . . . . . 72
LOAN_AMOUNT . . . . . . . . . . . . . . . . . . . . 72
LOAN_TERM . . . . . . . . . . . . . . . . . . . . . 72
Withdraw . . . . . . . . . . . . . . . . . . . . . . . . 72
WDRAW_SAVE . . . . . . . . . . . . . . . . . . . . . 72
WDRAW_AMOUNT . . . . . . . . . . . . . . . . . . . . 72
WDRAW_TERM . . . . . . . . . . . . . . . . . . . . . 72
Deposit . . . . . . . . . . . . . . . . . . . . . . . . . 72
DEPOSIT_FV . . . . . . . . . . . . . . . . . . . . . 72
DEPOSIT_AMOUNT . . . . . . . . . . . . . . . . . . . 72
DEPOSIT_TERM . . . . . . . . . . . . . . . . . . . . 72
String functions: . . . . . . . . . . . . . . . . . . . . . . 72
FILENAME$ . . . . . . . . . . . . . . . . . . . . . . . . 72
BUF$ . . . . . . . . . . . . . . . . . . . . . . . . . . 72
MID$ . . . . . . . . . . . . . . . . . . . . . . . . . . 72
LEFT$ . . . . . . . . . . . . . . . . . . . . . . . . . . 73
RIGHT$ . . . . . . . . . . . . . . . . . . . . . . . . . 73
SPACE$ . . . . . . . . . . . . . . . . . . . . . . . . . 73
CHR$ . . . . . . . . . . . . . . . . . . . . . . . . . . 73
STR$ . . . . . . . . . . . . . . . . . . . . . . . . . . 73
VAL . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
LEN: . . . . . . . . . . . . . . . . . . . . . . . . . . 73
POS . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
Appendix B CONTROL KEYS . . . . . . . . . . . . . . . . . . . . . . 74
Non-editing . . . . . . . . . . . . . . . . . . . . . . . . . 74
Editing . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
Appendix C Program specification . . . . . . . . . . . . . . . . . 75
Appendix D Lotus and SHEET . . . . . . . . . . . . . . . . . . . . 76
- v -
Appendix E Recalculation . . . . . . . . . . . . . . . . . . . . . 77
Appendix F FILE FORMAT . . . . . . . . . . . . . . . . . . . . . . 78
Header for SHT . . . . . . . . . . . . . . . . . . . . . . . . 78
Cell contents for SHT format . . . . . . . . . . . . . . . . . 78
Header for PRT . . . . . . . . . . . . . . . . . . . . . . . . 78
Cell contents for PART format . . . . . . . . . . . . . . . . 78
Appendix G Problems . . . . . . . . . . . . . . . . . . . . . . . . 78
Biorthym program . . . . . . . . . . . . . . . . . . . . . . . . . 80
Calendar program . . . . . . . . . . . . . . . . . . . . . . . . . 82
Roots . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
- vi -
SHEET User manual By Chor-ming Lung October 9, 1988
Preface: ________
I have used spreadsheet programs for quite a while. I feel
they are fantastic tools. I am quite familiar with Lotus 123. One
thing that I feel terrible is its MACRO language. It is not as
user-friendly as its display. I feel that BASIC is more powerful
and yet easier to use than MACRO.
Thinking that may be profitable to write a BASIC interpreter
in a spreadsheet environment, I started to write the program on
Oct. 1987. After a few months' struggle, I have created the
program and named it SHEET.
Making SHEET as shareware, I uploaded the first version on
February 1988 to GEnie and CompuServe. Right now, SHEET can do not
only numerical calculation, but also, graphic, string operations
and text files access. Its growth depends on you. If you like it,
please send me $25 or whatever you think it is worth. My address
is:
Mr. Chor-ming Lung
330 Tremont St A-708
Boston, MA 02116
U.S.A.
You can also send your comments or suggestions by Email. My
addresses are:
GEnie: LUNG
CompuServe: 72740,40
If you have access of GEnie, you can also leave your message
on the bulletin board. It is Page 475, Category 2, Topic 28.
Disclaimer: __________
I have made every effort to insure the accuracy of the
program. However, there is no warranty either express or implied
for its uses.
Requirements: ____________
* Atari 520 ST or 1040 ST with disk drive(s).
* Monochrome or color monitor.
* SHEETxxx.PRG, CALC.RSC and SHEET.CFG on the same directory. (xxx
is the version number of SHEET. Currently, it is SHEET20.PRG or
version 2.0)
* You must know how to operate with GEM.
* Some BASIC programming experience is desirable if you want to
use SHEET BASIC effectively.
Page 1
SHEET User manual By Chor-ming Lung October 9, 1988
Introduction: ____________
Thank you for using SHEET. SHEET is a combination of spreadsheet
and BASIC. The spreadsheet functions and commands make this BASIC
interpreter a lot easier to handle data and the BASIC interpreter make
the spreadsheet a lot powerful than many other spreadsheet programs
commercially available.
The arrangement of this manual consists of three parts. The first
part consists of 9 example worksheets as a tutorial. The second part
shows the details of how to use the spreadsheet commands. The last part
consists of BASIC commands and their usage.
If you have problems in locating cell cursor with the mouse, you
should read Appendix G. You should read example 1 thoroughly. It
contains informations for operating the spreadsheet. Example 3 is a
trivial program but it is an important one too. If you do not
understand the cell reference method in this program. You cannot
understand the rest examples. I have tried my best to describe how it
works. If you still have problem, you should read PART III about cell
reference.
There are three "big" program listings after the Appendixes. They
use many commands of SHEET BASIC. You should not miss them too.
Finally, I would like to thank all the users who provide
suggestions to make SHEET more useful and reliable. I like to thank Mr.
Donald A. Edwards and Dr. Stan Liebowitz specifically. Without their
enthusiasm, I can hardly imagine some of the features found in version
2.0. If you have made suggestions but they are not implemented yet, it
is not because they are being ignored. Instead, I do not have enough
time to get everything done on this version.
Page 2
SHEET User manual By Chor-ming Lung October 9, 1988
PART I -- TUTORIAL
Example 1: "AMORT.SHT"
This example shows you how to operate with SHEET. A lot of
the basic ideas of how to use the mouse and your keyboard with
your worksheet are being introduced. If you understand it, you can
read PART II without any difficulty.
Example 2: "ROSES.SHT"
This example shows you some advanced feature of spreadsheet
design. It introduces the idea of absolute and relative cell
reference.
Example 3: "RANDOM.SHT"
This example is a simple SHEET BASIC program. The main idea
introduces in this example is a new cell reference method. You can
use the cell as a cell reference or as a BASE which you can
stretch horizontally or vertically.
Example 4: "LOTTERY.SHT"
This example shows you how to print your output to your
printer. It generates 6 different numbers from 1 to 36, and sorts
those numbers in ascending order, and then prints those numbers to
your printer.
Example 5: "FTEST.SHT"
This file shows you the commands for ASCII file accesses.
Example 6: "NUMBER.SHT"
This file shows you how to use the file access commands to
retrieve data stored in ASCII file and then put them into
individual cells.
Example 7: "GRID.SHT"
This first graphic program shows the basic process of using
the graphic commands.
Example 8: "SINCURVE.SHT"
This file shows you how to draw a sine curve with the graphic
commands.
Example 9: "SPLINE.SHT"
This file shows you how to draw graph using data stored in
your worksheet.
Page 3
SHEET User manual By Chor-ming Lung October 9, 1988
Example 1: _________ AMORT.SHT
Amortization means killing something bit by bit. That is the
way we handle loans -- we pay little by little.
Suppose you are trying to borrow $100,000. Your financial
company provides two options for this loan.
- 30 years with annual interest rate 9%
- 20 years with annual interest rate 10.5%
Intuitively, you may choose option 1. Well, let us analyze this
problem with SHEET and see whether it is a wise choice.
First, we have to design our layout. We plan to use column A
for labels, column B for option 1 and column C for option 2.
Following are the steps for creating the template:
Cell entry Cell cursor Menu option/Description
---------------------------------------------------------
Find menu title "Sheet" and
select "Defaults". Choose Text
justification for Center (^)
and select OK.
B1 To move your cell cursor to B1,
either move the mouse cursor to
B1 and click the mouse button,
or use the arrow keys to move
the cell cursor. If you use the
mouse to move your cell cursor
and if the cell cursor
disappears, then you should
refer to Appendix F.
Option 1<Return> Type "Option 1" and terminate
it with <Return> key.
C1 Press the -> arrow key to move
from cell B1 to C1.
Option 2<Return>
B2
\-<Return> Type "\-" and terminate it with
<Return> key.
C2
\-<Return>
Find menu title "Sheet" and
choose "Defaults". Choose Text
justification for Right (")
A3
Amount borrowed<Return>
A4
Annual interest rate<Return>
A5
Total number of terms<Return>
A6
Terms per year<Return>
A7
Page 4
SHEET User manual By Chor-ming Lung October 9, 1988
\-<Return>
B7
\-<Return>
C7
\-<Return>
A8
Payment<Return>
"\-" means to fill up the cell with "-". You should notice
that entries on column A occupy some areas of column B and C. We
need to enlarge the width of column A to make rooms for entries on
column B and C. To widen column A, you need to place the mouse
pointer within the gap between column letter A and B. Press the
mouse button and drag it to the right hand side. (Alternately, you
can select the menu item "Col widths", enter the column name and
your desired width.) When you press and drag you mouse, you should
see the rubber box expanding. If the rubber box is large enough
to cover all the characters of column A, then you can release your
mouse button. Every text in column A is right justified now.
Well, a template without formulas is not a template at all.
The formula we need for this calculation is "LOAN_PAY". It needs
four arguments -- amount borrowed, annual interest rate, total
number of terms, terms per year. That is "LOAN_PAY(B3,B4,B5,B6)".
If you enter the above line into cell B8, SHEET will treat it as a
TEXT string instead of formula. That is because the leading
character of a cell entry determines its type. A cell starts with
"+", "-", digit, "(" or "." is a formula. A cell starts with alpha
character is a TEXT string. A '+' symbol before "LOAN_PAY" will
tell SHEET to treat it as a formula. (NOTE: If you are used to
Lotus 123, you may tempt to add @ symbol before "LOAN_PAY". @
symbol in SHEET has different meaning. All the functions in SHEET
does not require @ symbol.) Here is the steps for entering
"+loan_pay(b3,b4,b5,b7)" on cell B8 and copy the content of B8 to
C8.
Entry Mouse Mouse button Editing buffer
cursor display
--------------------------------------------------------------
+loan_pay( +loan_pay(
B3
Mouse click +loan_pay(B3
B4
<Control>+ Mouse click +loan_pay(B3,B4
B5
<Control>+ Mouse click +loan_pay(B3,B4,B5
B6
<Control>+ Mouse click +loan_pay(B3,B4,B5,B6
) +loan_pay(B3,B4,B5,B6)
<Return>
B8
Page 5
SHEET User manual By Chor-ming Lung October 9, 1988
<Control>+ Mouse click
C8
<Control>+ Mouse click
<Control C>
<Return>
"<Control>+Mouse click" means press the Control key and left mouse
button simultaneously. SHEET is case-insensitive. It will treat
"loan_pay" as "LOAN_PAY". After those steps, you should see
0.1e+38 on both B8 and C8. Don't panic. You are not suppose to pay
that much money even you borrow nothing. This number means
calculation error. During calculation, Lotus uses 11 bytes
(instead of 8 bytes) to store real number. Special bit pattern is
set in these 11 bytes to represent ERR. Unfortunately, I do not
have that much energy to create my own math package. So, I choose
the largest number to represent error.
It seems column B and column C are too close to each other.
You should be able to change the column width of column B and C
without any difficulty now. How wide should they be? It is up to
you.
It is time to enter our data to do analysis.
Entry Cell cursor
-----------------------------------
B3
100000 <Down arrow>
B4
0.09 <Return>
B5
360 <Return>
B6
12 <Return>
After you have typed 100000, you terminate your input by the
down arrow key. This action changes the editing direction to DOWN.
Whenever you terminate your input with <Return>, the cell cursor
will automatically move DOWN. B3 holds the $100,000 you want to
borrow. B4 holds the annual interest rate 9%. B5 holds the total
number of terms you must pay back to your financial company. It is
supposed to be a monthly payment. You have to pay 12 times each
year. Hence, the total number is 30*12 or 360. B6 holds the number
of terms per year. It is 12. NOTE: if your payment is collected
quarterly, then B6 should be 4 and B5 should be 30*4 or 120.
It is an exercise for you to enter 100000, 0.105, 240 and 12
into cells C3, C4, C5, and C6 are respectively. The payment for
option 1 is 804.622617 and it is 998.379887 for option 2. Probably
Page 6
SHEET User manual By Chor-ming Lung October 9, 1988
your financial company will ask you to pay $804.62 and $998.38
respectively. To reflect this fact, you must reformat your
display. We need to change B3..C3 and B8..C8 to DOLLAR format and
B4..C4 to percent (%) format.
Key Mouse button Mouse cursor position
----------------------------------------------------
B3
<Control>+Mouse click
C3
<Shift>+ Mouse click
B8
<Control>+Mouse click
C8
<Shift>+ Mouse click
<Control R>
Select DOLLAR
The above operations define two blocks. <Control>+mouse click
on a cell defines the start of a block and <Shift>+Mouse click on
a cell defines the end of a block. The first block is B3..C3 and
the second block is B8..C8. <Control R> activates the reformat
dialog box. Selection DOLLAR will change cells contained in those
blocks to DOLLAR format. I think it is an easy exercise for you to
change B4..C4 to percent format.
The payment just tells us we need to pay more for less terms.
That is natural. The hidden truth is: we pay more money in option
1 than option 2. How can we find it? Well, we pay 804.62 each
month, and we need to do it 360 times. So, the total amount we pay
for option 1 is 360*804.62, and 240*998.38 for option 2. In cell
A9, enter "Total payment". In B9, enter 360*b8 and in C9, enter
240*c8. Comparing the results on B9 and C9, you will find you pay
$50,000 less in option 2. So, if you can affort $998.38 per month,
you should choose option 2.
Now, it is up to you whether you want to save your first
worksheet. If you think you don't need it any more. Press the
window close button and SHEET will give you a last chance to save
it. If you want to save it for future reference, you should press
<Control S> and enter the file name in the file selector dialog
box.
You may even want to print it out to show your spouse that
his/her decision is right or wrong. The following steps will print
cells A1..C10.
Key Mouse button Mouse cursor
----------------------------------------------------
A1
<Control>+Mouse click
C10
Page 7
SHEET User manual By Chor-ming Lung October 9, 1988
<Shift>+ Mouse click
<Control P>
Fill out the Print dialog box
<Return>
After this example, I assume you can operate this program without
any difficulty. The tedious description of cell entries and mouse
operations will not appear in the following examples.
Page 8
SHEET User manual By Chor-ming Lung October 9, 1988
Example 2: _________ ROSES.SHT
This is an inventory problem. Assume that you are selling
roses. Each dozen costs 4 dollars and you are selling for 12
dollars per dozen. According to your pass experience, you know the
probabilities you can sell 10 dozens, 11 dozens, 12 dozens in one
day are 0.60, 0.23, 0.17. How many dozens of roses should you
order to get the best profit?
PROFIT= 12 * actual number of dozens sold - 4 * number of dozens
ordered
For example, if you order 11 dozens and you sell only 10 dozens of
them, then you earn:
PROFIT = 12*10-4*11=76
Here we need to create the array of profits for actual
calculation.
ordered/ 10 11 12
sold
10 80 76 72
11 80 88 84
12 80 88 96
You may ask what is the meaning of ordered 10 and sold 11? It
means you sell all the 10 dozens of roses and still there are
customers asking for roses. The number of roses sold includes
actual number of dozens sold and number of dozens customers
asking. If you have sold all the roses you have, you cannot give
anything to the late customers. So, your profit is 12*10-4*10=80.
If you are using calculator to find out those number, then you are
not a good spreadsheet user. To calculate the profit table, you
must use formula. Now, we assume that the numbers ordered are
stored from C1..E1 and the numbers sold are stored from B3..B5.
The function we need is IFF which means IF Function (I choose this
name because IF is a reserved key word for BASIC). This function
require 3 arguments. The first one is a conditional expression,
the second one is an expression returned when the condition is
true, the last one is an expression returned when the condition is
false.
Here is the logic: IF the number of dozens we order is greater
than the number of dozens we sell, we use the following formula to
calculate PROFIT:
PROFIT = 12 * dozens ordered - 4 * dozens sold
That is because we can sell no more than we ordered. On the other
hand, if the number of dozens we order is not greater than the
number of dozens we sell, then we can sell as many as we can. The
PROFIT will be:
PROFIT = 12 * dozens sold - 4 * dozens ordered
Page 9
SHEET User manual By Chor-ming Lung October 9, 1988
So, we enter the following formula in C3:
+IFF(C1<B3,12*C1-4*C1,12*B3-4*C1)
Now, we can copy this formula to the rest of the table. The COPY
command does not handle overlap cells correctly. So, we need to do
it twice. The first COPY copies C3 to C4..C5 and the second one
copies C3 to D3..E5.
If you compare the result you get from SHEET with the above table,
you may say, column C is correct, but column D and E is totally
different. What is wrong? Let us examine the content in cell D3.
It is:
+IFF(D1<C3,12*D1-4*D1,12*C3-4*D1)
COPY command will copy the source to destination and modify the
destination cell if its content refers to other cells. The column
letter in C3 is increased by one in D3. Let us examine the cell
content in cell D4. It is:
+IFF(D2<C4,12*D2-4*D2,12*C4-4*D2)
In cell D4, not only the column letter changes but also the row
number changes. Well, the cell reference we choose is called
relative cell reference. We want COPY command just changes what we
want but not every cell reference. To do this, we need to add a $
symbol before the column letter or the row number. The corrected
formula for cell B3 should be:
+IFF(C$1<$B3,12*C$1-4*C$1,12*$B3-4*C$1)
and you should do the COPY again.
Now, we need to find the EXPECTED PROFIT. It is:
EP = prob selling 10 * PROFIT selling 10 + prob selling 11 *
PROFIT selling 11 + prob selling 12 * PROFIT selling 12
You can setup the one formula and copy it to find EP for ordering
10, 11 and 12. But you can also use the MATRIX function to
calculate it easily. Assume we put the probabilities (0.6, 0.23,
0.17) in cells C7..E7. Enter "Expected profit" in cell A8 and
enter the formula "+matrix(c8,[c7..e7]*[c3..e5]) in cell A7. You
should get 80, 80.8 and 78.84 in cells C8..E8. Hence to get more
profit, you should order 11 dozens of roses.
The above two examples show you some fundamental operations and
calculations for using SHEET. The following examples will show you how
to program in SHEET BASIC. Some BASIC experience is required.
Page 10
SHEET User manual By Chor-ming Lung October 9, 1988
Example 3 _________ RANDOM.SHT
Now, I am going to show you the unusual part of this
spreadsheet program -- the BASIC interpreter. This example will
generate 100 random numbers and calculate the AVERAGE, MEDIAN,
MINIMUM, MAXIMUM, VARIANCE and STANDARD DEVIATION of those
numbers. "C1 : !R=0" means that cell C1 has the statement
"!R=0". If you want to enter the program yourself, you should move
your cell cursor to C1 and then enter "!R=0" without the double
quotes. To run the following BASIC program, please load
"RANDOM.SHT" and enter "@run c1" without the double quotes in your
editing buffer.
C1 : !R=0
C2 : ! LOOP
C3 : ! EXIT IF R=100
C4 : !A1[0,R]= RAND
C5 : !R=R+1
C6 : ! ENDLOOP
C7 : !B3= AVERAGE(A1..A100)
C8 : !B4= STD(A1..A100)
C9 : !B5= VAR(A1..A100)
C10 : !B6= MIN(A1..A100)
C11 : !B7= MAX(A1..A100)
C12 : !B8= MEDIAN(A1..A100)
The above listing is printed by SHEET. Here is a brief
explanation for each line.
C1: R is a variable and its content is initialized to 0. There is a
fundamental difference between SHEET BASIC and traditional BASIC
in handling variables -- YOU MUST initialize all the variables
first. If any calculation involved with uninitialized variables,
you will be flagged and SHEET BASIC will terminate its operation.
Other BASICs treat uninitialized variables as zero. You may have a
hard time to check typo errors. So, I FORCE users to initialize
variables to eliminate this problem.
C2: LOOP is not available in other BASICs. This flow control command
is from Modula-2. I think sometimes it is better than WHILE ..
WEND.
C3: EXIT IF is part of LOOP statement. It is an exit for the LOOP. If
the conditional expression for EXIT IF is true, then the next
command being executed is the one following ENDLOOP.
C4: RAND is the random number generating function. The left hand side
of the assignment statement is A1[0,R] which is a new way to
access or store result in cells. We treat cells in spreadsheet as
two dimensional array. You can use the column letter and row
number as a cell reference or use the cell reference as a BASE and
increases the BASE vertically or horizontally by two numbers.
These two numbers are separated by comma and embraced by "[" and
"]". For example A2[3,4] is equivalent to D6. (We assume column
letter A is 1 and B is 2 and so on. A2=[1,2] and A2[3,4] =
[1,2]+[3,4] = [4,6]= D6). In the above listing, if R=12, A1[0,R]
Page 11
SHEET User manual By Chor-ming Lung October 9, 1988
is referred to cell A13 (A1[0,12] = [1,1]+[0,12] = [1,13] = A13).
C5: Increase the variable R by one.
C6: ENDLOOP statement will force the BASIC interpreter jumps back to
C3 and test whether R=100. If it is, then the next statement being
executed is in C7.
C7: Stores the AVERAGE values of the 100 random number in cell B3
C8: Stores the STANDARD DEVIATION in cell B4
C9: Stores the VARIANCE in cell B5
C10: Stores the minimum value to B6
C11: Stores the maximum value to B7
C12: Stores the MEDIAN in cell B8
The logic of the above program will be more straight forward
if we use FOR .. NEXT statement..
You can generate 100 numbers by using the spreadsheet
commands only. In cell A1, enter "+rand". COPY a1 to a2..a100.
Enter "+average(a1..a100)" in cell b3 and so on.
Well, there are some difference between these two approach.
If you want to get another set of random numbers, you can "@run
c1" again. But if you are using the spreadsheet commands, you must
COPY it over and over again to get another 100 random numbers.
Those AVERAGE, MIN,MAX .. functions will hinder your inputs when
you are in automatic recalculation mode.
If you are programming in traditional BASICs, you know that
it is inconvenient to view the array which holds the 100 random
numbers and you need to write a lot of subroutine to calculate
AVERAGE,MIN,MAX .... SHEET provides you the spreadsheet
functionality to help you to handle your date easily and the BASIC
interpreter helps the spreadsheet perform something that many
other spreadsheet users cannot dream of. That is some of the
benefit of having a BASIC interpreter in a spreadsheet
environment.
Page 12
SHEET User manual By Chor-ming Lung October 9, 1988
Example 4: _________ LOTTERY.SHT
Everyone wants to be rich. Here is a program in SHEET BASIC
which may help you to get rich. Well, if you do get any profit,
please share it with me (grin). This is a lottery numbers
generating program. Here is the listing from SHEET.
C1 : ! REDIRECT TO "prn:"
C2 : !R=1
C3 : ! LOOP
C4 : !A1[0,R]= INT( RAND*36+1)
C5 : !J=1:F= TRUE
C6 : ! LOOP
C7 : ! EXIT IF J=R
C8 : ! IF A1[0,J]=A1[0,R] THEN F= FALSE
C9 : ! EXIT IF F= FALSE
C10 : !J=J+1
C11 : ! ENDLOOP
C12 : ! IF F= TRUE THEN R=R+1
C13 : ! EXIT IF R=7
C14 : ! ENDLOOP : SAVE SCREEN
C15 : ! SORT "A", A2..A7, A1
C16 : ! PRINT A2,A3,A4,A5,A6,A7
C17 : ! WAIT : RESTORE SCREEN : EXITGRAPH
Here is a brief explanation.
C1: REDIRECT TO a file. This I/O redirection command causes all the
subsequent PRINT commands output to the file specified. There are
some pre-defined files. "prn:" is the printer, "con:" is the
monitor and "aux:" is the rs-232 port.
C2: R is a loop counter.
C3: The outer loop for generating 6 different numbers.
C4: INT( RAND*36+1) will generate a random number from 1 to 36, and
this number is stored from A2..A7 depends on the value of R. If
R=1 then A1[0,R] will be A2 (Remember A1 is the BASE and its
coordinate is [1,1], A1[0,R] = [1,1]+[0,1]=[1,2] = A2).
C5: J is another loop counter which increases from 1 to R. F is a flag
to see whether the previous generated number has been picked. F is
TRUE means no it is not. By the way, TRUE and FALSE are pre-
defined constants. TRUE is equal to 1 and FALSE is 0.
C6: An inner loop to check whether the last number has been picked.
C7: If J=R, it means we check every numbers.
C8: Otherwise, compares each old numbers with the new number, if one
of them are equal to it, then we set F to FALSE
C9: Well, this is the second EXIT IF statement within a LOOP. You can
have multiple EXIT IF within a LOOP. There is no limit about it.
C10: Increase J by 1
C11: End of the inner loop
C12: If no numbers is equal, then we request for another random number.
C13: If 6 numbers have been picked, the loop should be terminated.
C14: SAVE SCREEN is a command to save your current display. It is
useful if you are printing on screen.
C15: SORT is a worksheet command which sort a block in descending or
ascending order. The first string "A" tells SORT to sort in
Page 13
SHEET User manual By Chor-ming Lung October 9, 1988
ascending order. A2..A7 is the block being sorted and A1 holds the
column that we used for determining the place where we try to
compare.
C16: PRINT cell A2 to A7. You can use comma or semicolon to separate
each item for PRINT statement. Comma will make the next print
position align to 10 characters wide tab.
C17: WAIT is useful when you output the data to screen. It stops all
the process just wait for you to press <Return> key. It should be
placed before you restore your screen. SORT will not change the
appearance of the updated cells. We need to refresh the screen.
EXITGRAPH is a command to refresh the screen. By the way, you can
update your screen manually by pressing <Alt-F>.
One thing you may notice in this program is I/O redirection.
Here is a reason why you need to redirect your data to disk file.
A user from Canada told me that his JUKI 6100 Daisywheel printer
does not respond to the "end of line" character. So, he prints his
worksheet to disk and then use 1st Word to print it. If I don't
support I/O redirection in SHEET BASIC, he may need to buy another
printer or stop using SHEET..
Currently, SHEET BASIC has command to redirect output only.
Future version should provide input redirection.
Page 14
SHEET User manual By Chor-ming Lung October 9, 1988
Example 5: _________ FTEST.SHT
The file name of this example is "FTEST.SHT". A1..A5 is a
program testing the output file commands. A7..A13 is a program
testing the input file commands. To run it, enter "@run a1" and
"@run a7" separately.
A1 :! OPEN "o",#1,"test.txt"
A2 :! PRINT #1,"123,456 789": PRINT #1
A3 :! PRINT #1,"5623.23,84.38 723.12"
A4 :! CLOSE #1
A5 :! END
A7 :! GET_FILE "\*.txt", FILENAME$
A8 :! OPEN "i",#1, FILENAME$
A9 :!R=1: SAVE SCREEN
A10 :! LOOP
A11 :! INPUT #1, BUF$
A12 :! PRINT BUF$
A13 :!R=R+1: ENDLOOP
A14 :! END
Explanation: Explanation
A1: Before we can access a file, we need to open it first. Statement
A1 open an output file in channel #1 and the file name is
"test.txt". Channel number is in the range of 0~9.
A2: Print the string "123,456 789" and a blank line to the file
A3: Print another string to the file. You can output not only a string
but also numeric data on the same line separated by comma or
semicolon.
A4: Close channel number 1.
A7: Use the file selector to get the file name from user. Note:
FILENAME$ is a string variable that is changed whenever you change
the content of the file selector dialog box.
A8: Open that file as input file on channel #1
A9: R is the counter for line being read. Save our current screen
first.
A10: Enter a loop to read the file line by line.
A11: Read a line from the file to BUF$. BUF$ is a string variable which
can be modified by INPUT statement only.
A12: Print the line being read to the terminal.
A13: Increase the counter of line number and repeat the loop. NOTE,
there is no EXIT IF statement. There is no way to detect end of
file in current SHEET BASIC. You must let it go forever until the
interpreter finds end of file error and stops your program. You
may have some extra "Syntax error" errors. It is normal.
NOTE: After you are done, you can enter the command "@restore
screen" to restore your screen. Also, program A7 can be used
to read other text files. You can even read this document by
choosing this file and you can stop and resume the print
action by <Ctrl-S> and <Ctrl-Q>.
Page 15
SHEET User manual By Chor-ming Lung October 9, 1988
Example 6: _________ NUMBER.SHT
The file name for the following program is "NUMBER.SHT".
There are two programs in this spreadsheet. The first program
generates data to file "test.txt". It is exactly the same program
of Example 5. L7..L16 reads the data from the file "test.txt" and
separate each of line into different cells. Subroutine P1..P11 is
used to separate each data into different cells.
L1 :! OPEN "o",#1,"test.txt"
L2 :! PRINT #1,"123,456 789": PRINT #1
L3 :! PRINT #1,"5623.23,84.38 723.12"
L4 :! CLOSE #1
L5 :! END
L7 :! GET_FILE "*.txt", FILENAME$
L8 :! OPEN "i",#1, FILENAME$
L9 :!R=0
L10 :! LOOP
L11 :! INPUT #1, BUF$
L12 :! IF ( ASC( BUF$)<>0) THEN GOSUB P1
L13 :!R=R+1
L14 :! ENDLOOP
L15 :! CLOSE #1
L16 :! END
P1 :!P=1:C=1
P2 :! LOOP
P3 :!Q= POS(" ,", BUF$,P)
P4 :! EXIT IF Q=0
P5 :!A1[C,R]= VAL( MID$( BUF$,P,Q-P))
P6 :!C=C+1
P7 :!P=Q+1
P8 :! ENDLOOP
P9 :!A1[C,R]= VAL( MID$( BUF$,P,100))
P10 :! RETURN
Explanation: Explanation
L1~L5: It is the same code for Example 5 A1~A5.
L7: Uses the file selector to get the file name
L8: Open the file for INPUT.
L9: R is a counter for row number.
L10: We enter an infinite loop to read data lines.
L11: Read a line from the data file.
L12: Test whether the line being read is empty or not. If it is not,
then we call subroutine P1 to separate the data into cells.
L13: Increase our row number by one.
L14: Repeat our loop
L15 and L16 is never being executed. They are there for decoration
only.
P1: P is the starting search position and C is the column number. It
is zero origin. C=1 means we start from column B.
P2: Enter the loop to separate BUF$ into pieces.
P3: We assume the number is separate by a blank space or comma. POS is
a function that tries to find the position of a blank space or
Page 16
SHEET User manual By Chor-ming Lung October 9, 1988
comma from BUF$ starting from P. If POS cannot find it, Q will be
zero.
P4: Check whether Q is zero. If it is, then we have checked the whole
data line for space and comma and that is the end of our loop.
P5: Store the value of BUF$ from P up to Q-1. It depends on the value
of C and R. If C is 1 and R is 0, then the value will be stored in
cell B1. (A1[1,0] is equivalent to B1).
P6: Increase our column number by one.
P7: Change our starting position for P to Q+1
P8: Repeat from P2.
P9: We still have one value after the last space or comma. It is
stored from P to the last character of BUF$. If the len of MID$ is
larger than the length of BUF$, then it returns a string from P to
the last character of BUF$.
P10: End our subroutine.
Page 17
SHEET User manual By Chor-ming Lung October 9, 1988
Example 7: _________ GRID.SHT
This small program shows the setup and exit process for using
graphics in SHEET BASIC. It just uses window 1 to draw the x and y-
axis then marks them. To run it, enter the command "@run d1"
D1 : ! WINDOW 1
D2 : ! SCALE -7,-2,7,2
D3 : ! CLEAR
D4 : ! LINE -7,0,7,0: LINE 0,-10,0,10
D5 : ! MARKX 0,-6,6,0.1,0.5
D6 : ! MARKY 0,-2,2,0.2,1
D7 : ! EXITGRAPH
D8 : ! END
Explanation: Explanation:
D1: The first command for graphics is WINDOW/VIEWPORT. WINDOW 1 means
that we are using window 1 for output.
D2: Setup the scaling factors. The minimum number for X is -7 and the
maximum number is 7 and -2, 2 for Y.
D3: Clear the window being used.
D4: Draws the x-axis and y-axis
D5: Making marks on x-axis from -6 to 6 with increment 0.1. A bigger
marker on increment of 0.5.
D6: Marks y-axis
D7: Legal way to exit from graphics commands
NOTE: ____
The first step on graphic command is WINDOW/VIEWPORT followed by
SCALE. After that, you can enter any graphic commands. After the graph
is done, you should use EXITGRAPH to reset the clipping area.
Otherwise, your screen may seem to be locked up.
Page 18
SHEET User manual By Chor-ming Lung October 9, 1988
Example 8: _________ SINCURVE.SHT
This example draws a sine curve on the current window. To run it,
enter the command "@run a1".
A1 : ! WINDOW
A2 : ! SCALE -7,-1.2,7,1.2
A3 : ! CLEAR
A4 : ! PLOT -2*PI, SIN(-2*PI)
A5 : ! FOR X=-2*PI TO 2*PI+0.1 STEP 0.1
A6 : ! LINE_TO X, SIN(X)
A7 : ! NEXT X
A8 : ! LINE -7,0,7,0: LINE 0,-1.2,0,1.2
A9 : ! MARKX 0,-7,7,0.5,1
A10 : ! MARKY 0,-1.2,1.2,0.1,0
A11 : ! LABELX 0,-7,7,1,"f0"
A12 : ! LABELY 0,-1.2,1.2,0.2,"f2"
A13 : ! WAIT 5: EXITGRAPH
Explanation: Explanation:
A1: WINDOW with no parameter means we are using current window for
output.
A2: SCALE for our sine curve.
A3: Clear our window
A4: Plot the first point of our sine curve
A5: A FOR loop to generate a sine wave from -2*PI to 2*PI
A6: Instead of plotting dots, we want to connect all the dots with
lines.
A8: Draws the x- and y-axis.
A9: Marks x-axis
A10: Marks y-axis
A11: Draws the labels on x-axis. They are integers (f0). F means the
number is displayed in fixed format. 0 means decimal place is 0.
So, it has no decimal place or it is integer.
A12: Draws the labels on y-axis. They are real number with 2 decimal
points.
A13: Waits for 5 seconds and then refreshes our window.
Page 19
SHEET User manual By Chor-ming Lung October 9, 1988
Example 9: _________ SPLINE.SHT
This time, we are going to use PLOT DATA and SPLINE to see how
wonderful SPLINE will be. This program has a lot of subroutines. I will
explain each. To run it, enter the command "@run c1".
F1 : !R=0
F2 : ! FOR X=-2*PI TO 2*PI+1 STEP 2
F3 : !J1[0,R]=X:K1[0,R]= SIN(X)
F4 : !R=R+1
F5 : ! NEXT X
Subprogram F1 generates 7 data sets for sine curve. The data for x
is stored in cells J1..J7 and data for y is stored in cells K1..K7.
D1 : ! WINDOW 1
D2 : ! CLEAR : SCALE -7,-2,7,2
D3 : ! PLOT DATA J1..J7,K1..K7,0,1
D4 : ! GOSUB C13
D5 : ! RETURN
Subroutine D1 plots the data stored in J1..J7,K1..K7 using no
marker and solid line. The command PLOT DATA plots data stored in
worksheet. J1..J7 and K1..K7 for x,y respectively. 0 means using dot
instead of marker for each point and 1 means using solid line to
connect points.
D7 : ! WINDOW 1
D8 : ! SCALE -7,-2,7,2
D9 : ! ATTRIBUTE 1,1
D10 : ! SPLINE J1..J7,K1..K7,0.1
D11 : ! RETURN
This subroutine draws the spline curve using color 1 (Black). The
first argument for ATTRIBUTE sets the color to 1 (Black) and using line
width 1. SPLINE uses cubic-spline method to draw smooth curve. J1..J7
and K1..K7 define the value for x,y. 0.1 defines the increment for
generating data on the spline-curve.
F7 : ! WINDOW 1
F8 : ! SCALE -7,-2,7,2
F9 : !X=-2*PI: PLOT X, SIN(X)
F10 : ! ATTRIBUTE 3,1
F11 : ! FOR X=-2*PI TO 2*PI STEP 0.1
F12 : ! LINE_TO X, SIN(X)
F13 : ! NEXT X
F14 : ! RETURN
Subroutine F7 draws the actual sine curve using color 3 (Green).
C13 : ! LINE 0,-2,0,2: LINE -7,0,7,0: RETURN
Subroutine C13 draws the x- and y-axis
Page 20
SHEET User manual By Chor-ming Lung October 9, 1988
C1 : ! GOSUB D1
C2 : ! GOSUB D7
C3 : ! GOSUB F7
C4 : ! WAIT 5
C5 : ! EXITGRAPH
C6 : ! END
Our main program. First, draws the sine curve with PLOT DATA. Plot
the sine curve with cubic spline routine, then draws the actual sine
curve. The sine curve using SPLINE is closer to the actual sine curve.
Actually, if you change cell F2 with "STEP 1" instead of "STEP 2" and
change cells D3 and D10 for ranges J1..J14,K1..K14, then you can hardly
tell the difference between the spline-curve and the actual sine-curve.
NOTE: the actual sine-curve generates 126 points and spline-curve uses
14 data points! Isn't it wonderful?
Page 21
SHEET User manual By Chor-ming Lung October 9, 1988
PART II -- Spreadsheet operations
MENU SELECTIONS: Briefly describes all the menu selection and its
usage.
MOVING AROUND: This session explains how to move the working area
to display different part of your worksheet.
EDITING: This session discusses about cell entries for
formula, constant, text, BASIC statement and
command. It shows you how to use the line editor.
CUSTOMIZED DISPLAY: This chapter tells you how to change the width of
individual column and the display format of
individual cell. Block is introduced in this _____
chapter too. Block is important to many worksheet
commands.
COPY AND MOVE: This chapter shows you how to reorganize your
worksheet. You need to know how to select blocks.
It also discusses about absolute and relative cell
references.
ERASE CELLS: This chapter explains to you how to erase the
unnecessary cells.
INSERT AND DELETE: This session discusses how to delete or insert a
row or a column. Column and row selection is
different from block.
LOAD, SAVE, PRINT: It explains the menu items under menu title "File".
Page 22
SHEET User manual By Chor-ming Lung October 9, 1988
MENU SELECTIONS
The menu titles are the strings that appear on the menu bar. ____________
Menu items refer to the strings that drop down when a menu title __________
is selected.
The menu titles for SHEET looks like this:
Desk File Sheet Row/Col Cells Graph Help ______________________________________________
TITLE ITEM Description
--------------------------------------------------------------------
Desk Desk
About SHEET General information about SHEET.
File File For a detail description of the following menu
items, please refer to the chapter "LOAD,
SAVE, PRINT"
New N Open a new worksheet window. The N on the
right hand side means that you can activate
this menu item by pressing <Control-N>. You
have a choice for changing your new worksheet
size. The maximal number of rows can be 50 to
9999.
Load L Load an old worksheet file from disk drive.
You can use <Control-L> to activate this menu
item.
Load WKS Load files in the format of Lotus 123 release
1A or 2. SHEET is not a Lotus clone, so it
will not translate everything. Whenever it
encounters problem, the translation process
will be terminated.
Load PART Load SHEET files saved by using "Save PART".
Its file extension is PRT.
Save S Save the whole worksheet file into your
diskette.
Save WKS It is supposed to save your worksheet in Lotus
123 file format. It is not working right now.
Save PART Save part of your worksheet into diskette. You
need to select blocks of cells before you
activate this menu item.
Print P Print part of your worksheet to disk/printer.
You need to select a block before you activate
this menu item.
Quit Q Quit to desktop.
Sheet Sheet
Erase Erase your current worksheet.
Defaults Set some default states of SHEET. You can
change the recalculation order which is saved
with your worksheet, change the TEXT
justication, change the current window name,
takes 32K back for screen redraw and have a
Page 23
SHEET User manual By Chor-ming Lung October 9, 1988
look of the memory remains.
Options In the dialog box of Options, you have three
choices.
Users have suggested for a Global format
for each column. "Cell format using previous
row" means that whenever you enter a new
element for the worksheet, it will try to find
the format of previous row on the same column
and uses it as default format. So, you do not
need to reformat again and again.
If you are moving the worksheet with
arrow keys, you will find that moving up and
down is a lot faster than moving left and
right. "Move to left/right Fast" provides an
option that you can move left and right faster
than before. One drawback for this option is
that the new column being displayed is
truncated to its own width. Hence you may have
problem in displaying BASIC program or text
string which spilled over other cells. If you
want to redraw the screen to display them
properly, you can use the command "@exitgraph"
or <Alt-F> to redraw the screen.
Two monochrome users have suggested me to
give an option for using the Small font to
display more rows per screen. I have make the
option available but it is not working yet.
Dates SHEET can display three different Date
types. The Dates menu item under menu title
Sheet provides you a new way to show your date
data. The components for a DATE are day, month
and year. Normally, day and year are displayed
as digits. Month can be digit, the first three
characters of the month or the whole alphabets
for that month and some countries put month
before day and some put day before month. To
overcome these differences, the date format is
configurable to your favour. dd and yy
represent day and year in digit. mm represents
month in digit. mmm represents month in three
letters and mmmm represents month in full
alphabets. Here are some examples. Assume that
the date is March the first, 1988.
Date format: Display:
-------------------------------------------------
mmddyy 030188
dd-mmm-yy 01-Mar-88
mmmm dd,19yy March 01,1988
dd/mm/yy 01/03/88
mm/dd/yy 03/01/88
NOTE: The file SHEET.CFG holds the defnition of
Date1, Date2 and Date3. It is updated whenever
Page 24
SHEET User manual By Chor-ming Lung October 9, 1988
you change the Date format. If SHEET.CFG is
not there when SHEET is loaded, then the
default formats will be used.
Row/Col Row/Col
Insert I Insert a row or a column. You need to select
that particular row/column first. Refer to the
chapter "INSERT AND DELETE" for detail
description.
Delete D Delete a row or a column. You need to select
that particular row/column first.
Col widths You can change the column width in a more
effecient way than just using mouse dragging
method. For example, if you want to make the
column width from column A to column O to
1,9,1,9,1,9,1,9,1,9,1,9,1,9,1, you can select
the menu item "Col widths" from menu title
"Row/Col", enter "A" for the leading column
and 1,9,1,9,1,9,1,9,1,9,1 in the edit field
"widths".
Cells Cells NOTE: YOU HAVE TO SELECT BLOCKS BEFORE YOU
ACTIVATE THE FOLLOWING MENU ITEMS.
Reformat R Changes the format of blocks. For a detail
description, please refer to "CUSTOMIZED
DISPLAY"
Erase E Erase cells on blocks specified. Please refer
to "ERASE CELLS".
Copy C Copy a block of cells to other block. Please
refer to "COPY AND MOVE"
Move M Move a block of cells to other place
Adjust A Adjust is used for changing TEXT string
to BASIC statements or changing TEXT
justifications. The first step in using ADJUST
is: Select the blocks being ajusted and then
press <Control A> or select this menu item.
You MUST not change TEXT that are not supposed
to be BASIC statements to BASIC statements.
The program may not recognize all your
characters and part of them may be truncated
with a character #.
Graph Graph This selections is not available yet. This selections is not available yet.
Selections
Layout
Legend
X-axis lable
Data Variable A
Data Variable B
Data Variable C
Data Variable D
Data Variable E
Data Variable F
Page 25
SHEET User manual By Chor-ming Lung October 9, 1988
Help Help
Entry Helps for entering formula, text strings,
BASIC statements and commands.
Formula Helps for formula, especially for calculation
priority
Built-in Func Helps for built-in functions.
Statement Helps for BASIC statements (v1.6)
Limits Helps for program limit (accuracy, maximum
numbe of variables and so on)
Graphic commands Helps for graphic commands for BASIC (v1.7)
PTEXT Helps for the graphic command PTEXT (PLOT
TEXT) (v1.7)
Commands Helps on other new BASIC commands (v2.0)
Console commands Helps on screen control and keyboard
commands. (v2.0)
Page 26
SHEET User manual By Chor-ming Lung October 9, 1988
MOVING AROUND
When you start SHEET, your screen is divided into menu bar and
worksheet window. The worksheet window can be divided into 5 major
areas. The status line is the first line under the window title. It is ___________
used for displaying current cell's content and worksheet status. The
editing buffer ,which is the line below status line, is used for ______________
entering or editing the current cell. The column bar is the line below __________
the editing buffer. The row bar is located on the left hand side of the _______
worksheet window. Both column and row bars are used for cell reference.
The biggest area is the working area for current worksheet. ____________
Each column is referred by a unique letter (from A to IU). The
first column is A, column B after A, C after B and so on. The one
following column Z is AA and after AZ comes BA, and so on. Each row
numbers are sequentially ordered from 1 to a variety of 50 to 9999
depends on the worksheet size you choose. A cell in the worksheet is
referred by column letter followed by row number. For example, at the
intersection of column B and row number 2 is cell B2.
Each spreadsheet can hold 255 columns and a variable number of
rows from 50 to 9999. It means you can access at most 2,549,745 cells.
Your screen can display a very small amounts of them. Therefore, you
have to view your spreadsheet by moving the working area. The following
operations let you move your working area by keyboard entry or mouse
button.
<Up arrow> If current cell cursor is not in row number 1, the
cell cursor will be moved one row up.
<Down arrow> If current cell cursor is not in row number 1280,
the cell cursor will be moved one row down.
<Left arrow> If current cell cursor is not in column A, the cell
cursor will be moved one column left.
<Right arrow> If current cell cursor is not in column IU, the
cell cursor will be moved one column right.
<Shift-arrows> If possible, the cell cursor will be moved by
"page" to the direction the arrow key specified.
<Clr-Home> This key moves the current cell cursor to A1.
<F5> When you press the function key F5, you can enter
the cell reference directly to move the cell cursor
to that cell. If the cell specified is not in the
working area, then the working area will be changed
and the new upper left cell is the cell specified.
You can use the slider bars to change the working area quite fast.
Also, you can locate a cell with mouse much faster than arrow keys. To
locate a cell with mouse:
- use vertical and horizontal slider bars to move to the
desired working area.
- put the mouse cursor over the desired cell and press
left button on your mouse.
Page 27
SHEET User manual By Chor-ming Lung October 9, 1988
EDITING
Types of cell entry: ___________________
There are 4 different types of cell entries in SHEET: numeric
constant, formula, text and BASIC statement. BASIC commands will
be executed immediately but it will not be stored in any cell. The
leading character of each entry will differentiate one from the
other.
Formula: Input string starts with "+", "-", DIGIT, "." or "(" Formula
Constant: Input string starts with "+", "-", DIGIT, "." or Constant
"("
Statement: Input string starts with "!" Statement
Command: Input string starts with "@" Command
Text: Input string starts with double quote, single quote, Text
"^", "\" or character other than "+", "-", DIGIT, ".",
"(", "!", "@".
Constant is an arithmetic expression which does not refer to Constant
other cell(s) or variable(s). A constant cell will not be constant
recalculated. You may notice that most of your inputs are
constants. Making the distinction between formula and constant constants formula constant
helps speeding up the spreadsheet recalculation time.
Statement and command are for BASIC interpreter. The Statement command
differences between statement and command are that command will be statement command command
executed immediately but will not be stored in current cell and
statement will not be executed but will be stored in the current statement
cell.
Text is a descriptive string. The first character of each Text
text entry has special effect for display.
Single quote('): The text is left justified.
Double quote("): If the width of the cell is larger than
the length of the text, the text is right
justified.
Circumflex (^): if the width of the cell is larger than
the length of the text, the text is
centered within the cell width.
Back slash(\): The text following back slash will be
used repetively filling the cell.
If a text string starts with character other than single
quote, double quote, circumflex or back slash, then the character
selected on "TEXT justification" of menu item "Defaults" under
menu title "Sheet" will be used as its leading character.
There is a command which you can change the first character
from one of (' " ^ \) to one of (' " ^ \ !). It is the menu item
"Adjust" under menu title "Cells".
Page 28
SHEET User manual By Chor-ming Lung October 9, 1988
Rules for constructing formula: ______________________________
When you are entering formula, the first character of your entry
must be "+","-", "0".."9", "." or "(". The following table summarizes
all such first characters and their usage.
Char Usage Example
-----------------------------------------------------------------
+ Used when the formula begins with a cell +A1/A2
reference or calling built-in function +SIN(A1)
- Used when the formula begins with -A1*20
negative constant value or negative value -12*0.2
of cell reference or negative value of -COS(A1)
function call
0-9 Used when the formula begins with a 12.3*20
constant number.
. Used when the formula begins with a .05*120
constant number.
( Used when the beginning part of the (A1-A2)/A3
formula's natural order of calculation
must be altered through the use of a pair
of parentheses.
NOTE: 1. ____ Versions before 2.0 of SHEET did not know that .05 is
0.05.
2. If part of the expression returns a string, then it will
treat as 0. It means you cannot do string calculation in
any cell. But the BASIC interpreter can do string
operations.
eg. 34+chr$(12) ==>34
"test1"+"test2" ==>0
4+len("some") ==>8
Page 29
SHEET User manual By Chor-ming Lung October 9, 1988
Cell input: __________
Whenever you type some keys, the corresponding characters
will be inserted into the editing buffer. You may notice the
status is changed from "Ready" to "Input". Your entry must be
terminated by the following method to change the status back to
"Ready". While you are in "Input" mode, the menu selections and
window resize function are not allowed, though you can change your
working area by moving the horizontal or vertical slider bars.
Whenever you terminate your input, you will get back to the
working area before you do editing.
Move the editing cursor: Move the editing cursor
<Shift-Left arrow> Moves the editing cursor to left by 1/2 the
length of the editing buffer.
<Shift-Right arrow> Moves the editing cursor to right by 1/2 the
length of the editing buffer.
MOUSE cursor Moves the mouse cursor to the character you
want it be and press left button on your
mouse, then the editing cursor will move to
that character.
Modify your input: Modify your input
<Insert> Toggles the INSERT mode to TYPEOVER mode and
vice versa.
<Backspace> Erases the character on the left hand side of
the editing cursor.
<Delete> Erases the character under the editing cursor.
Terminate your input: Terminate your input:
<Undo> Terminates input and the cell content is not
modified.
<Return> Terminates input and the cell cursor moves to
the direction specified by editing direction
(see below).
<UP arrow> Terminates input and the cell cursor moves one
row up if possible.
<Down arrow> Terminates input and the cell cursor moves one
row down if possible.
<Left arrow> Terminates input and the cell cursor moves one
column left if possible.
<Right arrow> terminates input and the cell cursor moves one
column right if possible.
Miscellaneous operations: Miscellaneous operations:
MOUSE if the mouse cursor is inside the working area
and if you press the left button on your
mouse, the cell coordinate that the mouse is
over will be inserted into the editing buffer.
<Control> MOUSE It works as above except a comma is inserted
before the cell coordinate.
<Shift> MOUSE Instead of comma, a through notation (..)
added before the cell coordinate.
Sliders You can change the working area by moving the
vertical or horizontal slider bars. Any
terminating key will resume the display back
to the working area before editing.
Page 30
SHEET User manual By Chor-ming Lung October 9, 1988
Modify a cell: _____________
To modify a cell, you have to move the cell cursor to the
desired cell. Press the function key <F2> and use the above method
to modify the cell content but you must terminate your change by ______________
<Return>. If you change your mind, you can always <Undo> what you
have done.
Editing direction: _________________
When you terminate your input by any arrow key, the editing
direction will be changed to the direction the arrow key
specified. When you press <Return> to terminate your input, the
cell cursor will move to the editing direction. If you want to
change the editing direction to neutral, you have to press
<Return> before your editing session.
e.g.
Cell Entry Cell cursor position
-------------------------------------------------------------
A1 1 <Down arrow> A2
A2 2 <Return> A3
The first entry change the editing direction to <Down>. The
second entry which terminates with <Return> is similar to
terminating with <Down arrow>.
Page 31
SHEET User manual By Chor-ming Lung October 9, 1988
CUSTOMIZED DISPLAY
SHEET can calculate number accurate up to 17 decimal places. In
most case, you need to display 2 decimal points only. For example, your
monthly loan payment may be 876.54321. You will not care about the
extra digits. You just want it to display as 876.54 or $876.54. That is
the situation in which you need to change its default display format.
There are two ways to change the appearance of your worksheets--
changing the width of individual column and changing the format of
individual cells.
Change column width: ___________________
The column width of a cell may affect the display. For numeric
result, if the format you specify cannot fit into the width of the
cell, then the cell will be filled up with "*". For text, you may not
be able to do right justification or center the text. To display the
result correctly, you may need to change the width of the column.
There are two ways to change the width of individual column. The
first way uses the mouse and the second way uses the menu item "Col
widths" under menu title "Row/Col".
MOUSE:
- Move the desired column within the working area
- Move the mouse cursor on the white gap after the column
letter (e.g. if you want to change the width of column
A, move the mouse cursor on the white gap after column
letter A)
- Press the left mouse button (do not release it)
- To expand the column width, drag the mouse to right and
release the button
- To shrink the column width, drag the mouse to left and
release the column
Col widths:
- Select the menu item "Col widths" under the menu title
"Row/Col".
- Enter the correct column letter
- Enter your desired column width.
- You can enter numbers separate by comma. Doing this will
change the column widths beginning from the column
letter specified.
Note: Note if your column width is only 1 character wide, then the
column letter will be pushed to the next column.
Page 32
SHEET User manual By Chor-ming Lung October 9, 1988
Default display format: ______________________
Text: Text Depends on the first character for justification.
Constant and Formula: Constant and Formula
The number of digits after the decimal point is six. It
is right justified and if possible, a blank space is
added at the rightmost place.
Column width: 9 characters per column. Column width
Display format: _______________
There are 9 different predefined display formats for formula formula
and constant types. The first one is the default format mentioned constant
above. The others are %, Fixed, Scientific, Dollar, ,(comma), % Fixed Scientific Dollar ,
Date1, Date2, Date3. Date1 Date2 Date3
Type Usage Example
--------------------------------------------------------
Default Display up to 6 decimal points 3.1234
% Use for interest rate, 0.05 will be
discount, tax and so on. displayed as 5%
Fixed Use for data which have 1.00
different decimal places. (eg 1.50
1,1.5. Most likely, you want it 2.00
to be 1.00, 1.50)
Scienti- Use for display data which is a 3.24e12
fic big or small number.
Dollar Use for data which are related $1,245.12
to money.
, Comma format is similar to 1,245.12
Dollar format excep there is no
dollar sign in front of it.
Date1 Use for displaying date related 9-Jul-88
value. Its format is dd-MMM-yy.
Date2 Its format is dd-mmm. 18-Jul
Date3 Its format is mmm-yy. Jul-88
TEXT Display formula instead of its
value.
HIDE The information on this cell
will not appear on the working
area
Page 33
SHEET User manual By Chor-ming Lung October 9, 1988
Block: _____
Before we reformat some cells, we need to tell SHEET which cells
we intend to do that. In SHEET, those cells are called blocks. In Lotus
123, we can define only one block at one time, but SHEET can accomodate
up to 18 blocks at one time.
A block is part of the spreadsheet. It is a rectangle which holds
at least one cell. To define a block, you need to select the starting ________
cell and stopping cell of the block. The process of defining the ____ _____________
starting cell requires mouse button and <Control> key, and stopping ______________ ________
cell requires mouse button and <Shift> key. ____
Here is an example to define cell A5 as a block:
- Move the mouse cursor within cell A5
- Press <Control> key and the left mouse button
simultaneously
You may notice that the selected block has been highlighted
by reversing its color.
Here is another example to show you how to define a block
containing cells from B5 to C14 (B5..C14):
- Move the mouse cursor within cell B5
- Press <Control> key and the left mouse button
simultaneously
- Move the mouse cursor within cell C14
-Press <Shift> key and the left mouse button simultaneously.
In summary, to define the starting cell, you have to move the _____________
mouse cursor over the first cell and press <Control> and left
mouse button simultaneously. To define the stopping cell, you have _____________
to move the mouse cursor over the last cell and press <Shift> and
left mouse button simultaneously. If no stopping cell is provided _____________
then the stopping cell is the starting cell.
Using above example, if you want to define block B5..B14
instead of B5..C14
- Move the mouse cursor within cell B14
- Press <Shift> key and the left mouse button simultan-
eously.
If block B5..C14 is the first block you defined, there is
another way to select this block:
- move the mouse cursor within the cell B5
- press the left mouse button and drag until you reach
cell C14
- release mouse button.
De-select blocks: ________________
Selecting any cell by a mouse button click will de-select all
the blocks being selected.
Page 34
SHEET User manual By Chor-ming Lung October 9, 1988
Change display format: _____________________
To reformat some cells:
- select blocks using above method
- Press <Control R>
or find menu title "Sheet" and choose menu item
"Reformat" under it.
- A dialog box will show up. Choose the format you want
from it.
You can also use the command "@REFORAMT" to reformat some
cells. The command should be:
- @reformat FORMAT_STRING, block1, block2, ....
- FORMAT_STRING consists of two part. The first part is
the format being used, and the second part is the
decimal places. The format of a cell can be "$", ",",
"%", "F", "S" "D1", "D2" and "D3". They represent the
format of "DOLLAR", ",", "PERCENT", "FIXED",
"SCIENTIFIC", "Date1", "Date2" and "Date3" respectively.
For example, if you want to reformat cell B2..C2 and B5..C5
to DOLLAR with decimal place of 2, you can issue the command
@reformat "$2",b2..c2,b5..c5
instead of selecting blocks and press <Control R>.
NOTE: NOTE YOU CAN ONLY REFORMAT THOSE CELLS WHICH EXIST. That is
because of Sparse Matrix. After a cell has been created,
the cell possesses attributes. If you format some empty
cells and insert something into those cells after that,
those cells appear as default. It is not a bug.
You can set the option in menu item "Options" under
"Sheet" to use the format of previous row. Then,
whenever you enter a new element for the worksheet, it
will try to find the format of previous row on the same
column and uses it as default format. So, you do not
need to reformat again and again.
Page 35
SHEET User manual By Chor-ming Lung October 9, 1988
COPY AND MOVE
COPY and MOVE commands require two blocks. The first block is
the source, and the second one is destination. You have to select
those blocks before you activate COPY command by <Control C> or
find the menu title "Cells" and choose menu item "Copy" under it.
To activate MOVE command, you press <Control M> or find menu title
"Range" and choose menu item "Move" under it.
You can use the command "@COPY source_block, dest_block" or
"@MOVE source_block, dest_block" to do the same thing.
Here is an example showing you how to copy the content on
cell A1 to A2..A5. Assume that the content of A1 is "1+A2". The
display of cell A1 should be 1.
Key Mouse button Effect Mouse cursor
position
-----------------------------------------------------------
A1
Control + Mouse click Cell A1 inverted
A2
Control + Mouse click Cell A2 inverted
A5
Shift + Mouse click Cell A2 through
A5 inverted.
Control C An alert box
display on screen
Return or Mouse click
on OK.
The operations prior to <Control C> define two blocks. The
first block consists of cell A1 only. The second block consists of
cells A2 through A5. <Control C> tells SHEET to copy the content
of the first block to the second block. If cell A6 does not
contain anything and you set the recalculation mode to Natural,
your display should be:
A Cell content
1 5 1+A2
2 4 1+A3
3 3 1+A4
4 2 1+A5
5 1 1+A6
Natural recalculation means formulas are calculated in their
dependent order. Cell A1 depends on the result of A2 (1+A2). So,
the calculation of A1 is postponed until A2 gets its result. A2
depends on A3 and A3 depends on A4 and so on. Thererfore, the
first formula being computed is A5. If a formula refers to an
empty cell, a zero will be returned. Hence, A5=1+A6=1+0=1, cell
A4=1+A5=1+1=2 and A3=1+A4=1+2=3 ...
Page 36
SHEET User manual By Chor-ming Lung October 9, 1988
One question you may ask: Why the cell reference of A2 in
cell A1 has been changed to A3, A4, A5 and A6 in cell A2, A3, A4
and A5 respectively? The answer is relative cell reference.
Relative and Absolue cell reference: ___________________________________
Cell reference can be absolute or relative. By adding a $
symbol before the column letter or/and row number, we designate a
part of a cell address as absolute or both parts as absolute. For
example:
$A2 denotes column (A) is absolute and the row (2) is
relative.
$A$2 denotes column (A) and row (2) are both absolute.
A$2 denotes column (A) is relative and the row (2) is
absolute
A2 denotes column (A) and row (2) are both relative.
During COPY, a formula in source block which contains
relative cell references will be modified. If the formula is
copied down, then the relative row number will be increased. If
the formula is copied to right hand side, then the relative column
letter will be increased. If you intend to copy "1+A2" to A2..A5,
then you must use formula "1+A$2" instead of "1+A2".
NOTE: NOTE:
A block can be a single cell (A2), part of a column
(A2..A5),part of a row (B20..E20) or a range (A4..C10). The
following describes how the program handle different types of
block COPY. Source is the first block and destination is the
second block. Result is the range size chosen by COPY.
Source Destination Result size
---------------------------------------------------------
Single cell any kind of block the largest range
column reference any kind of block the largest range
row reference any kind of block the largest range
range reference any kind of block the source range
You can use "@COPY" to copy from source to destination too. You can use "@COPY" to copy from source to destination too.
Examples:
- If you want to COPY C1 to C2..C20
* Control and mouse click on cell C1
* Control and mouse click on cell C2
* Shift and mouse click on cell C20
* <Control C>
OR: "@COPY c1,c2..c20"
- If you want to COPY C1..C12 to D1..D12
* Control and mouse click on cell C1
* Shift and mouse click on cell C12
* Control and mouse click on cell D1
* <Control C>
OR: "@COPY c1..c12,d1"
Page 37
SHEET User manual By Chor-ming Lung October 9, 1988
ERASE CELLS
After some practice, you may find some data in your worksheet
are no longer needed. Hence, you need the capability to erase
them. Before you activate the ERASE command, you must select the
desired cells as blocks. Yes, you can erase more than one block,
but the total number of blocks cannot exceed 18. Here is an
example showing you how to erase cells C1..D11, E5, F8 at one
time.
Key Mouse button Effect Mouse cursor
Position
----------------------------------------------------------------
C1
Control + Mouse button C1 is inverted
D11
Shift + Mouse button C1 through D11
are inverted
E5
Control + Mouse button E5 is inverted
F8
Control + Mouse button F8 is inverted
Control E An alert box for
ERASE command on
screen
Return or Mouse button The screen will be
on OK redrawn to show the
effect of erase.
Note: ____ Instead of using mouse operations, you can use the command
"@ERASE" to erase part of your worksheet. To do the same
thing as above, you can issue the command:
@erase c1..d11,e5,f8
Page 38
SHEET User manual By Chor-ming Lung October 9, 1988
INSERT AND DELETE
INSERT and DELETE commands work with row and column only. The
first thing before we activate INSERT or DELETE command, we must
select a row/column.
Row/Column selection: ____________________
Here is an example to show you how to select column D:
Mouse cursor position Mouse button
------------------------------------------------------
COLUMN BAR
column letter D
Click the mouse button
After you select column D, the whole column will be inverted.
This is an example showing you how to select row 3
Mouse cursor position Mouse button
------------------------------------------------------
ROW BAR
row number 3
Click the mouse button
After you select row 3, the whole row will be inverted.
To delete or insert a row/column, you must first select the
row/column by clicking on the column or row bar with desired
column letter or row number. After that, you can press <Control I>
or <Control D> for inserting or deleting. You can choose the menu
title "Row/Col" and choose menu item "Insert" or "Delete" to do
the same thing.
Note: ____ INSERT and DELETE commands work with row and column only.
- If you intend to add some blank rows or column but you do not
want to add the whole rows/columns, then you must use MOVE
command instead.
- If you intend to delete some cells, use ERASE instead of
DELETE.
Page 39
SHEET User manual By Chor-ming Lung October 9, 1988
LOAD, SAVE AND PRINT
If you take a look of the menu title "File", you may notice
that there are three types of load and save commands. Menu item
"Load" will load the ordinary SHEET files (they have a file
extension SHT). "Load WKS" will load Lotus 123 release 1A or
release 2 files. "Load PART" is similar to Lotus file combine
command. It is used for merging other worksheet with current
worksheet. The file you "Load" with part should be saved with
"Save PART" command and its file extension should be PRT.
Menu item "Save" will save the current file using SHT format.
"Save WKS" will save file in Lotus WKS format. "Save PART" is
similar to Lotus file extract command. It is used for saving some
part of the current worksheet. To activate this command, you
should select blocks to hold cells being output, and activate this
command.
Currently, you cannot save your worksheet in Lotus release 1A
format. Other than that, every thing is functional.
File extension: ______________
Lotus 123 release 1A uses WKS as its file extension. SHEET
uses SHT as its file extension. If you enter a file name in your
file selector dialog box, you should include the file extension.
The file extension for PART is PRT. If you are printing the
spreadsheet to disk, the file extension for this file should be
PRN.
Load: ____
There are three way to load a spreadsheet:
- From the menu title, choose "File"
Choose menu item "Load"
Select your file from the file selector box
- <Control-L>. Press <Control> and L key simultaneously
Select your file from the file selector box
- @load (This is a BASIC command).
Save: ____
There are three way to save a spreadsheet:
- From the menu title, choose "File"
Choose menu item "Save"
Enter your file in the file selector dialog box
- <Control-S>. Press <Control> and S key simultaneously
Enter your file in the file selector dialog box
- @save "filenmae" (This is a BASIC command.)
Load PART: _________
You can merge those cells that are saved with "Save PART"
into your current worksheet. To load those files, you should
select the menu item "Load PART" from the menu title "File".
Page 40
SHEET User manual By Chor-ming Lung October 9, 1988
Save Part: _________
You can save part of your worksheet to a file and then merge
it with other worksheet. It is especially useful for BASIC
programmer. They may create some BASIC routines that are needed
from file to file. Save them as PART and load them back for other
files. To save part of your worksheet, you need to select those
cells as blocks and activate the menu item "Save PART". The file
extension for this kind of file is PRT.
Lotus files: ___________
In many ways, SHEET is quite similar to Lotus 123. But I must
emphasize that I am not writing a Lotus clone. Instead, I am
trying to write a BASIC interpreter in a spreadsheet environment.
Even though, SHEET can read Lotus files, you may not be lucky
enough to convert all your existing Lotus files. SHEET will
convert those functions it supports. When SHEET encounters problem
in function convertion, it will stop loading the file. It can read
Lotus 123 release 1A or 2 files.
You may also notice that the converted SHT files are normally
bigger than WKS files. There are two reason for that. First, I use
a single data type -- double -- for all constant number. Lotus
separates number from integer to double. An integer is two bytes
long while a double is eight bytes. The second reason is that
Lotus stores formula in suffix order while SHEET stores formula in
infix order (e.g. 3+4 is in infix order, its suffix order
representation is 3 4 +). Suffix order not only saves storage (in
suffix order, parenthesis can be discarded and comma between
parameters can be discarded too), it also speeds up formula
calculation. You may ask me why I use infix order? Well, it is not
easy to write a BASIC interpreter using suffix order.
Print: _____
You can print your work sheet to a file or to printer. Before Before
you print, please specify the block which you want to print. The you print, please specify the block which you want to print
print routine will print those columns that fits on the margins.
If some columns on the print range are not print on the first
pass, they will be printed on later passes.
Note: Note The information you enter for the PRINT DIALOG will
not be saved with your spreadsheet file. It means
you have to type in your format each time you want
to print it.
Here is an example showing how to print the block A1..E12
Key Mouse button Effect Mouse cursor
position
----------------------------------------------------------------
A1
Control + Mouse button A1 is inverted
E12
Shift + Mouse button A1 through E12
are inverted
Page 41
SHEET User manual By Chor-ming Lung October 9, 1988
<Control P> PRINT dialog box
display
Change dialog box contents.
<Return> or Mouse button
on OK.
Let us examine what the components of the print dialog box
mean. If you want to print your worksheet to disk instead of
printer, enter D in the box after "Print to (P)rinter/(D)isk:".
Otherwise, enter P. If you want to print the formula (including
BASIC statement) of each cell, then enter Y for the question
"Print Formula (Y)es/(N)o"". Otherwise, enter N. NOTE: versions
before 2.0 prints formula row after row. The new version prints
formula column after column. It makes printing BASIC program
easier.
Right margin-->
<---------------------- Page width --------------------->
^ ********************************************************
| * ^ *
| * | Top margin and titles *
| * v *
| * ************************************** *
| * * * *
*<------>* * *
Page * Left * * *
Length * Margin * * *
* * * *
| * * * *
| * * * *
| * * * *
| * * * *
| * * * *
| * * * *
| * * * *
| * * * *
| * ************************************** *
| * ^ *
| * | Bottom margin *
| * v *
v ********************************************************
If you want to test your output before you actually print it,
you can enter the command:
@redirect to "prn:"
@print_block a1..g4,4,76
The commands above will print row 1 to row 4 from column A to
column G using left margin 4 and right margin 76 to your printer.
You can check the result before you actually print it. (That is
useful because you cannot stop PRINT when it actually prints).
Page 42
SHEET User manual By Chor-ming Lung October 9, 1988
PART III -- SHEET BASIC
A spreadsheet is a powerful computing tool. It is also a handy
environment for handling data. Yon can add, delete and change your data
with ease, and the spreadsheet program modifies all the required places
to reflect the changes.
Let the spreadsheet handle your data and numurical calculations,
and let the BASIC interpreter do the rest. SHEET BASIC can do
calculations, string operations, graphics and text files. The
combination of spreadsheet and BASIC provides an ideal environment for
using your computer productively.
The following is a brief explanation of each session of PART III.
Cells, Variables, String: This chapter introduces the new cell
reference method, variables and their naming
convention.
You cannot assign a string to a variable, but
you can assign a string to a cell. The rules for
accessing and operating strings are introduced too.
Flow control Statements: These statements change the execution sequence
of BASIC commands. The commands include:
IF .. THEN .. ELSE, GOTO, GOSUB .. RETURN, LOOP ..
EXIT IF .. ENDLOOP, FOR .. NEXT, and RUN
I/O Statements: REDIRECT TO, PRINT
Miscellaneous statements: SAVE SCREEN, RESTORE SCREEN, WAIT, DEFINE,
MESSAGE
Text files: GET_FILE, OPEN, CLOSE, INPUT, PRINT
Worksheet commands: COPY, MOVE, ERASE, REFORMAT, SORT, SWAP
Console commands: CLS, CURSORON, CURSOROFF, VIDEOHIGH, VIDEONORM,
GOTOXY, INKEY, UNGET, INPUT
Graphic commands:
Setup commands: WINDOW, VIEWPORT, SCALE, EXITGRAPH, CLEAR
Line/Dot commands: ATTRIBUTE, LINE, LINE_TO, PLOT, PLOT DATA,
SPLINE
Turtlegraphics: FORWARD, TURN, TURN_TO
Axis commands: MARKX, MARKY, LABELX, LABELY
Text command: PTEXT
Miscellaneous: MOUSE_POS
Page 43
SHEET User manual By Chor-ming Lung October 9, 1988
Cells and Variables
Cell reference with dimension: _____________________________
A cell reference is a combination of column letter and row number.
This type of access method is not enough for a programming language. In
SHEET, you can use a cell as a BASE and move it horizontally or
vertically by appending two numbers which are separated by comma and
embraced by "[" and "]". Each column letter has its own value. Column A
is 1, B is 2 and so on. For example, A2[3,4]. A2 is the BASE whose
coordinate is [1,2] and
A2[3,4]=[1,2]+[3,4]=[4,6]=D6.
Here are some other examples:
C1[4,7] which is equivalent to cell G8. C1 is equal to
[3,1] and C1[4,7]=[3,1]+[4,7] = [7,8]. Column G is
7. So the combination of this cell is G8.
H123[-2,4] H123=[8,123] and H123[-2,4] = [8,123]+[-2,4] =
[6,127] or F127.
Variables: _________
Variables must be initialized before being used. You can define up
to 150 variables. A variable name starts with an alpha character which
is followed by alphanumeric characters. Up to 15 characters are
significant. A variable name must not be identical with reserved
keywords, built-in functions and cell reference.
examples:
row=4 correct
pi=3.1415 incorrect (PI is a predefined constant)
a1=12 incorrect (it is cell A1)
1and2 incorrect (the first character is not letter)
There are so many keywords and built-in functions, how can one
differentiate them from the other? Keywords are embraced by leading and
trailing spaces. For example, IF is a keyword and when it is displayed,
it is " IF ". A built-in function has a leading space before the name.
For example, SIN(x) will be displayed as " SIN(X)". Variables do not
have any leading or trailing space.
Cell reference with variables _____________________________
The whole worksheet can be viewed as a two dimensional array. You
can access any cell by A1[c,r] where c is the column number and r is
the row number.
Not only you can access individual cell by using this method, you
can also use it for block. For example, assume that you store your
square matrix in cells a1..c3 and you want to store the inverse of this
matrix to d1..f3. You can use the following program to do this.
! matrix(d1,inverse[a1..c3])
! end
Assume that you need to solve another square matrix in which you
Page 44
SHEET User manual By Chor-ming Lung October 9, 1988
store it in cells a1..d4. You can easily change the argument of matrix
to:
! matrix(e1,inverse[a1..d4)
If you keep using this method to solve square matrix in different
sizes, you may find it is clumsy and easy to make mistakes. Here is
another program that makes the whole process of changing a lot easier
for you and less error prone.
!n=3
! matrix(a1[n,0],invers[a1..a1[n-1,n-1])
! end
n=3 so a1[3,0]=[1,1]+[3,0]=[4,1]=d1 and
a1[n-1,n-1]=[1,1][2,2]=[3,3]=c3.
All you need to change is the constant n.
String ______
You may store numerical data or TEXT string in a cell. To access a
cell as a string, you should add the "$" at the end of each cell
reference. For example:
A1$, A1[i,j]$
String operators for comparisons, concatenation and string
functions are added for BASIC users. REMEMBER, string functions and
concatenation are available only in BASIC statements and commands.
Currently, you can assign string to cells but not the 150 variables.
Two predefined string variables are provided for file name and input
buffer. They are FILENAME$ and BUF$. You can use them but you cannot
assign new value to them. FILENAME$ is changed whenever you use the
file item selector and BUF$ is changed whenever you use the command
INPUT. For more details, please refer to "Text files" for GET_FILE,
INPUT #n and "Console:" for INPUT.
If an empty cell is referred as a string, a null string will be
returned. If a cell with number is referred as a string, then a string
that is exactly being displayed for that cell will be returned. If the
cell is a TEXT string, a string excluding the leading character will be
returned. The leading character for a TEXT string is one of ' " ^ and
\.
If you are assigning a string to a cell, and if the string does
not contain one of those leading character, then the default character
defined in "TEXT justification" of menu item "Defaults" will be used as
the leading character for that cell.
WARNING: DO NOT MAKE ANY STRING LONGER THAN 249 CHARACTERS.
String operators: String operators
+: string concatenation. For example "Thi"+"s a test" will
return a string "This is a test".
=: string assignment or string equal comparison. E.g.
!a1$="^123": END. or !if a1$=b1$ then ...
Page 45
SHEET User manual By Chor-ming Lung October 9, 1988
<>, >, <, =>, >=, =<, <=
string comparisons for not equal, greater, less, equal or
greater and equal or less than.
Page 46
SHEET User manual By Chor-ming Lung October 9, 1988
Flow control Statements
IF .. THEN .. ELSE ..
Syntax:
IF conditionn THEN statements:ELSE statements
Description:
This statement is used to make a decision regarding program
flow based on the result returned by an expression.
Example:
IF A1=12 THEN A2=20:ELSE A2=30
NOTE: The following statements are not valid:
IF A1=12 THEN G3:ELSE GOTO G4
IF A1=12 GOTO G3
If you have used traditional BASICs before, you would know that
you can write "IF A1=12 THEN 120:ELSE GOTO 130" and "IF A1=12 GOTO
120" They are not valid in SHEET. Instead, you must change them
to:
IF A1=12 THEN GOTO G3:ELSE GOTO G4
IF A1=12 THEN GOTO G3
GOTO
Syntax:
GOTO cell.
Description:
This statement is used to branch unconditionally to a
specified cell.
Example:
GOTO A1
GOSUB .. RETURN
Syntax:
GOSUB cell
...
RETURN
Description:
These statements uses in pair for branching to, and returning
from a subroutine.
LOOP .. EXIT IF .. ENDLOOP
Syntax:
LOOP
...
EXIT IF condition
....
ENDLOOP
Description:
Those commands are used to execute a series of statements in
Page 47
SHEET User manual By Chor-ming Lung October 9, 1988
a loop until some conditions are true. Condition is a logical
expression which may be either TRUE or FALSE. Within the
LOOP, multiple EXIT IFs are allowed. It is adviced not to use
GOTO statement to break the loop.
Example:
The following example assigns 1 to 20 to cell A1 through A20.
R=0
LOOP
A1[0,R]=R+1
R = R+1
EXIT IF R=20
ENDLOOP
FOR .. NEXT
Syntax:
FOR assignment TO expression [STEP expression]
...
NEXT variable_being_assigned
Description:
These commands are used to execute a series of statements in
a definite number of times. STEP is optional. If no STEP
value being provided, it is assumed to be one. In other BASIC
interpreters, NEXT I,J means: increases value of I, when the _________
FOR loop for I is done, then increases value of J. You must
use NEXT I: NEXT J in SHEET BASIC. The number of nesting FOR _______________
loops is infinite.
RUN
Syntax:
RUN cell
Description:
RUN is similar to GOTO. The only difference is that RUN will
initialize the runtime stack and reset the symbol table. Most
of the time, RUN is used to execute BASIC codes starting from
a specified cell. If the cell is C1, you should enter "@run
c1" in the editing buffer.
Page 48
SHEET User manual By Chor-ming Lung October 9, 1988
I/O Statements
REDIRECT TO
Syntax:
REDIRECT TO "file"
Description:
This statement changes the output path of subsequent
PRINT commands to the specified file. There are a few
predefined files. prn: is the printer, con: is your monitor ____ ____
and aux: is the rs-232 port. Otherwise, they are disk files. ____
The default file is con:. Except con:, all the file output
are buffered. It means you may not get instant response.
If you redirect the output to printer and if your
printer is not on, an alert box will show up. If you choose
"Quit", the BASIC program being executed will be stopped. If
you choose "Continue", the subsequent PRINT commands will
output to the file prior to REDIRECT TO command. Most likely,
it is con:. You should turn your printer on before you choose
"Try again".
EXAMPLE:
REDIRECT TO "prn:"
PRINT statement
Syntax:
PRINT cell/cell$/number/string[,/;...]
Description:
This command prints numeric or string data to the file
specified by REDIRECT TO command. Implicitly, it is your
monitor. If no data following the PRINT command, a blank line
will be printed. String data must be enclosed by double
quotation marks. Each datum can be a cell reference,
variable, string or number. If more than one datum is going
to print, each of them should be separated by comma or
semicolon. If you are using comma to separated two data, the
second data will be aligned to the next TAB position. Each
TAB is 10 characters wide.
If you are printing cell content, the format specified
in that cell is used exactly as display. If the cell you
intended to print contains TEXT string, you should add "$" at
the end of the cell reference. Otherwise, you will print a
cell with zero.
If a number is being printed, the number will be
surrounded by a leading space and a trailing space. The
output format of variables is similar to a number.If you are
printing string, the text inside the embraced double
quotation marks will be outputed.
While you are printing, you can use <Control S> and
<Control Q> to suspend and resume your display. BUT don't _________
press <Control C> to stop your PRINT session. If you do press _____________________________________________________________
<Control C> while PRINT command is printing on console, YOU _____________________________________________________________
Page 49
SHEET User manual By Chor-ming Lung October 9, 1988
WILL GET back to desktop and you need to reboot the system to _____________________________________________________________
use SHEET. UES <ESC> to stop your BASIC program. ________________________________________________
Example:
PRINT a1,12;b1$;20;"This is a test"
Page 50
SHEET User manual By Chor-ming Lung October 9, 1988
Miscellaneous statements
If you print to console, your current display may be garbled.
Therefore, you need to save your screen before printing and
restore it after you are done.
SAVE SCREEN
Syntax:
SAVE SCREEN
Description:
Save current display to SCREEN reserved area. That is
the same area that is used for FAST screen update. If you
deny to reserve 32K for fast screen update, you may crash the
system when you use this command.
RESTORE SCREEN
Syntax:
RESTORE SCREEN
Description:
Restore the screen to the old display.
WAIT
Syntax:
WAIT
WAIT #
Description:
This statement suspends the execution of BASIC commands.
# means an integer number. If no number follows WAIT, it will
print "Press return to continue ..." on console and you must
press <Return> key to resume its operations. The printed
message will damage your display. Hence, you should save your
screen before calling WAIT.
If WAIT followed by an integer x, then the system will
wait for x seconds.
Example:
WAIT 5 (System suspends for 5 seconds)
DEFINE
Syntax:
DEFINE fname(parameters) = expression
Description:
This statement let user define its own functions. Fname
is the function name. Its naming convention follows the rule
for naming variable. You can put up to 9 parameters for each
user function. Each parameter is a variable separated by
comma. Each DEFINE statement must occupy a single cell and
user defined function is useful for numerical calculation
only.
Page 51
SHEET User manual By Chor-ming Lung October 9, 1988
MESSAGE
Syntax:
MESSAGE "text"
Description:
This command uses the GEM alert box to display a
message. "text" can be a string of up to 5 lines. Each line
is separated by "|" and the length of each line should not be
bigger than 40.
Example:
MESSAGE "Hello|World"
EXAMPLE: Address label program EXAMPLE
Assume that you store your address data in the current
spreadsheet. Column A holds the Last name. Column B holds the
First name. Column C holds the address and D,E,F holds City, State
and Zip respectively. The data starts from row 3. The following
program will print the data in your address sheet to the address
label for handling mails. Assume the program stores from cell
G1..G10 and you run it by "@run g1".
G1 : ! REDIRECT TO "prn:"
G2 : !R=0: SAVE SCREEN
G3 : ! LOOP
G4 : ! EXIT IF ISEMPTY(A3[0,R]): PRINT
G5 : ! PRINT A3[1,R]$;A3[0,R]$
G6 : ! PRINT A3[2,R]$
G7 : ! PRINT A3[3,R]$;A3[4,R]$;A3[5,R]$
G8 : ! PRINT : PRINT :R=R+1
G9 : ! ENDLOOP : WAIT
G10 : ! RESTORE SCREEN
Page 52
SHEET User manual By Chor-ming Lung October 9, 1988
Text files
SHEET should be the first spreadsheet which has the commands
for you to access any TEXT files. The command is line oriented.
These commands are intended for people who need to acquire data
from other sources. There are two constant strings that are
related to some of the Text file commands. FILENAME$ is a constant
string that can be changed by file item selecto dialog box. BUF$
is a constant string that is modified by any INPUT statement.
Using the file commands and string functions, you can do some
thing that is impossible for user of other spreadsheet programs.
GET_FILE
Syntax:
GET_FILE def_search, name$
Description:
Use the file item selector to find the file name.
Def_search is the default search string. It can be a string
with wild characters. For example "\*.SHT". name$ can be
FILENAME$ or a cell reference.
Example:
GET_FILE "\*.*",filename$
GET_FILE "\*.txt",a1$
OPEN
Syntax:
OPEN "o",#n,filename$
OPEN "i",#n,a1$
Description:
Open a file for input or output. "o" means output and
"i" for input. #n means the channel number. N should be a
number from 0 to 9. Filename can be FILENAME$ or a cell
reference.
Example:
OPEN "o",#1,FILENAME$
OPEN "i",#2,"test.txt"
CLOSE
Syntax:
CLOSE #n[,#m,...]
Description:
Close opened files. #n , #m are the channel number. They
should be in the range of 0 to 9. If you try to close an
unopened file, you will get an error message "Cannot close an
unopened file".
Example:
CLOSE #1
Page 53
SHEET User manual By Chor-ming Lung October 9, 1988
INPUT #n
Syntax:
INPUT #n,buf$
Description:
Read a line from the input file. #n is the channel
number. buf$ can be BUF$ or a cell reference. If the channel
number being referred is an output file or an unopened file,
you will get an error message "Channel number error".
Example:
INPUT #1, BUF$
INPUT #1, a1$
PRINT #n
Syntax:
PRINT #n, cell/cell$/string/number[,/;....]
Description:
PRINT #n is similar to PRINT except that PRINT outputs
to the file specified by REDIRECT TO (implicitly your
terminal). PRINT #n outputs to the channel number being
opened by OPEN command.
Example:
Assume that cell a1$="This is a test" and b1=12.34 and the
widths of column A and B are 9.
PRINT #1,a1;b1 ==> " 0 12.34 " to #1
PRINT #1,a1$;b1 ==> "This is a test 12.34 " to #1
Page 54
SHEET User manual By Chor-ming Lung October 9, 1988
Worksheet commands
SHEET BASIC is part of SHEET -- the spreadsheet program. It
is more convenient if you can use some of those worksheet commands
to handle data in your BASIC program. This new version of SHEET
provides some of the essential worksheet commands for you to
incorporate with your program. They are COPY, MOVE, ERASE,
REFORMAT, SORT and SWAP. SORT and SWAP are new commands for SHEET
too. Currently, they can be activated by BASIC commands only. They
work fine with cells whose formula do not refer to other cells.
That is because the cell references in those formula are not
updated when you SWAP them. Next release should remove this
restriction.
COPY
Syntax:
COPY block1, block2
Description:
Copy block1 to block2. A block can be a single cell or a
rectangle portion of the worksheet. The COPY command follows
the rule for COPYing cells by <Ctrl-C>.
Example:
COPY a1..b3, c1
MOVE
Syntax:
MOVE block1, block2
Description:
Move block1 to block2. It is similar to the command
<Ctrl-M>.
Example:
MOVE a1..b3, c1
ERASE
Syntax:
ERASE block1[,block2,...]
Description:
Erase portion of the worksheet specified by the blocks.
Up to 18 blocks can be erased at one time.
Example:
ERASE a1..b3,c1..d3
REFORMAT
Syntax:
REFORMAT FORMAT,block1[,block2,...]
Description:
Reformat portion of the worksheet. Up to 18 blocks can
Page 55
SHEET User manual By Chor-ming Lung October 9, 1988
be reformat at one time. FORMAT is a string which consists of
two part -- format and decimal places. A format can be "$",
",", "F", "S", "D1", "D2", "D3". "$" for DOLLAR, "," for
COMMA, "F" for FIXED, "S" for SCINETIFIC and "D1", "D2", "D3"
for DATE1, DATE2, and DATE3 respectively. No decimal places
should follow "D1","D2" and "D3".
Example:
REFORMAT "$2",A1..B3
(reformat a1..b3 for dollar and 2 decimal places)
REFORMAT "D1", C1..C12
(reformat C1..c12 for Date1 format)
SORT
Syntax:
SORT "A",block, cell1[,cell2,...]
SORT "D",block, cell1[,cell2,...]
Description:
Sort followed by "A" means sort block in ascending order
and "D" for descending order. Only the block will be affected
by the sort command. Cell1 points to the first column that is
used for comparison. Up to 6 of these cells can be used as
criteria for comparisons. In comparison, an empty cell is
smaller than a cell with numerical value and a cell with
numerical value is smaller than a cell contains TEXT string.
If the column being compared consists of BASIC statements,
SORT will be stopped. Currently, SORT does not modify the
formula on each cell being affected. You may have trouble in
sorting cells whose formula refers to other cells.
Example:
SORT "A",A1..B3,A1
SWAP
Syntax:
SWAP cell1, cell2
Description:
This command swaps cell1 with cell2. It swaps between
two cells. If you want to swap a row with other row, you
should use a FOR loop to do it one by one. Again, it has
problem with cell whose formula refers to other cell.
Example:
SWAP a1,b2
Page 56
SHEET User manual By Chor-ming Lung October 9, 1988
Console commands ________________
The Atari monitor emulates the VT52 terminal. You can use
those VT52 commands to control your output. This version of SHEET
provides some of the VT52 commands in the SHEET BASIC. They are
CLS, CURSORON, CURSOROFF, VIDEOHIGH, VIDEONORM and GOTOXY. One
function and two commands for reading keyboard entries are
provided. They are INKEY, UNGET and INPUT.
Screen coordinate: (1,1) is the upper left-corner, (80,1) is
the upper right-corner, (1,25) is the bottom left-corner and
(80,25) is the bottom right-corner.
CLS
Syntax:
CLS
Description:
Clear the current screen and move the cursor to the position
1,1.
CURSORON
Syntax:
CURSORON
Description:
Makes the screen cursor visible.
CURSOROFF
Syntax:
CURSOROFF
Description:
Turns the screen cursor off.
VIDEOHIGH
Syntax:
VIDEOHIGH
Description:
Makes the subsequent output characters in reverse color
VIDEONORM
Syntax:
VIDEONORM
Description:
Makes the subsequent output characters in normal color
GOTOXY
Syntax:
GOTOXY x,y
Description:
Moves the screen cursor to position x,y. x is in the
Page 57
SHEET User manual By Chor-ming Lung October 9, 1988
range of 1 to 80 and y is in the range of 1 to 25.
INKEY
Syntax:
a=INKEY
Description:
It is a function that returns the ASCII number of a key
being pressed. If no key or special keys are being pressed -1
will be returned. Arrow keys for Up, Down, Left, Right have
value of 129,130,131 and 132 respectively . Arrow keys with
Shift keys for Up, Down, Left, Right have values of
133,134,135 and 136.
Example:
A=INKEY
UNGET
Syntax:
UNGET x
Description:
Put x back to the input stream for later INPUT uses.
Example:
A=INKEY:.... :UNGET a
INPUT
Syntax:
INPUT buf$
Description:
buf$ can be either BUF$ or a cell reference. This
routine calls the standard C function gets to get your input.
If you UNGET a character before calling this command, the
UNGET character cannot be altered or displayed but it will be
the first character in your buf$.
Page 58
SHEET User manual By Chor-ming Lung October 9, 1988
Graphic commands ________________
Your monitor can be used to display pictures. A picture is a
collection of picture elements (pixels). Some people prefer to call a
pixel a dot. Normal graphic commands in other BASIC languages provide
you a mean to change each pixel in your display. The addressing method
they used are called Raster coordinate. The left top-corner is (0,0)
and the right bottom-corner is (639,399), (639,199) or (319,199) for
high, medium and low resolution.
In real world, we are dealing with Cartesian coordinate instead of
Raster coordinate. The left bottom-corner is the minimal point we can
use for display and the right top-corner is the maximal point. The
graphic commands provided by SHEET BASIC use the Cartesian coordinate
and those commands will translate Cartesian points to pixels.
SHEET BASIC is not the first BASIC interpreter which provide
scientific graphics (at least True BASIC is a few years earlier). But I
think it is the first BASIC provides cubic spline curves. Cubic spline
is a method scientists use to draw smooth curves.
The graphic commands are highly relocatable and they work on any
resolution. Hence, it is possible to use 16 colors to plot your graph
in low resolution.
The graphics commands are separated into the following categories:
Setup commands: Setup commands: WINDOW or VIEWPORT, SCALE, EXITGRAPH,
CLEAR
Line/Dot commands: Line/Dot commands: ATTRIBUTE, LINE, LINE_TO, PLOT, PLOT
DATA, SPLINE
Turtlegraphics: Turtlegraphics FORWARD, TURN, TURN_TO
Axis commands: Axis commands MARKX, MARKY, LABELX, LABELY
Text command: Text command: PTEXT
Miscellaneous: Miscellaneous: MOUSE_POS
CONVENTION: CONVENTION Optional parameters are bracketed. # means a
number. (x,y), (x1,y1) and (x2,y2) are measured in
Cartesian coordinate. An r added before x or y
means the point is in Raster coordinate.
Page 59
SHEET User manual By Chor-ming Lung October 9, 1988
Setup commands: ______________
WINDOW or VIEWPORT defines the part of the screen being used for
graphic output. They are the only commands whose parameters are in
Raster coordinate. SCALE defines the scaling factors that translate the
Cartesian corrdinate data into location on your display. CLEAR cleans
up the area defined by WINDOW or VIEWPORT. WINDOW and VIEWPORT change
the clipping area. The last command after your graph is done should be
EXITGRAPH which resets the clipping area to the current window.
Otherwise, you may have an illusion that your system is locked. In case
you feel your system is locked, you should try <Esc> first to see
whether a BASIC program is running. If the system is still lock-up,
enter the command "@EXITGRAPH" in blind and you should have the system
back.
WINDOW: ______
Syntax:
WINDOW [# [,rx1,ry1,rx2,ry2] ]
Description:
If no parameter provides for this command, then the
current window will be used for graphic output.
SHEET supports up to 4 windows. Each window has its
own number ranging from 1 to 4. This number corresponds
to the number you press ALT-1, ALT-2, ALT-3 or ALT-4.
So, you can use any window for graphics output.
If raster parameters provide, then the window
specified will be resized to those coordinates and the
work area of that window will be used for graphics
output. If a window changes it size, AES will ask SHEET
to redraw it. So, you may need to run your program twice
to get the graph.
If the window number specified has not been opened,
then you won't see the graph, but the BASIC interpreter
will not stop.
Examples:
WINDOW using the current window
WINDOW 1 using window 1
WINDOW 1,0,20,350,120 reposition and resize window 1
Hints:
If your program involves a lot of calculation, and
you don't want to run the program twice to get the
graph, you can stop your program by <Esc> key when you
see the window has been resized and then enter the same
run command again in blind. So, you have better to put
your WINDOW command on the first line.
Page 60
SHEET User manual By Chor-ming Lung October 9, 1988
VIEWPORT: ________
Syntax:
VIEWPORT rx1,ry1,rx2,ry2
Description:
WINDOW lets you to borrow the working area of a
spreadsheet window. VIEWPORT command lets you put your
picture anywhere in your screen. If the raster
parameters are 0,0,0,0 then the whole screen will be
used for graphic display. It is desirable if you want to
test the graph on medium resolution and display the
final graph on low resolution.
Examples:
VIEWPORT 0,0,0,0
VIEWPORT 0,0,350,120
SCALE: _____
Syntax:
SCALE xmin, ymin, xmax, ymax
Description:
SCALE setup the scaling factors which are used for
mapping Cartesian coordinate data to Raster coordinate
pixels. xmin,ymin specifies the left bottom-corner
coordinate and xmax,ymax the right top-corner. You
should make the scale a little bit larger than what you
want to leave some spaces for texts.
CLEAR: _____
Syntax:
CLEAR
Description:
This command clears the screen specified by WINDOW
or VIEWPORT commands.
EXITGRAPH: _________
Syntax:
EXITGRAPH
Description:
WINDOW and VIEWPORT commands will change the
clipping area. EXITGRAPH resets the clipping area to the
current window and refreshes it (sometimes your graph
may overwrite part of the current window).
Page 61
SHEET User manual By Chor-ming Lung October 9, 1988
Line/Dot commands: _________________
Each line posseses some attributes. It has color, width and its
type. A line can be a solid, or broken line. This is the type of a line
and there are 6 line types defined.
A dot has color and width. Instead of dot, sometimes you may want
to display your data using markers. There are 6 different markers
defined in SHEET BASIC graphic commands.
SHEET BASIC is part of the spreadsheet SHEET. You may have your
data stored in the spreadsheet. There are two special commands to plot
data stored in cells. They are PLOT DATA and SPLINE.
ATTRIBUTE: _________
Syntax
ATTRIBUTE color#,width
Description:
ATTRIBUTE command changes the color and width of
subsequent graphic commands. The default color is 2
which is red and the default width is 1.
LINE: ____
Syntax:
LINE x1,y1,x2,y2[,linetype]
Description:
This command draws a line from (x1,y1) to (x2,y2).
If no type specified, it is supposed to be solid line.
The parameter type is a number ranging from 1 to 6.
linetype:
1: solid line
2: long dashes
3: dots
4: dash-dots
5: dashes
6: dash-dot-dot
LINE_TO: _______
Syntax:
LINE_TO x1,y1[,type]
Description:
This command draws a line from the previous
position to (x1,y1). The last line/dot commands
specified the previous position. If the program is newly
executed, the previous position is (0,0).
Page 62
SHEET User manual By Chor-ming Lung October 9, 1988
PLOT: ____
Syntax:
PLOT x,y[,marker#]
Description:
If no marker number specified, PLOT command will
draw a dot on the position (x,y). The markers chosen for
SHEET BASIC are model after Lotus 123.
MARKER:
1: square
2: +
3: diamond
4: up-triangle
5: x
6: down-triangle
PLOT DATA: _________
Syntax:
PLOT DATA xrange, yrange[,marker#, linetype]
Description:
The data are stored in the spreadsheet. PLOT DATA
accesses x,y pairs from the xrange and yrange specified.
It will plot data from the first row to the last row in
the range. Please refers to PLOT for marker number and
LINE for linetype. NOTE: if your data are quite dense,
the linetype other than 1 may not break the line the way
you specified.
SPLINE ______
Syntax:
SPLINE xrange, yrange,increment
Description:
Given n points (knots), mathematicians can find n-
1 different cubic polynomials that pass through the n _________
points. This curve generated by those polynomials is
called spline-curve. It is a smooth curve and it may
represent the data more closely than connecting the
points with lines. Increment should be smaller than any
subinterval of the n points. NOTE: To use SPLINE, you NOTE: To use SPLINE, you
must provide at least 4 points. must provide at least 4 points.
Page 63
SHEET User manual By Chor-ming Lung October 9, 1988
Turtlegraphics: ______________
Turtlegraphics is quite interesting. I add these commands just for
fun. Only three commands are provided though.
FORWARD: _______
Syntax:
FORWARD distance
Description:
FORWARD command in LOGO uses pixel as measurement
for distance. SHEET BASIC uses the scale factors
specified by SCALE as measurement for distance. This
command moves the drawing pen specified by the parameter
distance. Distance can be negative which make FORWARD
acts as BACK in LOGO.
Example:
FORWARD 5.2
TURN, TURN_TO: ____ _______
Syntax:
TURN degree
TURN_TO degree
Description:
They are the only commands in SHEET BASIC using
degrees instead of radians. Degree 0 points to North.
East is 90 degree, South is 180 and West is 270. TURN
turns the active turtle by the amount specified in
rotation. TURN is similar to LEFT if the degree is
positive and RIGHT if the degree is negative. TURN_TO is
similar to SETHEADING in LOGO, which sets the direction
of the active turtle to a specified angle.
Page 64
SHEET User manual By Chor-ming Lung October 9, 1988
Axis command: ____________
It is easy to draw the x-axis and y-axis by using LINE command,
but you may also want to know where x=3 on the x-axis. MARKX and MARKY
commands help you to mark your axes with measurement. LABELX and LABELY
let you add the number 3 under the mark.
MARKX/MARKY: ___________
Syntax:
MARKX y_origin, xmin, xmax, xinc, xstep
MARKY x_origin, ymin, ymax, yinc, ystep
Description:
An example may help me to explain better. Suppose
that you are drawing an x-y graph for your income from
1970 to 1988 and your income ranges from 12,000 to
40,000.
SCALE 1969, 0, 1989, 45000
Making the x-axis one year larger on both sides give you
some blank spaces. You do not care about amount smaller
than 12,000. The following command draws the x-axis.
LINE 1969,12000,1989,12000
Your y_origin starts from 12000. xmin is 1970 and xmax
is 1988. You want to mark each year and have a bigger
mark on each five year. Here is the command:
MARKX 12000,1970,1988,1,5
MARKY works similarly.
NOTE: xstep should be a multiple of xinc.
LABELX/LABELY: _____________
Syntax:
LABELX y_origin, xmin, xmax, xinc, format
LABELY x_origin, ymin, ymax, yinc, format
Description:
I am using the same example on MARKX. Now, you want
to add the year 1970 to 1988 on your graph. The command
to do this is:
LABELX 12000,1970,1988,1,"f0"
You may find some of the numbers are overlapped. Then
you should adjust xinc.
Format is a string that specified what kind of format we
are going to use for displaying the labels. Currently,
there are two formats supported by LABELX and LABELY.
They are FIXED and SCIENTIFIC. The string starts with an
f or F means the labels are FIXED number. The number
following f/F is the decimal place. A scientific format
starts with an s/S. The format string ("f0") on the
example means that we are displaying integers.
Page 65
SHEET User manual By Chor-ming Lung October 9, 1988
Text command: ____________
PTEXT: _____
Syntax:
PTEXT x,y,string[,color, effects, rotation, height]
Description:
PTEXT plots string on the location (x,y). There are
some special effects:
1: bold face
2: light
4: italic
8: underline
16: outline
32: shadowed
If you want to use more than one special effects, you
can do that by adding the number for each effects. For
example, if you want to plot the string with bold face
and underline, you should use 9 (1+8) for the effects.
Rotation changes the direction the characters being
displayed.
0: East
900: North
1800: West
2700: South
Height of a font may be changed but you may not get a
good result. The following height numbers provide the
best result for small and normal fonts.
Color monitor:
small font: 4
normal font: 6
Monochrome monitor:
small font: 6
normal font: 13
Page 66
SHEET User manual By Chor-ming Lung October 9, 1988
Miscellaneous: _____________
MOUSE_POS: _________
Syntax:
MOUSE_POS x,y
Description:
x,y should be variables or cell references.
MOUSE_POS will wait for you to press the left button on
your mouse. If your mouse cursor is within the block
specified by WINDOW or VIEWPORT, then the Cartesian of
the mouse cursor will be stored on x,y. Otherwise, the
Raster coordinate is stored on x,y.
This command is useful for finding the value for
WINDOW, VIEWPORT or PTEXT. It is also useful to find
some data points from your graph.
Hints:
Mouse click response time varies not in a constant
way (refer to problems part). You may better add a WAIT ____
1 statement after the MOUSE_POS. So, user can press the _
button a little bit longer without causing problem.
Also, MOUSE_POS will keep polling until you press your
mouse button. It means your system is suspended for
mouse click. You cannot break its operation by pressing
<Esc>. You have better add an ALERT or MESSAGE telling
user to press the mouse button.
Page 67
SHEET User manual By Chor-ming Lung October 9, 1988
Tips on using SHEET BASIC
Interrupt: Interrupt
You can stop your BASIC program by pressing <Esc> key.
Using previous row format: Using previous row format
If your BASIC program generates a lot of data to the
worksheet, you should choose "Using previous format" from menu
item "Options" to non. It will speed up a little of your BASIC
program.
Error: Error
When the BASIC interpreter encounters error, it will tell you
what is wrong and where the offending code is. You can use F2 to
edit that line and re-run it again. If your offending cell is for
example G12, instead of re-run it, you can use "@goto g12" to
continue your program. It may save you some time.
Multiple statements: Multiple statements
You can enter more than one statement in a cell. Each
statement must be separated by colon. Entering multiple statements
in one cell will save some memory, but don't enter too many
because your editing buffer can hold 250 characters only.
Garbled screen: Garbled screen
If you output some data to your monitor, your current screen
display will be garbled. In case you forget to save your screen
before you print, you can rebuild your display by the following
steps:
- Select the menu bar and choose "About SHEET"
- Press return when the dialog shown up.
- If the number of windows being opened is less than 4,
you can choose "New" to open up a new worksheet and then
close it.
- If the number is 4, then you must move your window out
of the screen and move it back.
Adjust: Adjust
When I wrote the first SHEET BASIC program, I felt it is
clumsy to enter "!" at the beginning of each statement and I
forgot to enter "!" in some cells. If you have the same problems,
you can use the following technique to enter BASIC statement.
As we know, each BASIC statement starts with an alpha letter.
SHEET will treat any cell entry starts with an alpha letter as
TEXT string. You can enter your BASIC codes as text string and
then adjust them to BASIC statements. The steps to adjust codes:
- select blocks to cover the desired cells
- Press <Control A> and choose "BASIC (!)" to adjust the
text string to BASIC statements.
Page 68
SHEET User manual By Chor-ming Lung October 9, 1988
Appendix A BUILT-IN FUNCTIONS _____________________________
Math: ____
General functions:
ABS(x): Absolute function of x
INT(x): Integer part of x (e.g. +int(12.4) = 12)
FRAC(x): Fraction part of x (e.g. +frac(12.4) = 0.4)
FACT(x): Factorial of x. X < 34
SQR(x): Square function of x
SQRT(x): Square root of x
Trigo & logarithm functions:
SIN(x): Sine function of x. X is measured in radian
COS(x): Cosine function of x.
TAN(x): Tangent function of x.
ASIN(x): Arc sine function of x
ACOS(x): Arc cosine function of x
ATAN(x): Arc tangent of x
LOG(x): Logarithm base 10
LN(x): Logarithm base e
EXP(x): Exponent of x
RAD_DEG(x): Radian to degree
DEG_RAD(x): Degree to radian
DATE functions:
DATE(mm,dd,yy): Function for calculating Julian days.
DAY(x): Day of x. X is Julian day
MONTH(x): Month of x
YEAR(x): Year of x
NOTE: 1. You can use the following formula to find the
weekday of the date:
(date(mm,dd,yy)+1) mod 7
0 if Sunday, 1 if Monday and so on.
2. You can use the following formula to find the
number of days on a specific month.
case 1: mm<12 date(mm+1,1,yy)-date(mm,1,yy)
case 2: mm=12 date(1,1,yy+1)-date(mm,1,yy)
Matrix functions:
A matrix in a worksheet is a block embraced by "[" and
"]". For example, to make A1..C3 as a matrix, it should be
[A1..C3]. Versions before 2.0 of SHEET restrict individual
matrix size within 64K. This limit has been removed.
Matrix operators include *,+ and -. There is no priority
for those matrix operators. Formula is calculated from left
to right. Parenthesis can be used to change the calculation
order.
There are only two matrix functions. They are INVERSE
and TRANSPOSE. Their arguments must be a matrix or a matrix
operations grouped by parenthesis.
The sequence of activating matrix operations is:
Page 69
SHEET User manual By Chor-ming Lung October 9, 1988
MATRIX(cell, matrix_operations)
Cell: The cell reference of the MATRIX is the leading
location where matrix operations put their result.
Matrix_operations: Matrix operations can be matrix functions
or combination of matrix operators.
For example, to store the inverse of matrix [a1..c3] in
matrix [d1..f3], you should store the following formula
somewhere:
+MATRIX(d1,inverse[a1..c3])
Statistics: __________
AVERAGE( any number of parameters ): Average of parameters.
Parameters can be a single cell reference, an
expression, a block of cells.
MEDIAN( any number of parameters ): Median of parameters.
STD( any number of parameters): Standard deviation of parameters.
VAR( any number of parameters): Variance of parameters.
SQR_SUM( any number of parameters): Square sum of parameters.
eg. +AVERAGE(A1..F4,234,12+24.5,H2+34*sin(pi))
Miscellaneous: _____________
MAX(any number of parameters): Maximum value of parameters.
MIN(any number of parameters): Minimum value of parameters.
SUM(any number of parameters): Sum of all parameters.
RAND: Random number between 0 and 1.
IFF(condition,true_value, false_value): IF function
ISEMPTY(cell): return 1 if cell is empty, otherwise 0
TRUE: it is equal to 1
FALSE: it is equal to 0
ALERT ALERT(default, "[icon#][text][exit]")
Uses the alert box to get user's response.
icon#:
0: no icon
1: NOTE icon (!)
2: WAIT icon (?)
3: STOP icon (stop sign)
text:
text string can be up to 5 lines. Each line is separated by
"|". You cannot have more than 40 characters per line. If you
use icon, then the number of character per line must be
lesser than 40. Keep your character per line less than 30
will be safe.
exit:
Up to 3 exit button can be defined. Each of them is separated
by "|".
eg.
!A3=ALERT(1,"[1][This is a test|Wow][Ok|Cancel]")
Page 70
SHEET User manual By Chor-ming Lung October 9, 1988
COL_WIDTH COL_WIDTH(block)
Returns the total column widths that holds the block.
For example, if column width of A is 8 and B is 12 and C
is 4:
eg COL_WIDTH(a1) ==> 8
COL_WIDTH(b1..c1) ==> 16
LAST_ROW LAST_ROW(cell,r)
Returns the value stores in the last row on the column
the cell is. r tells you where the last row is. r is
zero origin. For example, if the last row of column B is
12 and cell B12 has a value of 123.45:
eg LAST_ROW(b20,row) ==> 123.45 and row=11
You can access the last cell by using B1[0,row]
LAST_COL LAST_COL(cell,c)
Returns the value stores in the last column on the row
the cell is. c tells you where the last column is. c is
zero origin. For example, if the last column of row 4 is
H and cell H4 has a value of 456.77:
eg LAST_COL(a4,col) ==> 456.77 and col=7
You can access the last cell by using a4[col,0]
SEARCH SEARCH(STRING,block,r,c)
A boolean function to find out whether STRING in the
block and r,c tell you where the cell is. r and c are
zero origin so you can access the cell by using a1[c,r].
NOTE: the comparison is case insensitive and a cell of
the block which has a substring includes STRING, then
the location of that cell will be stored in r and c. If
search fails, r and c will not be changed.
eg. Assume that cell A5$="What",b5$="is",c5$="your",
d5$="name?".
SEARCH("You",a5..d5,r,c) ==> 1 and r=4, c=2. To access
the cell that holds a substring of "YOU", you should use
a1[c,r]$ (it is c5$ in this case).
Finance: _______
Future growth:
GRATE(a time series data): Average growth rate.
GBASE(a time series data): Adjusted base value
GFUTURE(grate,gbase, predict time): Future values based on
grate and gbase
A time series data is data being collected in a fixed
frequency of time. They may be gathered yearly, quarterly,
monthly or daily. For example, you income is a time series
data that is increased yearly. For a more detail example,
refer to the example file "FG.SHT"
Investment:
INVEST_PV(final value, interest, compound frequency, terms):
Present value of investment
Page 71
SHEET User manual By Chor-ming Lung October 9, 1988
INVEST_FV(present value, interest, compound frequency,
terms): Future value of investment
INVEST_INTEREST(present value, final value, compound
frequency, terms):Compound interest of investment
INVEST_TERM(present value, final value, interest, compound
frequency): Terms of investment.
Please refer to the file "INVEST.SHT" for using those
functions.
Loan:
LOAN_PAY(amount borrowed, interest,total terms, number of
terms per year): Payment for each term.
LOAN_AMOUNT(payment, interest, total terms, terms per year):
Amount borrowed.
LOAN_TERM(amount borrowed, payment, interest, terms per
year): Total number of terms for loan.
Please refer to the file "LOAN.SHT" for using those
functions.
Withdraw:
WDRAW_SAVE(amount withdrawn, interest, terms, terms per
year): Initial Investment
WDRAW_AMOUNT(amount saved, interest, terms, terms per year):
Amount withdrawn.
WDRAW_TERM(amount saved, amount withdrawn, interest, terms
per year): Total terms
Please refer to the file "WITHDRAW.SHT" for using those
functions.
Deposit:
DEPOSIT_FV(amount, interest, total terms, terms per year):
Final value for deposit
DEPOSIT_AMOUNT(final value, interest, total terms, terms per
year): Regular deposit
DEPOSIT_TERM(amount, final value, interest, terms per year):
Total number of terms.
Please refer to the file "DEPOSIT.SHT" for using those
functions.
String functions: _________________
FILENAME$ FILENAME$ is a string variable that can only be modified
by file selector dialog box.
BUF$ BUF$ is a string variable that can be changed by the
command INPUT only.
MID$: MID$(STRING,s,len)
Returns a substring of STRING, starting from s with
Page 72
SHEET User manual By Chor-ming Lung October 9, 1988
length len. If s is larger than the length of the string
STRING, then a substring range error occur. If len+s is
larger than the length of STRING, then up to the last
character will be returned.
eg MID$("12345678",4,3) ==> "456"
MID$("12345678",14,3) ==> substring range error
MID$("12345678",4,20) ==> "45678"
LEFT$: LEFT$(STRING,len)
Returns a substring of STRING, starting from 1 with
length len. If len is larger than the actual length of
STRING, the whole STRING will be returned.
eg LEFT$("12345678",4) ==> "1234"
LEFT$("12345678",20) ==> "12345678"
RIGHT$: RIGHT$(STRING,len)
Returns a substring of STRING, from the far right
portion of STRING with length len. If len is larger than
the length of STRING, then substring range error occurs.
eg. RIGHT$("12345678",4) ==> "5678"
RIGHT$("12345678",20) ==> substring range error
SPACE$: SPACE$(num)
Returns a string with num of blank spaces. num must be a
positive integer.
eg. SPACE$(4) ==> " "
CHR$: CHR$(num)
Returns a string with one character whose ASCII number
is equal to num. num is in the range of 0 to 255.
eg CHR$(82) ==> "R"
STR$: STR$([FORMAT,]value)
Changes a number value to a string equivalent to that
value. FORMAT is optional. It is a string which consists
of three part -- width, format, decimal point. It is
equivalent to return a cell string with column width,
format and decimal place.
eg STR$(1234.5) ==> "1234.5"
STR$("12$2",1234.5) ==> " $1234.50"
VAL: VAL(STRING)
Returns the value of STRING. This function will strip
off characters other than "E", digits, "+", "-" and "."
first and do the translation later.
eg VAL("abc123.45") ==> 123.45
VAL(".23e-2") ==> 0.0023
VAL(".23ae-2") ==> 0.0023
LEN: LEN(STRING)
Returns the length of the STRING. An empty string has a
len of zero.
eg LEN("") ==> 0
LEN("123456") ==> 6
Page 73
SHEET User manual By Chor-ming Lung October 9, 1988
POS: POS(STRING1,STRING2,start)
Return the position of any character of STRING1 from
STRING2 beginning from start. All the character of
STRING1 and STRING2 will be changed to upper case first.
A zero will be returned if none has been found or start
is larger than the len of STRING2.
eg POS(" ", "What is this?",1)==> 5
A space in position 5.
POS("ts","What is this?",1)==>4
"t" is in position 4
POS("ts","What is this?",5)==>7
"s" is in position 7
POS("AT","What is this?",1)==>3
"A" is in position 3. Case insensitive.
POS(",-","What is this?",1)==>0
Appendix B CONTROL KEYS _______________________
Non-editing: ___________
<Esc> stops a running BASIC program.
<Up arrow> moves cell cursor one row up.
<Down arrow> moves cell cursor one row down.
<Left arrow> moves cell cursor one column left.
<Right arrow> moves cell cursor one column right.
<Shift><arrows> moves cell cursor to the direction specified
by the arrow key by page.
<Return> changes the editing direction to neutral.
<Control><A> adjusts TEXT string to BASIC statements or
text justification.
<Control><N> creates a new sheet
<Control><L> loads a sheet file
<Control><S> saves current spreadsheet
<Control><P> prints current spreadsheet
<Control><Q> exits to desktop
<Control><I> inserts a row or a column
<Control><D> deletes a row or a column
<Control><R> reformats cells
<Control><E> erases cells
<Control><C> copies cells
<Control><M> moves cells
<Control><mouse click> defines the starting cell of a block
<Shift><mouse click> defines the stopping cell of a block
<Alternate><1> switches to the first window
<Alternate><2> switches to the second window
<Alternate><3> switches to the third window
<Alternate><4> switches to the fourth window
<Alternate><F> refreshes the current window.
<Alternate><C> recalculates the spreadsheet by column
<Alternate><N> recalculates the spreadsheet by natural order
<Alternate><R> recalculates the spreadsheet by row
Page 74
SHEET User manual By Chor-ming Lung October 9, 1988
<F2> modifies the current cell's content
<F5> Goto a specified cell.
Editing: _______
<Up arrow> terminates and moves cell cursor one row up.
The editing direction changes to up direction.
<Down arrow> terminates and moves cell cursor one row down.
The editing direction changes to down.
<Left arrow> terminates and moves cell cursor one column
left. The editing direction changes to left.
<Right arrow> terminates and moves cell cursor one column
right. The editing direction changes to right.
<Shift><Left arrow> moves editing cursor to left.
<Shift><Right arrow> moves editing cursor to right.
<Return> terminates and moves cell cursor to the
direction specified by editing direction.
<Mouse click> if the mouse cursor is within working area,
the coordinate of the cell under mouse cursor
will be inserted into editing buffer; if the
mouse cursor is within the editing line, the
editing cursor will move to the character
under the mouse cursor.
<Control><mouse click> if the mouse cursor is within working area,
a comma and the coordinate of the cell under
mouse cursor will be inserted into editing
buffer.
<Shift><mouse click> if the mouse cursor is within working area, a
through symbol ".." and the coordinate of the
cell under mouse cursor will be inserted into
editing buffer.
Appendix C Program specification: ________________________________
SHEET is written in Mark Williams C. The accuracy for real
number (double) in Mark Williams C is up to 17 digits (Lotus 123
is up to 15 digits). The range of calculation is -1E-37 to
1E+37. Each spreadsheet can accomdate up to 2,549,745 (255*9999)
cells and the spreadsheet is implemented by using Sparse Matrix.
Any input to a cell will be tokenized before it is being inserted.
This program support up to 4 windows. The BASIC interpreter can be
used to handle graphics, string, and ASCII text files.
Accuracy: Accuracy up to 17 digits
Range: Range -1E-37 to 1E+37 (If you find 1e+37 in a cell, that
means the calculation for that cell is not correct)
Arithmetic operators: Arithmetic operators
"+", "-", "*", "/", "^", MOD
Logical operators: Logical operators:
AND, OR, NOT, "=", "<>", ">", "<", ">=", "<=",
"=>", "=<"
Built-In functions: Built-In functions
Page 75
SHEET User manual By Chor-ming Lung October 9, 1988
Math: SIN, COS, TAN, ASIN, ACOS, ATAN, LOG, EXP, LN, ABS,
INT, FRAC, SQR, SQRT, DATE, FACT, RAD_DEG, DEG_RAD,
PI (constant)
Statistics:
AVERAGE, MEDIAN, STD, VAR, SQR_SUM
Miscellaneous:
MAX, MIN, SUM, COUNT, TODAY, DAY, MONTH, YEAR,
TRUE, FALSE (TRUE and FALSE are constant, TRUE is 1
and FALSE is 0), IFF, ISEMPTY, ALERT, LAST_ROW,
LAST_COL, SEARCH.
Finance: GRATE, GBASE, GFUTURE, INVEST_PV, INVEST_FV,
INVEST_INTEREST, INVEST_TERM, LOAN_PAY,
LOAN_AMOUNT, LOAN_TERM, WDRAW_SAVE, WDRAW_AMOUNT,
WDRAW_TERM, DEPOSIT_FV, DEPOSIT_AMOUNT,
DEPOSIT_TERM
String: MID$, CHR$, LEFT$, RIGHT$, SPACE$, STR$, VAL, LEN,
ASC, FILENAME$(a string variable used for file item
selector) and BUF$(a string variable used by the
command INPUT).
Matrix: MATRIX, TRANSPOSE, INVERSE, *, +, -
Priority: Priority
Lowest: - AND, OR
- "+", "-"
- "*", "/", "^", MOD
- number, cell reference, variable, function call,
NOT, "+", "-" (unary operator)
Highest:- Parenthesis
Constants: Constants PI(0.31415926535897932e+01), TODAY(Julian days when
you press TODAY), TRUE(1), FALSE(0).
Cell reference: A cell can be referred by using its spreadsheet Cell reference
coordinate (e.g. D1) If you add a "$" in front of
the row number (in this example 1) or in front of
the column letter (D) then you make it an absolute
reference. If you add "$" at the end of the cell
reference, then you are using it as a string. You
can use a cell as BASE and move it vertically or
horizontally by appending two numbers which are
separated by comma and embraced by "[" and "]". The
first number increases the BASE horizontally and
the second number increases the BASE vertically.
For example A2[3,4]. You can separate A2 to [1,2]
(Column A has a value of 1, B has 2 and so on).
A2[3,4] = [1,2]+[3,4]=[4,6]=D6.
Variables: Variables Up to 150 variables can be defined. This number
includes the user defined function. It is intended
for BASIC usage. For the time being, you cannot use
those variables to hold any string.
Appendix D Lotus and SHEET __________________________
Page 76
SHEET User manual By Chor-ming Lung October 9, 1988
In Lotus, you must add a '@' symbol before every function
name. In SHEET, no special symbol is required to put before the
function name. However, if a function name is the first element of
your formula, a '+' or '(' is required to differentiate it from
TEXT.
Lotus requires you to put # symbol to embrace the logical
operators. For example , "+A1 #AND# A2". It is invalid in SHEET.
Actually, you will get 2 bombs if you enter the above formula. You
should avoid adding special character in your formula. The valid
form is "+A1 AND A2".
Lotus @COUNT function will count any cell containing a
numeric value or a label. SHEET will count only numeric value. For
example,
A _ B _
1 _ 12 This is a test
@COUNT(A1..B1) will return 2 and COUNT(A1..B1) will return 1.
Lotus @DATE function asks for arguments (YYYY,MM,DD) and DATE
function in SHEET asks for (MM,DD,YY). The first day of @DATE is
1-Jan-1900 while the first day of DATE is 1-Jan-4712 B.C.
Here is a list of Lotus functions which have equivalent SHEET
functions but using different names.
@FV(amount,interest,terms) = DEPOSIT_FV(amount,interest,terms,1)
@RATE(fv,pv,term) = INVEST_INTEREST(pv,fv,12,term*12)
@TERM(pmt,int,fv) = DEPOSIT_TERM(pmt,fv,int,12)
@PMT(amount,interest,terms) = LOAN_PAY(amount,interest,terms,12)
Appendix E Recalculation ________________________
Natural: Natural Recalculates each cell in the dependent order. NOTE:
some spreadsheet programs use dependent list to find the
dependent order and they can quickly recalculate those
cells being affected. SHEET uses recursive method to
recalculates cells. The effect is that, it blindly
recalculate every cell which contains formula and it is
slower than using dependent list. The positive side is
that, you have more memory to use than those spreadsheet
programs. This is a trade-off between speed and space.
Row: Row: Recalculate each row in the ascending order.
Column: Column: Recalculate each column in the ascending order.
Manual: Manual The spreadsheet stops automatic recalculation. User has
to press <ALT-N>, <ALT-R> or <ALT-C> for natural, row or
column recalculation, where <ALT-N> means press
<Alternate> key with character key <N> simultaneously.
Note: It is no longer true that natural recalculation is
slower than row or column recalculation.
Page 77
SHEET User manual By Chor-ming Lung October 9, 1988
Appendix F FILE FORMAT ______________________
Header for SHT: Header for SHT
- 270 bytes reserved data.
Currently, there are a few bytes have been used.
- 2 bytes for file version number, current version is 1.4
- 1 bytes for screen type when the file saved. 1 for color
monitor and 0 for monochrome monitor.
- 2 bytes for maximum row number.
- 40 bytes for window title. (39 characters with a null
character)
- 2 bytes: check-sum of the window title to make sure we are
handling the right work sheet.
- 256 bytes for width of each columns
- 2 bytes for window type (It is useless)
- 8 bytes for current window size
- 4 bytes for working area top row, top column
- 4 bytes for current cell cursor address
- 2 bytes for current recalculation mode
Cell contents for SHT format: Cell contents for SHT format
- 1 bytes for cell type (use this byte with 0x23 to do bit-wise
and. If it is not 0, then it is NUMBER).
- 2 bytes for length
- IF NUMBER:
2 bytes for row #, 1 byte for column #.
2 bytes for attribute and 1 byte for decimal place
8 bytes for calculating result.
- IF NOT NUMBER (it may be TEXT or STATEMENT, its decimal place
and calculating result are not needed)
2 bytes for row#, 1 byte for column #.
2 bytes for attribute (it may be blank cell)
- IF length <> 0, length bytes of tokens.
- The last cell type and length are 0 and 0.
Header for PRT: Header for PRT
- 4 bytes holding "PART"
Cell contents for PART format: Cell contents for PART format
- 4 bytes for cell row, column
- 2 bytes for display attribute
- 2 bytes for decimal place
- 2 bytes for tokens length
- 8 bytes for calculated result
- tokens length bytes of actual tokens
Appendix G Problems ___________________
A user told me his cell cursor keep flashing
ans: It won't happen very often. It seems it happens when the
system is newly bootup and you load SHEET right away. In
Page 78
SHEET User manual By Chor-ming Lung October 9, 1988
SHEET, you can locate the cell cursor by mouse click.
You can also select a block by prolonging your mouse
click (drag). When you press your mouse to move the cell
cursor, SHEET first moves your cell cursor to that
position and inverts the color of that cell. Mouse
response time seems a lot faster when the system is
newly bootup. Because of the fast response time, SHEET
thinks you are still pressing your mouse button. It
thinks you are trying to select a block then. That is
the reason why your cell cursor on and off. In case you
have this problem, you should choose some of the help
dialogs. 4 or 5 of them should slow down your mouse
response time. Another method found by a user is
provided here. After the system is booted, you type a
key and then load your SHEET program.
Why some of my input characters are being truncated?
ans: For versions before 2.0, if you type any illegal
character in your formula, you will be bombed out and
you must reboot your system to use SHEET. This new
version of SHEET prevents this problem but the rest
characters after the illegal character will be truncated
and a "#" symbol will be added at the end of the illegal
formula. You should rewrite your formula.
Page 79
SHEET User manual By Chor-ming Lung October 9, 1988
Biorthym program ________________
A German doctor found that our emotional, physical and
intellectual conditions change in sine curves with different
frequencies. Emotional condition changes in 23 days per cycle, physical
28 days, and intellectual 33 days. They are called biorthym.
The following SHEET BASIC program uses the data found to generate
the biorthym curves for you. You should enter your name in cell B1 and
your birthday in B2 using +DATE(mm,dd,yy), then you can run it by the
command "@run e1".
E1 : !B3= TODAY
E2 : !B4=B3-B2+1:START= DATE( MONTH( TODAY),1, YEAR( TODAY))-B2
E3 : ! IF B3<0 THEN MESSAGE "Date entry error": END
E4 : !EMOTION=START MOD 23
E5 : !PHYSICAL=START MOD 28
E6 : !INTELLECTUAL=START MOD 33
E7 : ! WINDOW 1,0,11,417,199
E8 : ! SCALE -4,-1.5,33,1.1
E9 : ! CLEAR :EC=2*PI/23:PC=2*PI/28:IC=2*PI/33
E10 : ! FOR X=1 TO 31
E11 : !Z1=EMOTION*EC:EMOTION=EMOTION+1
E12 : !Z2=PHYSICAL*PC:PHYSICAL=PHYSICAL+1
E13 : !Z3=INTELLECTUAL*IC:INTELLECTUAL=INTELLECTUAL+1
E14 : ! PLOT X, SIN(Z1),1: PLOT X, SIN(Z2),2: PLOT X, SIN(Z3),3
E15 : ! NEXT X
E16 : ! LINE 1,0,31,0
E17 : ! LINE 1,-1,1,1
E18 : ! MARKX 0,1,31,1
E19 : ! MARKY 1,-1,1,0.1,0
E20 : ! LABELX 0,2,30,2,"f0"
E21 : ! LABELY 1,-1,1,0.1,"f1"
E22 : ! PTEXT 11,-1.2,B1,1,1
E23 : ! PLOT -2,-1.35,1: PTEXT -1,-1.4,"Emotional"
E24 : ! PLOT 8,-1.35,2: PTEXT 9,-1.4,"Physical"
E25 : ! PLOT 20,-1.35,3: PTEXT 21,-1.4,"Intellectual"
E26 : ! EXITGRAPH
E27 : ! END
Explanations:
E1: Set the current date on cell B3.
E2: B4 holds the days of your life. Variable START holds the days from
your birth to the first day of current month. The plus 1 in
calculating B4 means that your first day is counted as 1. But
biorthym calculates the first day as 0. (Sine curve starts from 0
to 2*PI).
E3: This is unnecessary. I just want to show you how to display a
message.
E4: Since our emotional condition changes in a cycle of 23 days, we
can calculate the condition of the first day of current month by
using the operator MOD.
E5: Similar to E4
E6: Similar to E4
Page 80
SHEET User manual By Chor-ming Lung October 9, 1988
E7: Setup our graphic window
E8: Setup the scaling factor. Making the scaling factors a little
larger than what they actually are will provide some blank spaces
for texts.
E9: Clear the window area. EC is emotional constant. Emotional
condition changes in 23 days per cycle. Each cycle is 2*PI long.
PC for physical condition and IC for intellectual condition.
E10: A FOR loop to draw your graph. I just assume each month have 31
days. You may want to change 31 to its actual days.
E11~E13: Simple calculation for your conditions.
E14: Plot emotional condition with marker 1, physical with marker 2,
and intellectual with marker 3
E16~E17: Plot the x-axis and y-axis
E18: Draw marks on x-axis
E19: Draw marks on y-axis
E20: Draw label on x-axis. It is integer.
E21: Draw label on y-axis. It is real with 1 decimal place.
E22: Plot your name on screen. (B1 holds your name)
E23~E25: Plot texts showing the meaning for each marker.
E26: Legal way to return to your current window
E27: END program.
Page 81
SHEET User manual By Chor-ming Lung October 9, 1988
Calendar program ________________
The file name of this program is "CAL.SHT". This program
generates a month calendar and leaves some spaces for your monthly
activity. Here is the listing. To run it, enter "@run q1"
Q1 :!MON=10:YR=88:IBM= FALSE
Q2 :!WEEKDAY=( DATE(MON,1,YR)+1) MOD 7
Q3 :! IF (MON<12) THEN TDAY= DATE(MON+1,1,YR)- DATE(MON,1,YR): ELSE
TDAY= DATE(1,1,YR+1)- DATE(MON,1,YR)
Q4 :! IF IBM THEN GOSUB Q34: ELSE GOSUB Q42
Q5 :! ERASE A1..O64
Q6 :!B3$="^Sun":D3$="^Mon":F3$="^Tue":H3$="^Wed"
Q7 :!J3$="^Thu":L3$="^Fri":N3$="^Sat"
Q8 :!LINES=(WEEKDAY+TDAY)/7
Q9 :!LINES= INT(LINES)+( FRAC(LINES)>0)
Q10 :!WIDTH=60/LINES
Q11 :! FOR I=0 TO 62
Q12 :! FOR J=0 TO 14 STEP 2:A2[J,I]$= CHR$(VERT): NEXT J
Q13 :! NEXT I
Q14 :! FOR I=0 TO 12
Q15 :! IF (I MOD 2)=0 THEN B2[I,0]$="\"+ CHR$(HOR):B4[I,0]$="\"+
CHR$(HOR): ELSE B2[I,0]$="\"+ CHR$(MDOWN):B4[I,0]$="\"+ CHR$(PLUS)
Q16 :! NEXT I
Q17 :!A2$= CHR$(TLEFT):O2$= CHR$(TRIGHT):A4$= CHR$(PLEFT):O4$=
CHR$(PRIGHT)
Q18 :! FOR I=1 TO LINES
Q19 :!L=I*WIDTH: COPY A4..O4,A4[0,L]
Q20 :! NEXT I
Q21 :! FOR J=0 TO 12 STEP 2:C64[J,0]$= CHR$(MUP): NEXT J
Q22 :!A64$= CHR$(BLEFT):O64$= CHR$(BRIGHT)
Q23 :!L=1:J=1:M=WEEKDAY*2
Q24 :! FOR I=WEEKDAY TO WEEKDAY+TDAY+1
Q25 :! IF (I MOD 7)=0 THEN L=L+WIDTH:M=0
Q26 :!B4[M,L]=J:J=J+1:M=M+2: IF J=TDAY THEN I=42
Q27 :! NEXT I
Q28 :! SAVE SCREEN : REDIRECT TO "prn:"
Q29 :! PRINT SPACE$((80- LEN(V1$))/2);V1$: PRINT
Q30 :! PRINT_BLOCK A2..O64,4,76
Q31 :! WAIT 2: RESTORE SCREEN
Q32 :! END
Q34 :!MDOWN=194:MUP=193
Q35 :!PLEFT=195:PRIGHT=180
Q36 :!PLUS=197:VERT=179:HOR=196
Q37 :!TLEFT=218:TRIGHT=191
Q38 :!BLEFT=192:BRIGHT=217
Q39 :! RETURN
Q42 :!MDOWN=45:MUP=45
Q43 :!PLEFT=124:PRIGHT=124
Q44 :!PLUS=43:VERT=124:HOR=45
Q45 :!TLEFT=43:TRIGHT=43
Q46 :!BLEFT=43:BRIGHT=43
Q47 :! RETURN
Page 82
SHEET User manual By Chor-ming Lung October 9, 1988
Explanation: Explanation
Q1 Set the month and year we interest about in the variable mon and
yr. You cannot use month and year for the variable names because
they are names for built-in functions. IBM is TRUE if your printer
can print the IBM graphic characters. If you change the month and
year in cell Q1, please change the string in cell V1 at the same
time.
Q2: WEEKDAY stores the weekday of the first day of the month. WEEKDAY
is 0 if it is Sunday and 1 for Monday and so on.
Q3: TDAY is the total number of days in the specified month. It can be
calculated easily by subtracting the first date of one month after
mon from the first date of mon.
Q4: Initializes some constants depend on whether IBM is TRUE or FALSE.
Q5: Erase cells from A1 through O64
Q6: Row 2 and 4 holds the frame for the title of weekdays. Row 3 holds
the title.
Q8: Normally, we need 4, 5 or 6 lines to hold a monthly calendar. To
calculate how many lines are need for our current month, we can do
it by adding the WEEKDAY and TDAY and divides it by 7.
Q9: This is a little bit tricky. FRAC is a function returns the
fractional part of a numerical value. In this case, LINES from Q8
is equal to 5.285714. FRAC(LINES) returns 0.285714. It is greater
than zero. TRUE is 1 and 0 is FALSE. (FRAC(LINES)>0) returns a
value of 1 and the INT(LINES) returns a value of 5. So we need 6
lines to hold our current monthly calendar.
Q10: The width for each line is 60/LINES. Since the value of LINES can
be 4,5 or 6. So, width is an integer.
Q11~Q22 Fills the frame for our calendar. TLEFT, is the TOP left-
corner, TRIGHT is the TOP right-corner, BLEFT is the Bottom left-
corner and BRIGHT is the Bottom right-corner. VER is a vertical
bar, HOR is a horizontal bar. PLEFT is a T character turns -90
degree and PRIGHT is a T character turns 90 degrees. MDOWN is a T
character and MUP is a T character turns 180 degrees. They are
used to join lines in the edges. PLUS is a + character.
Q23~Q27 Fills the Date into the calendar.
Q28~Q31 prints the calendar to your printer.
Q32: You should always end your program with END.
Q34~Q39: Defines constants for IBM=TRUE
Q42~Q47: Defines constants for IBM=FALSE
Page 83
SHEET User manual By Chor-ming Lung October 9, 1988
Roots _____
ROOTS.SHT is a sample program which finds roots for any single
variable functions. There are some techniques which are not covered in
other sample programs. You should study it even you have no interest in
finding roots for a function.
B1 : !XMIN=-5:YMIN=-5:XMAX=5:YMAX=100:ST=0.1:ERR=0.000001
B2 : ! DEFINE F(X)=3*X*X-2
B3 : ! IF ALERT(2,"[0][Which resolution you are
in?][Medium|High]")=2 THEN WINDOW 0,0,20,639,399: ELSE WINDOW
0,0,11,639,199
B4 : ! SCALE XMIN,YMIN,XMAX,YMAX
B5 : ! CLEAR
B6 : ! PLOT XMIN,F(XMIN)
B7 : ! FOR X=XMIN TO XMAX STEP ST
B8 : ! LINE_TO X,F(X)
B9 : ! NEXT X
B10 : ! LINE XMIN,0,XMAX,0: LINE 0,YMIN,0,YMAX
B11 : !X= ALERT(1,"[0][Can you see any|root in your graph?][Yes|No]")
B12 : ! IF X=2 THEN MESSAGE "Change your minimum and|maximum values
and try again": EXITGRAPH
B13 : ! IF X=2 THEN END
B14 : ! MESSAGE "Locate mouse cursor|to the left of a|root in your
graph"
B15 : ! MOUSE_POS AX,AY
B16 : ! MESSAGE "Locate mouse cursor|to the right of a|root in your
graph"
B17 : ! MOUSE_POS BX,BY
B18 : ! LOOP
B19 : !AY=F(AX):BY=F(BX)
B20 : ! EXIT IF ( ABS(AY)<ERR OR ABS(BY)<ERR)
B21 : !HALF=(AX+BX)/2:HY=F(HALF)
B22 : ! IF (AY*HY<0) THEN BX=HALF: ELSE AX=HALF
B23 : ! ENDLOOP
B24 : ! IF ABS(AY)<ERR THEN A1=AX: ELSE A1=BX
B25 : ! EXITGRAPH
B26 : ! END
For solving different functions, you should adjust B1, B2.
Explanations: Explanations
B1: min,ymin,xmax,ymax setup the scaling factors. ST is step value for
plot the function. ERR is the error tolerance. If you want to have
a more accurate result, you should make ERR smaller.
B2: The function we are trying to find roots for. Remember, each
function definition should have its own cell
B3: Open the largest window on Medium or High resolution. Remember
WINDOW 0 is the current window. The rest numbers are the raster
coordinate.
B4: Set the scaling factors.
B5: Clear our window
Page 84
SHEET User manual By Chor-ming Lung October 9, 1988
B6: Plot the first point
B7: Plot the rest of the graph.
B10: Draw the x- and y-axes.
B11: A root on a graph is the place where it hits the x-axis. If you
see the curve hits the x-axis, you should answer Yes.
B12: If the curve does not hit x-axis, then leave a reminder for user
to change the plotting range. If you set up a large value for x
range, and you still can't see the root, then the function defined
may not have a root. You cannot use this program to find it.
B13: EXITGRAPH command is not working quite well. It does not like
anything following it. I still don't know why. The END command
should follow EXITGRAPH, but there is a bug which force us to end
the program this way.
B14: If you see a root, you can use the mouse to locate the left handed
side value for the root. This message just reminds user to select
the left handed side value for the root. You may need to hold your
mouse a little longer that usual.
B15: User input the left handed side value by pointing the mouse cursor
to the left of the point that hits the x-axis, and click your
mouse button. You can point to anywhere on the left handed side of
the root.
B16~B17:We need the right handed side value. The program should be
stopped quite fast. If you see nothing happen for a long time, you
should press the mouse button again. If it is still waiting for
mouse button, <Esc> key will not stop the program.
B18: A loop using bi-section method to find the root of a function.
B24: If we found the root, we put it in cell A1
B25: Normal exit
B26 End of the program.
Page 85