Input type changes if output not captured

Question

I'm working on a function that does different things depending on the type of the input. Unfortunately, I've found that the type of the input depends on whether the output is captured or not.

Function what_type(x As Variant) As String
    Debug.Print TypeName(x)
    what_type = TypeName(x)
End Function

Sub range_test()
    Dim rng As Range
    Set rng = Sheets("Test").Range("F28:G28")
    what_type (rng)
    Debug.Print what_type(rng)
End Sub

Running range_test prints

Variant()
Range
Range

which is not what I'd expect.

What is the rationale for this peculiar behaviour/what's the big picture here?

Answer 1

In your first call of what_type, you have passed a parameter of (rng), i.e. the values of rng as a Variant array. This results in the Debug.Print displaying Variant().

In your second call of what_type, you have passed a parameter of rng, i.e. the actual range object. This results in the Debug.Print displaying Range, and that being passed back as the result, which then is again Debug.Printed.

If your first call was changed to just

what_type rng

it, too, would display Range as the variable type.

Or if your second call was changed to

Debug.Print what_type((rng))

it would display Variant() both inside the function and as the returned result.

P.S. It is always a bad idea to invoke a function using the syntax for a subroutine. A function returns a value (or, at least, it should) and it is always a good idea to dispose of that returned value in some way, be that an assignment to a variable, or passing it to another function/subroutine.