访问在Excel中公开为COM的.NET函数 [英] Accessing .NET function exposed as COM in Excel
问题描述
在将vba转换为vb6并创建一个.dll - 如何 - 提示，提示和风险，讨论了如何将VBA代码转换为VB.NET代码，并将VB.NET中的函数访问为COM在Excel中
在Excel中，必须通过VBA访问该函数：
公共函数getParameterNumberOfMaterial（）As Integer
Dim myclass as New ExcelExample.ExcelVB
getParameterNumberOfMaterial = myclass.getParameterNumberOfMaterial（）
结束函数
这意味着对于暴露给用户的每个VBA功能，转换到VB.NET时，我必须像上面那样编写一个包装器。
有没有办法直接使用函数而不写VBA包装器？也就是说，在Excel中，用户可以直接使用 getParameterNumberOfMaterial（），就像在VBA函数中一样，没有我写VBA包装器。
您可以直接从Excel直接调用COM可见的.DLL中的.NET函数，尽管您需要稍微修改它们，使之与COM兼容Excel中。
这个例子将在C＃中，因为这是我熟悉的，但VB.Net中的概念应该是一样的。
打开一个新的类项目，并添加一行来导入InteropServices库：
使用System.Runtime.InteropServices;
声明一个包含要调用的函数的界面：
public interface ISample
{
对象func1（int p1）;
对象func2（string p1）;
}
界面中列出的功能只能在Excel中使用。注意函数返回类型是'object'。这是Excel需要在表格中正确显示结果。
声明将实现该接口的类，并通过COM公开。
[ClassInterface（ClassInterfaceType.None）] //正确的声明COM可见类的方式
public class Sample：ISample
{
public object func1（int p1）
{
//示例实现
object [，] retval = new object [1,1]
retval [0 ，0] =a;
return retval;
}
public object func2（string p1）
{
// ....
}
Excel希望所有返回类型都是对象的二维数组，因此您需要以这种方式转换函数的返回值。 / p>
您还需要添加一些有助于注册和取消注册DLL的功能：
// helper函数获取给定类型的注册表项
private static string GetSubKeyName（Type type）
{
returnCLSID\\ {+ type .GUID.ToString（）。ToUpper（）+} \\可编程;
}
//在注册类时调用
[ComRegisterFunctionAttribute]
public static void RegisterFunction（Type type）
{
/ /注册自动化DLL函数将可见Excel
RegistryKey key = Registry.ClassesRoot.CreateSubKey（GetSubKeyName（type））;
if（key！= null）
{
key.SetValue（string.Empty，Environment.SystemDirectory + @\mscoree.dll）;
}
}
//当类被注销时调用
[ComUnregisterFunctionAttribute]
public static void UnregisterFunction（Type type）
{
try
{
//从注册表中删除自动化DLL
Registry.ClassesRoot.DeleteSubKeyTree（GetSubKeyName（type））;
}
catch（Exception ex）
{
Debug.Print（ex.Message + ex.StackTrace）;
}
}
- 在属性屏幕中在应用程序选项卡中，单击组装信息，然后选中使组装COM-Visible。单击确定。
- 在Build选项卡中，单击底部附近的注册COM Interop。
- 构建项目。
在Excel中，单击开发人员功能区，单击加载项，自动化，然后向下滚动到您的项目。它将以[namespace]的形式列出。[classname]
您现在应该可以直接在Excel工作表中调用函数：
= func1（12）
此处还有一个帖子来自MSDN博客，但是相当老了
PS如果有任何部分需要转换为VB.Net，请让我知道，我一定可以协助。
In Convert vba to vb6 and create a .dll - how to - hints, tipps and risks, it was discussed how to convert VBA code into VB.NET code and access the function in VB.NET as COM in Excel.
In Excel, the function has to be accessed this way via VBA:
Public Function getParameterNumberOfMaterial() As Integer
Dim myclass as New ExcelExample.ExcelVB
getParameterNumberOfMaterial = myclass.getParameterNumberOfMaterial()
End Function
This means for every VBA function exposed to the user, I have to write a wrapper as above when converting to VB.NET.
Is there a way to use the function directly without writing the VBA wrapper? That is, in Excel, the user can directly use getParameterNumberOfMaterial(), just like the orignal VBA function, without me writing a VBA wrapper.
You can certainly call .NET functions in a COM-visible .DLL directly from Excel, though you will need to modify them slightly to make then compatible with COM and Excel. This example will be in C# since that's what I'm familiar with, but the concept should be the same in VB.Net.
Open a new Class project, and add a line to import the InteropServices library:
using System.Runtime.InteropServices;
Declare an interface that will contain the functions you want to call:
public interface ISample
{
object func1(int p1);
object func2(string p1);
}
Only functions listed in the interface will be available in Excel. Note the functions return type is 'object'. This is needed for Excel to properly display the result in the sheet.
Declare the class that will implement the interface, and be exposed via COM.:
[ClassInterface(ClassInterfaceType.None)] // the proper way to declare COM-visible classes
public class Sample : ISample
{
public object func1(int p1)
{
// sample implementation
object[,] retval = new object[1,1]
retval[0,0] = "a";
return retval;
}
public object func2(string p1)
{
// ....
}
Excel expects all return types to be two-dimensional arrays of objects, so you'll need to convert the return values of your functions this way.
You will also need to add a couple functions that will assist with registering and unregistering the DLL:
// helper function to get Registry key of given type
private static string GetSubKeyName(Type type)
{
return "CLSID\\{" + type.GUID.ToString().ToUpper() + "}\\Programmable";
}
// called when class is being registered
[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type type)
{
// register the automation DLL function will be visible to Excel
RegistryKey key = Registry.ClassesRoot.CreateSubKey(GetSubKeyName(type));
if (key != null)
{
key.SetValue(string.Empty, Environment.SystemDirectory + @"\mscoree.dll");
}
}
// called when class is being unregistered
[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type type)
{
try
{
// remove automation DLL from registry
Registry.ClassesRoot.DeleteSubKeyTree(GetSubKeyName(type));
}
catch (Exception ex)
{
Debug.Print(ex.Message + ex.StackTrace);
}
}
- In the Properties screen of your project, in "Application" tab, click "Assembly Information", and check "Make assembly COM-Visible". Click OK.
- In "Build" tab, click "Register for COM Interop" near the bottom.
- Build the project.
In Excel, click the Developer ribbon, click "Add-ins", "Automation" and scroll down to your project. It will be listed in the form [namespace].[classname]
You should now be able to call the functions directly in an Excel worksheet:
=func1(12)
There is also a post on this from an MSDN blogger, but it is quite old
P.S. If there is any part of this you need assitance with converting to VB.Net, please let me know and I can certainly assist.
这篇关于访问在Excel中公开为COM的.NET函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
