我曾经在其他地方问过这个问题,但从来没有找到人知道如何使用VB.NET为VBA IDE构建插件。这是否是可能的?有人可以给我提供一个示例吗?
如果需要使用IDTExtensibility2接口编写COM Add-in,可以从新项目中选择共享Add-in项目模板。
编辑
否则,如果要从头创建此Add-in,则需要执行以下操作:
[ComVisible(true), Guid("YourGeneratedGuid"), ProgId("YourAddinName.Connect")]
这是一个起点实现,首先将“YourAddinName”替换为您的AppName,并为“YourGeneratedGuid”创建一个GUID。您需要将Add-in注册到正确的注册表位置,请查看下面的注册表键以了解大致情况,并在注册表键中替换一些变量。
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
这是注册Addin的注册表.key脚本,注意您需要更改一些设置才能正确注册它。
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" 标记必须包含大括号 {},并且与上面的 "attrib" 中的 GUID 相同;标记 "YourAssemblyNameFullTypeName" 必须是程序集的全名;标记 "YourAddinName.Connect" 必须与上面的 "attrib" 中设置的 ProgId 相同。
另外提示:
还发现这个有用,可能会为您节省几个小时的谷歌搜索。
'HKEY_CURRENT_USER\Software\Microsoft\VBA\6.0\Common
'FontFace=Courier New (STRING - Default if missing)
'FontHeight=10 (DWORD - Default if missing)
很遗憾,almog.ori的方法对我没有用。这是我为了帮助未来的人们而创建的版本:
创建一个名为“VBEAddIn”的C#或VB.NET类库项目。
使用“项目”,“添加引用...”菜单,“浏览”选项卡,将以下Interop程序集添加为项目的引用。
Extensibility (C:\Program Files\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Common\Extensibility.dll) - 如果它不在那里,请尝试 C:\Program Files (x86)\ ,如果你使用的是 x64 电脑。
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)
向您的项目添加一个类,并使用以下代码:
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#:
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
}
}
在项目的“属性”窗口中:
在“应用程序”选项卡中,确保“程序集名称”和“根命名空间”均设置为“VBEAddIn”。
在“编译”选项卡中,确保“注册COM互操作”复选框已被选中。我们不会使用适当的regasm.exe工具手动注册程序集以进行COM互操作。但请注意,“注册COM互操作”复选框仅会将插件dll注册为32位COM库,而不是64位COM库。
在“编译”选项卡中,“高级编译选项”按钮上,确保“目标CPU”组合框设置为“AnyCPU”,这意味着程序集可以作为64位或32位执行,取决于加载它的.NET Framework。
在“签名”选项卡中,确保未选中“对程序集进行签名”。
接下来添加注册表键,请将下面的代码片段保存为带有.reg扩展名的ASCI文件,并双击该文件将值添加到注册表中。
重要提示:在执行reg文件之前,更改路径:“CodeBase”=“file:///C:\Dev\VBEAddIn\VBEAddIn\bin\Debug\VBEAddIn.dll”
Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Microsoft\VBA\VBE\6.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"
如果出现以下错误:
'VBEAddIn'无法加载。
将其从可用的附加程序列表中删除?
那么很可能是您没有更改路径 "CodeBase"="file:///C:\Dev\VBEAddIn\VBEAddIn\bin\Debug\VBEAddIn.dll"。
并检查注册表中的CodeBase键(如果不存在,请添加带有CodeBase的字符串regkey):
然后关闭Office应用程序,再次从Visual Studio中构建VBE AddIn,打开Office(Excel,Outlook,Word等),Alt+F11,附加组件菜单>附加组件管理器并选择AddIn,选中“已加载/未加载”。
最后的技巧是解决此问题的方法:
如果以上仍然失败,请关闭Office应用程序,转到Visual Studio,项目属性>生成选项卡>选中COM互操作注册>构建解决方案,并打开Office Add In> Alt + F11> AddIns菜单> AddIn Manager并单击"已加载/未加载"
本答案使用了Carlo's Quintero (MZTools)的一些信息,经过了修改,参考:http://www.mztools.com/articles/2012/MZ2012013.aspx
我也发现这个参考资料对于从C#或VB.NET制作VBA DLL非常有帮助:
创建一个新的C#(或VB.Net)项目,并选择类库作为模板类型。
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可见。
从Access VBA编辑器引用类型库。
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
还要注意项目的 GUID(在 C# 的 AssemblyInfo.cs 文件中)与“Connect”类的 GUID 不同。
如果 GUID 相同,则在检查时会导致“无法转换为类型库”的错误:项目属性 > 生成选项卡 > 注册 COM 互操作。