Determining the full type of a variable

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)

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][1]: 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.

[1]: https://msdn.microsoft.com/en-us/library/windows/desktop/ms221170%28v=vs.85%29.aspx "the enumerated var type"

You have the code that determines if the variable is a Variant already. Now all you need to do is get the subtype, right? There's a built in function for exactly that: VarType.

It has limitations though. It only works for native types. It always returns vbUserDefinedType (36) for user defined types. Although, I suppose you could special case that with a call to TypeName in order to finish the job off.