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

Check the selection type

   <source lang="vb">

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

</source>
   
  

Get the type name

   <source lang="vb">

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

</source>
   
  

Get the type name of an array

   <source lang="vb">

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

</source>
   
  

Loop all table definitions in Database

   <source lang="vb">

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

</source>
   
  

Returning the Active Window, Inspector, or Explorer

   <source lang="vb">

"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

</source>
   
  

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

   <source lang="vb">

Sub hasFormula()

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

End Sub

</source>
   
  

Use TypeName to get the object data type

   <source lang="vb">

Sub TypeSheet()

   MsgBox "This sheet is a " & TypeName(ActiveSheet)

End Sub

</source>