{ End Bracket }

C# and VBA: Like Oil and Water

Ken Getz

Some things just don't mix as well as you would like. Take C# and Microsoft® Excel 2003 or Word 2003, for example. Not only are these applications huge productivity tools, but they both also provide access to large object models that you can program against from your own applications. The problem is none of the documentation for the object models includes code samples for developers writing managed code, and writing Visual Basic® for Applications (VBA) code is quite unlike writing C# code.

I spent much of summer 2004 creating managed code samples exercising many of the methods of both Word and Excel, providing samples in both C# and Visual Basic .NET. The task seemed simple—400 code snippets, all converted from Visual Basic .NET to C#—but was much harder than you might imagine.

It's not that it's difficult to convert code from Visual Basic .NET to C#, or to simply create code that automates Word or Excel in C#. What caused me pain in this particular project was that when you attempt to communicate with objects designed to be consumed by VBA clients, you're living in a world that didn't take C# and its design goals into account when it was being created. One thing is for sure: because of its VBA lineage, it's a lot easier accomplishing Office automation from Visual Basic .NET than it is from C#. I can't dig into the details here, so I'll just point out a few delectable tidbits, with links to more info later on.

From my perspective, the least pleasant issue is that of passing parameters to Word members. Before taking Word and its design to task, remember that it started its career including Word Basic rather than VBA, back in the Pleistocene era of computing. In those days, it was typical for scripting languages to accept and expect all parameters to be passed by reference, such that incoming parameters were simply full-featured aliases of the original variables, and this habit remains unbroken in Word to this day (a lot of design decisions are also a relic of designing for COM).

For the most part, all parameters must be passed by reference when calling a method in Word, so code that looks like the following Visual Basic .NET snippet needs to be converted for use in C#:

' Visual Basic .NET ' wdApp refers to the Word Application object. wdApp.Windows.Arrange(Word.WdArrangeStyle.wdTiled) // C# object value = Word.WdArrangeStyle.wdTiled; wdApp.Windows.Arrange(ref value);

It's not a terrible burden, but it does force you to create a variable each and every time you want to pass a parameter.

Optional parameters provide separate challenges when working with both Excel and Word. When programming Excel, you simply have to accept the fact that although VBA welcomes optional parameters, C# doesn't handle them in the same way (for an explanation of why this is the case, you can take a look at Anders Hejlsberg's TechEd 2004 discussion of the subject at Whiteboard with Anders Hejlsberg). Many Excel methods take advantage of optional parameters to ease the burden for VBA developers, and you must supply a value for each of the missing parameters. Luckily, the Type.Missing value indicates to the Primary Interop Assembly (PIA) that your code intends to accept the default value for the optional parameter.

Passing optional parameters to Word adds an extra challenge because they're passed by reference. Luckily, the PIA takes care of marshaling the values, so you needn't worry about "by reference" side effects passing a single variable to handle all the optional parameters. The following snippets demonstrate a technique for handling optional parameters when programming against the Word object model:

' Visual Basic .NET ' wdApp refers to the Word Application object. ThisApplication.Documents.Open("C:\Test\MyNewDocument") // C# object filename = @"C:\Test\MyNewDocument"; object missing = Type.Missing; wdApp.Documents.Open(ref filename, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing);

Of course, there are more issues and many subtleties. For more information, check out a longer article I wrote previously on the topic (Programming Office Applications Using Microsoft Visual C#), and for the real scoop, you should take a look at Eric Carter's blog (Type.Missing, C#, and Word). He's the expert on this particular subject.

Ken Getz is a senior consultant with MCW Technologies. He is coauthor of ASP .NET Developers Jumpstart (Addison-Wesley, 2002), Access Developer's Handbook (Sybex, 2001), and VBA Developer's Handbook, 2nd Edition (Sybex, 2001). Reach him at keng@mcwtech.com.