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

问题描述

我曾在其他地方问过这个问题，但从未发现有人知道如何使用VB.NET为VBA IDE构建外接程序。可能吗有人可以给我指出一个例子吗？

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?

推荐答案

可能需要使用IDTExtensibility2接口编写一个com插件，选择共享

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. 创建新的项目类库


2. 添加引用可扩展性，它应该在列表中。您可能需要下载适用于您的Office版本的PIA。 （也许是VSTO，但我对此不确定）


3. 再次将对 Microsoft.Vbe.Interop的引用添加到PIA中。


4. 在属性标签中选中注册Com Interop框。


5. 可选在调试设置标签中，将启动更改为外部程序，并输入以下路径： programfiles文件夹中的excel exe（如果打算用于excel），这是允许项目可调试的。


6. 可选在命令选项中添加一个进入工作表的条目或将在启动时使用宏显示插件对话框的word doc，对于开发而言，简化调试体验是很有意义的。例如 C：\vbe.xlsm


7. 可选还将启动路径设置为工作表目录，例如 C：\


8. 实现在可扩展性程序集中找到的接口 IDTExtensibility2。


9. 将此类称为连接（这只是一个首选项）


10. 使用以下属性

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）]

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

此处提供了一个实现入门，首先用您的AppName替换 YourAddinName，并为 YourGeneratedGuid创建一个向导。
您需要将Addin注册到正确的注册表位置，查看后面的注册表项以了解一个主意，并替换注册表项中的一些变量。

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

这是注册插件的注册表.key脚本，请注意，您需要更改某些设置才能注册它

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_USER\Software\Microsoft\VBA\VBE\6.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"

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

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.

侧面注意

这也很有帮助，可能会节省您几个小时的谷歌搜索时间。

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

'HKEY_CURRENT_USER\Software\Microsoft\VBA\6.0\Common
'FontFace=Courier New (STRING - Default if missing)
'FontHeight=10 (DWORD - Default if missing)                

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