Why Does SET Command Change Data Type?

[skirudder]

Whenever I declare a variable as a ListObject and then use the SET command, it changes the data type from and object to a string. Why is this and is there a way to avoid it?

>>> use code - tags <<<

Sub SetTest()
   Dim tbl As ListObject
   Debug.Print "VarType BEFORE:", VarType(tbl) 'returns 9
   Set tbl = ActiveSheet.ListObjects("Table1")
   Debug.Print "VarType AFTER:", VarType(tbl) 'returns 8
End Sub

 

[vletm]

skirudder​

Why should those be same?
Aren't You compare two different types?

VarType function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

 

[skirudder]

skirudder​

Why should those be same?
Aren't You compare two different types?

VarType function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

Isn't

ActiveSheet.ListObjects("Table1")

a ListObject?

 

[Marc L]

Why is this and is there a way to avoid it?

That's normal like you can check in VBA help when the object variable is not initialized.​

So just obviously check the variable once it is not Nothing …​

Sub Demo1()
    Dim Tbl As ListObject
    Debug.Print "Before :", VarType(Tbl), TypeName(Tbl)
    Set Tbl = ActiveSheet.ListObjects(1)
    Debug.Print "After :", VarType(Tbl), TypeName(Tbl)
    Set Tbl = Nothing
    Debug.Print "End :", VarType(Tbl), TypeName(Tbl)
End Sub

 

[skirudder]

Thank you

 

[p45cal]

The sentence to pay attention to in the help is: "If an object is passed and has a default property, VarType(object) returns the type of the object's default property." So once tbl has been assigned the listobject, vartype returns the type of the default property, which seems to be the listobject's Name property. (Add the line X = tbl after tbl has been assigned and you'll see it's "Table1", a string.)

Consider using :
Debug.Print TypeName(tbl)

 

[skirudder]

Thank you.