ListObject Returning a String

Question

I want to return a ListObject from a function but it seems like it implicitly converts it to a string. I am confused. Can someone point out what I am doing wrong?

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

Not sure what you mean. The `Address` property will return a string corresponding to the range. — SJR, Oct 06 '17 at 18:31
@SJR Sorry for the confusion. I edited my post slightly. When I just print `MyTable` it prints `Table1` as a string. But I need to return a table object, not the table name. My knowledge of VBA is progressing but I may not be understanding something. — Brian, Oct 06 '17 at 19:10
Well it does, hence you can use the Unlist method. What are you actually trying to achieve? — SJR, Oct 06 '17 at 19:37
@SJR I want to set an instance of a custom class to the table object. My class initialize event adds the table and sets a private modular variable of type `ListObject` via the property. When I instantiate the class I want to set the class property = to `MyTable`. — Brian, Oct 06 '17 at 19:45
`Debug.Print MyTable` prints the value of the *default property* of MyTable - in this case `Name`, which is "Table1". This is similar to what you'd get using (eg) a `Range` object: `Debug.Print Range("A1")` will print the value from A1 because `Value` is the default property of `Range`. See: http://www.cpearson.com/excel/DefaultMember.aspx You can use `TypeName(MyTable)` to confirm the type of your variable. — Tim Williams, Oct 08 '17 at 06:09

thx1138v2 · Answer 1 · 2017-10-07T12:17:00.090

-1

ListObjects in wks.ListObjects.Add(xlSrcRange, rngTable, , xlYes) is a collection. Collection items have two properties, a name and a value . In this case, the name is "Table1" since you didn't assign a name to rngTable after creating it. When the .Add is performed the collection item being added is given the name of "Table1" and the value is the address of the rngTable structure in memory.

When you execute Debug.Print MyTable, which is a collection item, it is printing the default property of the collection item which is its name, "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

The following link may clear it up for you. How do I reference tables in Excel vba?

edited Oct 07 '17 at 12:17

answered Oct 06 '17 at 20:03

thx1138v2

566
3
6

I'm not sure I follow. So how do I return an object of type `ListObject`? – Brian Oct 06 '17 at 21:30
You are already returning a ListObject item. I added code to the answer to help you understand what is happening in your code. The way you had it coded was confusing what a ListObject item is since you called it MyTable. When you Debug.Print, you are printing the name of the ListObject rather than anything to do with the table. Check out the link at the bottom of the answer. – thx1138v2 Oct 07 '17 at 12:22
Tables ARE ListObjects. All you did was change the name of my variable. – Brian Oct 07 '17 at 15:26

ListObject Returning a String

1 Answers1