返回字符串的ListObject

Question

我想从函数返回一个ListObject，但它似乎隐式地将其转换为字符串。我很困惑。有人能指出我做错了什么吗？

Sub TestMyFunction()
    Dim MyTable As ListObject
    Set MyTable = RangeToTable
    Debug.Print MyTable '---MyTable = "Table1"
    MyTable.Unlist
End Sub

Private Function RangeToTable() As ListObject
    Dim wks As Worksheet
    Set wks = ActiveWorkbook.Worksheets(sREPORT_DATA_1)

    Dim rngTable As Range
    Set rngTable = wks.Range("A1").CurrentRegion

    Set RangeToTable = wks.ListObjects.Add(xlSrcRange, rngTable, , xlYes)
End Function

Answer 1

wks.ListObjects.Add(xlSrcRange，rngTable，，xlYes)中的ListObjects是一个集合。集合项有两个属性，一个名称和一个值。在本例中，名称为"Table1“，因为您在创建rngTable后没有为其指定名称。在执行.Add时，所添加的集合项的名称为"Table1“，值是内存中rngTable结构的地址。

当您执行Debug.Print MyTable时，它将打印集合项的默认属性，即其名称"Table1“。

Sub TestMyFunction()
    Dim MyLO As ListObject
    Set MyLO = RangeToTable
    Debug.Print MyLO '---MyLO = "Table1"
    MyLO.Unlist
End Sub

Private Function RangeToTable() As ListObject
    Dim wks As Worksheet
    Set wks = ActiveWorkbook.Worksheets(sREPORT_DATA_1)

    Dim rngTable As Range
    Set rngTable = wks.Range("A1").CurrentRegion

    Set RangeToTable = wks.ListObjects.Add(xlSrcRange, rngTable, , xlYes)
End Function

下面的链接可能会让你明白这一点。How do I reference tables in Excel vba?