Documentation Resource for Office VBA developers

Question

Where do I find helpful documentation for VBA programming? something like SitePoint for HTML development. MSDN simply lists down and describes the operation and properties/methods of classes instead of explaining what they are used for, or when to use them.

Books are also welcome.

Answer 1

Any particular Object models that you were interested in (Excel Object Model, Access Object Model, etc) or all of them? I had a copy of Excel 2003 VBA from Wrox that was pretty decent, they have updated it for Excel 2007 (although I don't believe that there would be a difference between the 2003 and 2007 versions as extending VBA was pulled years ago and there was a push to move to VSTO).

As a general VBA reference, you'd be best to pick up a good VB6 resource, since if I recall correctly, VBA is a subset of the VB6 language. Something like Professional Visual Basic 6 Programmers Reference would be good.

EDIT:

The MSDN Visual Basic for Applications page looks like a good starting point for resources. Here are the Language References

Answer 2

I have been working with Excel/VBA on and off over the past two years and have been very disappointed in the quality of the basic documentation. The "help" file is almost worthless. The MSDN version of the same thing is better because it has better internal links, but still quite thin. Here are some resources which I have found helpful:

Writing Excel Macros with VBA, 2nd Edition by Steven Roman. Chapters 5-8 have a decent "tour" of the language (you can find snippets in Google Books):

http://www.amazon.com/Writing-Excel-Macros-VBA-2nd/dp/0596003595#noop

The VB 6.0 Programmer's Guide, which mostly also applies to VBA:

http://msdn.microsoft.com/en-us/library/aa716285%28VS.60%29.aspx

There are also various Excel "guru" consultant websites out there that have a lot of example material. Much of it is pitched Excel in general or to non-programmers, but can still be quite useful for picking up VBA in general. Two such sites in particular are from Chuck Chip Pearson and Charles Williams:

http://www.cpearson.com/excel/mainpage.aspx

http://www.decisionmodels.com/

I was about to type a disparaging comment about how I never found a VBA language spec, but I figured I'd Google it again and lo and behold, from only just about a year ago:

http://msdn.microsoft.com/en-us/library/dd361851%28PROT.10%29.aspx

This might contain the answers to some questions I have resolved only by experimentation and encoding the results as unit tests...

Answer 3

If you are finding the MS documentation, one approach is to make use of the macro recorder. It won't generate high quality code, but if you record the actions you want your program to take and review the code it generates using F1 on any object you want to look at deeper, it can be a good start. You just have to use a little common sense and clean up the macro code after.