是否有一个地方可以存储所有 Microsoft Office 产品都可以访问的 VBA 代码..? [英] Is there a place to store VBA code that's accessible to all Microsoft Office products..?

问题描述

是否有一种方法可以存储所有 Microsoft Office 产品通用且可访问的 VBA 源代码?我在 Access 和 Excel 中使用了多种函数，有时甚至在 Word 中使用.我经常修改和添加功能和模块.在项目之间复制、粘贴和维护代码会变得很乏味，我不可避免地会忽略一些东西.

Is there a way of storing VBA source code that's common and accessible to all Microsoft Office products..? I have a variety of functions which I use in both Access and Excel, and sometimes even Word. I modify and add to the the functions and modules frequently. The copying, pasting, and maintaining of code between projects can get tedious, and I inevitably overlook something.

我知道我可以做一些事情，比如在 VB6 或 DotNet 中编写类/ocx/addin，编译它，然后在我的 VBA 项目中引用它，但我希望有更简单的东西.

I know I could do something like write a class/ocx/addin in VB6 or DotNet, compile it, and reference it in my VBA projects, but I was hoping for something more simple.

如果有一种方法可以将 VBA 代码存储在独立文件中，并使用 VBA IDE 以某种独立模式打开它们，那就太好了，但我知道 IDE 不能那样工作......据我所知.

If there was a way to store VBA code in independent files, and open them with the VBA IDE in a standalone mode of some sort, that would be outstanding, but I know the IDE doesn't work that way...as far as I know.

推荐答案

我知道我可以做一些事情，比如在 VB6 或 DotNet 中编写类/ocx/addin，编译它，然后在我的 VBA 项目中引用它，但我希望有更简单的东西.

I know I could do something like write a class/ocx/addin in VB6 or DotNet, compile it, and reference it in my VBA projects, but I was hoping for something more simple.

不幸的是，这是您最好的选择.

Unfortunately that's your best bet.

如您所知，VBA 代码是托管在宿主文档中的:VBA 加载项仍与其宿主文档(.xlam 等)相关联，因此无法与其他宿主共享.

As you know VBA code is hosted, wrapped-up in a host document: a VBA add-in is still tied to its host document (.xlam, etc.), and as such can't be shared with another host.

在 VBA 领域中，意味着在 VBA 项目之间共享，是类型库 - 与 VBA 项目分开编译的东西，以及参考em> 来自任意数量的项目.

In VBA-land what's meant to be shared between VBA projects, is type libraries - things you compile separately from the VBA project, and reference from as many projects as you like.

如果您有 VB6 IDE，则可以编译 VBA 项目可以引用的 32 位 DLL.问题是它不适用于 64 位主机 - 解决方案是使用您选择的 .NET 语言编写的 .NET 类型库，并使 COM 可见.请注意，COM 兼容性确实限制了您可以在 API 中公开的内容:例如，您不能公开泛型，并且方法重载看起来很奇怪.

If you have a VB6 IDE, you can compile a 32-bit DLL that VBA projects can reference. The problem is that it won't work with 64-bit hosts - the solution is a .NET type library, written in the .NET language of your choice, made COM-visible. Note that COM-compatibility does restrict what you can expose in your API: for example you can't expose generics, and method overloads will look weird.

如果类型库不是您希望探索的途径，那么您的选择就相当有限，而且 IMO 不是最佳选择.

If a type library isn't an avenue you wish to explore, then your choices are rather limited, and IMO sub-optimal.

IMO 唯一可以工作"的是一些公共文件夹中的一堆导出的代码文件，需要使用该代码的 VBA 项目需要从字面上导入这些代码文件.这里的风险是，如果您进行任何更改，使用该代码的未修改版本的其他 VBA 项目将不会看到"这些更改，IOW 通过这样做，您将需要修复错误的次数乘以使用该代码的项目数量.

IMO the only thing that can "work" is a bunch of exported code files in some common folder, and the VBA projects that need to use that code need to literally import these code files. The risk here being, that if you make any changes, other VBA projects using an unmodified version of that code will not "see" these changes, IOW by doing that you're multiplying the number of times you need to fix a bug by the number of projects using that code.

或者您可以使用一些代码使用 VBIDE 可扩展性类型库来确保导入的模块集始终与该公共位置中的导出文件完全匹配.

Or you could have some code that uses the VBIDE extensibility type library to ensure the set of imported modules always match exactly with the exported files in that common location.

这篇关于是否有一个地方可以存储所有 Microsoft Office 产品都可以访问的 VBA 代码..?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！