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.

I came across this recently but have not gone further than cursory review. At a glance, though, it looks like it may be what you're looking for. — David Zemens
– David Zemens, Commented Oct 11, 2015 at 0:50
I saw it too, from what I could see, it uses a custom "object" CObject or such that is essentially a dictionary which is fed to the json conversion function — user3791372
– user3791372, Commented Oct 11, 2015 at 1:24
Hmmm ok I looked a little closer (but still not close enough) my first impression was that it was a generic Stringify function, but now I think maybe it's specific only to the one particular Class. — David Zemens
– David Zemens, Commented Oct 11, 2015 at 12:56
One idea, not always ideal, would be to use JSON or XML representation of your data instead of VBA class object. — David Zemens
– David Zemens, Commented Oct 11, 2015 at 12:58
I can't think of a situation where that's ever ideal. Objects are far more flexible than json data — user3791372
– user3791372, Commented Oct 11, 2015 at 17:48

Makah · Accepted Answer · 2018-02-27 22:15:42Z

15

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"
   }
}

answered Feb 27, 2018 at 22:15

Makah

4,5233 gold badges52 silver badges68 bronze badges

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

1 Comment

Erik Over a year ago

For handling double quotes in the value, a simple Replace is enough. value = """" & Replace(dict(key), """", "\""") & """" For a complete solution, replace also `vbBack->\b; vbCr->\r; vbLf->\n; vbTab->\t; \->\`

user3791372 · Accepted Answer · 2015-10-11 17:51:10Z

1

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

answered Oct 11, 2015 at 17:51

user3791372

4,7158 gold badges49 silver badges82 bronze badges

Comments

yoel halb · Accepted Answer · 2021-11-18 00:52:55Z

0

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.

answered Nov 18, 2021 at 0:52

yoel halb

12.8k3 gold badges59 silver badges58 bronze badges

Collectives™ on Stack Overflow

Converting objects to JSON in VBA

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related