Determining the Object Type in Excel VBA

It is beneficial to declare an object variable with the appropriate type. However, for many objects, the type is not always obvious. In such cases, the TypeName() function proves very useful. You may already know it for determining the data type of a variable, but it can also identify arrays and object types.

If the argument passed to TypeName() is of the Variant data type without an assigned subtype, the function returns « Empty ».

Below is an example using a variety of variables and objects:

Sub DetermineObjectType()
    Dim i As Integer
    Dim a(1 To 5) As Double
    Dim b
    Dim c As Variant
    Dim Ws1 As Worksheet   
    Set Ws1 = ThisWorkbook.Worksheets("Sheet1")
    ThisWorkbook.Worksheets("Sheet2").Activate   
    Range("A1").Value = TypeName(i)
    Range("A2").Value = TypeName(a)
    Range("A3").Value = TypeName(b)
    Range("A4").Value = TypeName(c)
    Range("A5").Value = TypeName(ThisWorkbook)
    Range("A6").Value = TypeName(ThisWorkbook.Name)
    Range("A7").Value = TypeName(Ws1)
    Range("A8").Value = TypeName(Ws1.Range("A1:A5"))
    Range("A9").Value = TypeName(Ws1.Range("A1:A5").Borders)
    Range("A10").Value = TypeName(Ws1.Range("A1:A5").Font)   
    Set Ws1 = Nothing
End Sub

Explanation:

• The types of the variable i (an Integer) and the array a (an array of Double) are recognized by TypeName().
• Variable b, which has no declared data type, defaults to the Variant type. In such cases, TypeName() returns « Empty ».
• Variable c is explicitly declared as Variant, so TypeName() will also return « Empty » if it holds no assigned value.
• Name is a property of type String, representing a text value.
• Borders and Font are sub-objects belonging to the types Borders and Font, respectively.
• This example demonstrates how TypeName() can be used to inspect both simple variables and more complex object hierarchies.