How can I assign a Variant to a Variant in VBA?

Question

(Warning: Although it might look like one at first glance, this is not a beginner-level question. If you are familiar with the phrase "Let coercion" or you have ever looked into the VBA spec, please keep on reading.)

Let's say I have an expression of type Variant, and I want to assign it to a variable. Sounds easy, right?

Dim v As Variant

v = SomeMethod()    ' SomeMethod has return type Variant

Unfortunately, if SomeMethod returns an Object (i.e., a Variant with a VarType of vbObject), Let coercion kicks in and v contains the "Simple data value" of the object. In other words, if SomeMethod returns a reference to a TextBox, v will contain a string.

Obviously, the solution is to use Set:

Dim v As Variant

Set v = SomeMethod()

This, unfortunately, fails if SomeMethod does not return an object, e.g. a string, yielding a Type Mismatch error.

So far, the only solution I have found is:

Dim v As Variant

If IsObject(SomeMethod()) Then
    Set v = SomeMethod()
Else
    v = SomeMethod()
End If

which has the unfortunate side effect of calling SomeMethod twice.

Is there a solution which does not require calling SomeMethod twice?

Answer 1

In VBA, the only way to assign a Variant to a variable where you don't know if it is an object or a primitive, is by passing it as a parameter.

If you cannot refactor your code so that the v is passed as a parameter to a Sub, Function or Let Property (despite the Let this also works on objects), you could always declare v in module scope and have a dedicated Sub solely for the purpose of save-assigning that variable:

Private v As Variant

Private Sub SetV(ByVal var As Variant)
    If IsObject(var) Then
        Set v = var
    Else
        v = var
    End If
End Sub

with somewhere else calling SetV SomeMethod().

Not pretty, but it's the only way without calling SomeMethod() twice or touching its inner workings.

---

Edit

Ok, I mulled over this and I think I found a better solution that comes closer to what you had in mind:

Public Sub LetSet(ByRef variable As Variant, ByVal value As Variant)
    If IsObject(value) Then
        Set variable = value
    Else
        variable = value
    End If
End Sub

[...] I guess there just is no LetSet v = ... statement in VBA

Now there is: LetSet v, SomeMethod()

You don't have a return value that you need to Let or Set to a variable depending of its type, instead you pass the variable that should hold the return value as first parameter by reference so that the Sub can change its value.

Answer 2

Dim v As Variant
For Each v In Array(SomeMethod())
    Exit For 'Needed for v to retain it's value
Next v
'Use v here - v is now holding a value or a reference