SpreadCE Help


Macros
Macros can be used to create user-defined functions to use in your spreadsheets, and user-defined commands to automate tasks.
User-defined functions
Macros are useful for user-defined functions in 2 situations:
1. You may have a complex function, which is composed of many nested functions, which is used in many places on your worksheet. A user-defined function would save you a lot of typing by specifying the actual calculation in only one place.
2. You may need to use an iterative or recursive calculation that has to be performed a certain number of times before a result is found.
These are the steps for entering a user defined function:
1. Use the Insert Macro menu option to insert a macro sheet in the workbook. Macros must be stored on macro sheets. You can have many macros on a single macro sheet.
2. Enter the macro function statements. A user-defined function macro will usually begin with one or more ARGUMENT functions to receive the parameters, and it must end with a RETURN function.
This example calculates the approximate integral of the function sin(x) between a lower and upper limit using the trapezium rule and dividing the area into 100 slices:


 
A
B
C
D


1
Integral:
=ARGUMENT(,1,D1)
lower:
 


2
 
=ARGUMENT(,1,D2)
upper:
 


3
 
=SET.VALUE(D3,0)
sum:
 


4
 
=SET.VALUE(D4,SIN(D1))
y0:
 


5
 
=SET.VALUE(D5,SIN(D2))
yn:
 


6
 
=SET.VALUE(D6,1)
i:
 


7
 
=WHILE(D6<D7)
steps:
100


8
 
=SET.VALUE(D8,D1+(D2-D1)*D6/D7)
x:
 


9
 
=SET.VALUE(D3,D3+SIN(D8))
 
 


10
 
=SET.VALUE(D6,D6+1)
 
 


11
 
=NEXT()
 
 


12
 
=RETURN((D2-D1)/D7/2*(D4+D5+2*D3))
 
 


3. Use the Insert Name Define menu option to add a name for the macro. For the example above, you could define a name called 'integral', which refers to cell: Macro1!$B$1. Note that the macro name in cell A1 is for our reference only, it is not used by the program.
4. Then on your worksheet (or even in another macro), use the defined name like an ordinary worksheet function name.
For the example above, if you enter 0 in cell A1 on your worksheet, and =PI() in cell A2 on your worksheet, and =integral(A1,A2) in cell A3, then cell A3 will display 1.9998355 (higher accuracy could be obtained with a larger number of steps but the calculation would take correspondingly longer).
Here is an example that uses recursion. Because macro sheets do not directly support recursion, I have implemented a simple stack to store the values at each level by concatenating them together in a string.
This function displays a number as text:


 
A
B
C
D
E


1
Say number:
=ARGUMENT(,1,D1)
input:
 
 


2
 
=SET.VALUE(D2,"")
output:
 
 


3
 
=SET.VALUE(D1,INT(D1))
temp:
 
 


4
 
=IF(D1<=0,RETURN(D2))
 
 
 


5
 
=IF(D1>=1000000)
 
 
 


6
stack input
=SET.VALUE(E1,D1&"|"&E1)
 
 
 


7
stack output
=SET.VALUE(E2,D2&"|"&E2)
 
 
 


8
get millions
=SET.VALUE(D3,saynumber(D1/1000000))
 
 
 


9
unstack output
=SET.VALUE(D2,LEFT(E2,FIND("|",E2)-1))
 
 
 


10
 
=SET.VALUE(E2,RIGHT(E2,LEN(E2)-FIND("|",E2)))
 
 
 


11
unstack input
=SET.VALUE(D1,LEFT(E1,FIND("|",E1)-1))
 
 
 


12
 
=SET.VALUE(E1,RIGHT(E1,LEN(E1)-FIND("|",E1)))
 
 
 


13
update output
=SET.VALUE(D2,D2&D3&" million")
 
 
 


14
update input
=SET.VALUE(D1,MOD(D1,1000000))
 
 
 


15
check if finished
=IF(D1=0,RETURN(D2))
 
 
 


16
 
=SET.VALUE(D2,D2&IF(D1<100," and "," "))
 
 
 


17
 
=END.IF()
 
 
 


18
 
=IF(D1>=1000)
 
 
 


19
stack input
=SET.VALUE(E1,D1&"|"&E1)
 
 
 


20
stack output
=SET.VALUE(E2,D2&"|"&E2)
 
 
 


21
get thousands
=SET.VALUE(D3,saynumber(D1/1000))
 
 
 


22
unstack output
=SET.VALUE(D2,LEFT(E2,FIND("|",E2)-1))
 
 
 


23
 
=SET.VALUE(E2,RIGHT(E2,LEN(E2)-FIND("|",E2)))
 
 
 


24
unstack input
=SET.VALUE(D1,LEFT(E1,FIND("|",E1)-1))
 
 
 


25
 
=SET.VALUE(E1,RIGHT(E1,LEN(E1)-FIND("|",E1)))
 
 
 


26
update output
=SET.VALUE(D2,D2&D3&" thousand")
 
 
 


27
update input
=SET.VALUE(D1,MOD(D1,1000))
 
 
 


28
check if finished
=IF(D1=0,RETURN(D2))
 
 
 


29
 
=SET.VALUE(D2,D2&IF(D1<100," and "," "))
 
 
 


30
 
=END.IF()
 
 
 


31
 
=IF(D1>=100)
 
 
 


32
stack input
=SET.VALUE(E1,D1&"|"&E1)
 
 
 


33
stack output
=SET.VALUE(E2,D2&"|"&E2)
 
 
 


34
get hundreds
=SET.VALUE(D3,saynumber(D1/100))
 
 
 


35
unstack output
=SET.VALUE(D2,LEFT(E2,FIND("|",E2)-1))
 
 
 


36
 
=SET.VALUE(E2,RIGHT(E2,LEN(E2)-FIND("|",E2)))
 
 
 


37
unstack input
=SET.VALUE(D1,LEFT(E1,FIND("|",E1)-1))
 
 
 


38
 
=SET.VALUE(E1,RIGHT(E1,LEN(E1)-FIND("|",E1)))
 
 
 


39
update output
=SET.VALUE(D2,D2&D3&" hundred")
 
 
 


40
update input
=SET.VALUE(D1,MOD(D1,100))
 
 
 


41
check if finished
=IF(D1=0,RETURN(D2))
 
 
 


42
 
=SET.VALUE(D2,D2&" and ")
 
 
 


43
 
=END.IF()
 
 
 


44
 
=IF(D1>=20)
 
 
 


45
 
=SET.VALUE(D2,D2&CHOOSE(INT(D1/10)-1, "twenty", "thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety"))
 
 
 


46
 
=SET.VALUE(D1,MOD(D1,10))
 
 
 


47
 
=IF(D1=0,RETURN(D2))
 
 
 


48
 
=SET.VALUE(D2,D2&" ")
 
 
 


49
 
=END.IF()
 
 
 


50
 
=SET.VALUE(D2,D2&CHOOSE(D1, "one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "ten", "eleven", "twelve", "thirteen", "fourteen", "fifteen", "sixteen", "seventeen", "eighteen", "nineteen"))
 
 
 


51
 
=RETURN(D2)
 
 
 


If we create a name ('SayNumber') for this function (Macro1!$B$1), we can enter 1234567890 in cell A1 on our worksheet, and =saynumber(A1) in cell A2 on our worksheet, and cell A2 will display:
one thousand two hundred and thirty four million five hundred and sixty seven thousand eight hundred and ninety
User-defined commands
Here is a command macro example.
This macro moves to the beginning of row 5 on the current sheet, inserts a new row, puts the current date into column A, formats it, and moves to column B ready for entry.


 
A
B


1
NewEntry:
=SELECT(!$A$5)


2
 
=INSERT(3)


3
 
=FORMULA(TODAY())


4
 
=FORMAT.NUMBER("dd/mm/yy")


5
 
=SELECT(!$B$5)


6
 
=RETURN()


We create a name for this macro, e.g. "NewEntry", pointing to the reference "Macro1!$B$1", and assign control key "t" to it.
Then on our main sheet, we can press Ctrl+t and the macro will be run. Note that the macro name in cell A1 is for our reference only, it is not used by the program.
Note that the following control keys are reserved for program operation, and should not be assigned to your command macros:

1 Format Cells
c Edit Copy
d Edit Fill Down
f Edit Find
g Edit Goto
n File New
o File Open
r Edit Fill Right
s File Save
v Edit Paste
x Edit Cut
z Edit Undo
; insert date
. insert time

Other control keys may be reserved in future versions of the program.