使用 VB.NET 为 VBA IDE 构建插件
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/1942626/
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
Build add-in for VBA IDE using VB.NET
提问by Oorang
I have asked this elsewhere, but have never found anyone knows how to build an add-in for VBA IDE using VB.NET. Is it even possible? Could someone point me to an example?
我在其他地方问过这个问题,但从未发现有人知道如何使用 VB.NET 为 VBA IDE 构建加载项。甚至有可能吗?有人能给我举个例子吗?
回答by almog.ori
It is possible you need to write a com addin using IDTExtensibility2 interface, select the shared addin project template from new project.
您可能需要使用 IDTExtensibility2 接口编写一个 com 插件,从新项目中选择共享插件项目模板。
EDIT
编辑
Otherwise to create this addin from scratch you will need to do the following:
否则,要从头开始创建此插件,您需要执行以下操作:
- Create a new project class library
- Add references to "Extensibility", it should be in the list. You may need to download the PIAs for your version of office. (and perhaps VSTO but i am unsure on this point)
- Add references to "Microsoft.Vbe.Interop" again should be with the PIAs.
- Check the box "Register for Com Interop" in the properties tab.
- OPTIONALIn the debug settings tab change the startup to external program and enter the path to the excel exe in the programfiles folder (if this is intended for excel) this is to allow the project to be debuggable.
- OPTIONALIn the command options add a entry to a worksheet, or word doc that will show the addin dialog using a macro on startup, for development this makes sense to streamline the debugging experience. eg "C:\vbe.xlsm"
- OPTIONALAlso set the startup path to the worksheet directory eg "C:\"
- Implement the interface "IDTExtensibility2" found in "Extensibility" assembly.
- Call this class "Connect" (this is just a preference)
- Attribute the class with the following
- 创建一个新的项目类库
- 添加对“可扩展性”的引用,它应该在列表中。您可能需要为您的 Office 版本下载 PIA。(也许还有 VSTO,但我不确定这一点)
- 再次添加对“Microsoft.Vbe.Interop”的引用应该与 PIA 一起使用。
- 选中属性选项卡中的“注册 Com Interop”框。
- 可选在调试设置选项卡中,将启动更改为外部程序,并在 programfiles 文件夹中输入 Excel exe 的路径(如果这是用于 excel),这是为了允许项目可调试。
- 可选在命令选项中添加一个条目到工作表或 word doc,它将在启动时使用宏显示插件对话框,对于开发来说,这有助于简化调试体验。例如“C:\vbe.xlsm”
- 可选 还将启动路径设置为工作表目录,例如“C:\”
- 实现在“Extensibility”程序集中找到的接口“IDTExtensibility2”。
- 称此类为“Connect”(这只是一个偏好)
- 使用以下属性对类进行属性
[ComVisible(true), Guid("YourGeneratedGuid"), ProgId("YourAddinName.Connect")]
[ComVisible(true), Guid("YourGeneratedGuid"), ProgId("YourAddinName.Connect")]
Heres an implementation to get you started, Firstly replace the "YourAddinName" with your AppName and Create a Guid for "YourGeneratedGuid". You will need to register the Addin into the right Registry location, see the registry keys that follow to get an idea, also replace some vars in the registry keys.
这是让您入门的实现,首先将“YourAddinName”替换为您的 AppName 并为“YourGeneratedGuid”创建一个 Guid。您需要将 Addin 注册到正确的注册表位置,查看后面的注册表项以获取想法,还需要替换注册表项中的一些变量。
Imports System
Imports System.Drawing
Imports System.Linq
Imports System.Runtime.InteropServices
Imports Extensibility
Imports Microsoft.Vbe.Interop
Namespace VBEAddin
''' <summary>
''' The object for implementing an Add-in.
''' </summary>
''' <seealso class='IDTExtensibility2' />
<Guid("YourGeneratedGuid"), ProgId("YourAddinName.Connect")> _
Public Class Connect
Implements IDTExtensibility2
Private _application As VBE 'Interop VBE application object
#Region "IDTExtensibility2 Members"
''' <summary>
''' Implements the OnConnection method of the IDTExtensibility2 interface.
''' Receives notification that the Add-in is being loaded.
''' </summary>
''' <param term='application'>
''' Root object of the host application.
''' </param>
''' <param term='connectMode'>
''' Describes how the Add-in is being loaded.
''' </param>
''' <param term='addInInst'>
''' Object representing this Add-in.
''' </param>
''' <seealso class='IDTExtensibility2' />
Public Sub OnConnection(ByVal application As Object, ByVal connectMode As ext_ConnectMode, ByVal addInInst As Object, ByRef [custom] As Array)
_application = CType(Application,VBE)
End Sub
Private Sub onReferenceItemAdded(ByVal reference As Reference)
'TODO: Map types found in assembly using reference.
End Sub
Private Sub onReferenceItemRemoved(ByVal reference As Reference)
'TODO: Remove types found in assembly using reference.
End Sub
Private Sub BootAddin()
'Detect change in active window.
End Sub
''' <summary>
''' Implements the OnDisconnection method of the IDTExtensibility2 interface.
''' Receives notification that the Add-in is being unloaded.
''' </summary>
''' <param term='disconnectMode'>
''' Describes how the Add-in is being unloaded.
''' </param>
''' <param term='custom'>
''' Array of parameters that are host application specific.
''' </param>
''' <seealso class='IDTExtensibility2' />
Public Sub OnDisconnection(ByVal disconnectMode As ext_DisconnectMode, ByRef [custom] As Array)
End Sub
''' <summary>
''' Implements the OnAddInsUpdate method of the IDTExtensibility2 interface.
''' Receives notification that the collection of Add-ins has changed.
''' </summary>
''' <param term='custom'>
''' Array of parameters that are host application specific.
''' </param>
''' <seealso class='IDTExtensibility2' />
Public Sub OnAddInsUpdate(ByRef [custom] As Array)
End Sub
''' <summary>
''' Implements the OnStartupComplete method of the IDTExtensibility2 interface.
''' Receives notification that the host application has completed loading.
''' </summary>
''' <param term='custom'>
''' Array of parameters that are host application specific.
''' </param>
''' <seealso class='IDTExtensibility2' />
Public Sub OnStartupComplete(ByRef [custom] As Array)
'Boot dispatcher
End Sub
''' <summary>
''' Implements the OnBeginShutdown method of the IDTExtensibility2 interface.
''' Receives notification that the host application is being unloaded.
''' </summary>
''' <param term='custom'>
''' Array of parameters that are host application specific.
''' </param>
''' <seealso class='IDTExtensibility2' />
Public Sub OnBeginShutdown(ByRef [custom] As Array)
End Sub
#End Region
End Class
End Namespace
Here is the registry .key script to register the Addin, note you will need to change some of the settings in order to register it properly.
这是用于注册 Addin 的注册表 .key 脚本,请注意,您需要更改一些设置才能正确注册它。
Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Microsoft\VBA\VBE.0\Addins\YourAddinName.Connect]
"CommandLineSafe"=dword:00000000
"Description"="Description for your new addin"
"LoadBehavior"=dword:00000000
"FriendlyName"="YourAddinName"
[HKEY_CLASSES_ROOT\CLSID\{YourGeneratedGuid}]
@="YourAddinName.Connect"
[HKEY_CLASSES_ROOT\CLSID\{YourGeneratedGuid}\Implemented Categories]
[HKEY_CLASSES_ROOT\CLSID\{YourGeneratedGuid}\InprocServer32]
@="mscoree.dll"
"ThreadingModel"="Both"
"Class"="YourAddinName.Connect"
"Assembly"="YourAssemblyNameFullTypeName"
"RuntimeVersion"="v2.0.50727"
"CodeBase"="file:///PathToAssembly"
[HKEY_CLASSES_ROOT\CLSID\{YourGeneratedGuid}\ProgId]
@="YourAddinName.Connect"
NOTEthe tokens "YourGeneratedGuid" must have the curly braces {} included and be the same as the Guid in the attrib above, the token "YourAssemblyNameFullTypeName" must be the Assembly full name, the token "YourAddinName.Connect" must be the same ProgId set in the attrib above.
注意标记“YourGeneratedGuid”必须包含大括号 {} 并且与上面属性中的 Guid 相同,标记“YourAssemblyNameFullTypeName”必须是程序集全名,标记“YourAddinName.Connect”必须是相同的 ProgId设置在上面的属性中。
SIDE NOTE
边注
Also found this helpful, might save you couple hours googling.
也发现这很有帮助,可能会为您节省几个小时的谷歌搜索。
'HKEY_CURRENT_USER\Software\Microsoft\VBA.0\Common
'FontFace=Courier New (STRING - Default if missing)
'FontHeight=10 (DWORD - Default if missing)
回答by Jeremy Thompson
Unfortunately almog.ori's steps didn't work for me. Here is my version to aid people in the future:
不幸的是,almog.ori 的步骤对我不起作用。这是我将来帮助人们的版本:
Create a C# or VB.NET Class Library project named "VBEAddIn"
Add the following Interop assemblies as references to the project, using the "Project", "Add Reference..." menu, "Browse" tab.
Extensibility (C:\Program Files\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Common\Extensibility.dll) - if its not there try the C:\Program Files (x86)\ if your using a x64 PC.
Microsoft.Office.Interop.Excel (C:\Program Files\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office14\Microsoft.Office.Interop.Excel.dll)
Microsoft.Vbe.Interop (C:\Program Files\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office14\Microsoft.Vbe.Interop.dll)
(optionally) Microsoft.Vbe.Interop.Forms (C:\Program Files\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office14\Microsoft.Vbe.Interop.Forms.dll)
创建一个名为“VBEAddIn”的 C# 或 VB.NET 类库项目
使用“项目”、“添加引用...”菜单、“浏览”选项卡,添加以下互操作程序集作为对项目的引用。
可扩展性(C:\Program Files\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Common\Extensibility.dll)- 如果它不存在,请尝试 C:\Program Files (x86)\ 如果您使用的是 x64 PC。
Microsoft.Office.Interop.Excel (C:\Program Files\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office14\Microsoft.Office.Interop.Excel.dll)
Microsoft.Vbe.Interop (C:\Program Files\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office14\Microsoft.Vbe.Interop.dll)
(可选)Microsoft.Vbe.Interop.Forms(C:\Program Files\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office14\Microsoft.Vbe.Interop.Forms.dll)
Add a class to your project with the following code:
使用以下代码向您的项目添加一个类:
VB.Net:
VB.Net:
Imports Microsoft.Office.Interop
Imports Extensibility
Imports System.Windows.Forms
Imports System.Runtime.InteropServices
Imports Microsoft.Vbe.Interop
<ComVisible(True), Guid("3599862B-FF92-42DF-BB55-DBD37CC13565"), ProgId("VBEAddInVB.Net.Connect")> _
Public Class Connect
Implements Extensibility.IDTExtensibility2
Private _VBE As VBE
Private _AddIn As AddIn
Private Sub OnConnection(Application As Object, ConnectMode As Extensibility.ext_ConnectMode, _
AddInInst As Object, ByRef custom As System.Array) Implements IDTExtensibility2.OnConnection
Try
_VBE = DirectCast(Application, VBE)
_AddIn = DirectCast(AddInInst, AddIn)
Select Case ConnectMode
Case Extensibility.ext_ConnectMode.ext_cm_Startup
Case Extensibility.ext_ConnectMode.ext_cm_AfterStartup
InitializeAddIn()
End Select
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
End Sub
Private Sub OnDisconnection(RemoveMode As Extensibility.ext_DisconnectMode, _
ByRef custom As System.Array) Implements IDTExtensibility2.OnDisconnection
End Sub
Private Sub OnStartupComplete(ByRef custom As System.Array) _
Implements IDTExtensibility2.OnStartupComplete
InitializeAddIn()
End Sub
Private Sub OnAddInsUpdate(ByRef custom As System.Array) Implements IDTExtensibility2.OnAddInsUpdate
End Sub
Private Sub OnBeginShutdown(ByRef custom As System.Array) Implements IDTExtensibility2.OnBeginShutdown
End Sub
Private Sub InitializeAddIn()
MessageBox.Show(_AddIn.ProgId & " loaded in VBA editor version " & _VBE.Version)
End Sub
End Class
C#:
C#:
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Runtime.InteropServices;
using Extensibility;
using Microsoft.Vbe.Interop;
using System.Windows.Forms;
namespace VBEAddin
{
[ComVisible(true), Guid("3599862B-FF92-42DF-BB55-DBD37CC13565"), ProgId("VBEAddIn.Connect")]
public class Connect : IDTExtensibility2
{
private VBE _VBE;
private AddIn _AddIn;
#region "IDTExtensibility2 Members"
public void OnConnection(object application, ext_ConnectMode connectMode, object addInInst, ref Array custom)
{
try
{
_VBE = (VBE)application;
_AddIn = (AddIn)addInInst;
switch (connectMode)
{
case Extensibility.ext_ConnectMode.ext_cm_Startup:
break;
case Extensibility.ext_ConnectMode.ext_cm_AfterStartup:
InitializeAddIn();
break;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void onReferenceItemAdded(Reference reference)
{
//TODO: Map types found in assembly using reference.
}
private void onReferenceItemRemoved(Reference reference)
{
//TODO: Remove types found in assembly using reference.
}
public void OnDisconnection(ext_DisconnectMode disconnectMode, ref Array custom)
{
}
public void OnAddInsUpdate(ref Array custom)
{
}
public void OnStartupComplete(ref Array custom)
{
InitializeAddIn();
}
private void InitializeAddIn()
{
MessageBox.Show(_AddIn.ProgId + " loaded in VBA editor version " + _VBE.Version);
}
public void OnBeginShutdown(ref Array custom)
{
}
#endregion
}
}
In the Project properties window of the project:
在项目的项目属性窗口中:
In the "Application" tab, ensure that both the Assembly name and Root namespace are set to "VBEAddIn".
In the "Compile" tab, ensure that the "Register for COM interop" checkbox is checked. We wont bother with registering assembly for COM Interop manually with the proper regasm.exe tool. However note that the "Register for COM interop" checkbox would only register the add-in dll as 32-bit COM library, not as 64-bit COM library.
In the "Compile" tab, "Advanced Compile Options" button, ensure that the "Target CPU" combobox is set to "AnyCPU", which means that the assembly can be executed as 64-bit or 32-bit, depending on the executing .NET Framework that loads it.
In the "Signing" tab, make sure the "Sign the assembly" is unticked.
在“应用程序”选项卡中,确保程序集名称和根命名空间都设置为“VBEAddIn”。
在“编译”选项卡中,确保选中“注册 COM 互操作”复选框。我们不会费心使用适当的 regasm.exe 工具手动为 COM Interop 注册程序集。但是请注意,“注册 COM 互操作”复选框只会将加载项 dll 注册为 32 位 COM 库,而不是 64 位 COM 库。
在“编译”选项卡的“高级编译选项”按钮中,确保将“目标 CPU”组合框设置为“AnyCPU”,这意味着该程序集可以作为 64 位或 32 位执行,具体取决于执行的加载它的 .NET Framework。
在“签名”选项卡中,确保未选中“签署程序集”。
Next add the registry Keys, save the below snippet as a ASCI file with a reg extension and double click it to add the values to the registry.
接下来添加注册表项,将以下代码段保存为带有 reg 扩展名的 ASCI 文件,然后双击它以将值添加到注册表中。
Important Note:Before you execute the reg file, change the path: "CodeBase"="file:///C:\Dev\VBEAddIn\VBEAddIn\bin\Debug\VBEAddIn.dll"
重要说明:在执行 reg 文件之前,更改路径:"CodeBase"="file:///C:\Dev\VBEAddIn\VBEAddIn\bin\Debug\VBEAddIn.dll"
Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Microsoft\VBA\VBE.0\Addins\VBEAddIn.Connect]
"CommandLineSafe"=dword:00000000
"Description"="Description for your new addin"
"LoadBehavior"=dword:00000000
"FriendlyName"="VBEAddIn"
[HKEY_CLASSES_ROOT\CLSID\{3599862B-FF92-42DF-BB55-DBD37CC13565}]
@="VBEAddIn.Connect"
[HKEY_CLASSES_ROOT\CLSID\{3599862B-FF92-42DF-BB55-DBD37CC13565}\Implemented Categories]
[HKEY_CLASSES_ROOT\CLSID\{3599862B-FF92-42DF-BB55-DBD37CC13565}\InprocServer32]
@="mscoree.dll"
"ThreadingModel"="Both"
"Class"="VBEAddIn.Connect"
"Assembly"="VBEAddIn"
"RuntimeVersion"="v2.0.50727"
"CodeBase"="file:///C:\Dev\VBEAddIn\VBEAddIn\bin\Debug\VBEAddIn.dll"
[HKEY_CLASSES_ROOT\CLSID\{3599862B-FF92-42DF-BB55-DBD37CC13565}\ProgId]
@="VBEAddIn.Connect"
- Build the VBE Add-in in Visual Studio and open Excel.
- Open its VBA editor (Alt + F11).
- Go to the "Add-Ins", "Add-In Manager..." menu to check that the add-in is correctly registered.
- Load the add-in. You should see the message box "VBEAddIn.Connect loaded in VBA editor"
- 在 Visual Studio 中构建 VBE 加载项并打开 Excel。
- 打开它的 VBA 编辑器 (Alt + F11)。
- 转到“加载项”、“加载项管理器...”菜单以检查加载项是否已正确注册。
- 加载加载项。您应该会看到消息框“VBEAddIn.Connect 在 VBA 编辑器中加载”
If you get this error:
如果您收到此错误:
'VBEAddIn' could not be loaded.
Remove it from the list of available Add-Ins?
无法加载“VBEAddIn”。
从可用加载项列表中删除它?
Its likely you didn't change the path "CodeBase"="file:///C:\Dev\VBEAddIn\VBEAddIn\bin\Debug\VBEAddIn.dll"
您可能没有更改路径 "CodeBase"="file:///C:\Dev\VBEAddIn\VBEAddIn\bin\Debug\VBEAddIn.dll"
And check that the CodeBase key is in the registry (add a string regkey with the CodeBase if it doesn't exist):
并检查 CodeBase 项是否在注册表中(如果 CodeBase 不存在,则添加一个字符串 regkey ):
Then close down the Office application, build the VBE AddIn again from Visual Studio, Open Office (Excel, Outlook, Word, etc) and Alt + F11, AddIns menu > AddIn Manager and select the AddIn and Tick Loaded/UnLoaded.
然后关闭 Office 应用程序,再次从 Visual Studio 构建 VBE 插件,打开 Office(Excel、Outlook、Word 等)和 Alt + F11,插件菜单 > 插件管理器并选择插件并勾选加载/卸载。
Final Trick to overcome this issue:
克服这个问题的最终技巧:
If that still fails, close the Office app, goto Visual Studio, Project Properties > Build Tab > Tick Register for COM Interop > Build Solution and open the Office Add In > Alt + F11 > AddIns Menu > AddIn Manager and click Loaded/Unloaded.
如果仍然失败,请关闭 Office 应用程序,转到 Visual Studio,项目属性 > 构建选项卡 > 勾选注册 COM Interop > 构建解决方案,然后打开 Office 插件 > Alt + F11 > 插件菜单 > 插件管理器,然后单击加载/卸载。
This answer uses some info from Carlo's Quintero (MZTools) that I've modified, Ref: http://www.mztools.com/articles/2012/MZ2012013.aspx
这个答案使用了我修改过的 Carlo's Quintero (MZTools) 中的一些信息,参考:http://www.mztools.com/articles/2012/MZ2012013.aspx
回答by Curtis Inderwiesche
I also found this reference to be helpful in making a VBA DLL from C# or VB.NET:
我还发现此参考有助于从 C# 或 VB.NET 制作 VBA DLL:
Create a new C# (or VB.Net) project and select Class Library as the template type.
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace SimpleCalc { public class Calc { private int numberOne = 0; private int numberTwo = 0; public void SetNumberOne(int number) { numberOne = number; } public void SetNumberTwo(int number) { numberTwo = number; } // Add two integers public int Add() { return numberOne + numberTwo; } } }
Configure project properties to make it COM visible.
- Register for COM Interop.
- Compile the project.
- Copy the type library file to Windows system folder.
- Reference the type library from Access VBA editor.
Use the DLL in your VBA code.
Public Function test() Dim lngResult As Long Dim objCalc As SimpleCalc.Calc Set objCalc = New SimpleCalc.Calc objCalc.SetNumberOne (3) objCalc.SetNumberTwo (6) lngResult = objCalc.Add() End Function
创建一个新的 C#(或 VB.Net)项目并选择 Class Library 作为模板类型。
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace SimpleCalc { public class Calc { private int numberOne = 0; private int numberTwo = 0; public void SetNumberOne(int number) { numberOne = number; } public void SetNumberTwo(int number) { numberTwo = number; } // Add two integers public int Add() { return numberOne + numberTwo; } } }
配置项目属性以使其 COM 可见。
- 注册 COM 互操作。
- 编译项目。
- 将类型库文件复制到 Windows 系统文件夹。
- 从 Access VBA 编辑器引用类型库。
在 VBA 代码中使用 DLL。
Public Function test() Dim lngResult As Long Dim objCalc As SimpleCalc.Calc Set objCalc = New SimpleCalc.Calc objCalc.SetNumberOne (3) objCalc.SetNumberTwo (6) lngResult = objCalc.Add() End Function
Made available by GeeksEngine.com
回答by Seth Moore
I would imagine you could call a .NET DLL from your VBA code (never actually done this myself). Just create a VB Class Library project and create a DLL to use in your VBA.
我想你可以从你的 VBA 代码中调用一个 .NET DLL(我自己从来没有这样做过)。只需创建一个 VB 类库项目并创建一个 DLL 以在您的 VBA 中使用。
After a quick google it looks like you would need to set "Register for Com Interop" = True under Project Properties->Build, but like I said, I've never actually tried this before.
在快速谷歌之后,您似乎需要在 Project Properties->Build 下设置“Register for Com Interop”= True,但就像我说的,我以前从未真正尝试过。
回答by Gener4tor
Also take care that the Guid of the project (in case of c# in assamblyInfo.cs) is different from the Guid of the Class "Connect".
还要注意项目的 Guid(如果是 assamblyInfo.cs 中的 c#)与“Connect”类的 Guid 不同。
Having the same Guid results in a "could not be converted to a type library"-error when checking: Project Properties > Build Tab > Register for COM Interop
检查时,具有相同的 Guid 会导致“无法转换为类型库”-错误:项目属性 > 构建选项卡 > 注册 COM 互操作