Anguilla Library Computer Club
Resources

Spreadsheet Lesson #5: Macros

A macro is a sequence of keystrokes and commands which can be performed automatically. Any sequence can be assigned a single letter name such as M and be invoked from the keyboard with ALT-M. You store macros within the spreadsheet itself in one or more cells. You assign the first cell a Range Name of BACKSLASH "\" followed by any letter. When you invoke the macro, it continues executing down through rows until it finds a blank row or you press ESC.

Activity: Record, Playback and Edit a Macro

Your First Macro: Type {RT}{RT} into cell A1. Those are curly braces and {RT} means Cursor Right (must be upper-case). Assign cell A1 a Range Name of \R by typing /RNC\R[ENTER]. When you see [ENTER] we want you to press the ENTER key. You have written a macro!
Play Your Macro: Press ALT-R to invoke your macro and see your cursor jump right 2 columns. Try it again. You can now create up to 26 shortcut keys, ALT-A through ALT-Z.
Write Another Macro: Type {LET g1,@today} into cell A3. Remember to always leave a blank row after each macro. To invoke this macro press F3 and type A3[ENTER]. Do you see a funny number like 35393.36 in cell G1? This is current date and time! Don't worry--we will make it look better.
Record A Macro: To record your keystrokes as you type them, enable Macro Compose mode by typing /WMC. Your keystrokes will now be recorded. Press F5 for Go To and type G1[ENTER]/RFD4[ENTER] and then stop the recording by typing /WMC again. When you are prompted for a blank row to deposit the macro in type A5[ENTER].
Assign Macro Name: Look at your macro in cell A5. It should read: {GOTO}G1~/RFD4~ where TILDE "~" means [ENTER] and {GOTO} is the code for F5. To assign this macro the name ALT-D, type /RNC\D[ENTER].
Playback Your Macro: Execute your macro by typing ALT-D. Do you see today's date in cell G1?
Enhance the Macro: Edit the A5 macro to put today's date in G1 after setting the Cell Format to D4. Go to A5 where the macro is stored and press F2 for Edit. Type {LET g1,@today}[ENTER]. Now go to G1 and type 0[ENTER] to obscure the date. Does ALT-D show today's date again?

Advanced:

The @today function can also print the time if the cell has format D7 (HH:MM). Expand the ALT-D macro in A5 to store @today in cell H1 and format cell H1 as D7. Does ALT-D now update the time?
Why not put your macros where they won't clutter up the main screen? Move the macro in A5 to A28. Can you still invoke it with ALT-D?
If you want this macro to execute whenever the spreadsheet opens, name it \0.