vba function that can return objects and scalars

Question

I'm trying to write a vba function in Excel that can pass both object and scalar types. I can get the function to work properly by using a Variant as the signature, however, I cannot get the returned value from where it is called due to objects needing "set" and scalars not needing it. I wrote the following test case below. Can someone help me get it working, please?

I'm working on my own version of a JSON class module. To "load" the external data I have a reader which uses a recursive function which needs to do this (in case you were wondering).

EDIT: I cannot call the function twice like how QHarr suggested. QHarr method does work with the code below, however, this is not the full code, just a sample. Two reasons, 1) the function reads external data and calling this twice loses the current line being read, and 2) the actual function is recursive so calling it like this would result in unpredictable ways.

Sub test_passing_scalars_and_objects()

    Dim i As Long
    Dim var As Variant

    For i = 0 To 3

        Set var = pass_it(i)
        Debug.Print "cleared index:=" & i

    Next i

End Sub

Function pass_it(ByVal val As Long) As Variant

    Dim d As New Scripting.Dictionary
    Dim c As New Collection
    Dim arr() As Variant

    Select Case val
        Case Is = 0 ' dictionary
            d.Add "101", 101
            d.Add "202", 202
            Set pass_it = d

        Case Is = 1 ' collection
            c.Add "101"
            c.Add "202"
            Set pass_it = c

        Case Is = 2 ' array
            ReDim arr(1)
            arr(0) = "101"
            arr(1) = "202"
            pass_it = arr

        Case Is = 3 ' scalar
            pass_it = "101"
    End Select

End Function

You wrote, "I'm working on my own version of a JSON class module". Why? Plenty of VBA JSON parsers already detailed on StackOverflow. I tend to use the ScriptControl, exceldevelopmentplatform.blogspot.com/2018/01/… — S Meaden
– S Meaden, Commented Jan 11, 2019 at 0:47
But. if you really want to unify object types and scalars then copy .NET and box the scalars in an object. learn.microsoft.com/en-us/dotnet/csharp/programming-guide/types/… — S Meaden
– S Meaden, Commented Jan 11, 2019 at 0:48

QHarr · Accepted Answer · 2019-01-11 00:24:48Z

3

You could return an array with a boolean indicating if Object (as IsObject not allowed due to calling only once).

Option Explicit
Public Sub test_passing_scalars_and_objects()
    Dim i As Long, var As Variant
    For i = 0 To 3
        var = pass_it(i)
        If var(0) Then
            Set var = var(1)
        Else
            var = var(1)
        End If
        Debug.Print "cleared index:=" & i
    Next i
End Sub

Public Function pass_it(ByVal val As Long) As Variant
    Dim d As New Scripting.Dictionary, c As New Collection, arr() As Variant

    Select Case val
        Case Is = 0 ' dictionary
            d.Add "101", 101
            d.Add "202", 202
            pass_it = Array(True, d)

        Case Is = 1 ' collection
            c.Add "101"
            c.Add "202"
             pass_it = Array(True, c)

        Case Is = 2 ' array
            ReDim arr(1)
            arr(0) = "101"
            arr(1) = "202"
            pass_it = Array(False, arr)

        Case Is = 3 ' scalar
            pass_it = Array(False, "101")
    End Select
End Function

edited Jan 11, 2019 at 0:24

answered Jan 11, 2019 at 0:12

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

twegner Over a year ago

I'm not sure I can use this. You are calling the function twice, first by evaluating what the return type is, then by capturing the return. The thing is I am reading the external file real-time and cannot rewind the reader. So after my first call the reader is digesting new data. Sorry I should have stated this limitation. I will edit my question.

QHarr Over a year ago

Can you pass back an 0-1 array with 0 containing a boolean stating if object or not? Then you can read the array and access the object/other from index 1 from that?

twegner Over a year ago

YES. That works. I knew it was something that simple.

S Meaden Over a year ago

I like this @QHarr.

Collectives™ on Stack Overflow

vba function that can return objects and scalars

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related