There are two primary ways to query the types of your variables in VBA: the TypeName function and the TypeOf...Is expression. Let's explore both options.
The TypeName function returns a string that represents the variable's type.
' --== Immediate Window ==-- ?TypeName(42) Integer ?TypeName(42.0) Double ?TypeName("42") String ?TypeName(CreateObject("Scripting.Dictionary")) Dictionary
The TypeOf...Is expression checks to see if the type of the variable is compatible with the given type.
For example, let's say I add a text box named Text0 to a new form. I then add the following code to the form's Load event:
Private Sub Form_Load() Debug.Print "Text0's type name: "; TypeName(Me.Text0) Debug.Print "Type name of Text0's value: "; TypeName(Me.Text0.Value) Debug.Print Debug.Print "Is a type of textbox? "; TypeOf Me.Text0 Is Access.TextBox Debug.Print "Is a type of control? "; TypeOf Me.Text0 Is Access.Control Debug.Print "Is a type of object? "; TypeOf Me.Text0 Is Object Debug.Print "Is a type of form? "; TypeOf Me.Text0 Is Access.Form End Sub
Here's the output from the above code:
'--== Immediate Window (output of Form_Load Sub) ==-- Text0's type name: TextBox Type name of Text0's value: Null Is a type of textbox? True Is a type of control? True Is a type of object? True Is a type of form? False
When would you use TypeName?
There are four times that I prefer (or have) to use TypeName:
- Value types
- Error logging
The TypeOf...Is expression can only be used with object types in VBA. For example, TypeName works fine here:
Foo = 42: ?TypeName(Foo) Integer
But if we try to do the same sort of thing with TypeOf, we get a compiler error:
I often use TypeName when I'm debugging because I don't have to know what type it could be; I just have to know the name of the variable.
This benefit also lends itself well to error logging, especially if you have access to the call stack because you're using vbWatchdog.
Finally, if the object contains a late-bound type, then TypeOf won't work.
What do I mean by a late-bound type? For example, the Dictionary class is part of the Microsoft Scripting Runtime. That reference is not included by default in Access applications.
The following code will always work:
The code below, though, will fail if Microsoft Scripting Runtime is not included as a reference:
To clarify, the problem in the above screenshot is not the call to CreateObject("Scripting.Dictionary"). Rather, the problem is that the Dictionary type is defined in the Microsoft Scripting Runtime, which is not included as a reference.
If I include Microsoft Scripting Runtime as a reference, then the code works fine:
When would you use TypeOf?
Generally speaking, if my situation doesn't fall into one of the four categories above, I use the TypeOf...Is expression. It offers several advantages over TypeName:
- Compile-time checks
- Interface support
- Multiple matches
- Greater specificity
Since TypeOf uses the type name itself, most typos in your code will be caught at compile time. On the other hand, with TypeName, you would be comparing the variable type to a string. A typo in your string won't be caught until run time...and maybe not even then, depending on how you're doing the comparison.
Let's say you have a class that implements an interface. If you use TypeName on an instance of the class, it will only ever return the name of the class. However, you can use the TypeOf expression to see if that class implements a certain interface.
I demonstrate this technique in my Graduate Level Error Handling article, where I write about recreating Python's
.__repr__ method to fetch identifying information from specific instances of classes to aid in troubleshooting bugs.
The TypeOf...Is expression will return true for every compatible object type of a given variable. The TypeName function, on the other hand, can only ever return a single string representation of the variable's type.
For example, all three lines below will return true if Me.Text0 is a textbox control:
Debug.Print "Is a type of textbox? "; TypeOf Me.Text0 Is Access.TextBox Debug.Print "Is a type of control? "; TypeOf Me.Text0 Is Access.Control Debug.Print "Is a type of object? "; TypeOf Me.Text0 Is Object
However, TypeName only tells us that the object is a "TextBox":
What if you have multiple references in your project that provide overlapping class names. Like, oh, I don't know, the DAO and ADO classes' Recordset type?
Private Sub Form_Load() Debug.Print TypeName(Me.Recordset) Debug.Print TypeOf Me.Recordset Is DAO.Recordset Debug.Print TypeOf Me.Recordset Is ADODB.Recordset End Sub '--== Output in immediate window: ==-- Recordset2 True False
Try doing that with TypeName!
Rule of Thumb
Generally speaking, if you can do it with a TypeOf...Is expression, you should.
But TypeName still has great value of its own.