The Woes of Excel's Optional Parameters and C# ... a (potential) Workaround

re: The Woes of Excel's Optional Parameters and C# ... a (potential) Workaround

XL-Dennis — Thu, 07 Feb 2008 18:04:50 GMT

Nice solution which I noew have implemented in my own C# managed add-in template.

Thanks and keep up the good level,

Dennis

re: The Woes of Excel's Optional Parameters and C# ... a (potential) Workaround

Johan Nordberg — Fri, 08 Feb 2008 12:05:01 GMT

It not just the Type.Missing. It's all the ref parameters as well. You can't just have true/false as a parameter, you have to have that as a variable and use "ref myVar". Yuck!

It's too sad that a good fix for this isn't in the PIA or in some other way included in VSTO. The C# templates could have overloaded methods. I'm sure you could generate this from the typelibraries and included it in VSTO.

Atleast for the common things like Workbook.Open, Save, Application.Intersect, Union, many range methods etc.

If it's to bloated with 32 overloads on a single method, maybe you could have 2 overload, one with the most common scenario, like Workbook.Open(filename) and one with Workbook.Open(filename, paramarray[] params)

I love C#, but for Office development I use VB.Net because it's impossible to write clean code with this ref Type.Missing madness.

If the Office platform should be a professional developer platform I think you must target C# developers in a better way than today. As I see it, Office developments needs VB.Net today. But I like C# alot better than VB.

re: The Woes of Excel's Optional Parameters and C# ... a (potential) Workaround

Johan Nordberg — Fri, 08 Feb 2008 12:06:04 GMT

Btw... The really bad thing with reflection is that you don't get IntelliSense and compile time error checking. And isn't relection alot slower since it's evaluated at runtime?

Using C# 3.0 Extension Methods to make life easier when using optional parameters in Office PIAs

Noticias externas — Fri, 29 Feb 2008 20:11:26 GMT

Gabhan Berry (read his blog - he has lots of Excel programmability related information in there!) wrote

Analysing Column Data using C# (... with a little Custom Drawing thrown in for fun)

Gabhan Berry: Excel Programming — Tue, 11 Mar 2008 21:24:34 GMT

When presented with a table of data in Excel, sometimes it is useful to be able to learn some quick facts

re: The Woes of Excel's Optional Parameters and C# ... a (potential) Workaround

Gabhan Berry — Thu, 10 Apr 2008 18:59:20 GMT

Johan, yes ... reflection is slow so we should avoid (if we can) using reflection too often. Gauging what is too often and what is not is something I think we each need to decide ourselves, based on our own application's requirements.

How to code a .NET RefEdit Control

Excel Programming — Wed, 25 Jun 2008 01:57:13 GMT

One of the controls frequently used by Excel is the reference edit box. This control is used to gather

re: The Woes of Excel's Optional Parameters and C# ... a (potential) Workaround

Mark Southern — Mon, 30 Jun 2008 19:53:58 GMT

Thank you! This will come in very handy!

There are also situations where you might not want to have a reference to Microsoft.Office.Interop.Excel in your code. In these circumstances, the following code could be a useful addition (in which case, your "Address" example comes down to:

string addr = ExcelUtility.RangeInvoke<string>(excelApp.Selection, "Address", null, null, ExcelUtility.FieldValue("XlReferenceStyle","xlA1"));

)

//...

public static Type ExcelType(String name)

{

Type t = System.Reflection.Assembly.Load("Microsoft.Office.Interop.Excel").GetType("Microsoft.Office.Interop.Excel." + name, true, true);

return t;

}

public static object FieldValue(string typeName, string fieldName)

{

Type t = ExcelType(typeName);

FieldInfo fi = t.GetField(fieldName);

return fi.GetValue(t);

}

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

{

return Invoke<TRet>(Obj, ExcelType(TypeName), MethodName, CallerParams);

}