Excel 不保存 VBA 引用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16548861/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel isn't saving VBA reference
提问by nvuono
I created an Excel .xlsm file with a bunch of functionality in some VBA Modules/Classes and now I've decided to separate out the code because it will be shared across 4 different sites.
我创建了一个 Excel .xlsm 文件，其中包含一些 VBA 模块/类中的一系列功能，现在我决定将代码分开，因为它将在 4 个不同的站点之间共享。
I saved the vba_code.xlsm to a shared location and created my 4 different siteXYZ.xlsm files.
我将 vba_code.xlsm 保存到共享位置并创建了 4 个不同的 siteXYZ.xlsm 文件。
In each siteXYZ.xlsm file I would go to the "Tools | References" menu in the VBA editor and add a reference to the shared vba_code.xlsm at a shared file location \share_location\fileLocation\vba_code.xlsm
在每个 siteXYZ.xlsm 文件中，我将转到 VBA 编辑器中的“工具 | 引用”菜单，并在共享文件位置 \share_location\fileLocation\vba_code.xlsm 添加对共享 vba_code.xlsm 的引用
At this point, I tested out the functions in siteXYZ.xlsm and everything would work fine.
此时，我测试了 siteXYZ.xlsm 中的功能，一切正常。
However, every time I saved siteXYZ.xlsm, closed Excel and then reopened the file it would lose the reference to my vba_code.xlsm file.
但是，每次我保存 siteXYZ.xlsm、关闭 Excel 然后重新打开文件时，它都会丢失对我的 vba_code.xlsm 文件的引用。
How can I keep the VBA references saved with my siteXYZ.xlsm file?
如何将 VBA 引用保存在我的 siteXYZ.xlsm 文件中？
采纳答案by Charles Williams
The usual method of achieveing this is by saving your vba_code.xlsm as an addin (XLA or XLAM) and storing it in the shared location, then adding the addin to Excel in your 4 different sites.
You can also extend this approach by using your own Addin Loader instead of Excel's.
There is a working example of an Addin Loader at
http://www.decisionmodels.com/downloads.htm
实现此目的的常用方法是将您的 vba_code.xlsm 保存为插件（XLA 或 XLAM）并将其存储在共享位置，然后将该插件添加到您的 4 个不同站点的 Excel 中。
您还可以使用自己的 Addin Loader 而不是 Excel 来扩展这种方法。http://www.decisionmodels.com/downloads.htm 上
有一个 Addin Loader 的工作示例
回答by nvuono
After spending hours searching for an answer and trying various methods such as adding a digital signature to the vba_code.xlsm file and trying to programmatically add the reference on Workbook_open I found a forum post describing the problem:
在花了数小时寻找答案并尝试了各种方法（例如将数字签名添加到 vba_code.xlsm 文件并尝试以编程方式在 Workbook_open 上添加参考）后，我发现了一个描述问题的论坛帖子：
My siteXYZ.xlsm file had no VBA code or macros defined within it so Excel refused to save the VBA Project and as a result did not save the VBA Reference to vba_code.xlsm.
我的 siteXYZ.xlsm 文件中没有定义 VBA 代码或宏，因此 Excel 拒绝保存 VBA 项目，结果没有保存对 vba_code.xlsm 的 VBA 引用。
The solution was simple:
解决方法很简单：
Add ANYVBA code to the siteXYZ.xlsm and save it.
将任何VBA 代码添加到 siteXYZ.xlsm 并保存。
I just double-clicked ThisWorkbook under the VBA editor and added a function to Workbook_open that doesn't do anything:
我只是在 VBA 编辑器下双击了 ThisWorkbook 并向 Workbook_open 添加了一个不执行任何操作的函数：
Private Sub Workbook_open()
End Sub
回答by Chad Crowe
Following @nvuono
关注@nvuono
You need to add some kind of module/reference to the excel file for it to save the references you have added.
您需要为 excel 文件添加某种模块/引用以保存您添加的引用。
Private Function addJunkModuleToGetReferencesToSave(ByRef wb As Workbook)
Set new_module = wb.VBProject.VBComponents.Add(vbext_ct_ClassModule)
new_module.name = "Junk"
new_module.CodeModule.AddFromString ""
End Function
