在 Excel 中从 VBA 调用 VB.Net
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23987067/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calling VB.Net from VBA in Excel
提问by Adam12344
I am trying to get some code working between Excel VBA and VB.Net. I have the following code in VB.Net in Visual Studio..
我试图让一些代码在 Excel VBA 和 VB.Net 之间工作。我在 Visual Studio 的 VB.Net 中有以下代码..
Public Class ThisAddIn
Private Sub ThisAddIn_Startup() Handles Me.Startup
End Sub
Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown
End Sub
Public Function Multiplication()
Dim activeWorksheet As Excel.Worksheet = CType(Application.ActiveSheet, Excel.Worksheet)
Dim Range1 As Excel.Range = activeWorksheet.Range("A1")
Dim Range2 As Excel.Range = activeWorksheet.Range("A2")
Dim x As Integer = Range1.Value
Dim y As Integer = Range2.Value
Return (x * y)
End Function
End Class
I also have this code in VBA that I am trying to run
我在 VBA 中也有这个代码，我正在尝试运行
Sub Macro1()
Dim x
x = ThisAddIn.Multiplication
MsgBox (x)
End Sub
From Visual Studio I hit Start and I put this code into the Excel VBA for the new window that pops up.
在 Visual Studio 中，我点击了“开始”，然后将此代码放入 Excel VBA 以用于弹出的新窗口。
I get the error '424 Object Required' on the VBA line "x = ThisAddIn.Multiplication". How can I call a VB.Net function from VBA?
我在 VBA 行“x = ThisAddIn.Multiplication”上收到错误“424 Object Required”。如何从 VBA 调用 VB.Net 函数？
Edit: I am using Excel 2013, VB.Net version 2013(VB12), and I guess I am targeting .Net version 4.5+
编辑：我使用的是 Excel 2013，VB.Net 版本 2013(VB12)，我想我的目标是 .Net 版本 4.5+
Edit: I tried adding the following code:
编辑：我尝试添加以下代码：
Imports System
Imports System.Collections.Generic
Imports System.Text
and
和
Private Sub Test()
Dim testClass As New ThisAddIn
MsgBox testClass.Multiplication()
End Sub
Based off this guide, but its still not working. I could not find the options to connect the project to Excel mentioned in Step 3 because it is an old guide. Maybe this is my problem?
基于本指南，但它仍然无法正常工作。我找不到将项目连接到步骤 3 中提到的 Excel 的选项，因为它是旧指南。也许这是我的问题？
回答by Govert
Instead of using VSTO for making the Excel add-in, another approach is to make an Excel-DNAadd-in with the VB.NET code. These are convenient, since you don't need admin access to load and register, and you can create high-performance UDFs and COM servers in a single add-in.
不是使用 VSTO 制作 Excel 插件，另一种方法是使用 VB.NET 代码制作Excel-DNA插件。这些很方便，因为您不需要管理员访问权限来加载和注册，并且您可以在单个加载项中创建高性能 UDF 和 COM 服务器。
This will allow you to create a user-defined function (UDF) in your VB.NET, which can be used directly form the worksheet. Your UDF code might look like this:
这将允许您在 VB.NET 中创建用户定义函数 (UDF)，该函数可以直接从工作表中使用。您的 UDF 代码可能如下所示：
Public Module MyFunctions
<ExcelFunction(Description:="Useful custom multiplication function")>
Function MultiplyThem(val1 As Double, val2 As Double) As Double
Return val1 * val2
End Function
End Module
And you can call this from a cell as =MultiplyThem(A1, A2).
您可以从单元格中将其称为=MultiplyThem(A1, A2).
Or course you can also make macros (Subs) that automate to the Excel object, make custom Ribbon tabs etc.
或者当然，您还可以制作Sub自动处理 Excel 对象的宏（s），制作自定义功能区选项卡等。
As an easy start for integrating with VBA, functions and macros in the add-in can be directly called from a VBA project using Application.Run("MultiplyThem", 3, 5).
作为与 VBA 集成的一个简单开始，可以使用Application.Run("MultiplyThem", 3, 5).
Going further, you can set up your Excel-DNA add-in as a COM Server, which allows you to Tools->Referencethe .xll add-in from a VBA project, and then access your add-in's exported object model from VBA (even with IntelliSense). Two nice articles with step-by-step instruction on making a such a COM Server with Excel-DNA were written by Mikael Katajam?ki:
更进一步，您可以将 Excel-DNA 加载项设置为 COM 服务器，这允许您从 VBA 项目工具->引用.xll 加载项，然后从 VBA 访问加载项的导出对象模型（即使使用智能感知）。Mikael Katajam?ki写了两篇很好的文章，其中包含有关使用 Excel-DNA 制作此类 COM 服务器的分步说明：
回答by MDA
if you want to call a function from vb.net first you must reference your dll to vba ftom tools->reference. after reference you must define object from your class into dll file. if your dll file name is mydll the below code is written in vba like below:
如果你想首先从 vb.net 调用一个函数，你必须将你的 dll 引用到 vba ftom tools->reference。引用后，您必须将类中的对象定义到 dll 文件中。如果你的 dll 文件名是 mydll，下面的代码是用 vba 编写的，如下所示：
dim x as mydll.ThisAddIn
set x =new mydll.ThisAddIn
dim y
y=x.Multiplication
