vba 无法关闭工作簿，因为 Excel 2010 认为它仍在被引用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8088673/
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
Cannot close workbook because Excel 2010 thinks it is still being referenced
提问by mischab1
I have an Excel 2010 macro application that is made up of several workbook files.
我有一个由多个工作簿文件组成的 Excel 2010 宏应用程序。
FileName (ProjectName)
VFApp.xlsm (VFAppVBA)
VFReportClasses.xlsm (VFReportVBA)
VFImportClasses.xlsm (VFImportVBA)
VFDataClasses.xlsm (VFDataVBA)
ZShared.xlsm (ZSharedVBA)
FileName (ProjectName)
VFApp.xlsm (VFAppVBA)
VFReportClasses.xlsm (VFReportVBA)
VFImportClasses.xlsm (VFImportVBA)
VFDataClasses.xlsm (VFDataVBA)
ZShared.xlsm (ZSharedVBA)
- Shared is referenced by all the other workbooks.
- Data is referenced by App, Report, & Import.
- Report and Import are both referenced by App.
- Shared 被所有其他工作簿引用。
- 数据由 App、Report 和 Import 引用。
- Report 和 Import 都被 App 引用。
I'm trying to close all the referenced files when the App file is closed. This works for everything except ZShared.xlsm. For it I get the 'This workbook is currently referenced by another workbook and cannot be closed.'error.
当 App 文件关闭时，我试图关闭所有引用的文件。这适用于除 ZShared.xlsm 之外的所有内容。为此，我得到“此工作簿当前被另一个工作簿引用，无法关闭”。错误。
The only semi-helpful info I found online was http://support.microsoft.com/kb/211856and http://support.microsoft.com/default.aspx?scid=kb;en-us;159794. I added some logging to all the classes in ZShared. All instances of it's classes are getting correctly terminated and I don't have any workbook objects pointing to ZShared.
我在网上找到的唯一半有用的信息是http://support.microsoft.com/kb/211856和http://support.microsoft.com/default.aspx?scid=kb;en-us;159794。我向 ZShared 中的所有类添加了一些日志记录。它的类的所有实例都正确终止，我没有任何指向 ZShared 的工作簿对象。
I even tried renaming the files like (http://www.allquests.com/question/3996335/Upgrading-Add-ins-from-XL2003-to-XL2007.html).
我什至尝试重命名文件（http://www.allquests.com/question/3996335/Upgrading-Add-ins-from-XL2003-to-XL2007.html）。
The weird thing is, if I manually open VFDataClasses.xlsm before opening VFApp.xlsm, then ZShared will close out correctly without error. If I just open VFApp.xlsm and let it open all the other referenced files, I get the above error.
奇怪的是，如果我在打开 VFApp.xlsm 之前手动打开 VFDataClasses.xlsm，那么 ZShared 将正确关闭而不会出错。如果我只是打开 VFApp.xlsm 并让它打开所有其他引用的文件，则会出现上述错误。
Anybody have any suggestions for figuring out why Excel thinks the workbook is still referenced?
有人对弄清楚为什么 Excel 认为工作簿仍然被引用有任何建议吗？
Clairification:
澄清：
- Each workbook references the others via Tools -> References.
- ZShared consists of public classes and modules with public subs, functions, and constants as well as 3 global variables.
- I'm not, that I know of, doing anything to reference ZShared's workbooks or other Excel objects.
- The global variables are in ZShared so I don't have to recreate them in each App that references ZShared. They are handled via public
InitializeGlobalClassesandTerminateGlobalClasses - I added an
m_InstanceIdto every class and debug logging to allClass_InitializeandClass_Terminateso I am 100% certain that all instances of the classes are being terminated correctly.
- 每个工作簿通过工具 -> 参考引用其他工作簿。
- ZShared 由公共类和模块组成，具有公共子、函数和常量以及 3 个全局变量。
- 据我所知，我没有做任何事情来引用 ZShared 的工作簿或其他 Excel 对象。
- 全局变量在 ZShared 中，所以我不必在每个引用 ZShared 的应用程序中重新创建它们。它们通过公共
InitializeGlobalClasses和TerminateGlobalClasses - 我
m_InstanceId向每个类添加了一个并调试日志记录到所有类Class_Initialize，Class_Terminate因此我 100% 确定类的所有实例都被正确终止。
I wonder if it is possible Excel is creating some hidden reference to the workbook based on using a Withstatement or some such that isn't being cleared correctly. On the other hand, it makes no sense that this would only happen if ZShared was not already open when the App started.
我想知道 Excel 是否可能基于使用With语句或某些未正确清除的语句创建对工作簿的一些隐藏引用。另一方面，只有在应用程序启动时 ZShared 尚未打开时才会发生这种情况是没有意义的。
采纳答案by mischab1
I never found an answer to why Excel thinks the file is still referenced. As a work-around I ended up doing 2 things.
我从来没有找到为什么 Excel 认为该文件仍被引用的答案。作为一种解决方法，我最终做了两件事。
- Hid the ZShared workbook in Excel so normal users can't see it.
- Set ZShared to Read Only so I can replace the file with an updated version even if people still have it open.
- 在 Excel 中隐藏 ZShared 工作簿，因此普通用户无法看到它。
- 将 ZShared 设置为只读，这样我就可以用更新的版本替换文件，即使人们仍然打开它。
If anyone knows of a way to peer into Excel's internals to see why its reference garbage collection is not working, I'd love to know.
如果有人知道一种查看 Excel 内部结构以了解其引用垃圾收集为何不起作用的方法，我很想知道。
