jsonexcelparsingvbaobject

Parsing JSON in Excel VBA


I have the same issue as in Excel VBA: Parsed JSON Object Loop but cannot find any solution. My JSON has nested objects so suggested solution like VBJSON and vba-json do not work for me. I also fixed one of them to work properly but the result was a call stack overflow because of to many recursion of the doProcess function.

The best solution appears to be the jsonDecode function seen in the original post. It is very fast and highly effective; my object structure is all there in a generic VBA Object of type JScriptTypeInfo.

The issue at this point is that I cannot determine what will be the structure of the objects, therefore, I do not know beforehand the keys that will reside in each generic objects. I need to loop through the generic VBA Object to acquire the keys/properties.

If my parsing javascript function could trigger a VBA function or sub, that would be excellent.


Solution

  • If you want to build on top of ScriptControl, you can add a few helper method to get at the required information. The JScriptTypeInfo object is a bit unfortunate: it contains all the relevant information (as you can see in the Watch window) but it seems impossible to get at it with VBA. However, the Javascript engine can help us:

    Option Explicit
    
    Private ScriptEngine As ScriptControl
    
    Public Sub InitScriptEngine()
        Set ScriptEngine = New ScriptControl
        ScriptEngine.Language = "JScript"
        ScriptEngine.AddCode "function getProperty(jsonObj, propertyName) { return jsonObj[propertyName]; } "
        ScriptEngine.AddCode "function getKeys(jsonObj) { var keys = new Array(); for (var i in jsonObj) { keys.push(i); } return keys; } "
    End Sub
    
    Public Function DecodeJsonString(ByVal JsonString As String)
        Set DecodeJsonString = ScriptEngine.Eval("(" + JsonString + ")")
    End Function
    
    Public Function GetProperty(ByVal JsonObject As Object, ByVal propertyName As String) As Variant
        GetProperty = ScriptEngine.Run("getProperty", JsonObject, propertyName)
    End Function
    
    Public Function GetObjectProperty(ByVal JsonObject As Object, ByVal propertyName As String) As Object
        Set GetObjectProperty = ScriptEngine.Run("getProperty", JsonObject, propertyName)
    End Function
    
    Public Function GetKeys(ByVal JsonObject As Object) As String()
        Dim Length As Integer
        Dim KeysArray() As String
        Dim KeysObject As Object
        Dim Index As Integer
        Dim Key As Variant
    
        Set KeysObject = ScriptEngine.Run("getKeys", JsonObject)
        Length = GetProperty(KeysObject, "length")
        ReDim KeysArray(Length - 1)
        Index = 0
        For Each Key In KeysObject
            KeysArray(Index) = Key
            Index = Index + 1
        Next
        GetKeys = KeysArray
    End Function
    
    
    Public Sub TestJsonAccess()
        Dim JsonString As String
        Dim JsonObject As Object
        Dim Keys() As String
        Dim Value As Variant
        Dim j As Variant
    
        InitScriptEngine
    
        JsonString = "{""key1"": ""val1"", ""key2"": { ""key3"": ""val3"" } }"
        Set JsonObject = DecodeJsonString(CStr(JsonString))
        Keys = GetKeys(JsonObject)
    
        Value = GetProperty(JsonObject, "key1")
        Set Value = GetObjectProperty(JsonObject, "key2")
    End Sub
    

    A few notes:

    • If the JScriptTypeInfo instance refers to a Javascript object, For Each ... Next won't work. However, it does work if it refers to a Javascript array (see GetKeys function).
    • The access properties whose name is only known at run-time, use the functions GetProperty and GetObjectProperty.
    • The Javascript array provides the properties length, 0, Item 0, 1, Item 1 etc. With the VBA dot notation (jsonObject.property), only the length property is accessible and only if you declare a variable called length with all lowercase letters. Otherwise the case doesn't match and it won't find it. The other properties are not valid in VBA. So better use the GetProperty function.
    • The code uses early binding. So you have to add a reference to "Microsoft Script Control 1.0".
    • You have to call InitScriptEngine once before using the other functions to do some basic initialization.