E x c e l   M a c r o s  

A macro is a packaged sequence of changes of an Excel sheet. If these changes have to be done again and again, it makes sense to automize the process. Excel has to be told what the sequence of changes is once, then a shortcut is assigned to this sequence. Whenever you press the shortcut, the sequence of changes will be done automatically.

Defining macros is already very close to (visual) programming.

Example 1: Adding numbers macro

Download this Excel sheet, save it and open it.Unfortunately, in order to be able to run the macros (in this and all other examples) you have to change the security settings by choosing Tools|Macro|Security and selecting a low security level. You need to save it before you can start. Macros in Excel files from untrusted sources may contain so-called Macro viruses.

You see a red and a blue cell, both containing numbers. Reset the blue one to 0, and change the red one arbitrarily. Whenever you use the shortcut "Ctrl-a", the value of the red cell is added to the value of the blue cell.

Now try to make the same on your own. Start a new sheet, and color cell A3 red and cell B3 blue. Click "Tools|Macro|Record New Macro". Excel proposes "macro 1" as a name, which is fine. Add an "a" to the shortcut definition. Click OK and the macro recording starts. Now what you want to do write into B3 is the formula "=A3+B3"; you want to add the value of A3 to the value of B3 and write the result into B3. Unfortunately a cell can not refer to itself, so you have to create a copy of B3 somewhere (say at C3) first, and then add A3 and C3. Actually this copy cannot be a formula with a reference to B3 (otherwise you would again have a cyclic reference), but must be a static copy. So what you do is the following:

1. Make sure that relative referencing button on the Macro toolbar is deactivated.
2. Select B3 and copy it: "Ctrl-c".
3. Select C3 and paste "Ctrl-v".
4. Write the formula "=A3+C3" into cell B3.
5. Select cell B3 and copy: "Ctrl-c".
6. Finally, select cell B3 again and choose "Edit|Paste Special" and select "values" in the dialog box.

The last two steps destry the formula in B3. After the execution of the macro, B3 doesn't contain a formula, but just a number again.

Example 2: Changing address formats

Download this Excel sheet, save it and open it. Assume some office provides you with addresses in a 1-row format (like those on the sheet), but you always need them in a 3-row format. Select the first name of any person and press "Ctrl-a", and see what happens. To go from the 3-row to the first row format, you select the first name and press "Ctrl-b".

The "Ctrl-d" macro shuffles two adjacent addresses in the 1-row format.

Click "Tools|Macro" and delete all three macros, and try to do them on your own. First try to recreate the one going from 1-row format to 3-row format. Note that this (as all three in this example) is an instance where relative referencing is required (see above)---what happens depends on where the selected cell is.

Maybe it is best if you do the transformation once, say for the first row by hand to see what is required. You select a first name cell (say whose contents is "Adam"). Then you move one row down and insert three times a row, to create the necessary space. You cut C1 and paste it to A2. You cut D1 and paste it into A3. That's it. Undo everything until it looks as before.

Now try to record the macro. First select a first name cell, again A1. After filling out name and shortcut ("Ctrl-a") for the new macro, start recording.

1. The relative referencing button on the Macro toolbar should be activated.
2. Move one row down (use mouse or arrow keys).
3. Click "Insert|Row".
4. Click "Insert|Row" again.
5. Click "Insert|Row" again.
6. Select cell C1 and cut, press "Ctrl-x"
7. Select cell A2 and paste, press "Ctrl-v"
8. Select cell D1 and cut, press "Ctrl-x"
9. Select cell A3 and cut, press "Ctrl-v"
10. Finally, select the next first name cell, A5, and stop recording.

Test your macro. If it works, try to record the other two (going from 3-row format to 1-row format, and shuffling adjacent 1-row entries) as well.

Examples 3 and 4: Collatz's 3x+1 problem

The 3x+1 function is a function defined for natural numbers. If x is an even natural number, then f(x)=x/2, otherwise f(x)=3x+1. In this and this Excel sheet, you can iteratively apply the function f to any start numbers. That is, you look at the sequence x, f(x), f(f(x)), f(f(f(x))), ... . Examples are 34, 17, 52, 26, 13, 40, 20, 10, 5, 16, 8, 4, 2, 1, 4, 2, 1, 4, 2, 1, ..... It is a very famous conjecture, stated by the late mathematician Lothar Collatz, that eventually you will always face ... 4, 2, 1, 4, 2, 1, .... no matter where you started. You can use the worksheets to verify this claim for small start numbers, but nobody could prove the conjecture yet---you will get famous if you can do it,

Try to create something like that by yourself, if time allows.

Example 5: The adding numbers macro revisited

Example 6: Another adding-numbers-macro

Download a slightly more convenient adding machine. Here you don't need shortcuts or buttons, but the value of the red cell is added to the value of the blue cell whenever you select cell A4. This usually happens if you wrote a number in the red cell and pressed the return key.

This macro is event driven, and is not recorded but rather written. You rightclick on "Sheet 1" at the bottom of the page, and select "View Code". Then you type the following code in

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> "$A$4" Then Exit Sub
Target.Offset(-1, 1).Value = Target.Offset(-1, 1).Value + Target.Offset(-1, 0).Value
End Sub

This is already Visual Basic programming. It looks complicated but it is not.

Erich Prisner, September 2004