I am working on a library of COM Add-in and Excel Automation Add-in, whose core codes are written in C#. I'd like to set an optional argument for the function and I know that this is legal for both C# and VBA, and even Excel WorksheetFunction. But I find that finally the optional argument works exclusively for COM and Automation add-in, meaning that if one add-in is run first, then works well but the optional argument of the other one will not work.
Below please see the example:
In the VS 2013 solution, I have two projects: one is called TestVBA and another one is called TestExcel.
TestVBA is for the COM add-in and built through the "Excel 2013 Add-in" and there are two .cs files:
ThisAddIn.cs
This file is generated automatically and modified a little bit. The codes are
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Tools.Excel;
namespace TestVBA
{
public partial class ThisAddIn
{
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
}
private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
{
}
private ExcelVBA oExcelVBA;
protected override object RequestComAddInAutomationService()
{
if (oExcelVBA == null)
{
oExcelVBA = new ExcelVBA();
}
return oExcelVBA;
}
#region VSTO generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InternalStartup()
{
this.Startup += new System.EventHandler(ThisAddIn_Startup);
this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
}
#endregion
}
}
TestVBA.cs
This file is the main calculation file of COM add-in. The codes are
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
namespace TestVBA
{
[ComVisible(true)]
[ClassInterface(ClassInterfaceType.AutoDual)]
public class ExcelVBA
{
public int TestAddVBA(int a = 1, int b = 1)
{
return a + b;
}
}
}
Another TestExcel is for the Excel Automation add-in and built through the C# "Class Library" and there are two .cs files either:
BaseUDF.cs
This file defines the decoration of two attributes. The codes are
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Runtime.InteropServices;
using Microsoft.Win32;
namespace BaseUDF
{
[ClassInterface(ClassInterfaceType.AutoDual)]
[ComVisible(true)]
public abstract class BaseUDF
{
[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type)
{
// Add the "Programmable" registry key under CLSID.
Registry.ClassesRoot.CreateSubKey(
GetSubKeyName(type, "Programmable"));
// Register the full path to mscoree.dll which makes Excel happier.
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)
{
// Remove the "Programmable" registry key under CLSID.
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();
}
// Hiding these methods from Excel.
[ComVisible(false)]
public override string ToString()
{
return base.ToString();
}
[ComVisible(false)]
public override bool Equals(object obj)
{
return base.Equals(obj);
}
[ComVisible(false)]
public override int GetHashCode()
{
return base.GetHashCode();
}
}
}
TestExcel.cs
This file is the main calculation file of Excel Automation add-in. The codes are
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Win32;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
using Extensibility;
namespace TestExcel
{
[Guid("7127696E-AB87-427a-BC85-AB3CBA301CF3")]
[ClassInterface(ClassInterfaceType.AutoDual)]
[ComVisible(true)]
public class TestExcel : BaseUDF.BaseUDF
{
public int TestAddExcel(int a = 1, int b = 1)
{
return a + b;
}
}
}
After building, the two add-ins have been registered in the system and in Excel we can use them successfully.
For the Automation add-in, we call them in the spreadsheet as =TestAddExcel(2,3) and =TestAddExcel() both of them work very well and give the right result 5 and 2. However, when I try to call the COM add-in via
Sub TestVBA_Click()
Dim addIn As COMAddIn
Dim TesthObj As Object
Set addIn = Application.COMAddIns("TestVBA")
Set TestObj = addIn.Object
Range("Output").Value2 = TestObj.TestAddVBA(2, 3)
Range("Output").Offset(1, 0).Value2 = TestObj.TestAddVBA()
End Sub
The first call with all arguments existing works well, but for the second one with arguments missing shows the error Type mismatch.
The interesting thing is, when I close the test excel file and open it again, this time I test the COM add-in first, still via the above VBA codes, both two calls work very well. Then when I test the two spreadsheet functions which used to work well, only the first one is good, the second one with arguments missing =TestAddExcel() fails with #VALUE!.
It would be very nice if someone can help with this strange issue.
