A downloadable tool

"In 1978 Dan Bricklin invented the interactive VISIble CALCulator.
        In 2021 I turned it into a BASIC 10-Liner."
                ____  _  _  ___  ____  __      _  ___   ___   _
               (_   )( \/ )/ __)( ___)(  )    / )(__ \ | __) / )
                / /_  )  (( (__  )__)  )(__  / /  / _/ |__ \/ _ \
               (____)(_/\_)\___)(____)(____)(_/  (____)(___/\___/
 (c) 2021 Nick Shcherbyna (Lviv, Ukraine)       computer.history.lviv@gmail.com
                                Category "SCHAU"
 ZXcel/256, as one might guess, is a spreadsheet program written in ZX Spectrum
 BASIC. It supports strings and numbers, formulas, 8 colors, printing, and even
 copy/paste with automatic updating of references. There are 10x20 cells, named
 A1 to J20, with 3 visible columns (via horizontal scrolling).
 This software was developed and tested in BASINC Version 1.7. Download it at:
 Copy and paste line-by-line, or just File->Open... ZXCEL.BAS, then RUN. Tape
 image is also provided, use LOAD "" in any Speccy emulator.
   [q]   - move the selection up
   [a]   - move the selection down
   [o]   - move the selection left
   [p]   - move the selection right
   [w]   - print preview mode (i.e., hide selection)
   [y]   - print using ZX Printer (from the preview)
   [0]   - pick the black color (default)
   [1]   - pick the blue color
   [2]   - pick the red color
   [3]   - pick the magenta color
   [4]   - pick the green color
   [5]   - pick the cyan color
   [6]   - pick the yellow color
   [7]   - pick the light gray color (i.e., hide cell)
   [x]   - clear the selected cell
   [c]   - copy from the selected cell
   [v]   - paste into the selected cell (and update references)
 [ENTER] - input a string/number/formula into the selected cell
 Start formula with =. Integers and floating point numbers are supported. Cells
 are referred as A1, A2, ..., A20, B1, B2, ..., B20, ... ..., J1, J2, ..., J20.
 BASIC arithmetic operations are supported (+ - * / ^), e.g. =2*(A1+B1)+E5/100.
 Frozen references (e.g. $C$14, $B29, A$5) are NOT supported. Division by zero,
 recursive references (e.g. A1=2*B1+1, B1=C1/100, C1=A1+1.2), and syntax errors
 will crash the program!
         A           B           C           D           E
 1 | 1st Name  | Last Name | Salary, $ |         0 |
 2 | John      | Doe       |      3000 | =D1+C2    |
 3 | Nick      | Johnson   |      4500 | =D2+C3    |
 4 | Pam       | Cooper    |      3333 | =D3+C4    |
 5 |           |    Total: | =D4       |           |
 6 |           |           |           |           |
 will calculate
         A           B           C           D           E
 1 | 1st Name  | Last Name | Salary, $ |         0 |
 2 | John      | Doe       |      3000 |      3000 |
 3 | Nick      | Johnson   |      4500 |      7500 |
 4 | Pam       | Cooper    |      3333 |     10833 |
 5 |           |    Total: |     10833 |           |
 6 |           |           |           |           |
 Everything is calculated in the proper order. User can fill D2, copy it, then
 paste into D3 and D4, automatically obtaining the expected formulas.
 A really exciting feature of ZX Spectrum BASIC, thanks to the genius of Steve
 Vickers, is its VAL() function. Today we would rather call it EVAL(), because
 unlike other versions that just convert the string argument into a number, it
 evaluates the BASIC expression. Moreover, the provided expression can contain
 another VAL(), and it will be computed first!
 Sounds like we have almost everything for our spreadsheet, just write the UI?
 Not so good. ZX BASIC requires variable initialization before use. While this
 is a good coding practice, LETting all cells preinitialized is a disaster for
 our very limited code space (I voluntary decided to support 10x20=200 cells).
 It will require almost 8 lines with 256 characters limit. In theory one would
 do that by POKEs to BASIC's system data structures, but this is a thin ice of
 nearly self-modified code (prohibited by the 10-Liner contest rules). Left it
 as an exercise to the reader.
 So we need an extra translation layer, call it ZXcel to BASIC compiler. Cells
 now are stored in the 2-dimentional array C$(10,20,93). Here 3rd dimension is
 not a typo, that is how ZX BASIC reserves the string space. The example above
 will compile into (parentheses are optional for single argument calls):
  C$      (1,         (2,          (3,                 (4,                (5,
 ,1) |"0"        |"0"        |"0"          |"0"                      |
 ,2) |"0"        |"0"        |"3000"       |"VAL C$(4,1)+VAL C$(3,2)"|
 ,3) |"0"        |"0"        |"4500"       |"VAL C$(4,2)+VAL C$(3,3)"|
 ,4) |"0"        |"0"        |"3333"       |"VAL C$(4,3)+VAL C$(3,4)"|
 ,5) |"0"        |"0"        |"VAL C$(4,4)"|"0"                      |
 ,6) |           |           |             |                         |
 - String is compiled into "0".
 - Number is compiled into its string representation.
 - Formula is tokenized and transformed. Each occurence of "A".."J" ("a".."j")
   followed by one or two digits is parsed into pair of indices (i,j) and then
   output as VAL C$(i,j). Other characters remain unchanged.
 After any changes all cells with formulas are iterated sequentally and filled
 by VAL C$(i,j) result. While not optimal, it will produce the desired result,
 always performing full recursive evaluation. Optimization techniques found in
 professional spreadsheet software, such as topological sorting, require extra
 memory and code space.
 Widespread spreadsheet software, such as Microsoft Excel or LibreOffice Calc,
 allows to copy the formula and paste it into another cell, with automatically
 updated references. For example, copying D2=D1+C2 and pasting it into D3 will
 emit D2+C3, while pasting it into D4 will produce D3+C4, etc. This definitely
 useful feature is implemented in ZXcel/256, except the frozen references that
 does not change (like $A$10).
 Assume we copy a formula from the cell (W,H), and paste it into (X,Y). To fix
 references we perform parsing, very similar to compilation process above, and
 replace each reference (I,J) as (I-W+X,J-H+Y).
 The THEN clause in an IF statement is executed until the end of line. This is
 undesirable. The scoped IF <cond> THEN ... ENDIF construct can be written as:
 FOR i=1 TO <cond> : ... : NEXT i.
 Comparsions in ZX BASIC produce 0 (FALSE) or 1 (TRUE), unlike MS BASIC, where
 TRUE is -1. The result of X AND Y is 0 if Y is zero, and X otherwise.
 The hidden power of VAL() is already explained above.
 0. Declare 2D array S$(10,20,9) for visible representation of cells.
    Declare 2D array A(10,20) for cell colors (zeroed, so black by default).
    Declare 2D array T$(10,20,31) for user INPUT strings (stored as is).
    Declare 2D array C$(10,20,93) for compiled cells (read PRINCIPLE above).
    Initialize N$ as an empty cell.
    (X,Y)=(1,1) is a selected cell, Z is horizontal scrolling position.
    Set visibility flag V (cleared for print preview).
    (W,H)=(0,0) is a copied cell (0 means not copied yet).
    Clear screen.
    Print row numbers, initialize compiled cells to 0.
    Define function D() that checks if argument is a digit.
 1. Define function L() that checks if argument is a lowercase letter.
    Initialize V$ to the VAL BASIC token (you cannot just write "VAL").
    Put "ZX" at the left top corner.
    Clear lines 22 and 23 (USR 3438 is a documented entrypoint).
    Print keyboard tips.
 2. Print the current cell content (as previously INPUTted by user).
    Iterate visible cells (I,J) to redraw. Also print column names.
    Wait for keypress and store the code into K$.
 3. Assume there is no input in L$.
    Change the selection row (Y) if [q] or [a] is pressed.
    Change the selection column (X) if [o] or [p] is pressed.
    Change the scrolling position (Z) if needed.
    If [ENTER] is pressed:
    - clear lines 22 and 23 (USR 3438 is a documented entrypoint);
    - INPUT the current cell content into L$;
    - redraw stuff from (0) and clear L$ if length exceeded the limit.
 4. Fast path for navigation keys, skip to (2).
    If there was user input in L$, assume string and initialize ?(X,Y) for S$,
    T$, and C$.
    Detect a number (should start with "+", "-", or digit, followed by digits
    and dots). If so, store the value into C$ and align right in S$.
 5. Skip if not a formula. Otherwise G$ will collect the compiled code. Refer
    to RULES above for details on parsing and compilation.
 6. Handle LPRINTing if [z] is pressed and print preview (V=0). Otherwise, if
    there was user input:
    - clear lines 22 and 23 (USR 3438 is a documented entrypoint);
    - show the "Be patient" message there;
    - if we compile, append the new output and return to (5). Append the last
      character if it was not parsed.
 7. Set the return line G to (1).
    Change the current cell color A(X,Y) if [0]-[7] is pressed.
    If there was any input, recalculate the whole spreadsheet. See EVALUATION
    above. Results in S$ are aligned right, like in (4).
 8. If [v] is pressed, and stuff was copied (W>0), perform paste as described
    above in COPY/PASTE. M$ is the source string, number, or formula. L$ will
    contain the result.
 9. Finish pasting, set return line G to (4) and clear the keycode K$. Handle
    copy if [c] is pressed, delete if [x] is pressed. Set the visibility flag
    V unless [w] is pressed. Return to (1) normally, or to (4) for simulating
    INPUT L$.
 The project goal was to verify the theory that a decent spreadsheet software
 can be created on ZX Spectrum with a help of its amazing VAL(). It generally
 succeeded. With a help of machine code one can create something as useful as
 Microsoft Excel or LibreOffice Calc.
 It was an one day hobby project, so there is a lot to improve. Left it as an
 exercise to the reader.</cond></cond>



Development log

Leave a comment

Log in with itch.io to leave a comment.