Determining the full type of a variable

Question

By the full type of a variable I mean the sort of information that you get in the immediate window:

I would like to determine the type information dynamically using VBA. The function TypeName() doesn't do what I want since it returns the subtype of a variant and doesn't distinguish between e.g. a variant variable holding a range, an object variable holding a range, and a range variable holding a range.

As a preliminary step, I wrote a function which detects if a variant is passed to it. It works by exploiting pass-by-reference semantics. The code does things with its argument that can only be done with a variant and will thus trigger an error if the passed variable isn't actually a variant:

Function IsVariant(var As Variant) As Boolean     Dim temp As Variant     Dim isVar As Boolean      If IsObject(var) Then         Set temp = var     Else         temp = var     End If      On Error Resume Next         Set var = New Collection         var = "test"         If Err.Number > 0 Then             isVar = False         Else             isVar = True         End If     On Error GoTo 0      If IsObject(temp) Then         Set var = temp     Else         var = temp     End If     IsVariant = isVar End Function 

Based on this, I wrote:

Function FullType(var As Variant) As String     If IsVariant(var) Then         FullType = "Variant/" & TypeName(var)     Else         FullType = TypeName(var)     End If End Function 

Testing code:

Sub TestTypes()     Dim R As Range     Dim Ob As Object     Dim i As Integer     Dim v1 As Variant     Dim v2 As Variant      v1 = 10     i = 10      Set v2 = Range("A1")     Set Ob = Range("A2")     Set R = Range("A3")      Debug.Print "v1: " & FullType(v1)     Debug.Print "i: " & FullType(i)     Debug.Print "v2: " & FullType(v2)     Debug.Print "Ob: " & FullType(Ob)     Debug.Print "R: " & FullType(R)   End Sub 

Output:

v1: Variant/Integer i: Integer v2: Variant/Range Ob: Range R: Range 

This is almost what I want -- but doesn't distinguish between an object variable holding a range and a range variable holding a range. I've tried to write a function called IsTypeObject which works similarly to IsVariant but can't seem to get it to work:

Function IsTypeObject(var As Variant) As Boolean     Dim temp As Variant     Dim isGeneric As Boolean      If (Not IsObject(var)) Or IsVariant(var) Then         IsTypeObject = False         Exit Function     End If      Set temp = var     On Error Resume Next         Set var = New Collection         Set var = ActiveWorkbook         If Err.Number > 0 Then             isGeneric = False         Else             isGeneric = True         End If     On Error GoTo 0      Set var = temp     IsTypeObject = isGeneric End Function 

Test:

Sub test()     Dim R As Range     Set R = Range("A1")     Debug.Print IsTypeObject(R) End Sub 

But this prints True even though I would think that the same pass-by-reference semantics which makes IsVariant work should also make IsTypeObject work (you can't assign a collection to a range). I've tried various tweaks but can't seem to distinguish between the generic object variables and specific object variables such as range variables.

So -- any ideas for how to dynamically get the full type of a variable? (The motivation is as part of a debug-log utility)

Answer 1

Yes, you can do this: it requires a little knowledge of pointers and the concept of 'dereferencing'...

Here's the code to do it:

Public Function VariantTypeName(ByRef MyVariant) As String ' Returns the expanded type name of a variable, indicating ' whether it's a simple data type (eg: Long Integer), or a ' Variant containing data of that type, eg: "Variant/Long"
 Dim iType As Integer Const VT_BYREF = &H4000&
 CopyMemory iType, MyVariant, 2
 ' iType now contains the VarType of the incoming parameter ' combined with a bitwise VT_BYREF flag indicating that it ' was passed by reference. In other words, it's a pointer, ' not the data structure of the variable (or a copy of it)
 ' So we should have VT_BYREF - and we'd always expect to ' when MyVariant is a Variant, as variants are a structure ' which uses a pointer (or pointers) to the stored data...
 ' However, the VBA implementation of a variant will always ' dereference the pointer - all the pointers -  passing us ' straight to the data, stripping out all that information ' about references...
 If (iType And VT_BYREF) = VT_BYREF Then     ' Bitwise arithmetic detects the VT_BYREF flag:     VariantTypeName = TypeName(MyVariant) Else     ' No VT_BYREF flag. This is a Variant, not a variable:     VariantTypeName = "Variant/" & TypeName(MyVariant) End If
 End Function 

(Declarations for the CopyMemory API function are a few paragraphs further down).

That needs some explanation, because the Visual Basic family of languages are designed to shield you from the implementation details of variables and their types - and from the concept of pointers in particular - and my code does involve a bit of lateral thinking.

In simple terms, your variables have a name - a string like 'intX' you see in your code; an area of memory allocated to contain the actual data; and an address for that memory.

That address will actually be for the start of the memory allocated to the variable, and the variable will be implemented as a structure in memory defined by offsets to the actual data, with the size (or length) of data - and, for complex types, by offsets to addresses to other structures in memory. Those sizes and offsets are predefined: they are the actual implementation of the variable, and we VBA developers rarely need to know about that - we declare the type, and its all done for us.

The first thing that you need to know today is that the first two bytes at address of a variable in VBA are the enumerated var type: that's how the VarType() Function works.

When the program passes that address, instead of passing a copied allocation of the data in memory, it passes that address as a pointer. Yes, I'm oversimplifying some of this, but VBA developers do actually know the difference between getting a pointer and a copy of the data: it's in the ByRef and ByVal identifiers we use for incoming parameters when we declare a function.

VBA and VB are very, very good at shielding us from the details: so good, that we can't use VarType and TypeName to detect that we've been passed a value, or a reference to it; or even a reference to a reference, to a reference.

This matters, because a variant is a wrapper for other variables, and the structure gives you a pointer to the variable that it contains with the var type to describe it: however, we have no way of knowing that in VBA - we are passed straight down the line indicated by the address, all the way to the data we are going to use, and the VBA varType never tells us that we went there indirectly by several hops through successive addresses defined by pointers.

However, that information does exist, if you're prepared to look at those two bytes behind the pointer using an API call.

As I said, those two bytes contain the var type - but there's more: they contain the var type combined with a bitwise marker VT_BYREF indicating that this is a reference, or pointer, to the stored data type, and not the data itself. So this code will reliably tell you your var type, with a little lateral thinking to get over VBA being helpful when we'd rather it wasn't:

Public Function DereferencedType(ByRef MyVar) As Long
 Dim iType As Integer
 Const VT_BYREF = &H4000&
 ' The first two bytes of a variable are the type ID with a ' bitwise OR to VT_BYREF if we were passed the variable by ' reference... Which is exactly what this function does:
 CopyMemory iType, MyVar, 2
 DereferencedType = iType ' Mod VT_BYREF
 'Use "Mod VT_BYREF" to separate out the type if you want
 End Function 

At a first glance, this function appears to be self-defeating: I'm passing the variable - variant or simple type - by reference, so it's always going to be combined with VT_BYREF. And I've commented-out the 'modulo' arithmetic anyway...

...And that's how it actually works: pass it a simple variable, and it'll tell you that you passed the variable by reference:

Dim str1 As String str1 = "One Hundred" Debug.Print "String Variable: " & DereferencedType(str1) 

...And you get the output vbString OR VT_BYREF:

String Variable: 16392 

But if you pass our function a string variant, VBA's implementation of the Variant will shield you from all that complexity about pointers and passing by reference - all the way down to the data - and give you your data with all that unwanted information stripped out:

Dim varX As Variant varX = "One Hundred" Debug.Print "String Variant:  " & DereferencedType(varX) 

...And you get the output:

String Variant:  8 

I'll leave you to code up an OR or NOT operation on the returned values with VT_BYREF, to give you the 'Variant/' label for your expanded string descriptors of the Variant/String and Variant/Long outputs.

[Edit: did that, it's at the top of the answer, implemented as VariantTypeName]

I recommend that you declare the CopyMemory API call as shown, with conditional compiler constants for all the environments you're likely to encounter:

 #If VBA7 And Win64 Then    ' 64 bit Excel under 64-bit Windows                            ' Use LongLong and LongPtr
     Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _                                     (Destination As Any, _                                      Source As Any, _                                      ByVal Length As LongLong)
 #ElseIf VBA7 Then     ' 64 bit Excel in all environments                       ' Use LongPtr only, LongLong is not available
     Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _                                     (Destination As Any, _                                      Source As Any, _                                      ByVal Length As Long)
 #Else                 ' 32 bit Excel
     Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _                             (Destination As Any, _                              Source As Any, _                              ByVal Length As Long) #End If 

Meanwhile, the harder question - getting Variant/Object/Range - will need further work. I can tell you that your variant contains a range, and I can tell that it's a variant and not itself a range: but I can't go down the chain of declarations to reveal that an object was declared as 'object' now that it points to a range:

 VarX is set equal to a range object variable:     varX: type=8204     Range   Dereferenced Type=9     rng1: type=8204     Range   Dereferenced Type=16393
 VarX is set equal to a range object's value, a 2-dimensional array:     varX: type=8204     Variant()   Dereferenced Type=8204     arr1: type=8204     Variant()   Dereferenced Type=8204
 The array variable is erased to Empty(). Inspect varX:     varX: type=8204     Variant()   Dereferenced Type=8204     arr1: type=8204     Variant()   Dereferenced Type=8204
 VarX is set equal to an 'object' variable, which has been set to a range:     varX: type=8204     Range   Dereferenced Type=9     obj1: type=8204     Range   Dereferenced Type=16393 

Here's the code that generated that, and the full output:

 Public Sub TestVar()
 Dim varX As Variant Dim str1 As String Dim lng1 As Long Dim rng1 As Excel.Range Dim arr1 As Variant Dim obj1 As Object
 Debug.Print "Uninitialised:" Debug.Print Debug.Print vbTab & "varX: type=" & VarType(varX) & vbTab & vbTab & TypeName(varX) & vbTab & "Dereferenced Type=" & DereferencedType(varX) Debug.Print vbTab & "str1: type=" & VarType(str1) & vbTab & vbTab & TypeName(str1) & vbTab & "Dereferenced Type=" & DereferencedType(str1) Debug.Print vbTab & "lng1: type=" & VarType(lng1) & vbTab & vbTab & TypeName(lng1) & vbTab & "Dereferenced Type=" & DereferencedType(lng1) Debug.Print
 varX = "One Hundred" str1 = "One Hundred" lng1 = 100 Debug.Print "varX and str1 are populated with the same literal:" Debug.Print Debug.Print vbTab & "varX: type=" & VarType(varX) & vbTab & vbTab & TypeName(varX) & vbTab & "Dereferenced Type=" & DereferencedType(varX) Debug.Print vbTab & "str1: type=" & VarType(str1) & vbTab & vbTab & TypeName(str1) & vbTab & "Dereferenced Type=" & DereferencedType(str1) Debug.Print vbTab & "lng1: type=" & VarType(lng1) & vbTab & vbTab & TypeName(lng1) & vbTab & "Dereferenced Type=" & DereferencedType(lng1) Debug.Print
 varX = 100 lng1 = 100 Debug.Print "varX and lng1 are populated with the same integer:" Debug.Print Debug.Print vbTab & "varX: type=" & VarType(varX) & vbTab & vbTab & TypeName(varX) & vbTab & "Dereferenced Type=" & DereferencedType(varX) Debug.Print vbTab & "str1: type=" & VarType(str1) & vbTab & vbTab & TypeName(str1) & vbTab & "Dereferenced Type=" & DereferencedType(str1) Debug.Print vbTab & "lng1: type=" & VarType(lng1) & vbTab & vbTab & TypeName(lng1) & vbTab & "Dereferenced Type=" & DereferencedType(lng1) Debug.Print
 varX = str1 Debug.Print "VarX is set equal to str1:" Debug.Print Debug.Print vbTab & "varX: type=" & VarType(varX) & vbTab & vbTab & TypeName(varX) & vbTab & "Dereferenced Type=" & DereferencedType(varX) Debug.Print vbTab & "str1: type=" & VarType(str1) & vbTab & vbTab & TypeName(str1) & vbTab & "Dereferenced Type=" & DereferencedType(str1) Debug.Print
 varX = lng1 Debug.Print "VarX is set equal to lng1:" Debug.Print Debug.Print vbTab & "varX: type=" & VarType(varX) & vbTab & vbTab & TypeName(varX) & vbTab & "Dereferenced Type=" & DereferencedType(varX) Debug.Print vbTab & "lng1: type=" & VarType(lng1) & vbTab & vbTab & TypeName(lng1) & vbTab & "Dereferenced Type=" & DereferencedType(lng1) Debug.Print
 Set varX = ActiveSheet.Range("A1:C3") Debug.Print "VarX is set equal to a range:" Debug.Print Debug.Print vbTab & "varX: type=" & VarType(varX) & vbTab & vbTab & TypeName(varX) & vbTab & "Dereferenced Type=" & DereferencedType(varX) Debug.Print
 Set rng1 = ActiveSheet.Range("A1:C3") Set varX = Nothing Set varX = rng1 Debug.Print "VarX is set equal to a range object variable:" Debug.Print vbTab & "varX: type=" & VarType(varX) & vbTab & vbTab & TypeName(varX) & vbTab & "Dereferenced Type=" & DereferencedType(varX) Debug.Print vbTab & "rng1: type=" & VarType(rng1) & vbTab & vbTab & TypeName(rng1) & vbTab & "Dereferenced Type=" & DereferencedType(rng1) Debug.Print
 arr1 = rng1.Value2 Set varX = Nothing varX = arr1 Debug.Print "VarX is set equal to a range object's value, a 2-dimensional array:" Debug.Print vbTab & "varX: type=" & VarType(varX) & vbTab & vbTab & TypeName(varX) & vbTab & "Dereferenced Type=" & DereferencedType(varX) Debug.Print vbTab & "arr1: type=" & VarType(rng1) & vbTab & vbTab & TypeName(arr1) & vbTab & "Dereferenced Type=" & DereferencedType(arr1) Debug.Print
 Erase arr1 Debug.Print "The array variable is erased to Empty(). Inspect varX:" Debug.Print vbTab & "varX: type=" & VarType(varX) & vbTab & vbTab & TypeName(varX) & vbTab & "Dereferenced Type=" & DereferencedType(varX) Debug.Print vbTab & "arr1: type=" & VarType(rng1) & vbTab & vbTab & TypeName(arr1) & vbTab & "Dereferenced Type=" & DereferencedType(arr1) Debug.Print
 Set obj1 = ActiveSheet.Range("A1:C3") Set varX = Nothing Set varX = obj1 Debug.Print "VarX is set equal to an 'object' variable, which has been set to a range:" Debug.Print vbTab & "varX: type=" & VarType(varX) & vbTab & vbTab & TypeName(varX) & vbTab & "Dereferenced Type=" & DereferencedType(varX) Debug.Print vbTab & "obj1: type=" & VarType(rng1) & vbTab & vbTab & TypeName(obj1) & vbTab & "Dereferenced Type=" & DereferencedType(obj1) Debug.Print
 End Sub 

Results:

 Uninitialised:     varX: type=0        Empty   Dereferenced Type=0     str1: type=8        String  Dereferenced Type=16392     lng1: type=3        Long    Dereferenced Type=16387
 varX and str1 are populated with the same literal:     varX: type=8        String  Dereferenced Type=8     str1: type=8        String  Dereferenced Type=16392     lng1: type=3        Long    Dereferenced Type=16387
 varX and lng1 are populated with the same integer:     varX: type=2        Integer Dereferenced Type=2     str1: type=8        String  Dereferenced Type=16392     lng1: type=3        Long    Dereferenced Type=16387
 VarX is set equal to str1:     varX: type=8        String  Dereferenced Type=8     str1: type=8        String  Dereferenced Type=16392
 VarX is set equal to lng1:     varX: type=3        Long    Dereferenced Type=3     lng1: type=3        Long    Dereferenced Type=16387
 VarX is set equal to a range:     varX: type=8204     Range   Dereferenced Type=9
 VarX is set equal to a range object variable:     varX: type=8204     Range   Dereferenced Type=9     rng1: type=8204     Range   Dereferenced Type=16393
 VarX is set equal to a range object's value, a 2-dimensional array:     varX: type=8204     Variant()   Dereferenced Type=8204     arr1: type=8204     Variant()   Dereferenced Type=8204
 The array variable is erased to Empty(). Inspect varX:     varX: type=8204     Variant()   Dereferenced Type=8204     arr1: type=8204     Variant()   Dereferenced Type=8204
 VarX is set equal to an 'object' variable, which has been set to a range:     varX: type=8204     Range   Dereferenced Type=9     obj1: type=8204     Range   Dereferenced Type=16393 

All in all, an interesting question: the short version of my answer is that you can disambiguate variants and simple types, but an an object declared as 'object' isn't amenable to that analysis.