使用 VB.NET 为 VBA IDE 构建插件
我在其他地方问过这个问题,但从未发现有人知道如何使用 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?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(6)
您可能需要使用 IDTExtensibility2 接口编写 com 插件,从新项目中选择共享插件项目模板。
编辑
否则,要从头开始创建此插件,您将需要执行以下操作:
这是一个帮助您入门的实现,首先将“YourAddinName”替换为您的AppName,并为“YourGenerateGuid”创建一个Guid。
您需要将插件注册到正确的注册表位置,查看后面的注册表项以了解情况,还需要替换注册表项中的一些变量。
这是用于注册插件的注册表 .key 脚本,请注意,您将需要更改一些设置才能正确注册它。
注意 标记“YourGenerateGuid”必须包含大括号 {},并且与上面 attrib 中的 Guid 相同,标记“YourAssemblyNameFullTypeName”必须是程序集全名,标记“YourAddinName”必须是程序集全名。 Connect”必须与上面属性中设置的 ProgId 相同。
旁注
还发现这很有帮助,可能会节省您几个小时的谷歌搜索时间。
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:
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.
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.
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.
不幸的是,almog.ori 的步骤对我不起作用。这是我的版本,以帮助将来的人们:
创建一个名为“VBEAddIn”的 C# 或 VB.NET 类库项目
使用“项目”、“添加引用...”菜单、“浏览”选项卡添加以下 Interop 程序集作为对项目的引用。
扩展性 (C:\Program Files\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Common\Extensibility.dll) - 如果不存在,请尝试 C:\Program Files (x86)\ if您使用的是 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)
使用以下代码将类添加到项目中:
VB.Net:
C# :
在项目的“项目属性”窗口中:
在“应用程序”选项卡中,确保程序集名称和根命名空间都设置为“VBEAddIn”。
在“编译”选项卡中,确保选中“注册 COM 互操作”复选框。我们不会费心使用适当的 regasm.exe 工具手动注册 COM Interop 程序集。但请注意,“注册 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
如果出现此错误:
您可能没有更改路径“CodeBase”=“file:///C:\Dev\VBEAddIn\VBEAddIn\bin\Debug\VBEAddIn.dll”
并检查 CodeBase 密钥是否在注册表中(添加一个字符串regkey 与代码库(如果不存在):
然后关闭 Office 应用程序,从 Visual Studio 再次构建 VBE AddIn,打开 Office(Excel、Outlook、Word 等)并按 Alt + F11,插件菜单>插件管理器并选择插件并勾选已加载/已卸载。
解决此问题的最后一个技巧:
如果仍然失败,请关闭 Office 应用程序,转到 Visual Studio,“项目属性”>“构建选项卡>勾选“注册 COM 互操作”>构建解决方案并打开 Office Add In > Alt + F11 >插件菜单>插件管理器并单击“已加载/已卸载”。
这个答案使用了我修改过的 Carlo's Quintero (MZTools) 的一些信息,参考: http ://www.mztools.com/articles/2012/MZ2012013.aspx
Unfortunately almog.ori's steps didn't work for me. Here is my version to aid people in the future:
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)
Add a class to your project with the following code:
VB.Net:
C#:
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.
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.
Important Note: Before you execute the reg file, change the path: "CodeBase"="file:///C:\Dev\VBEAddIn\VBEAddIn\bin\Debug\VBEAddIn.dll"
If you get this error:
Its likely you didn't change the path "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):
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.
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.
This answer uses some info from Carlo's Quintero (MZTools) that I've modified, Ref: http://www.mztools.com/articles/2012/MZ2012013.aspx
我还发现此参考对于从 C# 或 VB.NET 制作 VBA DLL 很有帮助:
创建一个新的 C#(或 VB.Net)项目并选择“类库”作为模板类型。
配置项目属性以使其 COM 可见。
在 VBA 代码中使用 DLL。
由 GeeksEngine.com 提供
I also found this reference to be helpful in making a VBA DLL from C# or VB.NET:
Create a new C# (or VB.Net) project and select Class Library as the template type.
Configure project properties to make it COM visible.
Use the DLL in your VBA code.
Made available by GeeksEngine.com
我想您可以从 VBA 代码中调用 .NET DLL(我自己从未真正这样做过)。只需创建一个 VB 类库项目并创建一个在 VBA 中使用的 DLL。
经过快速谷歌搜索后,您似乎需要在“项目属性”->“构建”下设置“Register for Com Interop”= True,但就像我说的,我以前从未真正尝试过这一点。
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.
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.
还要注意项目的 Guid(如果是 assamblyInfo.cs 中的 c#)与“Connect”类的 Guid 不同。
具有相同的 Guid 会导致“无法转换为类型库” - 检查时出错:Project Properties >构建选项卡>注册 COM 互操作
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".
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
我完全同意杰里米·汤普森上面的回答。然而,我花了几个小时试图让示例 VBEAddin 工作。直到我在 Connect 类代码中注意到 ProgID 属性为“VBEAddInVB.Net.Connect”,但用于创建 .reg 文件的注册表文本中,所有对程序名称的引用均为“VBEAddIn.Connect”。因此,我尝试将 Connect 类中的 ProgId 属性更改为“VBEAddIn.Connect”,重新构建它,并且成功了!我认为名称必须一致。我希望这可以节省其他人一些时间。
I agree wholeheartedly with Jeremy Thompson’s answer above. However, I spent hours trying to get the example VBEAddin to work. Until I noticed in the Connect Class code that the ProgID attribute was "VBEAddInVB.Net.Connect" but the Registry text that was meant to create a .reg file, had all the references to the program name as “VBEAddIn.Connect”. So I tried changing the ProgId Attribute to “VBEAddIn.Connect” in the Connect Class, rebuilt it, and it worked! I think the names have to be consistent. I hope this saves others some time.