VBA/Excel/Access/Word/Data Type/TypeName

Check the selection type

 
Sub EnterSquareRoot4()
    Dim Num As Variant
    If TypeName(Selection) <> "Range" Then
        MsgBox "Select a range first."
        Exit Sub
    End If
    Num = InputBox("Enter a value")
    If Not IsNumeric(Num) Then
        MsgBox "You must enter a number."
        Exit Sub
    End If
    If Num < 0 Then
        MsgBox "You must enter a positive number."
        Exit Sub
    End If
    ActiveCell.value = Sqr(Num)
End Sub

Get the type name

 
Public Sub TestActiveControl()
  If TypeName(ActiveSheet) <> "Worksheet" _
    Or TypeName(Selection) <> "Range" Then
    MsgBox "You can only run this macro in a range", vbCritical
    Exit Sub
  End If
End Sub

Get the type name of an array

 
Sub ArrayTest4()
    Dim intNum1 As Integer
    Dim intNum(1 To 10) As Integer
    Debug.Print "intnum1: " & TypeName(intNum1)
    Debug.Print "intnum: " & TypeName(intNum)
End Sub

Loop all table definitions in Database

 
Sub exaCheckTableDefs()
    Dim db As Database
    Dim tbl As TableDef
    Set db = CurrentDb
    Debug.Print db.TableDefs.Count
    For Each tbl In db.TableDefs
       Debug.Print tbl.Name & " - " & TypeName(tbl)
    Next
End Sub

Returning the Active Window, Inspector, or Explorer

 
"Displays a message box that states which window type is active, as long as there is an active window:
Sub state()
    If Not TypeName(ActiveWindow) = "Nothing" Then
        MsgBox "An " & TypeName(ActiveWindow) & " window is active."
    End If
End Sub

Uses VBA"s handy TypeName function to determine the data type of the FormulaTest variable

 
Sub hasFormula()
    Dim FormulaTest As Variant
    FormulaTest = range("A1:A2").hasFormula
    If TypeName(FormulaTest) = "Null" Then MsgBox "Mixed!"
End Sub

Use TypeName to get the object data type

 
Sub TypeSheet()
    MsgBox "This sheet is a " & TypeName(ActiveSheet)
End Sub