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

Result

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

I'm curious about the specifics where you wouldn't know what was being returned. In theory, you've declared a var to catch the return so when wouldn't you know what was being returned?
– Jeeped
Jun 28 at 10:46

@Jeeped I'm writing a buffer class which takes an array/collection of stuff. It loops through the stuff adding it to an internal store. Once the internal store reaches a certain capacity an event is raised and the stuff in the store is written to an array which can be read. Since the internal store is being constantly written to, I can't expose that, so I need to clone it somehow, which requires writing items of unknown type to a variant array.
– Greedo
Jun 28 at 10:52

2 Answers
2

Well one possible solution is to write to the variable directly, by passing it ByRef. The Function then has no return value so can become a Sub:

ByRef

Function

Sub

Sub Result(ByRef writeTo As Variant)
'... get item - the return value
If IsObject(item) Then
Set writeTo = item
Else
writeTo = item
End If
End Sub


called like

Dim myResult As Variant
Result myResult 'overwrites whatever writeTo contains


Here is a super simple example. We have a function this is given a worksheet object. It looks for "happiness" on that worksheet. If it finds "happiness" it returns a Range to indicate where it was found. If it cannot find "happiness", it returns a String error message:

Public Function WhereIsIt(sh As Worksheet) As Variant
Dim rng As Range

Set rng = sh.Cells.Find(what:="happiness", after:=sh.Cells(1, 1))

If rng Is Nothing Then
WhereIsIt = "only sadness"
Else
Set WhereIsIt = rng
End If
End Function


The only tricky part is how to use this function:

Sub MAIN()
Dim x As Variant
x = WhereIsIt(Sheets("Sheet1"))
If x = "happiness" Then
Set x = WhereIsIt(Sheets("Sheet1"))
MsgBox "a range" & vbCrLf & x.Address
Else
MsgBox "a message string" & vbCrLf & x
End If
End Sub


Another way is to use the TypeName() function:

Sub MAIN2()
Dim x As Variant, sh As Worksheet
Set sh = Sheets("Sheet1")

If TypeName(WhereIsIt(sh)) = "Range" Then
Set x = WhereIsIt(sh)
MsgBox "a range was returned" & vbCrLf & x.Address
Else
x = WhereIsIt(sh)
MsgBox "a message string was returned" & vbCrLf & x
End If
End Sub


I think that if this approach is going to be used, then the typename command may be a useful consideration as it would be more easily leverageable. This may be used with typname(x)="Range" in place of x="happiness"
– Taylor Scott
Jun 28 at 11:17

typename

typname(x)="Range"

x="happiness"

@TaylorScott You are correct! see my edit.
– Gary's Student
Jun 28 at 11:22

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.