作者:幸福不要躲008_784 | 来源:互联网 | 2022-12-06 20:22
我正在开发一个COM加载项和Excel自动化加载项库,其核心代码是用C#编写的.我想为函数设置一个可选参数,我知道这对于C#和VBA,甚至Excel WorksheetFunction都是合法的.但我发现最终可选参数专门用于COM和Automation加载项,这意味着如果首先运行一个加载项,那么效果很好但另一个加载项的可选参数将不起作用.
下面请看示例:
在VS 2013解决方案中,我有两个项目:一个被调用TestVBA
,另一个被调用TestExcel
.
TestVBA
用于COM加载项并通过"Excel 2013加载项"构建,有两个.cs
文件:
ThisAddIn.cs
此文件自动生成并稍作修改.代码是
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
///
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///
private void InternalStartup()
{
this.Startup += new System.EventHandler(ThisAddIn_Startup);
this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
}
#endregion
}
}
TestVBA.cs
此文件是COM加载项的主要计算文件.代码是
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;
}
}
}
另一个TestExcel
是Excel Automation加载项,通过C#"类库"构建,有两个.cs
文件:
BaseUDF.cs
此文件定义了两个属性的装饰.代码是
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
此文件是Excel Automation加载项的主要计算文件.代码是
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;
}
}
}
构建之后,两个加载项已在系统中注册,在Excel中我们可以成功使用它们.
对于自动化加载,我们称他们在电子表格=TestAddExcel(2,3)
和=TestAddExcel()
他们都很好地工作,并给予正确的结果5
和2
.但是,当我尝试通过COM调用COM加载项时
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
使用所有参数存在的第一个调用运行良好,但对于缺少参数的第二个调用显示错误Type mismatch
.
有趣的是,当我关闭测试excel文件并再次打开它时,这次我首先测试COM加载项,仍然通过上面的VBA代码,两个调用都工作得很好.然后,当我测试过去运行良好的两个电子表格函数时,只有第一个是好的,第二个缺少参数的函数=TestAddExcel()
失败了#VALUE!
.
如果有人可以帮助解决这个奇怪的问题,那将是非常好的.