vba 设置已打开的工作簿时下标超出范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18831127/
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
Subscript out of range when setting workbook that is already open
提问by MattB
I'm writing a macro to select and work with a workbook that is already open.
我正在编写一个宏来选择和使用已经打开的工作簿。
After obtaining the name through a userform, when I try to set the workbook, or interact with it otherwise, I get a subscript out of range error.
通过用户表单获取名称后，当我尝试设置工作簿或与之交互时，出现下标超出范围错误。
I have confirmed the workbook is open, and that the ExcelAp object is connecting to the right instance of Excel.
我已确认工作簿已打开，并且 ExcelAp 对象正在连接到正确的 Excel 实例。
Option Explicit
....
Public Sub TerminalOpen()
Dim WorkbookOpen As VbMsgBoxResult
Dim ExcelAp As Excel.Application
Dim FedExWkbk As Excel.Workbook
....
Else
Set ExcelAp = Excel.Application
WorkbookSelection.Show
WkbkFedExTracking = Trim(VBA.Left(WkbkFedExTracking, InStr(1, WkbkFedExTracking, ".", vbTextCompare) - 1))
Set FedExWkbk = ExcelAp.Workbooks(WkbkFedExTracking) <<<<< This is where the error occurs
FedExWkbk.Activate
End If
I tried late-binding the ExcelAp. This approach seems to have worked in other instances, but not in this one.
我尝试后期绑定 ExcelAp。这种方法似乎在其他情况下有效，但在本例中无效。
EDIT: The workbook name is stored in the WkbkFedExTracking string. It is cleaned up to just the name of the workbook without the .xlsx portion. Also, on debug I've checked that against the name of the open workbook, and it is correct.
编辑：工作簿名称存储在 WkbkFedExTracking 字符串中。它被清理为没有 .xlsx 部分的工作簿名称。另外，在调试时，我已经根据打开的工作簿的名称检查了它，它是正确的。
回答by MattB
This issue was resolved. It was a combination of a dropped .xlsx extension on the index name of the workbook as well as a stray instance of excel due to a poorly placed application.Visible = true line. See the comments on the original post for more details.
此问题已解决。它是工作簿索引名称上删除的 .xlsx 扩展名以及由于 application.Visible = true 行放置不当而导致的 excel 杂散实例的组合。有关更多详细信息，请参阅原始帖子的评论。
