By Ben McCormack


2010-05-06 15:03:13 8 Comments

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?

14 comments

@dashmug 2012-06-14 17:19:53

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.

@Koray Tugay 2014-10-07 05:27:38

Which one would you recommend?

@Ben McCormack 2010-05-06 15:41:37

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.

@Brian Behm 2012-01-03 02:06:12

is there a way with VB-JSON to pass it a Class object and return the corresponding JSON string? Thanks!

@Alexandre H. Tremblay 2012-01-30 00:51:17

How do you loop trough objects? Say p.Item("AddressClassification") contains 3 items. How can I loop over the items?

@Ben McCormack 2012-01-30 16:15:28

@AlexandreH.Tremblay You should be able to loop through the item just like you would loop through any array in VB6/VBA

@Koray Tugay 2014-10-07 06:20:30

@BenMcCormack Can you take a look at this please stackoverflow.com/questions/26229563/… ?

@DanH 2019-05-22 18:52:46

Understand this is an old post, but I recently stumbled upon it while adding web service consumption to an old VB6 app. The accepted answer (VB-JSON) is still valid and appears to work. However, I discovered that Chilkat has been updated to include REST and JSON functionality, making it a one-stop (though paid) tool for me. They even have an online code generator that generates the code to parse pasted JSON data.

JsonObject link

Code Generator link

@SIM 2017-06-16 08:29:57

As Json is nothing but strings so it can easily be handled if we can manipulate it the right way, no matter how complex the structure is. I don't think it is necessary to use any external library or converter to do the trick. Here is an example where I've parsed json data using string manipulation.

Sub GetJsonContent()
    Dim http As New XMLHTTP60, itm As Variant

    With http
        .Open "GET", "http://jsonplaceholder.typicode.com/users", False
        .send
        itm = Split(.responseText, "id"":")
    End With

    x = UBound(itm)

    For y = 1 To x
        Cells(y, 1) = Split(Split(itm(y), "name"": """)(1), """")(0)
        Cells(y, 2) = Split(Split(itm(y), "username"": """)(1), """")(0)
        Cells(y, 3) = Split(Split(itm(y), "email"": """)(1), """")(0)
        Cells(y, 4) = Split(Split(itm(y), "street"": """)(1), """")(0)
    Next y
End Sub

@John Foll 2019-08-08 14:10:27

That will work for simple JSON objects. It is not generic enough for objects with nested collections and nested objects.

@hamish 2016-08-29 08:49:47

Formula in an EXCEL CELL

=JSON2("{mykey:1111, mykey2:{keyinternal1:22.1,keyinternal2:22.2}, mykey3:3333}", "mykey2", "keyinternal2")

DISPLAYS: 22.2

=JSON("{mykey:1111,mykey2:2222,mykey3:3333}", "mykey2")

DISPLAYS: 2222

  • INSTRUCTIONS:
  • Step1. press ALT+F11
  • Step2. Insert -> Module
  • Step3. tools -> references -> tick Microsoft Script Control 1.0
  • Step4. paste this below.
  • Step5. ALT+Q close VBA window.

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

Public Function JSON(sJsonString As String, Key As String) As String
On Error GoTo err_handler

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

    Dim objJSON As Object
    Set objJSON = oScriptEngine.Eval("(" + sJsonString + ")")

    JSON = VBA.CallByName(objJSON, Key, VbGet)

Err_Exit:
    Exit Function

err_handler:
    JSON = "Error: " & Err.Description
    Resume Err_Exit

End Function


Public Function JSON2(sJsonString As String, Key1 As String, Key2 As String) As String
On Error GoTo err_handler

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

    Dim objJSON As Object
    Set objJSON = oScriptEngine.Eval("(" + sJsonString + ")")

    JSON2 = VBA.CallByName(VBA.CallByName(objJSON, Key1, VbGet), Key2, VbGet)

Err_Exit:
    Exit Function

err_handler:
    JSON2 = "Error: " & Err.Description
    Resume Err_Exit

End Function

@S Meaden 2016-07-11 10:07:45

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?

@J. Chomel 2016-07-12 13:06:18

Worked for me, thanks!

@hamish 2016-08-29 08:33:32

WOW best answer out of 10 I tried.. ! thanks.

@lucid_dreamer 2017-05-31 17:28:29

This should be the answer.

@drgs 2017-07-17 20:05:59

JSON keys are case sensitive (keys in VBA object returned by oScriptEngine.Eval are not)

@remon78eg 2017-11-22 14:28:17

this is vb6 example code, tested ok,works done

from the above good examples, i made changes and got this good result

it can read keys {} and arrays []

Option Explicit
'in vb6 click "Tools"->"References" then
'check the box "Microsoft Script Control 1.0";
Dim oScriptEngine As New ScriptControl
Dim objJSON As Object

''to use it
Private Sub Command1_Click()
  MsgBox JsonGet("key1", "{'key1': 'value1'  ,'key2': { 'key3': 'value3' } }")''returns "value1"
  MsgBox JsonGet("key2.key3", "{'key1': 'value1'  ,'key2': { 'key3': 'value3' } }") ''returns "value3"
  MsgBox JsonGet("result.0.Ask", "{'result':[{'MarketName':'BTC-1ST','Bid':0.00004718,'Ask':0.00004799},{'MarketName':'BTC-2GIVE','Bid':0.00000073,'Ask':0.00000074}]}") ''returns "0.00004799"
  MsgBox JsonGet("mykey2.keyinternal1", "{mykey:1111, mykey2:{keyinternal1:22.1,keyinternal2:22.2}, mykey3:3333}") ''returns "22.1"
End Sub

Public Function JsonGet(eKey$, eJsonString$, Optional eDlim$ = ".") As String
  Dim tmp$()
  Static sJsonString$
  If Trim(eKey$) = "" Or Trim(eJsonString$) = "" Then Exit Function
  If sJsonString <> eJsonString Then
    sJsonString = eJsonString
    oScriptEngine.Language = "JScript"
    Set objJSON = oScriptEngine.Eval("(" + eJsonString + ")")
  End If
  tmp = Split(eKey, eDlim)
  If UBound(tmp) = 0 Then JsonGet = VBA.CallByName(objJSON, eKey, VbGet): Exit Function

  Dim i&, o As Object
  Set o = objJSON
  For i = 0 To UBound(tmp) - 1
    Set o = VBA.CallByName(o, tmp(i), VbGet)
  Next i
  JsonGet = VBA.CallByName(o, tmp(i), VbGet)
  Set o = Nothing
End Function

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
  Set objJSON = Nothing
End Sub

@Patrick Böker 2015-07-09 13:58:23

VBA-JSON by Tim Hall, MIT licensed and on GitHub. It's another fork of vba-json that emerged end of 2014. Claims to work on Mac Office and Windows 32bit and 64bit.

@ComputerVersteher 2019-09-12 12:11:26

Included in VBA-Web perfect for web request. Works on Mac too.

@ozmike 2013-11-11 06:33:02

Here is a "Native" VB JSON library.

It is possible to use JSON that is already in IE8+. This way your not dependent on a third party library that gets out of date and is untested.

see amedeus' alternative version here

Sub myJSONtest()


Dim oJson As Object
Set oJson = oIE_JSON() ' See below gets IE.JSON object

' using json objects
Debug.Print oJson.parse("{ ""hello"": ""world"" }").hello ' world
Debug.Print oJson.stringify(oJson.parse("{ ""hello"": ""world"" }")) ' {"hello":"world"}

' getting items
Debug.Print oJson.parse("{ ""key1"": ""value1"" }").key1 ' value1
Debug.Print oJson.parse("{ ""key1"": ""value1"" }").itemGet("key1") ' value1
Debug.Print oJson.parse("[ 1234, 4567]").itemGet(1) '  4567

' change  properties
Dim o As Object
Set o = oJson.parse("{ ""key1"": ""value1"" }")
o.propSetStr "key1", "value\""2"
Debug.Print o.itemGet("key1") ' value\"2
Debug.Print oJson.stringify(o) ' {"key1":"value\\\"2"}
o.propSetNum "key1", 123
Debug.Print o.itemGet("key1") ' 123
Debug.Print oJson.stringify(o) ' {"key1":123}

' add properties
o.propSetNum "newkey", 123 ' addkey! JS MAGIC
Debug.Print o.itemGet("newkey") ' 123
Debug.Print oJson.stringify(o) ' {"key1":123,"newkey":123}

' assign JSON 'objects' to properties
Dim o2 As Object
Set o2 = oJson.parse("{ ""object2"": ""object2value"" }")
o.propSetJSON "newkey", oJson.stringify(o2) ' set object
Debug.Print oJson.stringify(o) ' {"key1":123,"newkey":{"object2":"object2value"}}
Debug.Print o.itemGet("newkey").itemGet("object2") ' object2value

' change array items
Set o = oJson.parse("[ 1234, 4567]") '
Debug.Print oJson.stringify(o) ' [1234,4567]
Debug.Print o.itemGet(1)
o.itemSetStr 1, "234"
Debug.Print o.itemGet(1)
Debug.Print oJson.stringify(o) ' [1234,"234"]
o.itemSetNum 1, 234
Debug.Print o.itemGet(1)
Debug.Print oJson.stringify(o) ' [1234,234]

' add array items
o.itemSetNum 5, 234 ' add items! JS Magic
Debug.Print o.itemGet(5) ' 234
Debug.Print oJson.stringify(o) ' [1234,234,null,null,null,234]

' assign JSON object to array item
o.itemSetJSON 3, oJson.stringify(o2)  ' assign object
Debug.Print o.itemGet(3) '[object Object]
Debug.Print oJson.stringify(o.itemGet(3)) ' {"object2":"object2value"}
Debug.Print oJson.stringify(o) ' [1234,234,null,{"object2":"object2value"},null,234]


oIE_JSON_Quit ' quit IE, must shut down or the IE sessions remain.
Debug.Print oJson.stringify(o) ' can use after but but IE server will shutdown... soon
End Sub

You can bridge to IE.JSON from VB.
Create a function oIE_JSON

Public g_IE As Object ' global


Public Function oIE_JSON() As Object


    ' for array access o.itemGet(0) o.itemGet("key1")
    JSON_COM_extentions = "" & _
            " Object.prototype.itemGet        =function( i ) { return this[i] }   ;            " & _
            " Object.prototype.propSetStr     =function( prop , val ) { eval('this.' + prop + '  = ""' + protectDoubleQuotes (val) + '""' )   }    ;            " & _
            " Object.prototype.propSetNum     =function( prop , val ) { eval('this.' + prop + '  = ' + val + '')   }    ;            " & _
            " Object.prototype.propSetJSON    =function( prop , val ) { eval('this.' + prop + '  = ' + val + '')   }    ;            " & _
            " Object.prototype.itemSetStr     =function( prop , val ) { eval('this[' + prop + '] = ""' + protectDoubleQuotes (val) + '""' )   }    ;            " & _
            " Object.prototype.itemSetNum     =function( prop , val ) { eval('this[' + prop + '] = ' + val )   }    ;            " & _
            " Object.prototype.itemSetJSON    =function( prop , val ) { eval('this[' + prop + '] = ' + val )   }    ;            " & _
            " function protectDoubleQuotes (str)   { return str.replace(/\\/g, '\\\\').replace(/""/g,'\\""');   }"

    ' document.parentwindow.eval dosen't work some versions of ie eg ie10?
     IEEvalworkaroundjs = "" & _
         " function IEEvalWorkAroundInit ()   { " & _
         " var x=document.getElementById(""myIEEvalWorkAround"");" & _
         " x.IEEval= function( s ) { return eval(s) } ; } ;"

    g_JS_framework = "" & _
      JSON_COM_extentions & _
      IEEvalworkaroundjs

    ' need IE8 and DOC type
    g_JS_HTML = "<!DOCTYPE html>  " & _
         " <script>" & g_JS_framework & _
                  "</script>" & _
         " <body>" & _
         "<script  id=""myIEEvalWorkAround""  onclick=""IEEvalWorkAroundInit()""  ></script> " & _
                 " HEllo</body>"

On Error GoTo error_handler

' Create InternetExplorer Object
Set g_IE = CreateObject("InternetExplorer.Application")
With g_IE
    .navigate "about:blank"
    Do While .Busy: DoEvents: Loop
    Do While .ReadyState <> 4: DoEvents: Loop
    .Visible = False ' control IE interface window
    .Document.Write g_JS_HTML
End With

Set objID = g_IE.Document.getElementById("myIEEvalWorkAround")
objID.Click ' create  eval
Dim oJson As Object

'Set oJson = g_IE.Document.parentWindow.Eval("JSON") ' dosen't work some versions of IE
Set oJson = objID.IEEval("JSON")

Set objID = Nothing
Set oIE_JSON = oJson

Exit Function
error_handler:
MsgBox ("Unexpected Error, I'm quitting. " & Err.Description & ".  " & Err.Number)
g_IE.Quit
Set g_IE = Nothing

End Function

Public Function oIE_JSON_Quit()
         g_IE.Quit
         Exit Function
End Function

Up vote if you find useful

@Wolfgang Kuehn 2014-02-09 17:36:28

Does not work with Excel 2013 and IE10: Cannot invoke methods on the returned JSON object. All I can do is cstr(oJson), which indeed gives [object JSON]

@ozmike 2014-02-12 00:31:00

thx I don't have 2013 to test but once I do I will look into it. If you can find a work around tell us.

@ozmike 2014-02-12 00:42:29

Thanks @amadeus I see your work around - thanks

@cyboashu 2014-11-13 06:41:14

Using JavaScript features of parsing JSON, on top of ScriptControl, we can create a parser in VBA which will list each and every data point inside the JSON. No matter how nested or complex the data structure is, as long as we provide a valid JSON, this parser will return a complete tree structure.

JavaScript’s Eval, getKeys and getProperty methods provide building blocks for validating and reading JSON.

Coupled with a recursive function in VBA we can iterate through all the keys (up to nth level) in a JSON string. Then using a Tree control (used in this article) or a dictionary or even on a simple worksheet, we can arrange the JSON data as required.

Full VBA Code here.Using JavaScript features of parsing JSON, on top of ScriptControl, we can create a parser in VBA which will list each and every data point inside the JSON. No matter how nested or complex the data structure is, as long as we provide a valid JSON, this parser will return a complete tree structure.

JavaScript’s Eval, getKeys and getProperty methods provide building blocks for validating and reading JSON.

Coupled with a recursive function in VBA we can iterate through all the keys (up to nth level) in a JSON string. Then using a Tree control (used in this article) or a dictionary or even on a simple worksheet, we can arrange the JSON data as required.

Full VBA Code here.

@Bjoern Stiel 2014-05-11 09:02:56

You could write an Excel-DNA Add-in in VB.NET. Excel-DNA is a thin library that lets you write XLLs in .NET. This way you get access to the entire .NET universe and can use stuff like http://james.newtonking.com/json - a JSON framework that deserializes JSON in any custom class.

If you are interested, here's a write up of how to build a generic Excel JSON client for Excel using VB.NET:

http://optionexplicitvba.com/2014/05/09/developing-a-json-excel-add-in-with-vb-net/

And here's the link to the code: https://github.com/spreadgit/excel-json-client/blob/master/excel-json-client.dna

@Wolfgang Kuehn 2014-02-09 20:58:12

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

@Bob77 2013-10-21 07:30:03

VB6 - JsonBag, Another JSON Parser/Generator should also be importable into VBA with little trouble.

@MarkJ 2010-05-07 08:46:17

I would suggest using a .Net component. You can use .Net components from VB6 via Interop - here's a tutorial. My guess is that .Net components will be more reliable and better supported than anything produced for VB6.

There are components in the Microsoft .Net framework like DataContractJsonSerializer or JavaScriptSerializer. You could also use third party libraries like JSON.NET.

@Ben McCormack 2010-05-07 13:27:27

Thanks for the suggestion. You bring up a good point that .NET components will be better supported than anything in VB6. That is certainly the case. However (and I could be wrong here), JSON is simple enough that even VB6 shouldn't have a problem with it. The VB-JSON code that I mentioned has so far worked really well.

@MarkJ 2010-05-07 13:57:52

@Ben JSON is simple, but you say the google code project used as a starting point still managed to have several bugs, so it's still possible to get it wrong.

Related Questions

Sponsored Content

50 Answered Questions

[SOLVED] Can comments be used in JSON?

  • 2008-10-28 20:39:03
  • Michael Gundlach
  • 2007337 View
  • 7067 Score
  • 50 Answer
  • Tags:   json comments

38 Answered Questions

[SOLVED] Jackson with JSON: Unrecognized field, not marked as ignorable

20 Answered Questions

[SOLVED] How can I deserialize JSON to a simple Dictionary<string,string> in ASP.NET?

6 Answered Questions

[SOLVED] Why does Google prepend while(1); to their JSON responses?

54 Answered Questions

[SOLVED] How can I pretty-print JSON in a shell script?

22 Answered Questions

16 Answered Questions

[SOLVED] Parse JSON in JavaScript?

11 Answered Questions

[SOLVED] Serializing to JSON in jQuery

40 Answered Questions

[SOLVED] How do I format a Microsoft JSON date?

34 Answered Questions

[SOLVED] What is the correct JSON content type?

Sponsored Content