VBA JSON Formatting Solutions

If you need to pretty-print, parse, or generate JSON in Excel, Access, or another Office app, the practical answer is still the same: VBA has no built-in JSON parser, so most projects import a JSON module and work with Dictionary and Collection objects. For most teams, that means using JsonConverter.bas from the open-source VBA-JSON project, then calling ParseJson to read JSON and ConvertToJson to emit compact or indented output.

That matters because most Office automation now touches REST APIs, webhooks, or configuration files. A search visitor landing on this page usually does not need theory first. They need the shortest reliable path to working code, compatibility notes, and the common mistakes that waste time in the VBA editor.

Quick Answer

• Use a JSON library instead of trying to hand-parse strings. In practice, VBA-JSON remains the standard choice for Office VBA projects.
• On Windows, many projects use Microsoft Scripting Runtime for Dictionary support. If the workbook must also run on Mac, use the cross-platform dictionary compatibility approach recommended by the library instead of assuming that Windows reference is available.
• Parse first, then pretty-print. Formatting invalid JSON does not fix syntax errors; a successful parse is your validation step.
• Arrays typically come back as Collection objects, so indexing is 1-based in VBA.

Recommended Setup for Real Office Automation

The most useful mental model is simple: JSON objects map to key/value containers, JSON arrays map to ordered lists, and JSON primitives map to normal VBA scalar values.

• JSON object: usually a Dictionary-like object.
• JSON array: usually a Collection.
• String, number, boolean, null: normal VBA values such as String,Double, Boolean, and Null.

One correction that trips people up: ArrayList is not part of Microsoft Scripting Runtime. If you are following common VBA-JSON patterns, expect arrays to be exposed as Collection items unless you intentionally build something else.

A practical setup looks like this:

1. Import the JSON module into your VBA project.
2. For Windows-only workbooks, add the Microsoft Scripting Runtime reference if you want early binding.
3. For Mac and Windows compatibility, avoid hard-coding a Windows-only reference and use the compatible dictionary option your JSON library documents.
4. Keep your JSON handling separate from your HTTP code so you can test parsing and formatting with saved samples first.

Parse and Pretty-Print JSON in VBA

If your goal is readable output for debugging, logging, or inspecting API responses, the clean workflow is: parse the raw string, then convert the parsed object back to JSON with indentation enabled.

' Requires JsonConverter.bas in the project
Dim rawJson As String
Dim parsed As Object
Dim prettyJson As String

rawJson = "{""customer"":{""name"":""Alice"",""tier"":""pro""},""tags"":[""excel"",""api""],""active"":true}"

Set parsed = JsonConverter.ParseJson(rawJson)

' Whitespace:=2 produces readable indentation
prettyJson = JsonConverter.ConvertToJson(parsed, Whitespace:=2)

Debug.Print prettyJson
' {
'   "customer": {
'     "name": "Alice",
'     "tier": "pro"
'   },
'   "tags": [
'     "excel",
'     "api"
'   ],
'   "active": true
' }

Two details are worth remembering here. First, ConvertToJson returns a string, so assign it with normal string assignment, not Set. Second, if ParseJson fails, the input is not valid JSON yet, which is exactly the signal you want before sending a payload to an API.

Accessing Parsed Values Safely

Dim data As Object
Dim customerName As String
Dim firstTag As String

Set data = JsonConverter.ParseJson(rawJson)

customerName = data("customer")("name")
firstTag = data("tags")(1)   ' Collections are 1-based

Debug.Print customerName
Debug.Print firstTag

This pattern is usually easier than building your own tokenizer or writing a custom pretty-printer. If you only need to inspect a sample payload before wiring it into your macro, using a JSON formatter first and then testing the cleaned string in VBA is often faster than debugging malformed text in the Immediate window.

Generate JSON for POST and PUT Requests

Many Office automation tasks need to send JSON, not just read it. The safest approach is to build a VBA object graph first, then serialize it. That avoids quoting mistakes, missing commas, and broken escaping.

Dim payload As Object
Dim tags As New Collection
Dim jsonBody As String

' Windows-friendly late binding example
Set payload = CreateObject("Scripting.Dictionary")

payload("reportName") = "Weekly Ops"
payload("active") = True
payload("owner") = "finance@example.com"

tags.Add "excel"
tags.Add "automation"
payload("tags") = tags

jsonBody = JsonConverter.ConvertToJson(payload, Whitespace:=2)

Debug.Print jsonBody

If the workbook must run on both Windows and Mac, swap the dictionary instantiation to the compatible dictionary class used by your project. The JSON pattern stays the same even if the concrete dictionary type changes.

Fetch JSON from an API and Format It

A common workflow is: request JSON from an API, parse it, then print or log the formatted version while you build the rest of the macro. On Windows, many VBA projects use MSXML2.XMLHTTP60 or a similar client object for this step.

Dim http As Object
Dim parsedResponse As Object
Dim apiUrl As String

apiUrl = "https://jsonplaceholder.typicode.com/posts/1"

Set http = CreateObject("MSXML2.XMLHTTP.6.0")

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

If http.Status = 200 Then
    Set parsedResponse = JsonConverter.ParseJson(http.responseText)
    Debug.Print JsonConverter.ConvertToJson(parsedResponse, Whitespace:=2)
Else
    Debug.Print "HTTP error: " & http.Status & " - " & http.statusText
End If

Set parsedResponse = Nothing
Set http = Nothing

If your code already makes the web request successfully, do not overcomplicate the transport layer. Treat HTTP and JSON as separate concerns: first get the response text, then parse and format it with the JSON library.

Common VBA JSON Problems and Fixes

• "User-defined type not defined": You probably added early-bound types without the required reference. Either add the reference or switch the variable to Object.
• Arrays behave strangely: Parsed arrays are commonly Collection objects, so use 1-based indexing.
• Long numeric IDs lose precision: Keep values such as order IDs or external record IDs as strings. In VBA-JSON, preserving very long numeric-looking values as text is the safer default because Excel and VBA cannot represent every large integer exactly.
• Pretty-printing still fails: The input is probably not valid JSON yet. Parse it first and fix the syntax error before trying to format it.
• You only need visual formatting, not VBA code: Use a formatter to validate and indent the payload outside the editor first, then paste the known-good JSON into your test macro.

When Manual Formatting Is Enough

A tiny custom pretty-printer can be acceptable if you are only reindenting already-valid JSON for display. But the moment you need to read fields, build nested payloads, or handle escaping correctly, manual string logic stops being a shortcut. In Office automation, it is usually cheaper to standardize on a parser than to debug home-grown JSON code later.

Conclusion

The durable VBA JSON solution is to use a library, keep your data in dictionary and collection objects, and let the serializer handle formatting. That gives you reliable pretty-printing, safer API payload generation, and less fragile Office automation code than manual string manipulation.