0

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
Improve this question
6
  • Not sure what you mean. The Address property will return a string corresponding to the range. Commented Oct 6, 2017 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. Commented Oct 6, 2017 at 19:10
  • Well it does, hence you can use the Unlist method. What are you actually trying to achieve? Commented Oct 6, 2017 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. Commented Oct 6, 2017 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: cpearson.com/excel/DefaultMember.aspx You can use TypeName(MyTable) to confirm the type of your variable. Commented Oct 8, 2017 at 6:09

1 Answer 1

Reset to default
-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?

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I'm not sure I follow. So how do I return an object of type ListObject?
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.
Tables ARE ListObjects. All you did was change the name of my variable.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.