I have been doing some Visual Studio Tools for Office (VSTO) development in Word of late, not exactly a pain free experience.
First thing to say is that even given all the marketing spiel, VSTO is not that different from VBA in older versions of Office.
To get going I create a new VSTO 2008 Word 2007 Template project based on an import of our old Word template (to get styles, layout etc.). I then basically cut and pasted the logic from our old VBA macros into an ActionPane in the new one and it just worked.
Well I said it just worked, I had out of habit I had picked a C# project, I should have chosen VB.NET (as is commonly recommenced for VSTO development) as then the code would have been virtually correct. As it was I had to spend a while adding {} and ; to do the language port.
So why did this work? VSTO is new(ish) any shiny and VBA is old and VB. However, they are both in effect wrapper APIs to the underlying Office COM Interop layer, so what they can do and how they do it is dependant on Office, in my case Word 2007. Ok you can do different things in Word 2007 to Word 2.0, but it is still in essence a Word processor where you manage content with ranges.
The reason people recommend VB.NET for VSTO development is due to the COM Interop. Visual Basic has always made it far easier to use COM objects, not least due to optional parameters and variant types.
Now as of yesterday some of these problems are addressed by the release of VSTO Power Tools and it COM wrapper API, but this API will certainly not address all issues.
Another thing that has has not changed over the years is the documentation, the MSDN references are OK but there is little is else. Especially when doing integration work with Sharepoint WebServices as I was. I have found I had to make heavy use of the debugger to investigate the XMLNode object being returned to work out that was going on.
In Word development I have commonly found that initially there are things I think I can’t do because the object I need does not appear to be exposed by the object model; only to find what I am looking for in a place that is not that obvious to me. However you still have to use the object in some ‘dark arts’ style to get the effect you require. A classic example of this is managing programmatically the document cover page from the building block gallery. To get this to work there are many hoops to jump though.
VSTO is powerfully but not as flexible as you would hope as you have to live inside the design metaphor of the Office application you are hosted in. You have to consider carefully if the functionality you are trying to add is really relevant to the application it will be hosted in.