vba-variant: how to handle Class vs. primitive types

Question

my function gets a collection and the items may be Objects or primitives how can I assign the item to a variant?

What I do now looks something like this:

Dim vItem As Variant
On Error Resume Next
vItem = oCollection.Item(sKey)
If Err.Number = 91 Then
    Set vItem = oCollection.Item(sKey)
On Error GoTo 0

I think it works, but I wonder if there is a better way to do it.

score 2 · Accepted Answer · edited Feb 06 '14 at 09:42

2

You may use the varType() function to test the type, alternatively if you are testing for specific types, you could use typeof.

        If VarType(oCollection.Item(sKey)) = vbObject Then
           Set vItem = oCollection.Item(sKey)
        Else
            vItem = oCollection.Item(sKey)
        End If

edited Feb 06 '14 at 09:42

TmTron

10,318
3
52
97

answered Feb 05 '14 at 09:10

SWa

4,174
21
40

That would work, but I need a solution to use `oCollection.Item(sKey)`. as explained [here](http://stackoverflow.com/questions/137845/determining-whether-an-object-is-a-member-of-a-collection-in-vba) by Mark Nold it is much faster (especially since I will have huge collections) – TmTron Feb 05 '14 at 15:36
How about: `If VarType(oCollection.Item(sKey)) = vbObject`? – SWa Feb 05 '14 at 16:17
yeah - thanks that works of course (that seemed to easy to me..) – TmTron Feb 06 '14 at 09:39
Is type checking different than `IsObject(oCollection.Item(sKey))` ? – user2426679 Sep 29 '18 at 07:00

user2426679 · Answer 2 · 2018-10-05T22:25:10.933

A slight improvement to the answer from @SWa is to create a helper function; this avoids having to copy/paste, for example, the oCollection.Item(sKey) part of op's answer.

Sub SetThisToThat(ByRef this As Variant, ByRef that As Variant)
    If IsObject(that) Then
        Set this = that
    Else
        this = that
    End If
End Sub

Some tests as an example:

Function Test()
    Call SetThisToThat(Test, "Function test")
End Function

Sub RunTest()
    MsgBox Test

    Dim s As String
    Call SetThisToThat(s, "Why must it be this hard?")
    MsgBox s
End Sub

@TmTron should use:

Call SetThisToThat(vItem, oCollection.Item(sKey))

vba-variant: how to handle Class vs. primitive types

2 Answers2

Linked