ZXcel / 256 by Nick Shcherbyna
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"
INTRODUCTION:
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).
EMULATION:
This software was developed and tested in BASINC Version 1.7. Download it at:
https://arda.kisafilm.org/blog/?p=1230&lang=en
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.
KEYS:
[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
FORMULAS:
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!
EXAMPLE:
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.
PRINCIPLE:
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) | | | | |
RULES:
- 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.
EVALUATION:
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.
COPY/PASTE:
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).
TRICKS:
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.
LINES:
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$.
CONCLUSION:
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>Download
Development log
- Image downloadApr 08, 2021
Leave a comment
Log in with itch.io to leave a comment.