How to Parse JSON With VBA

1. JSON Parsing
2. First Method to Parse JSON With VBA
3. Second Method to Parse JSON With VBA
4. Third Method to Parse JSON With VBA
5. Conclusion

JSON (JavaScript Object Notation) is a standard text-based format that represents structured data based on the object syntax of JavaScript. It is used to store and transfer data in web-based applications, for example, sending data from the server to the client that has to be displayed on a web page.

The syntax for JSON is as follows:

{ "name": "Alina", "age": 19, "gender": "female", }

The data in JSON is stored in key and value pairs, separated by commas. Each key is written in speech marks (""), and the corresponding value for the key follows after a colon.

JSON Parsing

Before discussing JSON parsing, it is necessary to discuss what parsing is. Converting a value from a datatype to another is known as parsing; for example, converting a string value to an int data type would require the user to parse the string value.

JSON parsing is converting the text-based JSON to JavaScript Objects that can be used in the program later. The parsing function also ensures that the data provided to it is valid JSON or not.

VBA allows you to parse JSON without using external libraries. Three solutions have been discussed below that enable the user to parse through JSON easily.

First Method to Parse JSON With VBA

The first method to parse JSON with the help of VBA is as follows:

Function DecodingOfJSON (jsonString As Variant)
    Set obj = CreateObject("ScriptControl"): obj.Language = "JScript"
    Set jsonDecode = obj.Eval("(" + jsonString + ")")
End Function

Sub main()
    Set arr = DecodingOfJSON(jsonString )
End Sub

The function DecodingOfJSON() takes jsonString as a parameter. Inside the function, an object obj is created, and the jsonString is evaluated.

The evaluation result of the jsonString is returned to an array arr, which stores all the JavaScript objects evaluated from JSON. Either ScriptControl or MSScriptControl.ScriptControl can be used to create an object.

Second Method to Parse JSON With VBA

The second method creates an XMLHTTP object. The client computer uses the XMLHTTP object to send an arbitrary HTTP request and receive a response from the request.

The Microsoft XML Document Object Model (DOM) parses the response and displays it according to the user’s requirements.

The code to parse JSON is shown below:

Sub parseJSON()

    Dim Book As Object
    Dim sc As Object

    Set sc = CreateObject("MSScriptControl.ScriptControl")
    sc.Language = "JScript"

    With CreateObject("MSXML2.XMLHTTP")

        .Open "GET", "http://www.omdbapi.com/?t=frozen&y=&plot=short&r=json", False
        .send
        Set Movie = sc.Eval("(" + .responsetext + ")")
        .abort

        With Sheets(7)
            .Cells(1, 1).Value = Book.Title
            .Cells(1, 2).Value = Book.Year
            .Cells(1, 3).Value = Book.Rated
            .Cells(1, 4).Value = Book.Released
            .Cells(1, 5).Value = Book.Writer

        End With
    End With
End Sub

The code creates a ScriptControl object to parse JSON. It gets the JSON from the URL mentioned in the .Open parameters.

The scripting language is set to JScript to parse JSON. The sc.Eval parses JSON and assigns the resulting JavaScript objects to the Book object.

The With statement allows the user to assign values of the JavaScript objects stored in the Book variable to different cells in sheet 7.

Note: The solution is a JScript parser instead of a JSON parser; it might install malicious code in the JSON. The ScriptControl.UseSafeSubset = True command can be used to avoid the JScript parser from adding any malicious code in the JSON.

Third Method to Parse JSON With VBA

The third method declares a Variant and splits the responsetext on the quote-comma-quote format that separates each item. The value can be extracted by looking for the quote that you require.

For example, if you require the last JavaScript object, find the last quote using the InStrRev function. The InStrRev function will return the object you were searching for.

Dim Items As Variant
Dim RequiredStr As Variant

Items = Split(.responsetext, """,""")
RequiredStr = Mid(Items(8), InStrRev(Items(8), """") + 1)

Conclusion

VBA allows the user to parse JSON without using any external libraries.

Macros can be created in Microsoft Applications to run the same lines of code repeatedly on different JSON. You only have to find the right solution that works for you and store it in a macro to use whenever required.