CODEWITHSUNDEEP
vbatypesvariant
Heinzi
Heinzi

Reputation: 172220

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

(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?

Upvotes: 15

Views: 8466

Answers (5)

omegastripes
omegastripes

Reputation: 12602

Dim v As Variant
Dim a As Variant
a = Array(SomeMethod())
If IsObject(a(0)) Then
    Set v = a(0)
Else
    v = a(0)
End If

Upvotes: 1

Cristian Buse
Cristian Buse

Reputation: 4558

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

Upvotes: 3

Sancarn
Sancarn

Reputation: 2824

It appears that I wasn't the only one with this issue.

The solution was given to me here.

In short:

Public Declare Sub VariantCopy Lib "oleaut32.dll" (ByRef pvargDest As Variant, ByRef pvargSrc As Variant)
Sub Main()
  Dim v as Variant
  VariantCopy v, SomeMethod()
end sub

It seems this is similar to the LetSet() function described in the answer, but I figured this'd be useful anyway.

Upvotes: 2

John Coleman
John Coleman

Reputation: 51998

You could use error trapping to reduce the expected number of method calls. First try to set. If that succeeds -- no problem. Otherwise, just assign:

Public counter As Long

Function Ambiguous(b As Boolean) As Variant
    counter = counter + 1
    If b Then
        Set Ambiguous = ActiveSheet
    Else
        Ambiguous = 1
    End If
End Function

Sub test()
    Dim v As Variant
    Dim i As Long, b As Boolean

    Randomize
    counter = 0
    For i = 1 To 100
        b = Rnd() < 0.5
        On Error Resume Next
            Set v = Ambiguous(b)
            If Err.Number > 0 Then
                Err.Clear
                v = Ambiguous(b)
            End If
        On Error GoTo 0
    Next i
    Debug.Print counter / 100

End Sub

When I ran the code, the first time I got 1.55, which is less than the 2.00 you would get if you repeated the experiment but with the error-handling approach replaced by the naïve if-then-else approach you discussed in your question.

Note that the more often the function returns an object, the less function calls on average. If it almost always returns an object (e.g. that is what it is supposed to return but returns a string describing an error condition in certain cases) then this way of doing things will approach 1 call per setting/ assigning the variable. On the other hand -- if it almost always returns a primitive value then you will approach 2 calls per assignment -- in which case perhaps you should refactor your code.

Upvotes: 2

tyg
tyg

Reputation: 14857

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.

Upvotes: 15

Related Questions