在 VBA 用户窗体中关闭打开的工作簿时出错
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10612502/
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
Error when closing an opened workbook in VBA Userform
提问by Prashant Kumar
In a subroutine, I want to open a workbook, do some reading from it, and close it.
For some reason, I get an error:
在一个子程序中，我想打开一个工作簿，从中读取一些信息，然后关闭它。
出于某种原因，我收到一个错误：
Run-time error '1004':
Method 'Close' of object _Workbook failed
I have identified a minimal code snippet to reproduce the problem.
Create a fresh excel file. In it, create a Userform. On that, create a Command Button with the following Click event code:
我已经确定了一个最小的代码片段来重现该问题。
创建一个新的 excel 文件。在其中创建一个用户表单。在此基础上，使用以下 Click 事件代码创建一个命令按钮：
Private Sub CommandButton1_Click()
Dim filename As String
Dim opened_workbook As Workbook
filename = Application.GetOpenFilename() ' User selects valid Excel file
Set opened_workbook = Application.Workbooks.Open(filename)
' File operations would occur here
opened_workbook.Close ' Exception thrown here
MsgBox "If you got here, it worked!"
Unload Me
End Sub
What really perplexes me is that this error doesn't happen with the same code when the Command button is not on a userform (on a plain button straight on the worksheet).
真正让我感到困惑的是，当命令按钮不在用户窗体上（在工作表上的普通按钮上）时，相同的代码不会发生此错误。
I don't even know what else to report or where to look to explain this behavior (besides StackOverflow!). I'm writing VBA using Excel for Mac 2011 and can move to Windows Excel 2010 if it makes a difference.
我什至不知道还有什么要报告的，也不知道去哪里解释这种行为（除了 StackOverflow！）。我正在使用 Excel for Mac 2011 编写 VBA，如果有所不同，可以转移到 Windows Excel 2010。
回答by Siddharth Rout
Yes, in Excel 2011, it is a bug (Undocumented - I haven't found a documentation for it yet). You have to slightly modify the code. Try this
是的，在 Excel 2011 中，这是一个错误（未记录 - 我还没有找到它的文档）。您必须稍微修改代码。尝试这个
Private Sub CommandButton1_Click()
Dim filename As String
Dim opened_workbook As Workbook
filename = Application.GetOpenFilename() ' User selects valid Excel file
Set opened_workbook = Application.Workbooks.Open(filename)
Unload Me
opened_workbook.Close
MsgBox "If you got here, it worked!"
End Sub
回答by Hopploppa
I had this exact problem on Excel 11 on Mac (Worked fine Excel 2013 on Windows), only the error occurred in a module sub that was called from the UserForm. If somebody (like me) is trying to use the workbook.close method from a sub/function in a module (or another location) that is not inside the UserForm itself you can't use 'Me'. 'Me' is only usable within the UserForm code itself.
我在 Mac 上的 Excel 11 上遇到了这个确切的问题（在 Windows 上运行良好的 Excel 2013），只有错误发生在从用户窗体调用的模块子中。如果有人（像我一样）试图从不在用户窗体内部的模块（或其他位置）中的子/函数中使用 workbook.close 方法，则不能使用“我”。“我”只能在用户窗体代码本身中使用。
Instead of 'Unload Me' use the unload function and the name of your UserForm.
使用卸载函数和用户窗体的名称代替“卸载我”。
Unload UserFormName
