用C＃编写的COM加载项与自动化加载项的可选参数 [英] Optional Argument of COM Add-in vs Automation Add-in Written in C#
问题描述
我正在研究COM加载项和Excel Automation加载项的库，其核心代码用C＃编写。我想为该函数设置一个可选参数，并且我知道这对于C＃和VBA甚至Excel WorksheetFunction都是合法的。但是我发现，最后，可选参数只对COM和Automation加载项有效，这意味着，如果首先运行一个加载项，则效果很好，但另一个加载项则无效。
下面请参见示例：
在VS 2013解决方案中，我有两个项目：一个称为 TestVBA ，另一个名为 TestExcel 。
TestVBA 用于COM加载项，并通过 Excel 2013加载项构建，并且有两个 .cs 文件：
-
ThisAddIn.cs
此文件会自动生成并进行一些修改。这些代码是
使用系统；
使用System.Collections.Generic;
使用System.Linq;
使用System.Text;
使用System.Xml.Linq;
使用Excel = Microsoft.Office.Interop.Excel;
使用Office = Microsoft.Office.Core;
使用Microsoft.Office.Tools.Excel；
命名空间TestVBA
{
公共部分类ThisAddIn
{
private void ThisAddIn_Startup（对象发送者，System.EventArgs e）
{
}
私有无效ThisAddIn_Shutdown（对象发送者，System.EventArgs e）
{
}
私有ExcelVBA oExcelVBA;
受保护的覆盖对象RequestComAddInAutomationService（）
{
if（oExcelVBA == null）
{
oExcelVBA = new ExcelVBA（）;
}
返回oExcelVBA；
}
#region VSTO生成的代码
///< summary>
/// Designer支持的必需方法-请勿使用代码编辑器修改
///的此方法的内容。
///< / summary>
private void InternalStartup（）
{
this.Startup + = new System.EventHandler（ThisAddIn_Startup）;
this.Shutdown + = new System.EventHandler（ThisAddIn_Shutdown）;
}
#endregion
}
}
-
TestVBA.cs
此文件是COM加载项的主要计算文件。这些代码是
使用系统；
使用System.Collections.Generic;
使用System.Linq;
使用System.Text;
使用System.Threading.Tasks;
使用System.Runtime.InteropServices;
使用Excel = Microsoft.Office.Interop.Excel;
使用System.Reflection;
命名空间TestVBA
{
[ComVisible（true）]
[ClassInterface（ClassInterfaceType.AutoDual）]
公共类ExcelVBA
{
public int TestAddVBA（int a = 1，int b = 1）
{
返回a + b;
}
}
}
另一个 TestExcel 用于Excel自动化加载项，并通过C＃类库构建，并且有两个 .cs 文件：
-
BaseUDF.cs
此文件定义两个属性的修饰。这些代码是
使用系统；
使用System.Collections.Generic;
使用System.Linq;
使用System.Text;
使用System.Threading.Tasks;
使用System.Runtime.InteropServices;
使用Microsoft.Win32;
名称空间BaseUDF
{
[ClassInterface（ClassInterfaceType.AutoDual）]
[ComVisible（true）]
公共抽象类BaseUDF
{
[ComRegisterFunctionAttribute]
public static void RegisterFunction（Type type）
{
//在CLSID下添加 Programmable注册表项。
Registry.ClassesRoot.CreateSubKey（
GetSubKeyName（type， Programmable））;
//将完整路径注册到mscoree.dll，这使Excel更满意。
RegistryKey键= Registry.ClassesRoot.OpenSubKey（
GetSubKeyName（type， InprocServer32），true）;
key.SetValue（，
System.Environment.SystemDirectory + @ \mscoree.dll，
RegistryValueKind.String）;
}
[ComUnregisterFunctionAttribute]
public static void UnregisterFunction（Type type）
{
//删除CLSID下的 Programmable注册表项。
Registry.ClassesRoot.DeleteSubKey（
GetSubKeyName（type， Programmable），false）;
}
私有静态字符串GetSubKeyName（Type type，
string subKeyName）
{
System.Text.StringBuilder s =
新系统.Text.StringBuilder（）;
s.Append（@ CLSID\ {）;
s.Append（type.GUID.ToString（）。ToUpper（））;
s.Append（@} \）;
s.Append（subKeyName）;
return s.ToString（）;
}
//从Excel隐藏这些方法。
[ComVisible（false）]
公共替代字符串ToString（）
{
return base.ToString（）;
}
[ComVisible（false）]
公共替代布尔值Equals（object obj）
{
return base.Equals（obj）;
}
[ComVisible（false）]
公共重写int GetHashCode（）
{
返回base.GetHashCode（）;
}
}
}
-
TestExcel.cs
此文件是主文件Excel Automation加载项的计算文件。这些代码是
使用系统；
使用System.Collections.Generic;
使用System.Linq;
使用System.Text;
使用System.Threading.Tasks;
使用Microsoft.Win32;
使用System.Runtime.InteropServices;
使用Excel = Microsoft.Office.Interop.Excel;
使用可扩展性；
命名空间TestExcel
{
[Guid（ 7127696E-AB87-427a-BC85-AB3CBA301CF3）]
[ClassInterface（ClassInterfaceType.AutoDual）]
[ComVisible（true）]
公共类TestExcel：BaseUDF.BaseUDF
{
public int TestAddExcel（int a = 1，int b = 1）
{
返回a + b;
}
}
}
构建后，将两个-ins已在系统中注册，并且可以在Excel中成功使用它们。
对于自动化加载项，我们在电子表格中将其称为 = TestAddExcel（2,3）和 = TestAddExcel（）两者都能很好地工作，并给出正确的结果 5 和 2 。但是，当我尝试通过以下方式调用COM加载项时：
Sub TestVBA_Click（）
Dim addIn作为COMAddIn
Dim TesthObj作为对象
设置addIn = Application.COMAddIns（ TestVBA）
设置TestObj = addIn.Object
范围（ Output）。Value2 = TestObj.TestAddVBA（2，3）
Range（ Output）。Offset（1、0）.Value2 = TestObj.TestAddVBA（）
End Sub
第一个具有所有自变量的调用效果很好，但是第二个缺少自变量的调用显示错误类型不匹配。
有趣的是，当我关闭测试excel文件并再次打开它时，这次我仍然通过上面的VBA代码首先测试COM加载项，两个调用都工作得很好。然后，当我测试过去两个可以正常工作的电子表格功能时，只有第一个很好，而第二个参数丢失的 = TestAddExcel（）失败，并显示 #VALUE！。
如果有人可以帮助解决这个奇怪的问题，那就太好了。
我不确定在不注册COM的情况下如何引用类库？现在知道了，您正在使用后期绑定。我不知道您能做到这一点（没想到它会让您） 怀疑是问题所在，它也与 Type mismatch 相匹配错误。
按照我的
可选地，如果上述方法不起作用，请遵循我的答案中的第三个解决方案，并引用自动化加载项并使用Early Binding，我已经对此进行了测试，并且效果很好：
Sub TestVBA1_Click（）
Dim addIn as COMAddIn
Dim TesthObj As Object
Set addIn = Application.COMAddIns（ TestVBA）
Set TestObj = addIn.Object
Debug.Print TestObj.TestAddVBA（2，3）
Debug.Print TestObj.TestAddVBA（）
Dim dotNetClass作为TestExcel .TestExcel
设置dotNetClass = New TestExcel.TestExcel
Debug.Print dotNet Class.TestAddExcel（7，3）
Debug.Print dotNetClass.TestAddExcel（）
End Sub
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.
I am not sure how you Referenced the class library without Registering for COM? I see now, you're using Late Binding. I didnt know you could do that (didn't think it would let you) and suspect that is the problem, it also matches the Type mismatch error.
Follow the second solution in my canonical answer here on the 3 methods to call .Net from Excel or VBA and make sure you Register for COM:
Click on the Build tab and check the check box that says "Register for COM Interop". At this point you have an extra step if you are running on Windows Vista or higher. Visual Studio has to be run with administrator privileges to register for COM Interop. Save your project and exit Visual Studio. Then find Visual Studio in the Start menu and right click on it and choose "Run as Administrator". Reopen your project in Visual Studio. Then choose "Build" to build the add-in.
Optionally if the above doesn't work, follow the third solution in my answer and reference the Automation Add-In and use Early Binding, I've tested this and it works perfectly:
Sub TestVBA1_Click()
Dim addIn As COMAddIn
Dim TesthObj As Object
Set addIn = Application.COMAddIns("TestVBA")
Set TestObj = addIn.Object
Debug.Print TestObj.TestAddVBA(2, 3)
Debug.Print TestObj.TestAddVBA()
Dim dotNetClass As TestExcel.TestExcel
Set dotNetClass = New TestExcel.TestExcel
Debug.Print dotNetClass.TestAddExcel(7, 3)
Debug.Print dotNetClass.TestAddExcel()
End Sub
这篇关于用C＃编写的COM加载项与自动化加载项的可选参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
