使用 VB.NET 为 VBA IDE 构建插件 [英] Build add-in for VBA IDE using VB.NET

问题描述

我在别处问过这个问题，但从未发现有人知道如何使用 VB.NET 为 VBA IDE 构建加载项.甚至有可能吗?有人能给我举个例子吗?

解决方案

您可能需要使用 IDTExtensibility2 接口编写一个 com 插件，从新项目中选择共享插件项目模板.

编辑

否则，要从头开始创建此插件，您需要执行以下操作:

1. 新建项目类库
2. 添加对可扩展性"的引用，它应该在列表中.您可能需要为您的 Office 版本下载 PIA.(也许还有 VSTO，但我不确定这一点)
3. 再次添加对Microsoft.Vbe.Interop"的引用应该与 PIA 一起使用.
4. 选中属性选项卡中的注册 Com Interop"框.
5. 可选 在调试设置选项卡中，将启动更改为外部程序，并在 programfiles 文件夹中输入 excel exe 的路径(如果这是用于 excel)，这是为了允许项目可调试.
6. 可选 在命令选项中添加一个条目到工作表或 word 文档中，该条目将在启动时使用宏显示插件对话框，对于开发来说，这有助于简化调试体验.例如C:vbe.xlsm"
7. 可选同时将启动路径设置为工作表目录，例如C:"
8. 实现在Extensibility"程序集中找到的接口IDTExtensibility2".
9. 称此类为Connect"(这只是一种偏好)
10. 使用以下属性对类进行属性

<块引用>

[ComVisible(true),Guid("YourGeneratedGuid"),ProgId("YourAddinName.Connect")]

这是一个帮助您入门的实现，首先将YourAddinName"替换为您的 AppName 并为YourGeneratedGuid"创建一个 Guid.您需要将 Addin 注册到正确的注册表位置，请参阅后面的注册表项以获取想法，同时替换注册表项中的一些变量.

导入系统导入 System.Drawing导入 System.Linq导入 System.Runtime.InteropServices导入可扩展性导入 Microsoft.Vbe.Interop命名空间 VBEAddin''' <总结>''' 用于实现插件的对象.''' </summary>''' <seealso class='IDTExtensibility2'/><Guid("YourGeneratedGuid"), ProgId("YourAddinName.Connect")>_公开课连接实现 IDTEExtensibility2Private _application As VBE '互操作 VBE 应用程序对象#Region "IDTExtensibility2 成员"''' <总结>''' 实现了 IDTExtensibility2 接口的 OnConnection 方法.''' 接收加载项正在加载的通知.''' </summary>''' ''' 宿主应用程序的根对象.''' </param>''' ''' 描述加载项的加载方式.''' </param>''' ''' 代表此插件的对象.''' </param>''' <seealso class='IDTExtensibility2'/>Public Sub OnConnection(ByVal application As Object, ByVal connectMode As ext_ConnectMode, ByVal addInst As Object, ByRef [custom] As Array)_application = CType(应用程序，VBE)结束子Private Sub onReferenceItemAdded(ByVal reference As Reference)'TODO:使用引用在程序集中找到的映射类型.结束子Private Sub onReferenceItemRemoved(ByVal reference As Reference)'TODO: 使用引用删除在程序集中找到的类型.结束子私有子 BootAddin()'检测活动窗口的变化.结束子''' <总结>''' 实现了 IDTExtensibility2 接口的 OnDisconnection 方法.''' 收到加载项正在卸载的通知.''' </summary>''' ''' 描述加载项的卸载方式.''' </param>''' ''' 特定于主机应用程序的参数数组.''' </param>''' <seealso class='IDTExtensibility2'/>Public Sub OnDisconnection(ByVal disconnectMode As ext_DisconnectMode, ByRef [custom] As Array)结束子''' <总结>''' 实现 IDTExtensibility2 接口的 OnAddInsUpdate 方法.''' 接收加载项集合已更改的通知.''' </summary>''' ''' 特定于主机应用程序的参数数组.''' </param>''' <seealso class='IDTExtensibility2'/>Public Sub OnAddInsUpdate(ByRef [custom] As Array)结束子''' <总结>''' 实现 IDTExtensibility2 接口的 OnStartupComplete 方法.''' 接收主机应用程序已完成加载的通知.''' </summary>''' ''' 特定于主机应用程序的参数数组.''' </param>''' <seealso class='IDTExtensibility2'/>Public Sub OnStartupComplete(ByRef [custom] As Array)'启动调度程序结束子''' <总结>''' 实现 IDTExtensibility2 接口的 OnBeginShutdown 方法.''' 接收主机应用程序正在卸载的通知.''' </summary>''' ''' 特定于主机应用程序的参数数组.''' </param>''' <seealso class='IDTExtensibility2'/>Public Sub OnBeginShutdown(ByRef [custom] As Array)结束子#结束区域结束班结束命名空间

这是用于注册 Addin 的注册表 .key 脚本，请注意您需要更改一些设置才能正确注册.

Windows 注册表编辑器 5.00 版[HKEY_CURRENT_USERSoftwareMicrosoftVBAVBE6.0AddinsYourAddinName.Connect]CommandLineSafe"=dword:00000000"Description"="新插件的说明""LoadBehavior"=dword:00000000"FriendlyName"="YourAddinName"[HKEY_CLASSES_ROOTCLSID{YourGeneratedGuid}]@="YourAddinName.Connect"[HKEY_CLASSES_ROOTCLSID{YourGeneratedGuid}Implemented Categories][HKEY_CLASSES_ROOTCLSID{YourGeneratedGuid}InprocServer32]@="mscoree.dll"线程模型"=两者""Class"="YourAddinName.Connect""Assembly"="YourAssemblyNameFullTypeName""运行时版本"="v2.0.50727""CodeBase"="file:///PathToAssembly"[HKEY_CLASSES_ROOTCLSID{YourGeneratedGuid}ProgId]@="YourAddinName.Connect"

注意 标记YourGeneratedGuid"必须包含花括号 {} 并且与上面 attrib 中的 Guid 相同，标记YourAssemblyNameFullTypeName"必须是程序集全名，标记YourAddinName.Connect"必须与上述属性中设置的 ProgId 相同.

边注

也发现这很有帮助，可能会为您节省几个小时的谷歌搜索.

'HKEY_CURRENT_USERSoftwareMicrosoftVBA6.0Common'FontFace=Courier New(STRING - 如果缺少则默认)'FontHeight=10(DWORD - 如果缺少则为默认值)

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?

解决方案

It is possible you need to write a com addin using IDTExtensibility2 interface, select the shared addin project template from new project.

EDIT

Otherwise to create this addin from scratch you will need to do the following:

1. Create a new project class library
2. 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)
3. Add references to "Microsoft.Vbe.Interop" again should be with the PIAs.
4. Check the box "Register for Com Interop" in the properties tab.
5. OPTIONAL In 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.
6. OPTIONAL In 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"
7. OPTIONAL Also set the startup path to the worksheet directory eg "C:"
8. Implement the interface "IDTExtensibility2" found in "Extensibility" assembly.
9. Call this class "Connect" (this is just a preference)
10. Attribute the class with the following

[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.

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.

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USERSoftwareMicrosoftVBAVBE6.0AddinsYourAddinName.Connect]
"CommandLineSafe"=dword:00000000
"Description"="Description for your new addin"
"LoadBehavior"=dword:00000000
"FriendlyName"="YourAddinName"


[HKEY_CLASSES_ROOTCLSID{YourGeneratedGuid}]
@="YourAddinName.Connect"

[HKEY_CLASSES_ROOTCLSID{YourGeneratedGuid}Implemented Categories]

[HKEY_CLASSES_ROOTCLSID{YourGeneratedGuid}InprocServer32]
@="mscoree.dll"
"ThreadingModel"="Both"
"Class"="YourAddinName.Connect"
"Assembly"="YourAssemblyNameFullTypeName"
"RuntimeVersion"="v2.0.50727"
"CodeBase"="file:///PathToAssembly"

[HKEY_CLASSES_ROOTCLSID{YourGeneratedGuid}ProgId]
@="YourAddinName.Connect"

NOTE the 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.

SIDE NOTE

Also found this helpful, might save you couple hours googling.

'HKEY_CURRENT_USERSoftwareMicrosoftVBA6.0Common
'FontFace=Courier New (STRING - Default if missing)
'FontHeight=10 (DWORD - Default if missing)                

这篇关于使用 VB.NET 为 VBA IDE 构建插件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！