通过将位于工作簿中任何位置的表的名称与变量一起传递来创建ListObject

Question

以下是我在Excel中使用VBA的场景。我有一个表(table01(，它包含工作簿中所有表的名称。我正在尝试使用ListObjects((引用Table01中的一个表，以便对该表执行操作。我在下面的行中得到一个调试错误#9。我在错误线上做错了什么？

这是我的简化代码：

---

Dim Table01 as ListObject 'list of all tables in workbook 
Dim Table02 as ListObject 'table selected from Table01 
Dim TableName as String 'name of table02 as listed in Table01 
Set Table01 = ActiveSheet.ListObjects("tTablesDetails")
TableName = Table01.TableList.DataBodyRange(SomRowNumber, SomeColumnNumber).Text
MsgBox (TableName) 'Shows table name I expected       
Set Table02 = ActiveSheet.ListObjects(TableName) 
'DEBUG ERROR 9 IS HERE
...rest of code

Answer 1

我不知道.TableList是什么。它不是ListObject AFAIK的方法/属性。您肯定需要声明someRowNumber、someColumnNumber的值，因为该范围中没有0,0。对于下面的示例，假设选定的表与Table01在同一张表中。如果你想引用其他工作表中的表并将其拉入(或者循环工作簿中的所有ListObjects并按名称测试；假设名称不重复(，你可能需要另一列来容纳父工作表，这样你就有了

Set Table02 = Worksheets("someSheetNamePulledFromTable01").ListObjects(TableName)

VBA:

Option Explicit
Public Sub test()
Dim Table01 As ListObject                    'list of all tables in workbook
Dim Table02 As ListObject                    'table selected from Table01
Dim TableName As String                      'name of table02 as listed in Table01
Dim someRowNumber As Long, someColumnNumber As Long
someRowNumber = 1: someColumnNumber = 1
Set Table01 = ActiveSheet.ListObjects("tTablesDetails")
TableName = Table01.DataBodyRange(someRowNumber, someColumnNumber).Text
MsgBox (TableName)                           'shows table name i expected
Set Table02 = ActiveSheet.ListObjects(TableName) 'DEBUG ERROR 9 IS HERE
End Sub

---

完全避免使用Activesheet，并始终使用明确的工作表名称。也许在右边的下一列中有ListObject的父工作表名称：

Option Explicit
Public Sub test()
Dim Table01 As ListObject
Dim Table02 As ListObject
Dim TableName As String, SheetName As String
Dim someRowNumber As Long, someColumnNumber As Long
someRowNumber = 1: someColumnNumber = 1
Set Table01 = ThisWorkbook.Worksheets("Sheet1").ListObjects("tTablesDetails") '<=avoid Activesheet reference and use explicit sheet name
TableName = Table01.DataBodyRange(someRowNumber, someColumnNumber).Text
SheetName = Table01.DataBodyRange(someRowNumber, someColumnNumber + 1).Text
MsgBox (TableName)                           'shows table name i expected
Set Table02 = ThisWorkbook.Worksheets(SheetName).ListObjects(TableName)
End Sub

Answer 2

我能够创建范围并使其工作并清理我的代码，例如：

Dim Table02 As Range

Set Table02=范围(TableName(.ListObject.DataBodyRanege

Range不在乎表在哪张纸上。