Determining the full type of a variable
Solution 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
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)
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)
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.
Solution 2:
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.