I’ve fielded this question a couple of times, so I thought I’d give to formal list here. These are the books I’ve used (and continue to) to get started with VBA/Excel programming (among other things). I also list the sites I visit regularly when I feel like expanding my Excel/VBA knowledge.
BOOKS
Excel 2010 Power Programming with VBA, by John Walkenbach – After I finished poking around Google trying to find the answers to my questions, I decided it was time to get serious and get some structured learning. This was the first book I bought, and remains one of the two I refer to the most.
Professional Excel Development, by Bullen, Bovey, and Green – This is the other book I hit on a regular basis. I never fail to learn something when I go to it (usually something I’ve learned before and had simply forgotten). But take note: this puppy is NOT for beginners. It’s barely for the intermediate programmers. There is a gold mine of information in there, but you really need to know what you’re doing in VBA to digest some of the content. But if you’re ready for it, you can’t do better.
Excel 2002 VBA Programmers Reference, by Bovey and Rosenberg – This one’s an old one, and has probably been updated since I bought it. But this is the one I’ve got. It has some nice stuff on connecting Excel to Access using both ADO and DAO, including how to connect to Excel as a data source with ADO, which I’ve referenced quite a bit.
Programming Excel with VBA and .NET, by Webb and Saunders – I like this one because it’s essentially a paper version of the Excel object model, but instead of being just a straight list of objects, properties, and methods, it gives some textual description of said objects, properties, and methods, and usually gives some nice short code examples of how to use them.
VBA Developer’s Handbook, 2nd Edition, by Getz and Gilbert – This one goes DEEP. Mike is Product Manager with Microsoft’s Developer Tools division, specializing in VBA. He knows this stuff inside out and upside down. The result is some incredibly deep knowledge that can be very useful, but can also go over your head. But it contains things you won’t find in a lot of places. You want to program media files? This is the book. Harness the information about your monitor? It’s in there. Build custom data structures? Yup. It’s all in there. But this this level of programming is pretty complicated, so tread carefully.
Pivot Table Data Crunching, by Jelen and Alexander – I love this one. It’s a compact book for a computer book (only 288 pages), but it gives so much great info on pivot tables. And not just pivot tables from a user interface perspective, but also includes how to program them with VBA.
Sams Teach Yourself SQL in 10 Minutes, by Ben Forta – This one isn’t a VBA book (as you can clearly see by the name). This one is a great, slim little gem that gives you the basics of how to program SQL. Syntax is everything. And since there will come a point where you have to hook an Excel sheet up to and Access database (don’t shake your head at me, I’m telling you, it WILL happen), this gives you the fundamentals you need to keep from screwing up the syntax.
WEBSITES
Really there are only three sites I hit up on a regular basis.
Daily Dose Of Excel – Dick has been blogging about Excel for at least ten years. He’s got an easy conversational style that’s fun to read and easy to digest. I landed on this site a number of times while searching for VBA solutions I couldn’t figure out on my own. There’s one thing about Dick’s site that makes it unique: it’s the site at which everybody gathers to talk about Excel and VBA. Daily Dose of Excel is not always updated in a timely manner, with sometimes a month between entries (Dick, like most of the rest of us, has a day job), and it’s not like the content is organized into neat categories with each topic covered and cataloged. You can navigate the categories dropdown list in the blog’s sidebar, but don’t expect a page listing all of the topics in an index-like fashion. And yet, every Big Name Excel Personality flocks to it. I don’t know why, but there it is. Daily Dose Of Excel is the Cheers of Excel blogs, and Dick is Sam “Mayday” Malone.
Contextures – This is Deb Dalgleish’s site. It is loaded with content, updated nearly every day. Deb also includes a weekly round up of other Excel related items she’s found on other sites throughout the week. Her content is categorized, with each category giving you examples and tips and tricks and sometimes even quirks to watch out for. To give you an example, I had a column in a sheet two days ago with broken data validation. You could type any old thing in the cells when you should have been limited to a specific list. I figured there had to be a way to fix it. I remembered Deb had a ton of content on data validation on her site, so I went digging. Sure enough, I found what I was looking for and fixed it. But if that’s not enough to convince you, let me add that Deb might have the largest collection of multimedia, primarily videos demonstrating how to implement some of the content she covers, of any Excel site I’ve ever seen.
Pearson Software Consulting – Chip Pearson’s site is a fantastic collection of explanations on all kinds on Excel and VBA topics. But what really makes the content pop is that Chip usually adds custom routines the make your programming easier. For example, you might need to do some work with Names in Excel. Chip walks through the aspects of to code with Names, and then puts the things he’s just discussed together into prepackaged functions that you can call to make your programming life easier. I’ve used his site as a reference point for years.
So that’s it. Those are my recommendations. Have fun.
Scott Lyerly 