Here is the spreadsheet program I’ve been working on for the last six weeks. It is written in mmBasic. It started out on my PicoCalc with a RP2040 board but because of heap memory problems, my PicoCalc ended up with a RP2350 board. The program actually ran on the RP2040 but would run out of memory when putting data into the spreadsheet. The program, named “SS1.bas”, runs just fine on the RP2350.
SS1.bas features a 10 column by 40 row total size and a 4 column by 15 row screen view. Columns and rows can be scrolled for viewing. The 60 cells shown on the screen are full size because no column/row headers are used. Navigation is done by a small window in the lower left-hand corner of the screen. To the right of this window is the “command” bar where all typing (cell labels, formulas, condition statements, etc.) take place.
The “feel” and running of SS1.bas is very similar to Excel except for one large difference. Formulas in Excel are written algebraically, but are written in RPN on this spreadsheet. - - - - Now, I can hear the cheers from the HP folks, but I also hear the groans from the TI folks. Please, give me a minute to explain something to the TI folks. RPN can be a bit daunting on a calculator because all data must be managed on a “stack”. That is not required in a spreadsheet. In a SS the data is stored in “cells” and thus each individual data can be “called” when needed while building the formula. Yeh! No parentheses needed! Just put 2 operands in the “command” bar and then put in the operator. Simple.
All other SS1.bas functions are very much like Excell. For instance, to enter something into a cell, first move the cursor with the arrow keys to the desired cell. If the desired cell is off screen, just keep tapping on the arrow key in the direction of the cell. The columns and rows will “scroll” to the required cell. (Keep your eye on the “Cell ID” window in the lower left-hand corner.) When the cursor is over the desired cell, tap the “Enter” key. This will activate the “Command” bar. Type in “whatever” and then tap the “Enter” key again to move the “whatever” from the “command” bar to the desired cell.
The above “whatever” can be several different types of things: numbers, text, cell ID, formulas, condition statements, etc. Each “thing” is entered the same way as described above (it takes 2 taps on the “Enter” key). Numbers are straight forward: “Enter”, 23.74, “Enter”. Text: Only 1 “word” can be entered. Thus, to enter “Ground Speed” into a cell you have to type “Ground.Speed”. Disregard the " ". The . can be anything like , _ | @ # etc. Just no spaces between words. Cell ID: A1 The columns go from A to J; the rows from 1 to 40. Formulas: Use RPN (Operands then Operator) One space between each “Token”. Condition statements: Condition True False
Files: Tap F1 key. “1New, 2Open, 3Save, 4Save As, Esc” appears in Command bar. Tap the number for the desired function. NOTE: Only the file name needs to be typed in. The “B:” and file extension (.ss1) will be provided by the program.
Cell Protection: Most users don’t have to worry much about protecting vital cells. However, some pilots have been known to accidently type in cells that contain miscellaneous calculations and have ended up at wrong airports. (Please don’t ask me how I happen to know that.) So, to that end, I use color to mark the “Label”, “Input”, “Output”, and “Formula” cells. Tap the “Tab” key and a green 1, yellow 2, red 3, and a blue 4 plus “Esc” will appear in the “Command” bar. Move the cursor over a cell and then tap the colored number to change the color of the “text” in the chosen cell. Tap “Esc” when finished coloring cells. I use green for labels and misc. cells, yellow for input cells, red for output cells, and blue for formula cells. Normally I put the misc. calculation cells out-of-view; like cell J40. In the above “Sun” picture, I put the “blue” cells in the lower right-hand corner just to give you an idea of what I’m talking about.
Hope you will enjoy the app. I’ve made only four SS with it so far and have found only one bug. (And I have corrected that one bug already.) I would appreciate your letting me know if you find any more problems with the app. I will try to keep it up to date. Feel free to modify it if you want. I know the . . . . . are a nuisance but understand that the program has to figure out what “type” of data is being typed in the “command” bar. - Jim
'============================
’ SS1.bas
'============================
’ c = cursor column (0b3 on screen)
’ r = cursor row (0b14)
’ h = horizontal scroll offset
'============================
Dim c, r, h, v
Dim cell$(10,40)
Dim bdit$, editPos, editing
Dim viewOffset
Dim fileMode, fileState, fileName$
Dim cellColor(10,40)
Dim colorMode
'============================
Sub InitProgram
c = 0
r = 0
h = 0
v = 0
editing = 0
bdit$ = “”
editPos = 0
viewOffset = 0
Local i, j
For j = 0 To 39
For i = 0 To 9
cellColor(i,j) = 1
Next i
Next j
FRAMEBUFFER CREATE
FRAMEBUFFER WRITE F
CLS
End Sub
'============================
Sub DrawScreen
CLS
DrawGrid
DrawCells
DrawUI
FRAMEBUFFER COPY F,N
End Sub
'============================
Sub DrawGrid
Colour RGB(0,155,0)
Local i
For i = 0 To 320 Step 20
Line 2, i, 318, i
Next
For i = 2 To 318 Step 79
Line i, 0, i, 299
Next
Line 318, 299, 318, 319
Colour RGB(Green)
DrawCursor c, r
End Sub
'============================
Sub DrawUI
Local visible$, cx
Colour RGB(Red)
Box 2, 300, 32, 20
UpdateCellID
Colour RGB(Green)
'========================
’ FILE MENU DISPLAY
'========================
If fileMode = 1 Then
Text 34,304, Space$(35)
Text 34,304, " 1-New 2-Open 3-Save 4-SaveAs Esc"
Exit Sub
EndIf
If fileMode = 2 Then
Text 34,304, Space$(35)
Text 34,304, fileName$
Exit Sub
EndIf
'========================
’ COLOR MODE
'========================
If colorMode = 1 Then
Text 34,304, Space$(35)
’ 1 = GREEN
Colour RGB(Green)
Text 50,304, “1”
’ 2 = YELLOW
Colour RGB(Yellow)
Text 75,304, “2”
’ 3 = RED
Colour RGB(Red)
Text 100,304, “3”
’ 4 = BLUE
Colour RGB(0,0,128)
Text 125,304, “4”
’ ESC label
Colour RGB(Green)
Text 150,304, “Esc”
Exit Sub
EndIf
'========================
’ NORMAL EDIT MODE
'========================
If editing = 1 Then
Text 34,304, Space$(35)
visible$ = Mid$(bdit$, viewOffset+1, 34)
Text 34,304, visible$
’ cursor
Colour RGB(Red)
cx = 34 + (editPos - viewOffset) * 8
Line cx, 304, cx, 316
Colour RGB(0,155,0)
Line 106, 304, 106, 316
Colour RGB(Green)
Else
Text 34,304, Space$(35)
EndIf
End Sub
'============================
Sub UpdateCellID
Local col$, cellID$
col$ = Chr$(64 + h + c + 1)
cellID$=col$+LTrim$(Str$(v+r+1))
Text 6,304, Space$(3)
Text 6,304, cellID$
End Sub
'============================
Sub DrawCursor(c, r)
Local x, y
x = c * 79 + 2
y = r * 20
Colour RGB(Yellow)
Box x, y, 80, 21
Colour RGB(Green)
End Sub
'============================
Sub DrawCells
Local sc, sr, x, y, val$, raw$, isCalc, maxChars, pad$
maxChars = 9 ’ fits nicely in 79px
For sr = 0 To 14
For sc = 0 To 3
raw$ = cell$(h + sc, v + sr)
val$ = GetDisplayValue$(h + sc, v + sr)
x = sc * 79 + 4
y = sr * 20 + 4
’ detect numeric (typed OR calculated)
isCalc = 0
If Instr(raw$, " ") > 0 Then isCalc = 1
If Left$(raw$,1)>=“A” And Left$(raw$,1)<=“Z” And Val(Mid$(raw$,2))>0 Then isCalc = 1
If Val(raw$) <> 0 Or raw$ = “0” Then isCalc = 1
’ FORMAT ONLY calculated values
If isCalc = 1 Then
’ trim numbers/formulas
If Len(val$) > maxChars Then
val$ = Left$(val$, maxChars)
EndIf
’ right justify numbers/formulas
pad$ = Space$(maxChars - Len(val$))
val$ = pad$ + val$
EndIf
’ APPLY CELL COLOR
Select Case cellColor(h+sc, v+sr)
Case 1
Colour RGB(Green)
Case 2
Colour RGB(Yellow)
Case 3
Colour RGB(Red)
Case 4
Colour RGB(0,0,128)
End Select
Text x, y, val$
Next sc
Next sr
End Sub
'============================
Sub ClearSheet
Local i, j
For j = 0 To 39
For i = 0 To 9
cell$(i, j) = “”
cellColor(i,j) = 1
Next i
Next j
c = 0
r = 0
h = 0
v = 0
End Sub
'============================
Sub HandleColorMode
Local k$
k$ = Inkey$
If k$ = “” Then Exit Sub
Select Case k$
’ EXIT
Case Chr$(27)
colorMode = 0
DrawScreen
Exit Sub
’ COLOR KEYS
Case “1”
cellColor(h + c, v + r) = 1
Case “2”
cellColor(h + c, v + r) = 2
Case “3”
cellColor(h + c, v + r) = 3
Case “4”
cellColor(h + c, v + r) = 4
’ MOVEMENT (same as normal mode)
Case Chr$(128)
If r > 0 Then
r = r - 1
ElseIf v > 0 Then
v = v - 1
EndIf
Case Chr$(129)
If r < 14 Then
r = r + 1
ElseIf v < 25 Then
v = v + 1
EndIf
Case Chr$(130)
If c > 0 Then
c = c - 1
ElseIf h > 0 Then
h = h - 1
EndIf
Case Chr$(131)
If c < 3 Then
c = c + 1
ElseIf h < 6 Then
h = h + 1
EndIf
End Select
DrawScreen
End Sub
'============================
Sub DoFileAction
Local fname$, i, j, p
On Error Skip 1
’ find separator safely
p = Instr(fileName$, “:”)
If p = 0 Then Exit Sub
If Len(fileName$) <= p Then Exit Sub
'extract raw name after “Open:”/“Save:”
fname$ = Mid$(fileName$, p + 1)
fname$ = LTrim$(fname$)
If fname$ = “” Then Exit Sub
fname$ = “B:” + fname$ + “.ss1”
Select Case fileState
Case 2 ’ OPEN
Open fname$ For Input As #1
If MM.Errno <> 0 Then
Text 34,304,“File Error!”
Pause 1000
Exit Sub
EndIf
For j = 0 To 39
For i = 0 To 9
Line Input #1, cell$(i,j)
Input #1, cellColor(i,j)
Next i
Next j
Close #1
Case 3,4 ’ SAVE / SAVEAS
Open fname$ For Output As #1
If MM.Errno <> 0 Then
Text 34,304,“File Error!”
Pause 1000
Exit Sub
EndIf
For j = 0 To 39
For i = 0 To 9
Print #1, cell$(i,j)
Print #1, cellColor(i,j)
Next i
Next j
Close #1
End Select
End Sub
'============================
Sub HandleFileMenu
Local k$
k$ = Inkey$
If k$ = “” Then Exit Sub
’ — MAIN MENU —
If fileMode = 1 Then
Select Case k$
Case “1”
ClearSheet
fileName$ = “”
fileState = 0
fileMode = 0
DrawScreen
Case “2”
fileState = 2
fileName$ = "Open: "
fileMode = 2
DrawScreen
Case “3”
If fileName$ <> “” Then
fileState = 3
DoFileAction
fileMode = 0
DrawScreen
Else
fileState = 4
fileName$ = "SaveAs: "
fileMode = 2
DrawScreen
EndIf
Case “4”
fileState = 4
fileName$ = "SaveAs: "
fileMode = 2
DrawScreen
Case Chr$(27)
fileMode = 0
DrawScreen
End Select
Exit Sub
EndIf
’ — NAME ENTRY —
If fileMode = 2 Then
Select Case k$
Case Chr$(13) ’ ENTER
DoFileAction
fileMode = 0
DrawScreen
Case Chr$(27) ’ ESC
fileMode = 0
DrawScreen
Case Chr$(8) ’ BACKSPACE
If Len(fileName$) > 0 Then
fileName$=Left$(fileName$, Len(fileName$)-1)
EndIf
DrawScreen
Case Else
If Len(k$) = 1 Then
fileName$ = fileName$ + k$
DrawScreen
EndIf
End Select
EndIf
End Sub
'============================
Sub StartEdit
bdit$ = cell$(h + c, v + r)
editPos = Len(bdit$)
viewOffset = 0
editing = 1
DrawScreen
End Sub
'============================
Sub HandleTyping
Local k$
k$ = Inkey$
If k$ = “” Then Exit Sub
Select Case k$
Case Chr$(13) ’ ENTER = save
cell$(h + c, v + r) = bdit$
editing = 0
DrawScreen
Exit Sub
Case Chr$(27) ’ ESC = cancel
editing = 0
DrawScreen
Exit Sub
Case Chr$(8) ’ BACKSPACE
If editPos > 0 Then
bdit$=Left$(bdit$,editPos-1)+Mid$(bdit$,editPos+1)
editPos = editPos - 1
EndIf
Case Chr$(130) ’ LEFT
If editPos>0 Then editPos=editPos-1
Case Chr$(131) ’ RIGHT
If editPos<Len(bdit$) Then editPos=editPos+1
Case Else
’ normal character input
If Len(k$) = 1 Then
bdit$=Left$(bdit$,editPos)+k$+Mid$(bdit$,editPos+1)
editPos = editPos + 1
EndIf
'— SCROLLING LOGIC —
End Select
'— SCROLLING LOGIC (ALWAYS RUN) —
If editPos < viewOffset Then
viewOffset = editPos
EndIf
If editPos >= viewOffset + 34 Then
viewOffset = editPos - 34
EndIf
DrawScreen
End Sub
'============================
Sub HandleKeys
Local k$
If fileMode > 0 Then
HandleFileMenu
Exit Sub
EndIf
If editing = 1 Then
HandleTyping
Exit Sub
EndIf
If colorMode = 1 Then
HandleColorMode
Exit Sub
EndIf
k$ = Inkey$
Select Case k$
Case Chr$(128) ’ UP
If r > 0 Then
r = r - 1
ElseIf v > 0 Then
v = v - 1
EndIf
DrawScreen
Case Chr$(129) ’ DOWN
If r < 14 Then
r = r + 1
ElseIf v < 25 Then
v = v + 1
EndIf
DrawScreen
Case Chr$(130) ’ LEFT
If c > 0 Then
c = c - 1
ElseIf h > 0 Then
h = h - 1
EndIf
DrawScreen
Case Chr$(131) ’ RIGHT
If c < 3 Then
c = c + 1
ElseIf h < 6 Then
h = h + 1
EndIf
DrawScreen
Case Chr$(9) ’ TAB = Color Mode
colorMode = 1
DrawScreen
Case Chr$(145) 'File Menu
fileMode = 1
DrawScreen
Case Chr$(13) ’ ENTER
StartEdit
End Select
End Sub
'============================
Sub ProcessToken(t$, s(), sp)
Local a, b
Select Case UCase$(t$)
'------------------
’ BASIC OPERATORS
'------------------
Case “+”
b = s(sp): sp = sp - 1
a = s(sp): sp = sp - 1
sp = sp + 1: s(sp) = a + b
Case “-”
b = s(sp): sp = sp - 1
a = s(sp): sp = sp - 1
sp = sp + 1: s(sp) = a - b
Case ""
b = s(sp): sp = sp - 1
a = s(sp): sp = sp - 1
sp = sp + 1: s(sp) = a * b
Case “/”
b = s(sp): sp = sp - 1
a = s(sp): sp = sp - 1
sp = sp + 1: s(sp) = a / b
Case “^”
b = s(sp): sp = sp - 1
a = s(sp): sp = sp - 1
sp = sp + 1: s(sp) = a ^ b
'------------------
’ CONSTANTS
'------------------
Case “PI”
sp = sp+1: s(sp)=3.14159265
'------------------
’ TRIG (DEGREES!)
'------------------
Case “SIN”
a = s(sp): sp = sp - 1
sp=sp+1: s(sp)=Sin(a3.14159265/180)
Case “COS”
a = s(sp): sp = sp - 1
sp=sp+1: s(sp)=Cos(a3.14159265/180)
Case “TAN”
a = s(sp): sp = sp - 1
sp=sp+1: s(sp)=Tan(a3.14159265/180)
Case “ASIN”
a = s(sp): sp = sp - 1
sp=sp+1: s(sp)=ASin(a)*180/3.14159265
Case “ACOS”
a = s(sp): sp = sp - 1
sp=sp+1: s(sp)=ACos(a)*180/3.14159265
Case “ATAN”
a = s(sp): sp = sp - 1
sp=sp+1: s(sp)=Atan(a)*180/3.14159265
'------------------
’ LOG / ROOT
'------------------
Case “SQR”
a = s(sp): sp = sp - 1
sp = sp + 1: s(sp) = Sqr(a)
Case “LOG”
a = s(sp): sp = sp - 1
sp = sp + 1: s(sp) = Log10(a)
Case “LN”
a = s(sp): sp = sp - 1
sp = sp + 1: s(sp) = Log(a)
'------------------
’ UTIL
'------------------
Case “ABS”
a = s(sp)
s(sp) = Abs(a)
Case "+/-"
a = s(sp)
s(sp) = -a
Case "INT"
a = s(sp): sp = sp - 1
sp = sp + 1: s(sp) = Int(a)
Case "RND"
a = s(sp): sp = sp - 1
If a >= 0 Then
sp = sp + 1: s(sp) = Fix(a + 0.5)
Else
sp = sp + 1: s(sp) = Fix(a - 0.5)
End If
'------------------
' CONDITION
'------------------
Case ">"
b = s(sp): sp = sp - 1
a = s(sp): sp = sp - 1
sp = sp + 1: s(sp) = (a > b)
Case "<"
b = s(sp): sp = sp - 1
a = s(sp): sp = sp - 1
sp = sp + 1: s(sp) = (a < b)
Case "="
b = s(sp): sp = sp - 1
a = s(sp): sp = sp - 1
sp = sp + 1: s(sp) = (a = b)
Case "IF"
Local falseVal, trueVal, cond
falseVal = s(sp): sp = sp - 1
trueVal = s(sp): sp = sp - 1
cond = s(sp): sp = sp - 1
sp = sp + 1
If cond <> 0 Then
s(sp) = trueVal
Else
s(sp) = falseVal
End If
Case Else
If Left$(t$,1)>="A" And Left$(t$,1)<="Z" And Val(Mid$(t$,2)) > 0 Then
sp = sp + 1
s(sp) = GetCellValue(t$)
Else
sp = sp + 1
s(sp) = Val(t$)
End If
End Select
End Sub
'============================
Function LTrim$(s$)
Do While Len(s$)>0 And Left$(s$,1)=" "
s$ = Mid$(s$,2)
Loop
LTrim$ = s$
End Function
'============================
Function EvalRPN(e$)
Local s(100)
Local sp, t$, i, c$, n
On Error Skip 1 ’ ← MUCH safer in MMBasic
sp = 0
t$ = “”
n = Len(e$)
For i = 1 To n
c$ = Mid$(e$, i, 1)
If c$ = " " Then
If t$ <> “” Then
ProcessToken t$, s(), sp
t$ = “”
End If
Else
t$ = t$ + c$
End If
Next i
If t$ <> “” Then ProcessToken t$, s(), sp
EvalRPN = s(sp)
End Function
'============================
Function GetCellValue(r$)
Local c, rr, x$
c = Asc(Left$(r$,1)) - 65
rr = Val(Mid$(r$,2)) - 1
If c < 0 Or c > 9 Or rr < 0 Or rr > 39 Then
GetCellValue = 0
Exit Function
End If
x$ = cell$(c, rr)
If x$ = “” Then
GetCellValue = 0
Exit Function
End If
If Instr(x$, " ") > 0 Then
GetCellValue = EvalRPN(x$)
ElseIf Left$(x$,1)>=“A” And Left$(x$,1)<=“Z” And Val(Mid$(x$,2))>0 Then
Local c2, r2
c2 = Asc(Left$(x$,1)) - 64
r2 = Val(Mid$(x$,2))
GetCellValue = Val(cell$(c2, r2))
Else
’ detect numeric string
If (Val(x$) <> 0) Or x$ = “0” Or x$ = “0.0” Then
GetCellValue = Val(x$)
Else
GetCellValue = 0 ’ text is NOT a number
End If
End If
End Function
'============================
Function GetDisplayValue$(c, r)
Local x$, o$
x$ = cell$(c, r)
If x$ = “” Then
GetDisplayValue$ = “”
Exit Function
End If
’ RPN formula (must contain spaces)
If Instr(x$, " ") > 0 Then
o$ = LTrim$(Str$(EvalRPN(x$)))
GetDisplayValue$ = o$
Exit Function
End If
’ cell reference like A1 (letter + number ONLY)
If Left$(x$,1)>=“A” And Left$(x$,1)<=“Z” And Val(Mid$(x$,2)) > 0 Then
o$ = LTrim$(Str$(GetCellValue(x$)))
GetDisplayValue$ = o$
Exit Function
End If
’ numeric
If (Val(x$) <> 0) Or x$ = “0” Then
GetDisplayValue$ = x$
Exit Function
End If
’ TEXT (default)
GetDisplayValue$ = x$
End Function
'============================
InitProgram
DrawScreen
Do
HandleKeys
Loop
End