Is There a JSON Parser for VB6 / VBA?

I am trying to consume a web service in VB6. The service - which I control - currently can return a SOAP/XML message or JSON. I am having a really difficult time figuring out if VB6's SOAP type (version 1) can handle a returned object - as opposed to simple types like string, int, etc. So far I cannot figure out what I need to do to get VB6 to play with returned objects.

So I thought I might serialize the response in the web service as a JSON string. Does a JSON parser exist for VB6?

Check out JSON.org for an up-to-date list (see bottom of main page) of JSON parsers in many different languages. As of the time of this writing, you'll see a link to several different JSON parsers there, but only one is for VB6/VBA (the others are .NET):

VB-JSON
- When I tried to download the zip file, Windows said the data was corrupt. However, I was able to use 7-zip to pull the files out. It turns out that the main "folder" in the zip file isn't recognized as a folder by Windows, by 7-zip can see the contents of that main "folder," so you can open that up and then extract the files accordingly.
- The actual syntax for this VB JSON library is really simple:
```
Dim p As Object
Set p = JSON.parse(strFormattedJSON)

'Print the text of a nested property '
Debug.Print p.Item("AddressClassification").Item("Description")

'Print the text of a property within an array '
Debug.Print p.Item("Candidates")(4).Item("ZipCode")
```
- Note: I had to add the "Microsoft Scripting Runtime" and "Microsoft ActiveX Data Objects 2.8" library as references via Tools > References in the VBA editor.
- Note: VBJSON code is actually based on a google code project vba-json. However, VBJSON promises several bug fixes from the original version.

Building on ozmike solution, which did not work for me (Excel 2013 and IE10). The reason is that I could not call the methods on the exposed JSON object. So its methods are now exposed through functions attached to a DOMElement. Didn't know this is possible (must be that IDispatch-thing), thank you ozmike.

As ozmike stated, no 3rd-party libs, just 30 lines of code.

Option Explicit

Public JSON As Object
Private ie As Object

Public Sub initJson()
    Dim html As String

    html = "<!DOCTYPE html><head><script>" & _
    "Object.prototype.getItem=function( key ) { return this[key] }; " & _
    "Object.prototype.setItem=function( key, value ) { this[key]=value }; " & _
    "Object.prototype.getKeys=function( dummy ) { keys=[]; for (var key in this) if (typeof(this[key]) !== 'function') keys.push(key); return keys; }; " & _
    "window.onload = function() { " & _
    "document.body.parse = function(json) { return JSON.parse(json); }; " & _
    "document.body.stringify = function(obj, space) { return JSON.stringify(obj, null, space); }" & _
    "}" & _
    "</script></head><html><body id='JSONElem'></body></html>"

    Set ie = CreateObject("InternetExplorer.Application")
    With ie
        .navigate "about:blank"
        Do While .Busy: DoEvents: Loop
        Do While .readyState <> 4: DoEvents: Loop
        .Visible = False
        .document.Write html
        .document.Close
    End With

    ' This is the body element, we call it JSON:)
    Set JSON = ie.document.getElementById("JSONElem")

End Sub

Public Function closeJSON()
    ie.Quit
End Function

The following test constructs a JavaScript Object from scratch, then stringifies it. Then it parses the object back and iterates over its keys.

Sub testJson()
    Call initJson

    Dim jsObj As Object
    Dim jsArray As Object

    Debug.Print "Construction JS object ..."
    Set jsObj = JSON.Parse("{}")
    Call jsObj.setItem("a", 1)
    Set jsArray = JSON.Parse("[]")
    Call jsArray.setItem(0, 13)
    Call jsArray.setItem(1, Math.Sqr(2))
    Call jsArray.setItem(2, 15)
    Call jsObj.setItem("b", jsArray)

    Debug.Print "Object: " & JSON.stringify(jsObj, 4)

    Debug.Print "Parsing JS object ..."
    Set jsObj = JSON.Parse("{""a"":1,""b"":[13,1.4142135623730951,15]}")

    Debug.Print "a: " & jsObj.getItem("a")
    Set jsArray = jsObj.getItem("b")
    Debug.Print "Length of b: " & jsArray.getItem("length")
    Debug.Print "Second element of b: "; jsArray.getItem(1)

    Debug.Print "Iterate over all keys ..."
    Dim keys As Object
    Set keys = jsObj.getKeys("all")

    Dim i As Integer
    For i = 0 To keys.getItem("length") - 1
        Debug.Print keys.getItem(i) & ": " & jsObj.getItem(keys.getItem(i))
    Next i

    Call closeJSON
End Sub

outputs

Construction JS object ...
Object: {
    "a": 1,
    "b": [
        13,
        1.4142135623730951,
        15
    ]
}
Parsing JS object ...
a: 1
Length of b: 3
Second element of b:  1,4142135623731 
Iterate over all keys ...
a: 1
b: 13,1.4142135623730951,15

Hopefully this will be a big help to others who keep on coming to this page after searching for "vba json".

I found this page to be very helpful. It provides several Excel-compatible VBA classes that deal with processing data in JSON format.

UPDATE: Found a safer way of parsing JSON than using Eval, this blog post shows the dangers of Eval ... http://exceldevelopmentplatform.blogspot.com/2018/01/vba-parse-json-safer-with-jsonparse-and.html

Late to this party but sorry guys but by far the easiest way is to use Microsoft Script Control. Some sample code which uses VBA.CallByName to drill in

'Tools->References->
'Microsoft Script Control 1.0;  {0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}; C:\Windows\SysWOW64\msscript.ocx

Private Sub TestJSONParsingWithCallByName()

    Dim oScriptEngine As ScriptControl
    Set oScriptEngine = New ScriptControl
    oScriptEngine.Language = "JScript"

    Dim sJsonString As String
    sJsonString = "{'key1': 'value1'  ,'key2': { 'key3': 'value3' } }"


    Dim objJSON As Object
    Set objJSON = oScriptEngine.Eval("(" + sJsonString + ")")
    Debug.Assert VBA.CallByName(objJSON, "key1", VbGet) = "value1"
    Debug.Assert VBA.CallByName(VBA.CallByName(objJSON, "key2", VbGet), "key3", VbGet) = "value3"

End Sub

I have actually done a series of Q&As which explore JSON/VBA related topics.

Q1 In Excel VBA on Windows, how to mitigate issue of dot syntax traversal of parsed JSON broken by IDE's capitalisation behaviour?

Q2 In Excel VBA on Windows, how to loop through a JSON array parsed?

Q3 In Excel VBA on Windows, how to get stringified JSON respresentation instead of “[object Object]” for parsed JSON variables?

Q4 In Windows Excel VBA,how to get JSON keys to pre-empt “Run-time error '438': Object doesn't support this property or method”?

Q5 In Excel VBA on Windows, for parsed JSON variables what is this JScriptTypeInfo anyway?

Is There a JSON Parser for VB6 / VBA?

Related

Recent Posts