More about the book Excel 2007 VBA Programmer's Reference

I read the 4 first chapters completely and skimmed the rest. The first Chapter, Primer in Excel VBA, is a great introduction that puts one on track to getting things done. The fourth Chapter, Using Ranges, is the second most useful one as Range objects are ubiquitous.

VBA Interactive

In the Visual Basic Editor, type Ctrl-G or click View -> Immediate Window. You'll get a handy little window where you can type VBA commands line-by-line and see exactly their effect on Excel. Nifty.

Getting Help in Excel

To get detailed help on VBA, in the Visual Basic Editor (accessible from the Developer Ribbon):

• Press 'F2' to open the Object Browser. The Object Browser shows you a list of all the classes, and allows you to search by the name of an entity (be it a function, method, property or class). When an enitity is highlighted, press ? for a detailed reference on how to use it. Use the Object Browser to avoid too much guesswork. In particular, if you want to know what you can do with an object, just browse its class (try it, for instance, with the ubiquitous 'Range' class) and get help on interesting sounding methods and properties.
• Press 'F1' when selecting an entity in the editor to get its detailed reference.

Excel VBA continued

vivi wanted to have a macro to copy-paste a column given its heading.

I am also checking out the book Excel 2007 VBA Programmer's Reference. I am liking it better than Walkenbach's book, as the primer (Chapter 1) already succinctly put me on the right track to figure out many useful tricks. I think I finally found a book that will allow me to quickly pick up Excel VBA without getting too bored :)

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.