How to read JSON in VBA

In another post, I introduced the VBA-JSON library for reading and creating JSON. Today, I'd like to share a function to read JSON with VBA code.

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

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

The JsonGet function basically returns a value when given a key, as shown below.

Debug.Print JsonGet("key1", "{'key1': 'value1' ,'key2': { 'key3': 'value3' } }")

The second example is when there are nested key-values, which can be obtained by accessing the nested keys in order.

Debug.Print JsonGet("key2.key3", "{'key1': 'value1' ,'key2': { 'key3': 'value3' } }")

The value of key2 has a new, nested key3 and value value3.

Debug.Print JsonGet("mykey2.keyinternal1", "{mykey:1111, mykey2:{keyinternal1:22.1,keyinternal2:22.2}, mykey3:3333}")

mykey2.keyinternal1 returns the value of keyinternal1 in mykey2.

The third example is when you have multiple nested JSONs and duplicate keys, and you want to get the key Ask in the 0th JSON of "result.0.Ask". If you give "result.0.Ask", it will return 0.00000074.

Debug.Print JsonGet("result.0.Ask", "{'result':[{'MarketName':'BTC-1ST','Bid':0.00004718,'Ask':0.00004799},{'MarketName':'BTC-2GIVE','Bid':0.00000073,'Ask':0.00000074}]}")