VBA JSON in Excel — Parse an API Response into Cells with VBA-JSON (Dictionary, Collection, Null)

TL;DR — VBA cannot read JSON on its own, and string tricks (Split, InStr, regex) break on the first nested object or escaped quote. Use the open-source VBA-JSON parser (JsonConverter.bas). It turns JSON into a tree of three things: objects become Dictionaries, arrays become Collections, and everything else becomes a plain value. Your code walks that tree, and at every step it has to ask what it is holding, because the tree has its own rules: Collections start at 1, reading a missing key adds it, null becomes Null, and long numeric IDs come back as strings.

Dim root As Object
Set root = JsonConverter.ParseJson("{""name"":""Berlin"",""tags"":[""a"",""b""]}")
Debug.Print root("name")          ' Berlin
Debug.Print root("tags")(1)       ' a    (first element is 1, not 0)

This is the second article in a cluster on getting data from the web into Excel: VBA HTTP request gets the text, this article turns it into a structure, and VBA web scraping covers pages without an API. The cluster idea is that a web request hands you text, not data. Between the server and your cells sit three translations you own: bytes to text, text to structure, structure to grid. JSON parsing is the middle one.

What you'll learn

• The mental model: a tree of Dictionaries and Collections
• Setting up VBA-JSON
• Reading values: Set or no Set
• Arrays start at 1
• Missing keys and null
• Numbers, IDs and dates
• Flattening records into a sheet
• Writing JSON without breaking it on a European Windows

The mental model: a tree of Dictionaries and Collections

JSON has only a few shapes, and the parser maps each one to something VBA already has:

So a parsed response is a tree whose branches are Dictionaries and Collections and whose leaves are values. Nothing in the tree remembers what you expected the API to send. If the API sends an array where you expected an object, or null where you expected a number, the tree says so, and your code either asks or crashes. That is the whole skill: walk the tree and check the kind of node before you use it. See VBA VarType and TypeName for the tools that ask.

Setting up VBA-JSON

1. Download JsonConverter.bas from the VBA-tools/VBA-JSON project on GitHub.
2. In the VBA editor, File > Import File and pick it. A module called JsonConverter appears.
3. Tools > References, tick Microsoft Scripting Runtime. The parser creates Dictionary objects and will not compile without it (see VBA References).

You may find older advice that evaluates JSON with ScriptControl and JScript. Do not use it: that control exists only in 32-bit Office, so the macro fails on every 64-bit installation, which is now the default.

Reading values: Set or no Set

A branch is an object and a leaf is a value, and VBA assigns them differently: objects need Set, values must not have it. When you know the shape, write it directly:

Dim root As Object, items As Collection
Set root = JsonConverter.ParseJson(body)
Set items = root("items")                 ' a branch: Set
Debug.Print items(1)("name")              ' a leaf

When you do not know the shape, for example a field that is sometimes a string and sometimes an object, ask with IsObject before you assign:

If IsObject(rec("address")) Then
    Set addr = rec("address")
Else
    addressText = rec("address")
End If

Also check the top level. Some APIs return an object with the records inside ({"items":[...]}), others return a bare array ([...]). TypeName(root) tells you which: "Dictionary" or "Collection".

Arrays start at 1

A JSON array is zero-based everywhere else: in JavaScript, in Python, in the API documentation. VBA-JSON stores it in a Collection, and a Collection's first element is 1. Code translated from the documentation's items[0] to items(0) stops with Subscript out of range, and a loop written For i = 0 To items.Count - 1 both fails on the first element and skips the last. Prefer For Each, which has no index to get wrong:

Dim rec As Object
For Each rec In root("items")
    Debug.Print rec("id"), rec("name")
Next rec

Missing keys and null

Real API records are not uniform. Optional fields are left out, and empty fields are sent as null. Both behave differently from what you might expect.

Reading a missing key adds it. This is how Scripting.Dictionary works: rec("discount") on a record without that key does not fail; it creates the key with an Empty value and returns Empty. Your code continues with a zero or an empty string that the API never sent, and the record now has a key it did not have. Ask first:

If rec.Exists("discount") Then d = rec("discount") Else d = 0

null becomes Null. A field sent as "phone": null is present, so Exists is True, but its value is VBA's Null. Assigning it to a String or Double variable stops with Invalid use of Null (error 94). Read leaves into a Variant and test with IsNull; see VBA IsNull.

A small helper keeps both checks out of every line:

Function JGet(ByVal node As Object, ByVal key As String, Optional ByVal fallback As Variant = Empty) As Variant
    JGet = fallback
    If Not node.Exists(key) Then Exit Function
    If IsObject(node(key)) Then Exit Function
    If IsNull(node(key)) Then Exit Function
    JGet = node(key)
End Function

Numbers, IDs and dates

Numbers become Double, except long ones. VBA keeps only 15 significant digits, so VBA-JSON stores very long integers (16 digits or more) as strings to keep every digit. Order numbers and account IDs are often that long. The result: in the same column, short IDs arrive as numbers and long ones as text. When you write them to a sheet, Excel shows a long numeric value as 1.23457E+15 and keeps only 15 digits. Treat IDs as text from the start: format the column as text (NumberFormat = "@") before you write, and never do arithmetic on them.

Dates arrive as strings. JSON has no date type; APIs send ISO 8601 text such as "2026-10-05T08:30:00Z". CDate does not accept that format. VBA-JSON includes JsonConverter.ParseIso, which converts it to a VBA Date in your local time zone. That shift is usually what you want for display, but it means the same record shows different times on machines in different time zones. If the time has to match the API exactly, keep the original string in another column.

Your own conversions follow the regional settings. VBA-JSON reads 3.14 correctly on any Windows. But if you take a number that the API sent as a string and convert it yourself, CDbl reads it the way the user's Windows writes numbers, not the way the API does. On a German or Spanish Windows, where the point is the thousands separator, CDbl("3.14") gives you 314. Use Val, which always expects a point, for text that came from an API.

Flattening records into a sheet

The usual goal is a table: one row per record, one column per field. Two rules make that reliable. First, choose the columns from the API's contract, not from the first record, because the first record may be missing optional fields. Second, fill a 2D array and write it once, not cell by cell (see VBA 2D arrays).

Sub JsonToSheet(ByVal body As String, ByVal target As Range)
    Dim root As Object, rec As Object
    Dim fields As Variant, out() As Variant
    Dim r As Long, c As Long

    Set root = JsonConverter.ParseJson(body)
    fields = Array("id", "name", "price", "updated")
    ReDim out(0 To root("items").Count, 0 To UBound(fields))

    For c = 0 To UBound(fields)
        out(0, c) = fields(c)                         ' header row
    Next c
    For Each rec In root("items")
        r = r + 1
        For c = 0 To UBound(fields)
            out(r, c) = JGet(rec, fields(c))          ' missing, null, nested -> Empty
        Next c
    Next rec

    target.Resize(UBound(out, 1) + 1, UBound(fields) + 1).Value = out
End Sub

Missing fields, null values and nested objects all become empty cells instead of errors or invented keys. For nested data such as an address object, decide on purpose: add columns like address.city with a second lookup, or write the nested records to a second sheet with the parent's ID as the link.

Writing JSON without breaking it on a European Windows

To send JSON, build a Dictionary and let the library write the text:

Dim order As New Dictionary
order("sku") = "A-100"
order("qty") = 3
order("price") = 12.5
body = JsonConverter.ConvertToJson(order)     ' {"sku":"A-100","qty":3,"price":12.5}

Do not glue JSON together by hand. Besides missing escapes for quotes and line breaks in text, VBA turns 12.5 into 12,5 on a German, French or Spanish Windows when it joins a number into a string, which makes the JSON invalid. A hand-built body works on the developer's machine and fails on the user's. The library writes the decimal point and escapes text correctly, and it writes VBA dates as ISO strings. Send the result with the POST shown in VBA HTTP request.

The judgment call: parse with a parser, then check every node

There are two decisions here. The first is not to parse JSON by hand. Split and InStr work on the sample response and break on the first comma inside a string, the first nested object or the first escaped quote. A real parser is one imported module.

The second is to treat the parsed tree as untrusted. Every branch can be missing, every leaf can be null, and every ID can be text. Put the checks in one helper like JGet, choose your columns from the contract, and the macro survives the record that does not match the sample.

And before you write any of it, ask whether you need VBA at all. If the job is "pull this API into a table on every refresh", Power Query's From Web reads JSON, expands records into columns and refreshes without a line of code. Use VBA when there is logic around the call: a POST, paging through results, or writing back.

How ExcelMaster helps

JSON code is written against a sample response and then meets the real one: an optional field missing, a null in a price, an ID too long for a number.

ExcelMaster reads the actual response before it writes the parsing code, maps each field to a column on purpose, and handles missing keys, nulls and long IDs instead of leaving them to a run-time error.

Frequently asked questions

How do I parse JSON in Excel VBA?

Import JsonConverter.bas from the VBA-JSON project, add a reference to Microsoft Scripting Runtime, then call Set root = JsonConverter.ParseJson(text). Objects become Dictionaries and arrays become Collections.

Why do I get Subscript out of range when reading a JSON array?

VBA-JSON stores arrays in a Collection, whose first element is 1, not 0. Use items(1) for the first element, or loop with For Each.

How do I check if a key exists in parsed JSON?

Use node.Exists("key"). Do not read the key to test it: reading a missing key from a Dictionary adds it with an Empty value.

Why do I get error 94 Invalid use of Null with JSON?

The field was sent as null, which VBA-JSON turns into Null. Read it into a Variant and test IsNull before assigning it to a typed variable.

How do I convert a VBA Dictionary to JSON?

Call JsonConverter.ConvertToJson(dict). It escapes text, writes decimal points correctly on any regional setting and converts dates to ISO strings.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1, VBA-JSON 2.3.1 — last verified 2026-10-05.

Related guides: VBA HTTP Request · VBA Web Scraping · VBA Dictionary · VBA Collection · VBA IsNull · VBA VarType and TypeName · VBA 2D Array · VBA References