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.

ExcelDNA

ExcelDNA (via hubFS) allows custom Excel functions to be written in .NET with zero deployment effort. In F#, it's as simple as defining a library file of annotated functions, wrapping it in some XML .dna file (where $SCRIPT is replaced by the content of the actual F# script), and copying the ExcelDNA.xll to give it the same name as your .dna file. Then, double-clicking on the .xll file opens up Excel augmented with the custom functions. It's excellent.

Visual Studio Tools for Office

Visual Studio Tools for Office (VSTO) is the official buzzword for programming Office, including Excel, in .NET. Now, I know what to Google for! Good starting points for programmers (not accountants!) include the MSDN library page for Visual Studio Tools for Office and the book Visual Studio Tools for Office: Using C# with Excel, Word, Outlook, and InfoPath (Microsoft .NET Development Series). This book, co-authored by no less than Eric Lippert, is geared towards professional software developers unlike the plethora of VBA books. Unfortunately, I cannot get the Excel AddIn sample to work as described in the book with Visual Studio 2008 and Excel 2007.

Setup for .NET and F# programming of Excel

• Setup MS Office 2007, Visual Studio 2008 and F# as necessary.
• Download and install the Office Interop Assemblies.
  
• Try out this test F# script, which opens Excel, creates a new workbook and fills in the first sheet with the table of squares from 1 to 12.

What are the options for programming Excel?

I asked the question over at StackOverflow.