VBA JSON

JSON, or JavaScript Object Notation, is a simple text data format used to store data and transmit data between applications. JSON format is used with many RESTful web APIs. There are no built-in JSON modules or libraries in VBA but there is a third-party solution available online.

VBA-JSON

VBA-JSON is available under the MIT license on GitHub and can be downloaded and used freely. The module has an intuitive API and can be used to parse or create JSON data.

Note: It may be necessary to fully qualify references to the Scripting.Dictionary class type within the module.

Option Explicit

Public Sub Example()

    'Import JsonConverter.bas
    'Set a reference to the Microsoft Scripting Runtime Library

    Dim FilePath As String
    FilePath = "C:\example.json"

    Dim FSO As Scripting.FileSystemObject
    Set FSO = CreateObject("Scripting.FileSystemObject")

    Dim TS As Scripting.TextStream
    Set TS = FSO.OpenTextFile(FilePath)

    Dim JsonContent As String
    JsonContent = TS.ReadAll()

    Dim JsonDict As Scripting.Dictionary
    Set JsonDict = JsonConverter.ParseJson(JsonContent)

    Debug.Print JsonDict("Message")
    Debug.Print JsonDict("Number")
    Debug.Print JsonDict("Array")(1), JsonDict("Array")(2), JsonDict("Array")(3)
    Debug.Print JsonDict("Object")("Example")

End Sub

"C:\example.json"

{
"Message":"Hello, World!",
"Number":100,
"Array":[1, 2, 3],
"Object": {"Example":"Something"}
}