Saturday, January 3, 2009

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.

Thursday, January 1, 2009

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 :)