Converting objects to JSON in VBA

Question

I know it's possible to use a library like VBAJSON to convert an array or a dictionary to json, but not a custom class instance in office 2013.

Searching turns up no libraries for handling objects to json, so I figure there must be some other way.

So, I'm wondering:

• is it possible for an object to be recursively converted to a dictionary so the to-json conversion can then happen, without having to write a lengthy custom "ToDictionary" method for each class?

• is there another way (other than not using objects) that json output can be reached from custom class objects?

  At the moment, I've written ToDictionary methods for each class to output the instance as a dictionary. It's messy, but gets the job done.

Answer 1

You can serialize the object to XML and from there it would be easier to convert to JSON and there are many posts online on how to do it.

Note however that converting XML to JSON isn't as trivial as it sounds and there are many pitfalls that you should be aware of.

Answer 2

It's an old question, but without an answer, so let me try to answer with code.

I built a function to convert VBA structure (using Dictionary) to JSON. This function accepts nested object (nested Dictionary):

Function ToJson(ByVal dict As Object) As String
    Dim key As Variant, result As String, value As String

    result = "{"
    For Each key In dict.Keys
        result = result & IIf(Len(result) > 1, ",", "")

        If TypeName(dict(key)) = "Dictionary" Then
            value = ToJson(dict(key))
            ToJson = value
        Else
            value = """" & dict(key) & """"
        End If

        result = result & """" & key & """:" & value & ""
    Next key
    result = result & "}"

    ToJson = result
End Function

Test:

Sub MyTest()
    Dim body As String
    Set dictSubValues = CreateObject("Scripting.Dictionary")
    Set dictBody = CreateObject("Scripting.Dictionary")

    dictSubValues.Add "SubValue1", "2.1"
    dictSubValues.Add "SubValue2", "2.2"

    dictBody.Add "Value1", "1"
    dictBody.Add "Value2", dictSubValues

    body = ToJson(dictBody)
    Debug.Print (body)
End Sub

Output:

{
   "Value1":"1",
   "Value2":{
      "SubValue1":"2.1",
      "SubValue2":"2.2"
   }
}

Answer 3

When I first started to write this question I was positvely stuck. Then I had a lightbulb moment to write a ToDictionary method for the class to convert the object to a dictionary how I want it.

So something like:

public function ToDictionary() as string
    dim d as dictionary
    set d = new dictionary
    d.add "id" Me!id
    d.add "title" Me!title
    ...
    Set ToDictionary = d
    set d = Nothing
end function