ModernJsonInVBA - GitLite

KO | EN

gitlite — search

#css #open-source #swift #tensorflow #machine-learning #cli #privacy #editor #c #artificial-intelligence

ModernJsonInVBA

(★ 22)

Turn JSON (and CSV / XML) text into clean Excel tables with one VBA module. Pick the part of the JSON you want, and it creates or updates a table for you. Keeps columns in a stable order and prevents accidental layout changes. No add-ins or extra libraries required. Just paste into Excel and run. Supports deep object and array nesting.

File Explorer

ZIP Download

- ModernJsonInVBA_AllO365.bas
- ModernJsonInVBA_Excel.bas
- .gitignore
- generate_payloads.py
- Performance_Payloads_.bas
- README.md
- Tests_CSVtoJSON.vba
- Tests_DottedKeys.vba
- Tests_EmptyUpsert.vba
- Tests_FileEncoding.vba
- Tests_JsonParser.vba
- Tests_Ndjson.vba
- Tests_NestedRoot.vba
- Tests_StringifyPretty.vba
- Tests_ToListObject.vba
- Tests_TryParse.vba
- Tests_UpsertReturns.vba
- Tests_XMLtoJSON.vba
- Json_Coalesce.bas
- Json_Common.bas
- Json_Csv.bas
- Json_Excel.bas
- Json_Excel_Export.bas
- Json_Model.bas
- Json_Ndjson.bas
- Json_Parser.bas
- Json_Serializer.bas
- Json_Tables.bas
- Json_Transforms.bas
- Json_Xml.bas
.gitignore
AGENTS.md
build_dist.py
CHANGELOG.md
CONFORMANCE.md
GETTING_STARTED.md
LICENSE
ModernJsonInVBA.xlsm
PERFORMANCE.md
README.md

# Use via CDN

jsDelivr

jsDelivr serves any public GitHub repository as a CDN with zero setup. Pick a version and a file to get a ready-to-paste link and snippet.

Version

File

Link

Example

Command Glossary

Commands referenced in this DOCs, explained below.

🔍

`HTTP`

View Details ▼

HTTPie: an HTTP client designed for testing, debugging, and generally interacting with APIs and HTTP servers.
See also: `xh`.

http {{https://example.com}}

Make a simple GET request (shows response headers and content):

http {{[-p|--print]}} {{H|B|h|b|m|Hh|Hhb|...}} {{https://example.com}}

Print specific parts of the content (`H`: request headers, `B`: request body, `h`: response headers, `b`: response body, `m`: response metadata):

http {{GET|POST|HEAD|PUT|PATCH|DELETE|...}} --proxy {{http|https}}:{{http://localhost:8080|socks5://localhost:9050|...}} {{https://example.com}}

Specify the HTTP method when sending a request and use a proxy to intercept the request:

# Project Badges

JSONTestSuite Visit
GitHub stars Visit
Last commit Visit
MIT License Visit

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

**Deterministic JSON (and CSV / XML) → Excel Tables → JSON Roundtrip**  
\
Pure VBA. No dependencies. No silent schema drift.  
\
RFC 8259 conformant: passes [JSONTestSuite](CONFORMANCE.md) with 95/95 valid documents accepted, 176/176 invalid documents rejected, and no crashes.  
\
Take nested or complex API payloads and  convert them into normalized Excel tables with parent keys preserved. Supports Excel formula and scalar value injection.

> [!TIP]
> **New here? Start with the [Getting Started guide](GETTING_STARTED.md).**
> Ten short examples, from a two-row table to nested API envelopes, each
> showing the JSON payload next to the Excel table it produces.

------------------------------------------------------------------------

## Contents

- [Getting Started guide (new users)](GETTING_STARTED.md)
- [Introduction](#introduction)
- [What This Solves](#what-this-solves)
- [Core Capabilities](#core-capabilities)
- [Performance](#performance)
  - [Payloads](#payloads)
  - [Timings (seconds)](#timings-seconds)
  - [Reproducing these numbers](#reproducing-these-numbers)
- [Conformance](#conformance)
- [Installation](#installation)
  - [Option 1: Single file, Excel](#option-1-single-file-excel-recommended)
  - [Option 2: Single file, all O365 apps](#option-2-single-file-all-o365-apps-word-powerpoint-access-excel)
  - [Option 3: The eleven source modules](#option-3-the-eleven-source-modules)
  - [Option 4: Use the provided workbook](#option-4-use-the-provided-workbook)
- [Requirements](#requirements)
- [Basic API Example](#basic-api-example)
  - [Refresh Mode](#refresh-mode)
  - [Append Mode](#append-mode)
  - [Strict Schema Mode](#strict-schema-mode)
  - [API Example With Nested Objects](#api-example-with-nested-objects)
- [Accessing Json Elements (Directly in VBA)](#accessing-json-elements-directly-in-vba)
- [Excel to JSON (Reverse Materialization)](#excel-to-json-reverse-materialization)
- [Understanding tableRoot](#understanding-tableroot)
- [HTTP Helper (Windows)](#http-helper-windows)
- [Schema Control](#schema-control)
- [Deterministic Errors](#deterministic-errors)
- [Function & Subroutine Reference](#function--subroutine-reference)

------------------------------------------------------------------------

## Introduction

**ModernJsonInVBA** is a pure-VBA JSON engine for structured Excel
workflows, organized as a small set of focused modules (parser,
serializer, transforms, tables, CSV/XML conversion, Excel integration).

It materializes JSON text into Excel tables (ListObjects) with
predictable, repeatable behavior.

The focus is control:

-   Explicit structural rules
-   Intentional schema changes
-   Deterministic failures
-   No hidden behavior

Excel becomes a controlled data surface rather than a loosely
interpreted one.

See the .XLSM file for:

-   Unit Tests
-   Examples
-   Performance Testing
-   A Quick Start Module
-   CSV / XML → Json Pipeline

------------------------------------------------------------------------

## What This Solves

Working with JSON in Excel commonly leads to:

-   Columns appearing in different orders
-   Tables silently changing shape
-   Layout drift over time
-   Hidden external dependencies
-   Fragile refresh logic

ModernJsonInVBA eliminates those risks through:

-   Stable column discovery order
-   Strict structural validation
-   Explicit schema controls
-   Deterministic error behavior

When JSON structure does not match the declared `tableRoot`, execution
stops with a clear, stable error.

No guessing.
No fallback tables.

------------------------------------------------------------------------

## Core Capabilities

-   Parse JSON into VBA Variants (objects, arrays, primitives)
-   RFC 8259 strict, verified against the full [JSONTestSuite](CONFORMANCE.md) conformance corpus
-   Convert VBA structures back into JSON
-   Flatten and rebuild object graphs
-   Discover array-of-object roots
-   Convert JSON tables into 2D arrays
-   Upsert Excel ListObjects deterministically
-   Enforce strict schema contracts when required
-   Round trip json → list object → json
-   Full non-BMP Unicode support via surrogate-pair parsing
-   Linear-time string processing for high-volume data
-   Handles arrays and objects nested to any depth

All implemented in pure VBA.

-   No `Scripting.Dictionary`
-   No COM references
-   No external libraries

<pre>
JSON Text
   ↓
Parser
   ↓
Tagged Object Model
   ↓
Array-of-Objects Root
   ↓
2D Array Materialization
   ↓
Excel ListObject Upsert
</pre>
------------------------------------------------------------------------

## Performance

Real-world timings across the JSON parser and Excel ListObject surface,
measured on seven deterministic payloads (~200 MB total) generated by
`json_payloads/generate_payloads.py`. The same table ships as
[PERFORMANCE.md](PERFORMANCE.md) and is regenerable with one macro.

**Benchmark environment**

- AMD Ryzen 7 9800X3D, 64 GB RAM
- Excel 16.0, 64-bit VBA
- Each value is one wall-clock `Timer` run; payloads are seeded, so the
  numbers are comparable across runs and machines

Everything is pure VBA: no `Scripting.Dictionary`, no COM references, no
external libraries.

### Payloads

| Payload | Rows | Cols | Size |
|---|---:|---:|---:|
| flat_10k | 10,000 | 10 | 2.1 MB |
| nested_50k | 50,000 | 9 | 9.2 MB |
| escapes_50k | 50,000 | 6 | 14.8 MB |
| wide_5k_200c | 5,000 | 200 | 16.0 MB |
| flat_100k | 100,000 | 10 | 21.6 MB |
| numbers_200k | 200,000 | 8 | 25.9 MB |
| flat_500k | 500,000 | 10 | 109.5 MB |

`flat_*` are plain mixed-type rows at three sizes; `nested` adds dotted
columns and tag arrays; `escapes` is deliberately hostile (every string
carries quotes, backslashes, newlines, and emoji); `wide` is 200 columns;
`numbers` is numeric-heavy.

### Timings (seconds)

Rows are pipeline steps; columns are payloads. Lower is better.

| Step | flat_10k | nested_50k | escapes_50k | wide_5k_200c | flat_100k | numbers_200k | flat_500k |
|---|---:|---:|---:|---:|---:|---:|---:|
| Read file | 0.0273 | 0.0977 | 0.1602 | 0.1719 | 0.2266 | 0.2695 | 1.1367 |
| Json_Parse (JSON to model) | 0.2383 | 1.5547 | 1.3242 | 2.2383 | 2.3672 | 4.3867 | 11.8320 |
| Json_Stringify (model to JSON) | 0.2461 | 1.6563 | 1.3828 | 1.7227 | 2.5625 | 2.9727 | 12.8242 |
| Upsert create (JSON to ListObject) | 0.3164 | 2.4492 | 2.0625 | 2.8828 | 3.3906 | 4.6719 | 17.6680 |
| Upsert refresh | 0.3789 | 2.7070 | 2.3711 | 3.4805 | 3.9219 | 5.6563 | 20.1758 |
| Export (ListObject to JSON) | 0.1211 | 3.4766 | 0.9414 | 0.7500 | 1.2617 | 0.9297 | 6.4297 |

A **500,000-row, 110 MB** JSON document parses into the in-memory model in
~12 s and materializes into a live Excel table (`Upsert create`) in ~18 s,
about **28,000 rows/sec**, written to the sheet in a single block with no
chunking. Numeric-heavy rows load fastest, near **43,000 rows/sec**.
`Upsert create` streams JSON straight into a 2-D array with no intermediate
object model, so the time beyond parsing is just the single Excel write.
Streaming covers nested table roots too (`$.data.items`): sibling members
around the table are skipped with validating scanners instead of being built
into the model, which cut a 150,000-row nested import from 4.1 s to 2.4 s.
Export clears **17 MB/s** on flat data and **28 MB/s** on numeric-heavy
data (integral cell numbers print through the integer formatter), slowing
only for `nested`, which rebuilds dotted column paths
(`customer.address.city`) object by object.

### Reproducing these numbers

```text
1. python json_payloads/generate_payloads.py     ' generate the payloads
2. Open ModernJsonInVBA.xlsm, press ALT+F11, then Ctrl+G (Immediate window)
3. Run_JsonPerfMatrix     ' the table above (Run_JsonPerfSuite for a per-payload view)
```

Both macros print Markdown to the Immediate window, ready to paste into an
issue or PR. Drop your own `*.json` files into `json_payloads/` and they are
picked up automatically.

## Conformance

The parser passes [JSONTestSuite](https://github.com/nst/JSONTestSuite), the
standard RFC 8259 conformance corpus: **95/95** valid documents accepted,
**176/176** invalid documents rejected, no crashes (deep-nesting attacks
reject through a trappable error instead of terminating the host). Files
whose point is invalid bytes rather than invalid JSON exercise
`Json_ReadTextFile`, the encoding-detecting file reader (UTF-8 with strict
validation, UTF-16 LE/BE by BOM, ANSI fallback for legacy files). Results,
implementation-defined choices, and reproduction steps are in
[CONFORMANCE.md](CONFORMANCE.md).

## Installation

Pick whichever fits your host and workflow.

### Option 1: Single file, Excel (recommended)

1.  Download `dist/ModernJsonInVBA_Excel.bas`
2.  Open your workbook and press `ALT + F11`
3.  File → Import File... and select that one file
4.  Save as `.xlsm`

One module holds the whole library: JSON parse and serialize, CSV and XML
conversion, and the Excel ListObject integration.

------------------------------------------------------------------------

### Option 2: Single file, all O365 apps (Word, PowerPoint, Access, Excel)

1.  Download `dist/ModernJsonInVBA_AllO365.bas`
2.  Open your Office document and press `ALT + F11`
3.  File → Import File... and select that one file

This module has no Excel references, so it imports into any O365 VBA host.
It covers JSON parse and serialize, flatten and unflatten, path resolution,
table extraction to a 2D array, and CSV and XML conversion. It leaves out the
`Excel_*` ListObject functions, which need Excel. Both single-file modules use
the same module name and the same function names, so code written against the
all-O365 module keeps working if you later move to the Excel module.

------------------------------------------------------------------------

### Option 3: The eleven source modules

Import the individual `.bas` files from `vba_source/` instead of a single
file. Use this to read or edit the library by concern, or to track it in
version control. All eleven are required:

| Module              | Responsibility                                    |
|---------------------|---------------------------------------------------|
| `Json_Common`       | Shared plumbing: tag constant, path utilities, hash index, string builder |
| `Json_Parser`       | JSON text → in-memory model (`Json_Parse`)        |
| `Json_Serializer`   | Model → JSON text (`Json_Stringify`)              |
| `Json_Model`        | Type checks, object get/set, path resolution      |
| `Json_Transforms`   | Flatten / unflatten / array-root discovery        |
| `Json_Tables`       | Table extraction, 2D conversion                   |
| `Json_Coalesce`     | Merging JSON arrays (child hoisting, concatenation) |
| `Json_Csv`          | CSV → JSON conversion                             |
| `Json_Xml`          | XML → JSON conversion                             |
| `Json_Excel`        | ListObject ingestion (upsert pipeline)            |
| `Json_Excel_Export` | ListObject / range → JSON export                  |

The two `dist/` single files are generated from these modules by
`build_dist.py`; edit the modules here and re-run it. The public API is
unchanged from the earlier single-module releases, so existing code keeps
working as-is.

------------------------------------------------------------------------

### Option 4: Use the provided workbook

1.  Download `ModernJsonInVBA.xlsm`
2.  Open the file and enable macros

You may export or import the modules into another workbook if needed.

------------------------------------------------------------------------

## Requirements

-   Excel with VBA support (Windows and macOS) for the Excel table features
-   Any VBA host (Word, PowerPoint, Access, Excel) for the all-O365 module
-   Macros enabled
-   No external references required

------------------------------------------------------------------------

## Basic API Example

Endpoint used:

https://jsonplaceholder.typicode.com/users

This endpoint returns a root array. `tableRoot` is `$`.

### Refresh Mode

``` vba
Public Sub Example_Api_Refresh()

Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Quick Start")

Dim jsonText As String
    jsonText = HttpGetText("https://jsonplaceholder.typicode.com/users")

'Clear existing values, add missing columns from JSON, preserve columns not found in JSON, preserve existing formulas
    Excel_UpsertListObjectFromJsonAtRoot _
        ws, "tUsers", ws.Range("A1"), _
        jsonText, "$", _
        True, True, False, True
    
    'Preserve existing values, append new values from JSON, ignore missing columns from JSON, preserve columns not found in JSON, preserve existing formulas, don't add formulas to newly appended data
    Excel_UpsertListObjectFromJsonAtRoot _
        ws, "tUsers2", ws.Range("A15"), _
        jsonText, "$", _
        False, True, False, True, False

End Sub
```

### Append Mode

``` vba
Excel_UpsertListObjectFromJsonAtRoot _
    ws, "tUsersLog", ws.Range("A1"), _
    jsonText, "$", _
    False, True, False
```

### Strict Schema Mode

``` vba
Excel_UpsertListObjectFromJsonAtRoot _
    ws, "tUsersStrict", ws.Range("A1"), _
    jsonText, "$", _
    True, False, True
```

------------------------------------------------------------------------

### API Example With Nested Objects

Also see: [API Product Intelligence Demo Case Study](https://github.com/WilliamSmithEdward/APIProductIntelligenceDemo)

``` vba

Public Sub Example_Api_Refresh()

On Error GoTo CleanFail

Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Quick Start")

' Fetch API
    Dim jsonText As String
    jsonText = HttpGetText("https://dummyjson.com/products")

' Products table
    Excel_UpsertListObjectFromJsonAtRoot _
        ws, "tProducts", ws.Range("A1"), _
        jsonText, "$.products", _
        True, True, False, True, True, True

' ---------------------------------------------
    ' Build key map for parent injections
    ' ---------------------------------------------
    Dim keyMap As New Collection
    keyMap.Add Array("id", "productId")

' Extract + merge reviews with parent key injected
    Dim reviewsJson As String
    reviewsJson = Json_CoalesceChildArrays( _
        jsonText, _
        "$.products", _
        "reviews", _
        True, _
        keyMap)

' Reviews table
    Excel_UpsertListObjectFromJsonAtRoot _
        ws, "tReviews", ws.Range("A35"), _
        reviewsJson, "$", _
        True, True, False, True, True, True

CleanExit:

Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Exit Sub

CleanFail:
    Resume CleanExit

End Sub
```

------------------------------------------------------------------------

### HTTP Helper (Windows)

``` vba
Private Function HttpGetText(ByVal url As String) As String

Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP.6.0")

http.Open "GET", url, False
    http.setRequestHeader "Accept", "application/json"
    http.send

If http.Status < 200 Or http.Status >= 300 Then
        Err.Raise vbObjectError + 1500, "HttpGetText", _
            "HTTP " & http.Status & " " & http.statusText & " | " & url
    End If

HttpGetText = CStr(http.responseText)

End Function
```

### HTTP Helper (Mac)

```vba
Private Function HttpGetTextMac(ByVal url As String) As String

Dim cmd As String
    Dim result As String
    
    ' Use curl (installed on all modern macOS systems)
    cmd = "curl -s -L -H ""Accept: application/json"" """ & url & """"
    
    result = MacScript("do shell script " & Chr(34) & cmd & Chr(34))
    
    If Len(result) = 0 Then
        Err.Raise vbObjectError + 1500, "HttpGetTextMac", _
            "HTTP request returned empty response | " & url
    End If
    
    HttpGetTextMac = result

End Function
```

------------------------------------------------------------------------

## Accessing Json Elements (Directly in VBA)

```vba
' =============================================================================
' Example_ReadValuesFromJson
'
' Purpose
'   Demonstrates how to extract values from JSON using the ModernJsonInVBA library.
'
' What this example shows
'   1) Parse JSON text into an in-memory structure
'   2) Access top-level values
'   3) Access nested values
'   4) Iterate arrays
'   5) Read properties from objects
'
' Important idea
'   JSON contains three main structures:
'
'       Object  -> { key : value }
'       Array   -> [ value, value, value ]
'       Value   -> text, number, true/false, or null
'
'   In this library:
'
'       JSON Object  -> VBA Collection (tagged internally)
'       JSON Array   -> VBA Collection
'       Values       -> normal VBA types (String, Double, Boolean, etc.)
'
' =============================================================================
Public Sub Example_ReadValuesFromJson()

' ------------------------------------------------------------
    ' Step 1: Create some example JSON
    ' (Normally this would come from an API response)
    ' ------------------------------------------------------------
    
    Dim jsonText As String
    
    jsonText = "{"
    jsonText = jsonText & """orders"":["
    
    jsonText = jsonText & "{"
    jsonText = jsonText & """orderId"":""A100"","
    jsonText = jsonText & """customer"":{""id"":""C01"",""name"":""Ada""},"
    jsonText = jsonText & """status"":""open"","
    jsonText = jsonText & """items"":["
    
    jsonText = jsonText & "{""sku"":""SKU-1"",""qty"":2,""price"":9.99,""promos"":[""P10"",""P20""]},"
    jsonText = jsonText & "{""sku"":""SKU-2"",""qty"":1,""price"":19.5,""promos"":[]}"
    
    jsonText = jsonText & "]"
    jsonText = jsonText & "},"
    
    jsonText = jsonText & "{"
    jsonText = jsonText & """orderId"":""A101"","
    jsonText = jsonText & """customer"":{""id"":""C02"",""name"":""Grace""},"
    jsonText = jsonText & """status"":""shipped"","
    jsonText = jsonText & """items"":["
    
    jsonText = jsonText & "{""sku"":""SKU-3"",""qty"":4,""price"":2.5,""promos"":[""P5""]}"
    
    jsonText = jsonText & "]"
    jsonText = jsonText & "}"
    
    jsonText = jsonText & "]"
    jsonText = jsonText & "}"
    
    
    ' ------------------------------------------------------------
    ' Step 2: Parse the JSON text
    '
    ' Json_ParseInto converts the JSON string into an in-memory
    ' structure that VBA can navigate.
    ' ------------------------------------------------------------
    
    Dim root As Variant
    Json_ParseInto jsonText, root
    
    
    ' ------------------------------------------------------------
    ' Step 3: Access a value using a JSON path
    '
    ' "$.orders[0].orderId"
    ' means:
    '
    ' root
    '   -> orders array
    '       -> first element
    '           -> orderId property
    ' ------------------------------------------------------------
    
    Dim orderIdV As Variant
    
    If Json_TryResolvePath(root, "$.orders[0].orderId", orderIdV) Then
        Debug.Print "First orderId:", orderIdV
    End If
    
    
    ' ------------------------------------------------------------
    ' Step 4: Access nested values
    ' ------------------------------------------------------------
    
    Dim custName As Variant
    
    Json_TryResolvePath root, "$.orders[1].customer.name", custName
    
    Debug.Print "Second order customer:", custName
    
    
    ' ------------------------------------------------------------
    ' Step 5: Extract the orders array
    '
    ' Arrays are represented as VBA Collections
    ' ------------------------------------------------------------
    
    Dim ordersV As Variant
    
    Json_TryResolvePath root, "$.orders", ordersV
    
    Dim orders As Collection
    Set orders = ordersV
    
    
    ' ------------------------------------------------------------
    ' Step 6: Loop through each order
    ' ------------------------------------------------------------
    
    Dim orderObj As Collection
    
    For Each orderObj In orders
        
        Dim idV As Variant
        Json_TryObjGet orderObj, "orderId", idV
        
        Debug.Print "Order:", idV
        
        
        ' --------------------------------------------------------
        ' Step 7: Access the items array inside each order
        ' --------------------------------------------------------
        
        Dim itemsV As Variant
        
        If Json_TryObjGet(orderObj, "items", itemsV) Then
            
            Dim items As Collection
            Set items = itemsV
            
            Dim itemObj As Collection
            
            For Each itemObj In items
                
                Dim skuV As Variant
                Json_TryObjGet itemObj, "sku", skuV
                
                Debug.Print "  SKU:", skuV
                
                
                ' ------------------------------------------------
                ' Step 8: Access the promo codes array
                ' ------------------------------------------------
                
                Dim promosV As Variant
                
                If Json_TryObjGet(itemObj, "promos", promosV) Then
                    
                    Dim promos As Collection
                    Set promos = promosV
                    
                    Dim promo As Variant
                    
                    For Each promo In promos
                        Debug.Print "    Promo:", promo
                    Next promo
                    
                End If
                
            Next itemObj
            
        End If
        
    Next orderObj
    
    
End Sub
```

------------------------------------------------------------------------

## Excel to JSON (Reverse Materialization)

ModernJsonInVBA also runs in reverse: deterministic **Excel Table to JSON**
conversion.

This enables:

- Exporting structured tables to APIs
- Serializing curated Excel datasets
- Creating reproducible JSON snapshots
- Round-trip validation workflows

### Function

```vb
Excel_ListObjectToJson(lo As ListObject, Optional includeBlanksAsNull As Boolean = False) As String
```

### Behavior

- Each table row becomes a JSON object
- Column order is preserved
- Row order is preserved
- Headers become property names
- Nested paths supported via dot notation (`a.b.c`)
- Literal dots supported via escape (`a\.b`)
- Array index paths (`[0]`) are intentionally rejected (error 905)
- Blank cells:
  - Skipped by default (key omitted)
  - Optional `includeBlanksAsNull=True` to emit explicit `null`

### Example

Given a table:

| id | name    | active |
|---:|---------|:------:|
| 1  | Alice   | TRUE   |
| 2  | Bob     | FALSE  |
| 3  | Charlie | TRUE   |

```vb
Dim jsonText As String
jsonText = Excel_ListObjectToJson(lo)
```

Produces:

```json
[
  {"id":1,"name":"Alice","active":true},
  {"id":2,"name":"Bob","active":false},
  {"id":3,"name":"Charlie","active":true}
]
```

### From a plain range

`Excel_RangeToJson` runs the same conversion on a plain range rather than a
formal ListObject. By default the first row supplies the property names:

```vb
' A1:C4 holds the same header row and data as the table above
jsonText = Excel_RangeToJson(ws.Range("A1:C4"))
```

A range holding the same data produces byte-identical JSON to the table
version. Pass `hasHeaderRow:=False` when the range has no header row; columns
are then named `Column1`, `Column2`, and so on. The remaining options
(`includeBlanksAsNull`, `parseJsonInCells`, `parseArraysOnly`,
`preserveFormulas`) match `Excel_ListObjectToJson`. A header-only range
returns `[]`.

### Determinism Guarantees

- No silent type coercion
- No hidden schema mutation
- Excel errors (`#N/A`, etc.) trigger stable error 1170
- Duplicate headers trigger 1121
- Blank headers trigger 1120

### Why This Matters

Most VBA JSON libraries only parse JSON.

ModernJsonInVBA supports **bidirectional structured transformation**:

```text
JSON Text
   ↓
Excel Table
   ↓
JSON Text
```

Excel becomes a structured JSON surface with full round-trip support.

## Understanding `tableRoot`

`tableRoot` defines which portion of the JSON becomes the Excel table.

It must resolve to:

-   An array of objects
-   Or `null` (treated as zero rows)

Anything else triggers a deterministic error.

Supported path patterns:

-   `$`
-   `$.property`
-   `$.property.child`
-   `$.array[0].items`
-   `$.a\.b.items` (a key containing a literal dot, escaped)

Zero-based indexing inside brackets.

A key that itself contains a dot is addressed with the same escape the
column headers use: `\.` is a literal dot inside a key and `\\` a literal
backslash. `$.a\.b.items` walks the key `a.b`; `$.a.b.items` walks `a` then
`b`. The same escapes work in `Json_TryResolvePath` and the coalesce paths.
Keys containing `[` are the one shape a path cannot address; read those
through `Json_Parse` + `Json_TryObjGet`, which take the literal key.

------------------------------------------------------------------------

## Schema Control

Three switches govern update behavior:

-   `clearExisting`
-   `addMissingColumns`
-   `removeMissingColumns`

### Recommended Default

True, True, False

Rows refresh.
New columns allowed.
Columns never disappear.

### Empty results

An empty array (or a null table root) never disturbs an existing table's
layout: a refresh clears the rows and keeps the schema, an append changes
nothing. When there is no prior table, the result is a zero-row table with
a single placeholder column named `value`, because a ListObject cannot
have zero columns; the first result with real headers replaces that
placeholder schema instead of merging with it.

------------------------------------------------------------------------

## Deterministic Errors

The engine stops execution on structural violations.

Common cases:

-   `tableRoot` not found → 1160
-   `tableRoot` not array-of-objects → 1162 / 1163
-   Duplicate headers → 1121
-   Blank headers → 1120
-   Invalid flag combination → 1101

Errors protect against:

-   Silent schema drift
-   Column collapse
-   Partial table corruption
-   Ambiguous data states

------------------------------------------------------------------------

## Function / Subroutine Reference

### Enums

- `Public Enum ExcelSourceFormat`  
  Source format selector for `Excel_UpsertListObjectFromSource`.

- `ExcelSourceFormat_JSON = 1`  
    Treat input as JSON text.
  - `ExcelSourceFormat_CSV = 2`  
    Treat input as CSV text.
  - `ExcelSourceFormat_XML = 3`  
    Treat input as XML text.

### JSON Parse / Serialize

- `Public Function Json_Parse(ByVal jsonText As String) As Variant`  
  Parses JSON text into the library's in-memory model. Returns a tagged `Collection` for objects, an untagged `Collection` for arrays, or a primitive `Variant` for scalar values.

- `Public Sub Json_ParseInto(ByVal jsonText As String, ByRef outValue As Variant)`  
  Parses JSON text into an output `Variant` passed by reference.

- `Public Function Json_TryParse(ByVal jsonText As String, ByRef outValue As Variant, Optional ByRef outError As String) As Boolean`  
  Non-raising parse. Returns `True` and fills `outValue` on success (with `outError` set to `""`); returns `False` on malformed input, with `outValue` set to `Null` and `outError` holding the position-aware reason (the same message `Json_Parse` would have raised). Use it for JSON of uncertain provenance (an API response that might be an error page, pasted input, an untrusted file) so you branch on the return value instead of installing an error handler.

- `Public Function Json_Stringify(ByVal v As Variant) As String`  
  Serializes the in-memory JSON model back into deterministic JSON text (compact, single line).

- `Public Function Json_StringifyPretty(ByVal v As Variant, Optional ByVal indentUnit As String = "  ") As String`  
  Serializes the model into indented ("pretty") JSON text, one member or element per line. `indentUnit` is repeated once per nesting level (two spaces by default; pass `vbTab` for tabs). Empty objects and arrays stay inline as `{}` and `[]`. The escaping, number formatting, and error contract match `Json_Stringify`, so the output parses back to an identical model.

## JSON Type / Object Helpers

- `Public Function Json_IsObject(ByVal v As Variant) As Boolean`  
  Returns `True` when `v` is a tagged JSON object in this library's model.

- `Public Function Json_IsArray(ByVal v As Variant) As Boolean`  
  Returns `True` when `v` is an untagged JSON array in this library's model.

- `Public Sub Json_ObjSet(ByVal obj As Collection, ByVal key As String, ByVal value As Variant)`  
  Sets or overwrites a property on a tagged JSON object while preserving deterministic order.

- `Public Function Json_ObjGet(ByVal obj As Collection, ByVal key As String) As Variant`  
  Gets a property value from a tagged JSON object. Raises an error if the key is missing.

- `Public Function Json_TryObjGet(ByVal obj As Collection, ByVal key As String, ByRef outValue As Variant) As Boolean`  
  Attempts to get a property value from a tagged JSON object without raising when the key is missing.

### Flatten / Unflatten / Path Utilities

- `Public Function Json_Flatten(ByVal parsedJson As Variant, Optional ByVal maxDepth As Long = 12, Optional ByVal tableRootToExpand As String = vbNullString, Optional ByVal arrayMode As Long = 0) As Collection`  
  Flattens nested JSON into a tagged object of `[path, value]` pairs for inspection, extraction, and table shaping.

- `Public Function Json_FlatGet(ByVal flatObj As Collection, ByVal path As String) As Variant`  
  Returns the primitive value stored at an exact flattened path.

- `Public Function Json_FlatContains(ByVal flatObj As Collection, ByVal path As String) As Boolean`  
  Returns `True` if the flattened object contains an exact path.

- `Public Function Json_Unflatten(ByVal flatObj As Collection) As Collection`  
  Reconstructs a nested tagged object from flattened `[path, value]` pairs. Array index paths are not supported.

- `Public Function Json_TryResolvePath(ByVal root As Variant, ByVal path As String, ByRef outValue As Variant) As Boolean`  
  Resolves a minimal JSONPath-like expression such as `$.orders[0].id`.

- `Public Function Json_TryReadBracketIndex(ByVal path As String, ByRef i As Long, ByRef outIndex As Long) As Boolean`  
  Low-level helper that parses a bracket index like `[3]` from a path string.

- `Public Function Json_ResolveArrayPath(ByVal root As Variant, ByVal path As String) As Collection`  
  Resolves a simple path like `$.products` directly to a JSON array collection.

### Array-of-Object Discovery / Table Extraction

- `Public Function Json_FindArrayObjectRoots(ByVal flatObj As Collection, Optional ByVal stopAfterFirst As Boolean = False) As Collection`  
  Scans flattened paths and returns candidate roots that look like array-of-object tables.

- `Public Function Json_ExtractTableRows(ByVal flatObj As Collection, ByVal tableRoot As String) As Collection`  
  Extracts tagged row objects from flattened JSON for a specific table root.

- `Public Function Json_TableTo2D(ByVal rows As Collection, ByRef headers As Variant) As Variant`  
  Converts a collection of tagged row objects into a 2D Excel-ready array and outputs headers in first-seen order.

### Excel ListObject Helpers / Upsert Pipeline

- `Public Function Excel_GetListObject(ByVal ws As Worksheet, ByVal tableName As String) As ListObject`  
  Finds a `ListObject` by name on a worksheet.

- `Public Function Excel_EnsureListObject(ByVal ws As Worksheet, ByVal tableName As String, ByVal topLeft As Range, ByVal headers As Variant) As ListObject`  
  Ensures a table exists. Creates it if missing using the supplied header list and top-left anchor.

- `Public Function Excel_UpsertListObjectOnSheet(ByVal ws As Worksheet, ByVal tableName As String, ByVal topLeft As Range, ByVal headers As Variant, ByVal data2D As Variant, Optional ByVal clearExisting As Boolean = True, Optional ByVal addMissingColumns As Boolean = True, Optional ByVal removeMissingColumns As Boolean = False, Optional ByVal preserveFormulaColumns As Boolean = True, Optional ByVal fillFormulasOnAppend As Boolean = True) As ListObject`  
  Core Excel upsert entry point for writing headers and a 2D array into a `ListObject`, with schema evolution and formula-preservation options. Returns the created or updated `ListObject`.

- `Public Sub Excel_ResizeTableToRowCol(ByVal lo As ListObject, ByVal finalHeaders As Variant, ByVal bodyRowCount As Long)`  
  Resizes a `ListObject` to the requested header/body shape while handling Excel table materialization edge cases.

- `Public Function Excel_UpsertListObjectFromJsonAtRoot(ByVal ws As Worksheet, ByVal tableName As String, ByVal topLeft As Range, ByVal jsonText As String, Optional ByVal tableRoot As String = "$", Optional ByVal clearExisting As Boolean = True, Optional ByVal addMissingColumns As Boolean = True, Optional ByVal removeMissingColumns As Boolean = False, Optional ByVal preserveFormulaColumns As Boolean = True, Optional ByVal fillFormulasOnAppend As Boolean = True, Optional ByVal nonTableArraysAsJson As Boolean = False) As ListObject`  
  High-level JSON-to-table ingestion entry point. Parses JSON, resolves a root array-of-objects, shapes rows and headers, then upserts into Excel. `tableRoot` defaults to `"$"` (the document root); pass a JSONPath such as `"$.data.items"` when the rows are nested under a key. Both the document root and simple nested roots stream directly into the table without building the object model; bracket-index paths resolve through the model. Returns the created or updated `ListObject`.

- `Public Function Excel_ListObjectToJson(ByVal lo As ListObject, Optional ByVal includeBlanksAsNull As Boolean = False, Optional ByVal parseJsonInCells As Boolean = False, Optional ByVal parseArraysOnly As Boolean = False, Optional ByVal preserveFormulas As Boolean = False) As String`  
  Converts an Excel table into a JSON array-of-objects. Supports nested dot-path headers and optional parsing of JSON text stored inside cells.

- `Public Function Excel_RangeToJson(ByVal rng As Range, Optional ByVal hasHeaderRow As Boolean = True, Optional ByVal includeBlanksAsNull As Boolean = False, Optional ByVal parseJsonInCells As Boolean = False, Optional ByVal parseArraysOnly As Boolean = False, Optional ByVal preserveFormulas As Boolean = False) As String`  
  Converts a worksheet range into a JSON array-of-objects, the same way `Excel_ListObjectToJson` handles a table. The first row supplies headers unless `hasHeaderRow` is False, in which case columns are named `Column1`, `Column2`, and so on.

- `Public Function Excel_UpsertListObjectFromSource(ByVal ws As Worksheet, ByVal tableName As String, ByVal topLeft As Range, ByVal sourceText As String, ByVal format As ExcelSourceFormat, Optional ByVal tableRoot As String = "$", Optional ByVal clearExisting As Boolean = True, Optional ByVal addMissingColumns As Boolean = True, Optional ByVal removeMissingColumns As Boolean = False, Optional ByVal preserveFormulaColumns As Boolean = True, Optional ByVal fillFormulasOnAppend As Boolean = True, Optional ByVal nonTableArraysAsJson As Boolean = False) As ListObject`  
  Unified ingestion entry point for JSON, CSV, or XML source text. Converts the source into JSON and routes through the deterministic Excel upsert pipeline. Returns the created or updated `ListObject`.

### CSV / XML / NDJSON Adapters

- `Public Function CsvFileToJson(ByVal filePath As String) As String`  
  Reads a CSV file from disk and converts it into a JSON array-of-objects. Reads through `Json_ReadTextFile`, so UTF-8 (with or without BOM), UTF-16, and legacy ANSI files all decode correctly.

- `Public Function CsvTextToJson(ByVal txt As String) As String`  
  Converts raw CSV text into a JSON array-of-objects using the library's built-in CSV parser.

- `Public Function XmlFileToJson(ByVal filePath As String) As String`  
  Reads an XML file from disk and converts it into JSON. Same encoding detection as `CsvFileToJson`.

- `Public Function XmlTextToJson(ByVal txt As String) As String`  
  Converts raw XML text into JSON using the library's lightweight pure-VBA XML parser.

- `Public Function NdjsonFileToJson(ByVal filePath As String) As String`  
  Reads an NDJSON (newline-delimited JSON / JSON Lines) file from disk and converts it into a JSON array. Same encoding detection as `CsvFileToJson`.

- `Public Function NdjsonToJson(ByVal txt As String) As String`  
  Converts NDJSON text (one JSON value per line) into a JSON array string, so each line becomes one record. Feeds straight into `Json_Parse` or the table upsert (`Excel_UpsertListObjectFromJsonAtRoot(ws, name, cell, NdjsonToJson(text), "$")`). Accepts `\n`, `\r\n`, and `\r`; blank lines are skipped.

- `Public Function Json_ReadTextFile(ByVal filePath As String) As String`  
  Reads a text file into a VBA string with encoding detection: UTF-16 LE/BE by BOM, then strict UTF-8 validation (BOM stripped if present), then the system ANSI codepage as the legacy fallback. Pure VBA with no Declare statements, so it works on every VBA host, Mac included. Used by all three `*FileToJson` adapters and available for your own file reading (for example `Json_Parse(Json_ReadTextFile(path))`).

### JSON Coalescing Utilities

- `Public Function Json_CoalesceChildArrays(ByVal parentJson As String, ByVal parentRoot As String, ByVal childProperty As String, Optional ByVal strictMode As Boolean = False, Optional ByVal parentKeyMap As Collection = Nothing) As String`  
  Pulls child arrays out of parent rows and merges them into one JSON array, optionally injecting parent fields, literals, or formulas.

- `Public Function Json_CoalesceArraysFromStrings(ByVal jsonStrings As Collection, Optional ByVal strictMode As Boolean = False) As String`  
  Merges multiple JSON array strings into a single JSON array string, with optional strict shape validation.

- `Public Function Json_CoalesceArraysFromRange(ByVal rng As Range, Optional ByVal strictMode As Boolean = False) As String`  
  Reads JSON array strings from an Excel range and merges them into one JSON array string.

### Excel Range Utility

- `Public Function Excel_RangeToJsonStrings(ByVal rng As Range) As Collection`  
  Reads non-empty cell values from a range and returns them as a collection of trimmed JSON strings.

---

## Support Open Source

XLIDE is open-source software. If it saves you time or helps your team keep VBA
workbooks maintainable, support helps keep the project moving.

- [GitHub Sponsors](https://github.com/sponsors/WilliamSmithEdward)
- [PayPal](https://www.paypal.com/donate/?business=ML855BRLNR838&no_recurring=0&item_name=VBA+has+always+treated+me+well.+It+was+how+I+first+grew+professional+as+a+programmer%2C+I%27m+happy+to+show+it+some+love+%E2%9D%A4%EF%B8%8F&currency_code=USD)
- [Cash App](https://cash.app/$williamesmithjcil)