ExcelUtility

Project Description
A C# class for working around the problems of having optional parameters in the Excel API.

This class offers a way to call Excel APIs with optional parameters from C# without having to specify a value for all optional parameters.

Overview
When coding in C# with Excel, it doesn't take long before you encounter the dreaded 'optional parameter' issue. If you have ever copied-and-pasted numerous System.Reflection.Missing.Value statements then you've probably wondered a few things.

Firstly, what is this and why is it happening? Secondly, how do I get round it?

More information about ExcelUtility can be found at my blog: http://blogs.msdn.com/gabhan_berry/archive/2008/02/06/excel-s-optional-parameters-and-missing-value-a-c-workaround.aspx

What's the Problem with Optional Parameters?
As you know, some of Excel's APIs (such as Application.Intersect or Range.Sort) use optional parameters. When you are coding in VBA you hardly notice this but it's a whole different story in C#.

As I briefly covered in my introduction to managed programming and Excel ( http://blogs.msdn.com/gabhan_berry/archive/2008/01/30/excel-and-managed-code-how-does-that-work.aspx), when you use a managed language to code against Excel, Excel's API is exposed as a collection of Runtime Callable Wrappers (RCWs). These are managed classes that have the same methods and properties as the COM classes they wrap. When you call Application.Intersect from C# you are really calling Application.Intersect on the RCW. The RCW then internally marshals the call to the real, COM Application.Intersect.

In other words, the RCWs expose managed wrappers of the COM methods.

However, the methods and properties that have optional parameters in COM are not optional in C#. As a C# programmer, you have to supply a value for every single parameter, regardless of whether or not it is optional in COM.

At first glance, this seems like not such a big deal. That is, however, until you realise that some methods (like Application.Intersect) take 30 parameters; 28 of which are optional!

The ExcelUtility Class
This class is very small and is mostly defined in about 30 lines of code. However, in these few lines I make use of reflection and generics and my explanation assumes you have some basic knowledge of these two topics.

The important method in the ExcelUtility class is the Invoke method. This method contains the code which discovers function signatures at runtime and handles the invocation of them.

public static TRet Invoke<TRet>(object Obj, Type ObjType, string MethodName, params object[] CallerParams) where TRet : class

The MethodName parameter is, well, the name of the method we want to call on the RCW. For example, if we want to call Application.Intersect then we set MethodName to the string "Intersect".

ObjType is the Type object of the class which we are invoking. The framework contains a class called Type which represents the metadata of data types. Every class has a Type which can be accessed at runtime. For the Excel Application class (well, the RCW) we get its Type object using typeof(Microsoft.Office.Interop.Excel.ApplicationClass).

Obj is the actual object which we want to invoke i.e. the Application instance.

Finally, the CallerParams array contains the parameters that the caller wants to pass into the function call. So, in the case of calling Application.Intersect, we would supply the range objects we want to intersect.

Notice that Invoke is a generic method. The generic parameter, TRet, specifies the type of the object to return. Invoke will attempt to cast the result of the dynamic function call to this type. So, if the method we are invoking returns a string then we specify string as the generic parameter i.e. string val = Invoke<string>( ... )

Using ExcelUtility
To invoke the Intersect method of the Range class, we write:

Excel.Range rng = ExcelUtility.ApplicationInvoke<Excel.Range>(_application, "Intersect", rng1, rng2);