Documentation Resource for Office VBA developers [closed]

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.

Kerguelen

Answer 1

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(VS.60).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(PROT.10).aspx

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

Atwater

Answer 2

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

Strawn

I don't know about object models but I want to play around with excel and an access database and the interaction between them.. – Kerguelen

Ok, I would suggest downloading the language references (compiled HTML help files) and looking through the examples, trying them out. You'll also want to look at MDAC (Microsoft Data Access Components), particularly ADO - msdn.microsoft.com/en-us/library/ms807642.aspx – Strawn

Well I was going thru MSDN but coming from a Java background the documentation comes like culture shock. Its like I've been living on another planet so far, I just can't seem to digest the MSDN documentation, I wonder if its just me or there are others that share the same feeling... – Kerguelen

Culture Shock in a good or bad way? I'd agree that there is a certain style to a lot of the MSDN documentation, it does get easier to read and comprehend the first time around with time... – Strawn

In a bad way...and I am looking for some alternatives for documentation I wonder how programmers work with MSDN or do they refer to books also... – Kerguelen

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.

Cadaver

Yes indeed! I was using the same strategy because of the documentation issues... – Kerguelen

Answer 4

Yes, VBA and MSOffice-specific documentation has gone from barely-with-value in older versions of MSDN Library, to now completely unusable shite.

Some idiot decided it would be A GOOD THING (TM) to decouple objects, methods, and properties, instead treating each as an independent, standalone concept. In the good ol' days, you could look up an object class (say, 'Range' for excel), click a link for Properties or Methods to see what p's or m's were supported for that class, and what they did specifically for that class.

Now, all we get is some generic description of the class, no link to it's properties and methods. Properties and Methods are listed alphabetically as their own generic entity, separate from the object classes.

So, there's no easy way, when programming, to find out how to USE an object class. What methods are available? What Properties? What do they do? We no longer know...

Gotha