VBA 参考库

VBA Reference Libraries

提问by user51498

I'm new to VBA and have been throwing together a small macro application for the Office. We've got about 80 users on essentially identical PC setups, and it will be accessed by all but a few users.

我是 VBA 新手，一直在为 Office 编写一个小型宏应用程序。我们有大约 80 个用户使用基本相同的 PC 设置，除了少数用户之外，所有用户都可以访问它。

I've been playing around with some automation of accessing web pages using the Web Services references, and I've also loaded the Microsoft Scripting Runtime references into the project. I attempted to run it on a test PC and it complained that there were missing references.

我一直在尝试使用 Web 服务引用访问网页的一些自动化，并且我还将 Microsoft Scripting Runtime 引用加载到项目中。我试图在测试 PC 上运行它，但它抱怨缺少引用。

I don't particularly want to go around 80 PCs and manually load the references.

我并不特别想在 80 台 PC 上手动加载引用。

My question, basically, is how should I manage the distribution of this macro-app to 80 odd users so as to ensure that the references will load every time for every user.

我的问题基本上是我应该如何管理这个宏应用程序向 80 多个用户的分发，以确保每次都为每个用户加载引用。

Thanks!

谢谢！

回答by Fionnuala

For the most part, late binding will solve problems with references in VBA, unless you have some unusual references. Most problems are caused by differences in library versions that can be overcome with late binding. With VBA, it is often recommended that you develop with early binding but release with late binding. The main disadvantage of late binding is changing built-in constants to values (speed is no longer the issue it used to be.)

在大多数情况下，后期绑定将解决 VBA 中的引用问题，除非您有一些不寻常的引用。大多数问题是由库版本的差异引起的，可以通过后期绑定来克服。使用 VBA，通常建议您使用早期绑定进行开发，但使用后期绑定进行发布。后期绑定的主要缺点是将内置常量更改为值（速度不再是以前的问题。）

So:

所以：

Dim fs As Object 'Instead of FileSystemObject '
Dim xl As Object 'Instead of Excel.Application '

Set fs=CreateObject("Scripting.FileSystemObject")
Set xl=CreateObject("Excel.Application")

'Value instead of built-in constant '
ForReading=2
Set f = fs.OpenTextFile("c:\testfile.txt", ForReading)

回答by Jayden

If you have references that your application depends on, that you know are not going to be on the target PCs, then I would strongly recommend you investigate some installer technology.

如果您有应用程序所依赖的参考资料，并且您知道这些参考资料不会出现在目标 PC 上，那么我强烈建议您研究一些安装程序技术。

Using the installer you should be able to install your macro, and install and register all appropriate references / libraries.

使用安装程序，您应该能够安装宏，并安装和注册所有适当的引用/库。

There are generally two flavours on windows, Windows Installer based technology and Script based technology.

Windows 上通常有两种风格，基于 Windows Installer 的技术和基于脚本的技术。

We use InstallShield for all of our deployment, although there are several options for you to use (there are several discussion on Stack Overflow).

我们将 InstallShield 用于我们的所有部署，尽管有几个选项供您使用（有几个关于 Stack Overflow 的讨论）。

Using windows installer technology, you can build MSI install files, which you are then able to deploy automatically using Group Policy.

使用 Windows 安装程序技术，您可以构建 MSI 安装文件，然后您可以使用组策略自动部署这些文件。

回答by Philippe Grondier

In addition to this answer, which is the bullet-proof solution to solve this kind of issue, but which is quite complex to implement, you can also write some code to be executed when your VBA application starts, checking the 'references' collection of the 'application' object. You can then check (1) if requested files (dll, ocx, tlb) are available on the computer and (2) if reference can be created (application.references.addFromFile ...).

除了这个答案，这是解决此类问题的防弹解决方案，但实现起来非常复杂，您还可以编写一些代码以在您的 VBA 应用程序启动时执行，检查“引用”集合“应用程序”对象。然后，您可以检查 (1) 请求的文件（dll、ocx、tlb）是否在计算机上可用以及（2）是否可以创建引用（application.references.addFromFile ...）。

Be careful: object declarations that might be 'reference dependent', such as:

小心：可能“依赖于引用”的对象声明，例如：

Dim cat as ADOX.catalog 

will raise a compilation bug if the reference is not active when the corresponding module is 'compiled'. I then advise you to isolate your 'reference checking procedure' in a startup module (equivalent to an 'autoexec') which deals only with VBA and basic application objects. Check it with your Help Files (Example: in Access, default references that can be used without external references are VBA, Access and DAO).

如果在“编译”相应模块时引用未激活，则会引发编译错误。然后，我建议您将“引用检查程序”隔离在仅处理 VBA 和基本应用程序对象的启动模块（相当于“autoexec”）中。检查您的帮助文件（例如：在 Access 中，无需外部引用即可使用的默认引用是 VBA、Access 和 DAO）。

EDIT:

编辑：

in case external references depend on other software package and (1) cannot be distributed with a MSI file or (2) can have multiple versions, I think the 'references.addFromFile' is the only solution that can apply. Example:

如果外部引用依赖于其他软件包并且 (1) 不能与 MSI 文件一起分发或 (2) 可以有多个版本，我认为“references.addFromFile”是唯一可以应用的解决方案。例子：

• You have an VBA/Access runtime client app that needs to refer to Word (msword.olb file).
• For licensing issues, you cannot freely distribute this file with your msi pack
• the olb file can be either the 'XP version or a newer one

• 您有一个需要引用 Word（msword.olb 文件）的 VBA/Access 运行时客户端应用程序。
• 对于许可问题，您不能随 msi 包自由分发此文件
• olb 文件可以是“XP 版本”或更新版本

Our solution is to have 2 tables on the client Access file. One lists all the references that have to be checked or added at startup time (Word will be one of them), and the other one lists all the possible locations of the file (depending if the user has the 'office11' version or a newer one), with a one to many relations between the 2 tables.

我们的解决方案是在客户端访问文件中有 2 个表。一个列出在启动时必须检查或添加的所有引用（Word 将是其中之一），另一个列出文件的所有可能位置（取决于用户使用的是“office11”版本还是更新版本）一个），两个表之间是一对多的关系。

So, the best strategy could be a mix between msi packs and management through code:

因此，最好的策略可能是通过代码混合 msi 包和管理：

• msi is great for distributing independant dll's or other files that are totally 'embedded' in your app, such as activeX controls (like scanners controls, report or file viewers, etc)
• code is the best solution where your app will have to communicate with other applications (word, excel, outlook, etc) that can exist in different versions on your user's machines.

• msi 非常适合分发完全“嵌入”在您的应用程序中的独立 dll 或其他文件，例如 activeX 控件（如扫描仪控件、报告或文件查看器等）
• code 是最好的解决方案，您的应用程序必须与其他应用程序（word、excel、outlook 等）进行通信，这些应用程序可能存在于用户计算机上的不同版本中。

回答by casperOne

Instead of having the documents expose the functionality, make it an add-in for Office (the suite, or the individual apps, your choice). This way, you don't have to deal with references.

与其让文档公开功能，不如让它成为 Office 的加载项（套件或单个应用程序，由您选择）。这样，您就不必处理引用。

Then, just distribute an install package with the add-in which registers the components and registers the add-ins with the appropriate Office apps.

然后，只需分发带有加载项的安装包，该加载项会注册组件并将加载项注册到相应的 Office 应用程序。

VB6 might be a good idea here, given it's similarity to VBA.

考虑到 VB6 与 VBA 的相似性，VB6 在这里可能是一个好主意。