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 how to use `TypeName` in VBA:

Open the Visual Basic for Applications editor in your Microsoft application (like Excel, Word, etc.) by pressing `ALT` + `F11`.
Insert a new module or use an existing one.
Create a procedure or use an existing one to write your code.

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

To execute the code, you can place the cursor inside the subroutine and press `F5`, or you can assign the macro to a button or another event within your Microsoft application.

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.

facebooklinkedin