I recently read Pro Excel 2007 VBA, written by Jim DeMarco and published by Apress. Not another book on Excel VBA, you may say, and I must admit I was thinking along those lines myself. But this book manages to find a space in the crowded field. The author came to Excel VBA via Access and Visual Basic (Classic), and this emphasis comes across in the book. There are many examples showing how to work with external data sources, and the author applies object oriented practices to his coding.
The book assumes no special background in VBA, but it is aimed at intermediate to advanced users with programming experience. Many of the examples start with the macro recorder, then clean up and enhance the recorded code. This is a realistic approach, because despite its flaws, the recorder usually produces serviceable pieces of code that can be quickly stitched together.
In addition to the macro recorder, the first section of the book introduces other aspects of VBA. The book discusses security settings, the VBA programming environment, standard code modules, class modules, user forms, and object oriented programming.
The book then launches into detailed examples that use DAO and ADO to import data into Excel. Many of the examples also cover SQL, and the author shows how to build classes to make working with the data more robust. The discussion of data techniques ends with XML, first as a data source, and then as a tool for modifying the Excel 2007 Ribbon.
There are examples that show how to use forms for simple data entry and for more detailed wizards, where the wizard operation is controlled by a series of classes. There is a chapter on using VBA to work with charts, and another on how to program pivot tables. The book finishes up with chapters on debugging and error handling, integrating Excel with other Office applications, and using ActiveX and .Net technologies with Excel.
Comparison of this book to similar books
John Walkenbach’s Excel 2007 Power Programming with VBA is written for skill levels from beginner to advanced, and it covers a broader range of Excel’s object model.
Excel 2007 VBA Programmer’s Reference by John Green, Stephen Bullen, Rob Bovey, and Michael Alexander is written for the same intermediate to advanced users as this book, but it is more of an encyclopedic handbook.
Professional Excel Development by Stephen Bullen, Rob Bovey, and John Green is written for advanced to highly advanced users.
This book, Pro Excel 2007 VBA, has the strongest emphasis on object oriented practices of this set of books, and while its breadth is limited (it has less than half the page count of the other books), its coverage of data techniques is solid.
Disclosure: The publisher provided me with a free copy of this book, in hopes that I would read it. I read the book and liked it, and wrote this review on that basis. I have received no compensation for writing this review.