I built a zero-copy JSON parser and writer for VBA with natural chained access

Hi r/vba,

I wanted to share a project I have been working on: a single-file **JSON parser** and writer for VBA.

The module is called JSON, and the goal is to make JSON handling in **Excel, Access, Word, PowerPoint, and other VBA hosts** fast, simple, and easy to drop into a project. You import one class file, JSON.cls, and then use the predeclared JSON class directly.

One of the greatest achievements of this module was parsing a gigantic 80MB JSON file in approximately 1-2 seconds.

A basic example looks like this:

Public Sub ReadJson() Dim text As String text = "{""name"":""Ryan"",""age"":18,""active"":true}"

Dim doc As JSON Set doc = JSON.Parse(text)

Debug.Print doc.StringValue("name") Debug.Print doc.NumberValue("age") Debug.Print doc.BoolValue("active") End Sub

Internally, it does not parse into nested Dictionaries or Collections. It builds a compact token tree over the original JSON text, then creates lightweight node wrappers only when you ask for them. That keeps parsing allocation lower, especially for larger API responses.

You can still use explicit accessors when you want clear typed reads:

Dim doc As JSON Set doc = JSON.Parse("{""user"":{""name"":""Ana"",""score"":42,""active"":true}}")

Dim user As JSON Set user = doc.Node("user")

Debug.Print user.StringValue("name") Debug.Print user.NumberValue("score") Debug.Print user.BoolValue("active")

One thing I especially wanted to point out is that the class also supports natural chained access through the default Item property. I do not see this style discussed much in VBA JSON examples, but it makes reads from known JSON shapes feel very clean:

Dim myJson As JSON Set myJson = JSON.Parse("{""names"": [""Ana"", ""Bia"", ""Caio""]}")

Debug.Print myJson("names")(0) Debug.Print myJson("names")(1) Debug.Print myJson("names").Count

That works because `myJson("names")` returns the array node, and then `(0)` reads the first item through the same default member.

For large arrays of objects, there is also token iteration so you do not need to create a wrapper object for every row:

Public Sub ReadRows(ByVal responseText As String) Dim doc As JSON Set doc = JSON.Parse(responseText)

Dim rows As JSON Set rows = doc.Node("rows")

If rows Is Nothing Then Exit Sub

Dim t As Long t = rows.FirstChildToken()

Do While t <> 0 Debug.Print rows.TokenString(t, "name") Debug.Print rows.TokenNumber(t, "score") Debug.Print rows.TokenBool(t, "active")

t = rows.NextToken(t) Loop End Sub

It also includes JSON writing. You can stringify parsed JSON, arrays, Collections, Dictionaries, primitive values, and nested JSON nodes:

Public Sub WriteJson() Dim data As Object Set data = CreateObject("Scripting.Dictionary")

data("name") = "JSON" data("language") = "VBA" data("fast") = True

Debug.Print JSON.StringifyValue(data, True) End Sub

Some current features:

* Single importable JSON.cls file * No external references required * Works with 32-bit and 64-bit Office * Zero-copy parsing over the source string * Lazy node wrappers * Typed accessors for strings, numbers, and booleans * Raw JSON field access * Token iteration for large arrays * Stringify support for common VBA values * Pretty printing and custom indentation

I built it for practical Office automation work where JSON responses can get large, and where I still want the calling VBA code to stay readable.

Github: [https://github.com/vbacollective/json](https://github.com/vbacollective/json)