Excel Macros / VBA

For vivi, Excel macros are probably the most appropriate way to automate Excel. Macros are a form of programming by demonstration. Excel macros generate VBA code, which can be inspected and modified. So, one can learn about how to do something in VBA by recording a macros and then looking at the code it generated, though it might not be the best way to do something.

I think I am going to use the book, Excel 2007 Power Programming with VBA to learn the basics.

Here is how I got started.

I setup Excel to include the Developer tab and enable all macros:

• Start, Excel Options, Popular, Show Developer tab in the Ribbon.
• Developer, Macro Security, Enable all macros.

On the Developer tab, Record Macro allow you to record a macro. It's rather intuitive, except that by default all references are absolute: if you edit B2, when you run the macro again, it will always edit B2 even if you run starting with a different cell. That's not very useful. Click Use Relative References below Record Macro. Now, if the current cell before you record your macro is B2, and you move to B3 and edit, then if you run your macro from C4, it will edit C5. You can view the Macros in the Visual Basic editor under the Modules folder.

Here is an example macro that generates the Fibonacci sequence starting from the current cell and going down (just the first 25 terms). I type '0' in the first cell, '1' in the cell below, then type and do ' =(click on the first cell)+(click on the second cell)' for the third cell. I select the remaining cells starting with the third, and I get the Fibonacci sequence. OK. Now I stop recording. Here is what the generated code looks like:

Sub FiboMacro()

    ActiveCell.FormulaR1C1 = "0"

    ActiveCell.Offset(1, 0).Range("A1").Select

    ActiveCell.FormulaR1C1 = "1"

    ActiveCell.Offset(1, 0).Range("A1").Select

    ActiveCell.FormulaR1C1 = "=R[-2]C+R[-1]C"

    ActiveCell.Select

    Selection.AutoFill Destination:=ActiveCell.Range("A1:A23"), Type:= _

        xlFillDefault

    ActiveCell.Range("A1:A23").Select

    ActiveCell.Offset(-2, 0).Range("A1").Select

End Sub

All these references to 'Ax' are actually relative to where I started, because they are ranges on the 'ActiveCell'. So my guess is that ActiveCell.Range("A2") means the cell below the active cell, etc., independently of what the active cell is.

So to try this macro, you can just copy/paste the above code in a new module in the Visual Basic editor.

OK, that's enough to get started.