CODEWITHSUNDEEP
vbaexcelobjectvariant
Greedo
Greedo

Reputation: 5523

Write result of function to a variable, where result could be an object

If I have a function which may return an object or a primitive type - within it I can do the following to handle those two cases:

Function Result() As Variant 'may be object or not
    '... get item - the return value
    If IsObject(item) Then
        Set Result = item
    Else
        Result = item
    End If
End Function

However, how can I do the same test for the variable where Result is stored without running the function two times? Eg

Dim myResult As Variant
If IsObject(Result) Then 'test return type of function
    Set myResult = Result
Else
    myResult = Result
End If

As

myResult = Result 'fails if Result returns object
Set myResult = Result 'fails if Result returns non-object

I am trying to write a series of objects/non objects to an array of variant type

Upvotes: 4

Views: 547

Answers (3)

Greedo
Greedo

Reputation: 5523

Well one possible solution is to write to the variable directly, by passing it ByRef. In a standard module:

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

called like

Dim myResult As Variant
LetSet(myResult) = 123                'myResult = 123
LetSet(myResult) = New Collection     'Set myResult = New Collection

Upvotes: 3

M. Johnstone
M. Johnstone

Reputation: 112

An alternative solution is to use the Array() function and wrap the function or property output in it.
From memory when I was testing my original solution it didn't work for where the value came from a property of a class only functions. This was an issue when using Interfaces or directly using the property and obtaining variant results.
Note: I don't think it will work for functions/properties returning a UDT or fixed string length.

For your example use the following:

Dim myResult As Variant
Dim resultOutput as Variant
resultOutput = Array(Result)
If VBA.IsObject(resultOutput(0)) Then
    set myResult = resultOutput(0)
Else
    myResult = resultOutput(0)
End If

Upvotes: 1

M. Johnstone
M. Johnstone

Reputation: 112

As an example a snippet from a class, that deals with calling class functions dynamically. It's work in progress for prototyping if can implement some basic VBA Reflection, based off RubberDuck Reflection API. As I'm dynamically calling class and module functions there's no reliable way to know in advance if a primitive or object type will be returned from a function call.

Note I haven't tested the code yet and will need some error handling added to the InvokeMember function.

Greedo's solution is similar expect the function result is a separate parameter by reference, that way can put in the function call eg. CallByName as the second parameter. The first parameter is the variable storing the result called by reference. The function result parameter y is by reference as an array could result and avoids copying the whole array in memory when passed in. 

Public Function InvokeMember(ByVal obj As Object, ParamArray args() As Variant) As Variant
    Assign InvokeMember, CallByName(obj, Me.FullName, Me.CallType, args)
End Function

In a module, code from VBA Extension Library

' Assign x to y regardless of object or primitive
Public Sub Assign(ByRef x as Variant, ByRef y as Variant)

    If IsObject(y) Then
        Set x = y
    Else
        x = y
    End If

End Sub

Thanks for the question it's helped me solve the same issue avoiding calling the same function twice when dealing with whether it's an object, array or primitive data type being returned.

Upvotes: 1

Related Questions