Determining the full type of a variable

Determining the Full Type of a Variable: A Dynamic Approach

Have you ever been in a situation where you just couldn't figure out the full type of a variable in your VBA code? The built-in TypeName() function might not always provide the detailed information you're looking for.

🤔 So, how can we dynamically determine the full type of a variable in VBA? 🕵️‍♀️

Let's dive into this challenging problem, explore common issues, and provide easy solutions for obtaining precise type information.

The Need for Full Type Information

In VBA, knowing the full type of a variable can be crucial for debugging purposes or when building a log utility. While TypeName() gives you the subtype of a variant, it fails to differentiate between variant variables holding ranges, object variables holding ranges, and range variables holding ranges.

To overcome this limitation, a preliminary step is necessary: creating a function that detects if a variant is passed to it. By leveraging pass-by-reference semantics, we can manipulate the argument in a way that only variants can handle. Consequently, an error will be triggered 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

Building on top of this function, we can now determine the full type of a variable using the FullType() function:

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

Let's Test It!

Now that we have our essential functions in place, let's put them to the test. As an example, we'll examine various variable types, including variants, integers, ranges, and objects.

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

The expected output is as follows:

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

The FullType() function successfully distinguishes between variant subtypes and provides the precise type information we need.

But What About Object Variables Holding Ranges?

Although our current implementation worked for most cases, it fails to differentiate between object variables holding ranges and range variables holding ranges. Let's address this issue now.

We can attempt to create another function, called IsTypeObject(), to tackle this problem. Just like IsVariant(), this function will leverage pass-by-reference semantics to manipulate the argument and determine whether it is an object variable or a specific object variable, such as a range variable.

However, be forewarned that this approach might not yield the expected result. Despite the apparent similarity in the implementation with IsVariant(), assigning a collection to a range variable won't necessarily trigger an error. This means that IsTypeObject() might erroneously return True even when dealing with range variables.

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

Running a simple test to check if IsTypeObject() works as expected:

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

Surprisingly, this code prints True, even though we would expect the same pass-by-reference semantics that make IsVariant() work to also work for IsTypeObject().

Despite numerous attempts, finding a reliable solution to distinguish between generic object variables and specific object variables (like range variables) remains elusive.

Your Turn!

We've explored different ways to dynamically get the full type of a variable in VBA. Although our implementation successfully handles most cases, resolving the distinction between object variables holding ranges and range variables holding ranges proved to be quite challenging.

🧠 Do you have any ideas or alternative approaches to tackle this problem? We'd love to hear them! Share your thoughts and solutions in the comments below.

Let's collaborate and create more efficient debugging tools that will make our lives as VBA developers easier! ✨👩‍💻👨‍💻

Remember, stay curious and keep exploring! 🚀