在另一个打开的工作簿中访问工作表时下标超出范围

Question

我想引用第一个打开的工作簿。 打开了 2 个工作簿：一个正在调用要执行的宏，另一个工作簿包含该宏。不知何故，代码经常运行流畅。但有时，在访问第一个打开的工作簿中的数据时会出现引用错误："下标超出范围"。

在此行上，发生错误：

Set mastersheet = Workbooks(1).Sheets("Setting")

因此，集合中应该有两个工作簿。我在监督什么？请记住，第一个打开的工作簿没有固定的名称，因此打开的工作簿的名称会更改。第二个工作簿(包含要执行的宏的工作簿(不会更改其名称。

Answer 1

遗憾的是，您没有提供更多代码或有关宏所在的代码"容器"类型的任何信息。以下解决方案假定宏位于工作表或工作簿代码容器中(例如，VBA 编辑器中的Sheet1或ThisWorkbook(。

可以从代码容器中获取工作簿。如果代码位于工作表代码容器中，请使用Me.Parent。如果是ThisWorkbook请使用Me.这些容器实际上是表示工作表/工作簿对象的类。所以Me指的是那个对象。工作表的父级是其工作簿。

因此，Workbook对象将设置为其容器工作簿。然后，打开的工作簿在For...Each中循环，并测试工作簿是否与代码容器工作簿相同，或者是否为另一个工作簿。如果是另一个，则退出循环。Debug.Print显示结果(两个不同的名称(，并演示如何继续使用单独的工作簿对象。

Sub GetOtherWorkbook()
Dim wbWithMacro As Workbook
Dim wbOther As Workbook, wb As Workbook
Set wbWithMacro = Me.Parent 'Assumes macro is in a "Sheet" code container
'Set wbWithMacro = Me   'Assumes macro is in "ThisWorkbook" code container
For Each wb In Workbooks
If Not wb Is Me Then
Set wbOther = wb
Exit For
End If
Next
Debug.Print wbWithMacro.Name, wbOther.Name
End Sub

Answer 2

我建议以下方法来查找Setting工作表：

Option Explicit
Public Sub FindSettingWorksheet()
Dim MasterSheet As Worksheet
Dim wb As Workbook
For Each wb In Workbooks 'loop through all open workbooks
On Error Resume Next 'stop error reporting
Set MasterSheet = wb.Worksheets("Setting") 'if this throws an error it's the wrong workbook
On Error GoTo 0 're-enable error reporting
If Not MasterSheet Is Nothing Then Exit For 'if we found the setting worksheet we can exit/stop
Next wb

If Not MasterSheet Is Nothing Then 'test if we found it
Debug.Print MasterSheet.Name
Else
Debug.Print "Settings not found"
End If
End Sub