How to use TypeName function in VBA?

In VBA (Visual Basic for Applications), the TypeName function is used to return a string that describes the data type of a variable or an expression. This can be particularly useful when you need to check the type of a variable at runtime or when working with Variant types that can contain any type of data.

Here’s an example demonstrating the usage of TypeName:


Sub ExampleTypeName()
    Dim myNumber As Integer
    Dim myString As String
    Dim myDate As Date
    Dim myArray() As Integer
    Dim myObject As Object
    Dim myVariant As Variant
    myNumber = 10
    myString = "Hello World"
    myDate = DateSerial(2023, 1, 1)
    myArray = Array(1, 2, 3, 4)
    Set myObject = Sheets("Sheet1") ' Assuming you are in Excel
    myVariant = Empty ' Default state of a Variant is empty
    ' Use TypeName to get the type of each variable
    Debug.Print TypeName(myNumber)  ' Prints "Integer"
    Debug.Print TypeName(myString)  ' Prints "String"
    Debug.Print TypeName(myDate)    ' Prints "Date"
    Debug.Print TypeName(myArray)   ' Prints "Variant()"
    Debug.Print TypeName(myObject)  ' Might print "Worksheet" (depends on context)
    Debug.Print TypeName(myVariant) ' Prints "Empty"
    ' Checking if a Variant contains specific data type
    myVariant = 15.5
    If TypeName(myVariant) = "Double" Then
        Debug.Print "Variant contains a Double"
    End If
End Sub

By using TypeName, you can make decisions based on the type of data stored in a variable, which may change during the execution of your code, especially when working with Variants or when interacting with objects from the host application.

Unlock Your Potential


Basic - Advanced


Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

Your email address will not be published. Required fields are marked *