MS Word extensibility: VBA macro versus .Net VSTO?

Question

One of my customers asked us to develop a "VBA macro". However, in the 2010s, it seems weird to me to still use such outdated language, and I'm thinking about trying to convince the customer to use VSTO dev instead. However, as I'm new to both worlds, I need help to fill a pro/cons page to be able to argue this.

Of course, the answer can't come without the actual requirement, let me try to resume:

Target : Word 2003/2007 (but I'm suspecting 2010 as a not yet known requirement) edit 2010 requirement confirmed

An external publishing system requires .doc file as input. The .doc file must have some specific styles applied : "Custom Header 1", "Custom header 2", etc.

The user can build documents, using Word, using two possible ways:

1. Start the new document using a .dot file deployed on the computer
2. Transform any existing document to match the target template

Users can "apply" the styles "simply" (simple UI): context menu, styles menu, custom action pane, etc.

By now, I see the following pro/cons:

1. VBA

   • Pros:
     • ?
     • quick and dirty development (quick part of the sentence)
     • The customer has already some in production macro
   • Cons:
     • hard to find skilled developer
     • quick and dirty development (dirty part of the sentence)

2. VSTO

   • Pros:
     • benefits of the .Net language (compiled, typed, rigorous, class library, etc.)
     • security model more flexible and powerful (trusting code signed with a trusted authority)
     • bridge to WPF panes possible
     • You work in Visual Studio and have access to its full set of features: refactoring, source control, etc.
   • Cons:
     • requires installation of the .Net framework (probably not an issue today) and VSTO runtime
     • harder to deploy
     • slightly more work at the start (but less in long term)

Answer 1

I am not familiar enough to .NET but here is my humble opinion about VBA:

VBA

• Pros :
  • easy to deploy and to make it work with the Office apps
  • quick and dirty development (quick part of the sentence) - agreed
• Cons :
  • hard to find skilled developer
  • hard to select a skilled developer and explain your customer he needs to invest in this skill
  • quick and dirty development (dirty part of the sentence) - partial agreement. It will be dirty if:
    • you give the project to a VBA beginner and don't frame him/her
    • your project gets too big in terms of requirements
  • requires to have the .Net framework installed (probably not an issue today) I don't think so (maybe a CONS of VSTO?)

I would say that if you only want to have some code or add-in to merge some syles, you could easily do it with VBA and it won't be dirty (unless you really want it to).

Answer 2

I'm answering myself to the question, as the project is finished.

I finally decided to write the application using a VBA macro. Here is the post-mortem conclusion of this choice :

Pros:

1. The team that will maintains the application has no C# knowledge, only VBA (main reason of my choice).
2. Poor security model : it's a pro because there is no other setup that putting the files in the right place.
3. No runtime prerequisites

Cons:

1. The deployment was supposed to be simple.
   • I was counting of the possibility to play with Word options "User-template directory" and "Startup template directory". But it wasn't possible, because the app is not related to a specific entity in the customer organisation. I wasn't allowed to "take ownership" of this settings for this application.
   • I ended in writing a custom NSIS script to deploy the application on the correct folders. With VSTO, a simple setup project (clickonce?) would have done the job.
2. The language is so prehistoric ! Collections index starts to 1, array to 0, no OOP, poor out-of-the box language and library feature. That is not always a problem, but in my case, modeling the business rule was a bit painful (representing hierarchical data in a tree was not an easy job)
3. Very limited integration with source control (code, as it's embeded in the .dot files, was not historisable. Only the full .dot was)
4. Error management very limited with VBA
5. Poor IDE

Remark:

As an experienced C# developer, the pros/cons may be partial a bit.

Answer 3

I am a heavy Excel VBA developer.

VBA pro: One of the major hurdles for me switching over to VSTO from VBA - and believe me, I love C# coding - is that there's no debug-on-the-fly which my userbase has got used to. I often jump straight into a VBA problem on the user's PC as it is happening, but with VSTO, that's not possible. (Unless someone can correct me.)

If your users have no such expectations this might be something you can easily live without.

VBA con: VBA is one of the those languages that are easy to play with and thus easy to make a mess with. It doesn't enforce "clean coding" principles which means while decent programmers can make great applications with them, VBA can become associated with hackjobs and sprawling, organic code due to its low bar of entry. VBA developers are often considered a lower class of developer for this reason, when really there is a failure to distinguish between those that use it wisely and those that don't.

I doubt anyone chooses VBA as their career language of choice, it just sort of happens to them. Aside from being hard to find skilled developers, too much VBA work might turn away potential hires as they don't want to be associated with the "quagmire of another unmanaged VBA sprawl". Some people take use of VBA as a statement about how "serious" you are about technology.

(I tend to see Perl in the same light; great for short scripts but when someone used to using for scripting starts to use for a larger piece of work - you tend to get something that's a bit unwieldy.)