I am having a lot of troubles with method overloading here and have no idea why only one method gets called each time regardless the number of parameters I pass into. Below is the sample code.
[ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
public class myClass
{
//constructor
public myClass() {}
//base method
public string myfunction(string id, string pk) {return results;}
//overloading method 1
public string myfunction(string id, string pk, string param1) { return results;}
//overloading method 2
public string myfunction(string id, string pk, string param1, string param2) {return results;}
[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type)
{
Registry.ClassesRoot.CreateSubKey(
GetSubKeyName(type, "Programmable"));
RegistryKey key = Registry.ClassesRoot.OpenSubKey(
GetSubKeyName(type, "InprocServer32"), true);
key.SetValue("",
System.Environment.SystemDirectory + @"\mscoree.dll",
RegistryValueKind.String);
}
[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type type)
{
Registry.ClassesRoot.DeleteSubKey(
GetSubKeyName(type, "Programmable"), false);
}
private static string GetSubKeyName(Type type,
string subKeyName)
{
System.Text.StringBuilder s =
new System.Text.StringBuilder();
s.Append(@"CLSID\{");
s.Append(type.GUID.ToString().ToUpper());
s.Append(@"}\");
s.Append(subKeyName);
return s.ToString();
}
}
So when I tested it in Excel, the base method worked fine and I could get the expected value. However, it would fail if I tried the rest two functions using method overloading. They simply didn't get called for some reason. Am I missing something in the code? Can anyone help me please? Thanks very much.
Edit:
Through some experiments, it occurs to me that there only 1 method can be recognized, which normally is the first method. So if I swap the order between base method and overloading method 1, the overloading method 1 will be called instead of the base method. Looks like the whole class just gets stuck in the first method and wouldn't go on.
Excelfriend, you can't polymorphize a a worksheet function. A worksheet function called, say,
GETSALESFORWEEK
can only have one entry point. If you provide two, sayGETSALESFORWEEK(int WeekNumber)
andGETSALESFORWEEK(string WeekName)
then Excel 2007 will expose these functions asGETSALESFORWEEK
andGETSALESFORWEEK_2
.
Well I guess that answers my questions. Sadly, no method overloading for custom Excel functions :(
Solution:
[ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
public class myClass
{
//constructor
public myClass() {}
//base method
/**public string myfunction(string id, string pk) {return results;}**/
//overloading method 1
/**public string myfunction(string id, string pk, string param1) { return results;}**/
//just use 1 method, but make the last two arguments optional by setting default values
public string myfunction(string id, string pk, string param1 = "", string param2 = "") {return results;}
[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type)
{
Registry.ClassesRoot.CreateSubKey(
GetSubKeyName(type, "Programmable"));
RegistryKey key = Registry.ClassesRoot.OpenSubKey(
GetSubKeyName(type, "InprocServer32"), true);
key.SetValue("",
System.Environment.SystemDirectory + @"\mscoree.dll",
RegistryValueKind.String);
}
[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type type)
{
Registry.ClassesRoot.DeleteSubKey(
GetSubKeyName(type, "Programmable"), false);
}
private static string GetSubKeyName(Type type, string subKeyName)
{
System.Text.StringBuilder s = new System.Text.StringBuilder();
s.Append(@"CLSID\{");
s.Append(type.GUID.ToString().ToUpper());
s.Append(@"}\");
s.Append(subKeyName);
return s.ToString();
}
}