VBA or .NET for working with Office?

Question

Hi All

I want to develop and automate Office software like 'Microsoft Excel 2007' And 'Microsoft Word 2007'. But I do not know how to use VBA or use C#/.Net Framework to do so.

What are the benefits of using VBA vs .Net framework?

Which one should I focus on learning?

Answer 1

It largely depends on how complex the problem is. If it is a simple thing, a quick VBA script might be enough. But if it is anything more than a few dozen lines of code I would opt for C# / .NET. Those technologies are clearly the strategic direction for Microsoft, and I find that you can be much more productive in that technology stack versus trying to do complicated things using VBA.

Answer 2

Both, VBA and .NET have their advantages and disadvantages when it comes to Office automation. Below are just some points right from the top of my head. The list is not complete and my personal opinion, so feel free to add/remove points as you like.

Pros of .NET are

• richer language and base class library (inheritance, object-orientation)
• supports class libraries
• generally better tool support, e.g. code analysis, testing, etc
• easier to integrate with version control

Cons of .NET

• requires more learning
• more difficult to deploy

Pros for VBA are

• rather slim
• code being executed natively in Office with no need for a slower COM bridge, therefore often faster
• macro recording can be used to automatically generate high-quality code

Cons of VBA

• is more of a legacy technology
• no support for a modern and rich GUI
• limited tool support

Both have in common

• Possible to use Windows API
• Possible to use COM

When it comes to learning and getting familiar with the Office object models, VBA is surely easier to learn, especially because it is so easy to record a macro and then simply play around in the VBA editor.

The bottom line is that VBA probably is best suited for smaller projects that should be easy to deploy whereas with .NET you are able to build powerful add-ons making use of the complete .NET Framework and providing a rich user experience.

Answer 3

I would say

if you won't use any other technologies than Office suite - go for VBA.

if you're planning to deal with things like creating xml files, using database connections, multiple threads etc. - .NET is better.

Additionally its good to look in the future. Are you planning to make career as a developer (go for .NET), or you're just productive type of person, who would like to make most of Office (go for VBA).

Answer 4

I suggest using VBA if you have little to no programming experience and here's why.

• You will get quick results.
• You will have a shorter learning curve.
• You can use the macro recorder to learn the language.
• There's a large support base for the language.

Just as Steve Roman pointed out in one of his Word Macro books, VBA is an excellent language to begin learning programming in general. No, you won't learn OOP, but you will learn procedural programming, variables, types, objects and much more in a simple, non-intimidating environment.

Answer 5

One way to look at it is to view it as the difference between going camping with a camper van and going camping with a fifth-wheel trailer and an F-350 to haul it with.

If Dave and Betty have a camper van (Excel), it comes with everything they need for an occasional trip to the beach for the weekend, and a "good-enough" 4-cylinder engine (VBA) to get them there and back.

But maybe Bubba wants to be viewed as a serious Camper. He'll want lots more flexibility to camp anywhere he wants to go, any time of the year, and take anything and anyone and anything he wants along, including the boat and the dog. So he'll buy the F-350 (Visual Studio), and a hitch that will pull campers, boats, skidoos, and horses, and the bed hardware for the fifth wheel - just in case. And he loves to spend time in the garage setting everything up to work together nicely. C#/.Net is great for all that. Every size winch and refrigeration unit you'll ever need or want will fit right in.

And they can tow the camper van along for local wheels.