Excel VBA JSON: Parse, Read, and Write Data

Excel VBA can work with JSON, but VBA has no built-in JSON parser. In desktop Excel, the usual approach is to add a VBA JSON parser, use it to turn JSON text into VBA objects, and then read or write those values in your workbook.

What JSON Means in Excel VBA

JSON, short for JavaScript Object Notation, is a text format for exchanging structured data. It is common in web APIs and configuration files. A JSON object holds named values, while a JSON array holds an ordered list. Values can be strings, numbers, true or false, null, objects, or arrays.

For example, an API might return an object containing an array of products. VBA can store the response as a string, but a parser is needed to interpret its structure safely. After parsing, an object’s properties and an array’s items can be accessed in VBA much like entries in a dictionary and elements in a collection.

This distinction matters: JSON is not an Excel cell format, and putting JSON text in a cell does not automatically split it into fields. A macro must parse the text and decide which values go into which cells.

Why VBA Needs a JSON Parser

VBA predates the widespread use of JSON and does not include a standard function such as ParseJSON. You could try to extract values with string searches or split operations, but that is fragile. A comma or colon inside a quoted string, escaped quotation marks, nested objects, and arrays can all make simple text manipulation return the wrong result.

A parser reads the structure according to JSON’s rules and creates VBA objects that represent it. A widely used community option is VBA-JSON, an open-source project that provides a JsonConverter module. Its ParseJson function parses JSON text; its ConvertToJson function can serialize supported VBA data structures back into JSON text. It is not a Microsoft feature, so check the project’s documentation and review code before adding it to a workbook.

Choose a Method for the Job

The right tool depends on whether the task is a one-time import, a repeatable workbook process, or a connection to a live service.

VBA with a parser: useful when a macro needs to parse JSON, apply workbook-specific rules, or run a repeated import from VBA.
Power Query: often more convenient for importing a JSON file or web source and shaping the result into tables. In Microsoft 365 for Windows, look under Data > Get Data for the From File or From Web options available in your build.
A dedicated application or service: worth considering when the workflow needs complex authentication, large-scale processing, or centralized access controls.

Excel for the web cannot run VBA macros. VBA is available in desktop Excel, including Microsoft 365 for Windows and Mac, but some Windows automation methods that rely on COM or ActiveX do not work on Mac. Power Query features and source connectors can also differ by platform and update channel.

Set Up a JSON Parser

For VBA-JSON, obtain the project from its official repository and follow the current installation instructions. In the Visual Basic Editor, open it with Alt+F11 on Windows, then use File > Import File to import the JsonConverter.bas module into the workbook’s VBA project. On Mac, open the editor using the Developer tab or the Tools > Macro > Visual Basic Editor command, depending on the Excel build.

The parser’s dictionary implementation can require different setup on Windows and Mac. On Windows, the project documents using the Microsoft Scripting Runtime reference for an early-bound Scripting.Dictionary configuration. On Mac, that Windows library is unavailable; follow the project’s current Mac instructions and any required dictionary class setup. Do not assume a reference or module from another workbook is already installed in this one.

Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) if it must retain VBA code. The parser module becomes part of the workbook’s VBA project, so users who receive the file need a desktop Excel version that supports macros and must follow their organization’s macro security policy.

Parse JSON and Read Its Values

Suppose the JSON contains an items array, with each item holding a product code and quantity. After importing the parser, the basic workflow is to pass the JSON string to JsonConverter.ParseJson, retrieve the array, and loop through its objects. This example writes the two fields to a worksheet named JSON, starting in row 2.

Option Explicit

Sub ImportSampleJson()
    Dim jsonText As String
    Dim parsed As Object
    Dim rows As Collection
    Dim item As Variant
    Dim ws As Worksheet
    Dim nextRow As Long

    ' Example payload with two records.
    jsonText = "{""items"":[{""sku"":""A-17"",""qty"":3},{""sku"":""B-04"",""qty"":8}]}"

    Set parsed = JsonConverter.ParseJson(jsonText)
    Set rows = parsed("items")
    Set ws = ThisWorkbook.Worksheets("JSON")

    ws.Cells(1, 1).Value = "SKU"
    ws.Cells(1, 2).Value = "Quantity"
    nextRow = 2

    For Each item In rows
        ws.Cells(nextRow, 1).Value = item("sku")
        ws.Cells(nextRow, 2).Value = item("qty")
        nextRow = nextRow + 1
    Next item
End Sub

Create a worksheet named JSON before running the example, and make sure JsonConverter.bas is in the project. The macro expects the top-level items array and both named properties to exist. For the supplied sample, it writes A-17 with 3 and B-04 with 8. Before running any macro, save a copy of the workbook: macro actions cannot be undone with Ctrl+Z.

Map Nested Data to Worksheet Columns

Real responses often nest data. An object might have a customer property containing another object, or each array item might include an address object. With a parser, code can follow that hierarchy, for example by reading a customer object and then its city property. The exact key names and nesting depend on the response; inspect a representative JSON payload before writing the mapping.

Plan the worksheet layout before importing. Decide which fields become columns, what to do when a field is absent, and whether arrays should become multiple rows or remain grouped. A missing key can cause a VBA error if code tries to read it unconditionally. A more robust macro checks whether the object contains the expected key before using the value, and handles missing or null values explicitly.

JSON types also deserve attention. A number can be written as a numeric cell value, while a date often arrives as a string and may need deliberate conversion. Do not assume every string that looks like a date has an unambiguous regional format. If leading zeros matter in an identifier, store it as text rather than allowing Excel to interpret it as a number.

Getting JSON from a Web API

For an API workflow, the macro has two separate jobs: send an HTTP request and parse the response body. A Windows VBA project can use a Windows HTTP client such as WinHTTP, then pass its ResponseText to JsonConverter.ParseJson. That transport step is separate from JSON parsing, and the appropriate client and authentication method depend on the API.

Before automating a service, read its documentation for the endpoint, required headers, authentication, rate limits, and response format. An HTTP success status does not guarantee the response is the JSON object your code expects; a service may return an error object, an empty body, or a different schema. Check the status and validate the parsed structure before writing results to cells.

Do not put passwords, API keys, or access tokens directly in a workbook that will be shared. Workbook protection is not a secure secret store. A token in VBA code or a worksheet can be exposed to someone with access to the file. Follow the service provider’s security guidance and your organization’s approved method for storing credentials.

Common Errors and How to Diagnose Them

“Sub or Function not defined” for ParseJson: confirm that JsonConverter.bas is imported into the same VBA project, and that the procedure is spelled correctly.
Object variable not set: check that object variables are assigned with Set and that the expected JSON object or array was found.
Key not found or subscript error: compare the property name and letter case with the actual response, and account for optional fields or a changed API schema.
JSON parse error: inspect the raw response. It may be truncated, malformed, or an HTML error page rather than JSON. Look for invalid escaping or unexpected characters before changing the parser.
Works on Windows but not Mac: check for Windows-only references, COM automation, or ActiveX dependencies. A parser may be portable while the code used to fetch the response is not.

During development, save the raw response to a text file or inspect it in the Immediate window before parsing. This separates a network or authentication problem from a parsing problem. Avoid logging sensitive response data where other users can see it.

Safety, Limits, and Maintenance

Macros can change workbook contents and make external requests, so only run code from sources you trust. Do not lower Excel’s macro security settings simply to make a downloaded workbook run. Keep a backup before running an import, especially if the code clears or replaces existing data.

JSON responses can change over time. APIs may add properties, rename fields, return null where a value used to be present, or paginate large result sets. Build checks for expected fields and record enough non-sensitive diagnostic information to identify failures. For large responses, writing values to a two-dimensional array and placing the array on the sheet in one operation can be more efficient than writing one cell at a time.

Also consider Excel’s worksheet limit of 1,048,576 rows. If an API returns more records than a sheet can hold, writing everything to one worksheet is not a suitable design. Use pagination, filter the data, or choose a storage or processing tool designed for larger datasets.

Frequently Asked Questions

Can Excel VBA read JSON without a library?
VBA has no built-in general-purpose JSON parser. A small, fixed JSON string can sometimes be handled with custom parsing code, but that approach is easy to break. A maintained parser is safer for nested or changing data.

Does Excel have a built-in JSON function?
Excel worksheet formulas do not provide a general built-in JSON parser. Power Query can import and transform JSON sources, while VBA requires a parser module for structured JSON handling.

Can Excel for the web run VBA JSON macros?
No. Excel for the web does not run VBA macros. Use desktop Excel for VBA, or consider Power Query or another web-compatible workflow where its available features meet the need.

Can VBA turn worksheet data into JSON?
Yes. A macro can build supported VBA dictionaries and collections and pass them to a serializer such as VBA-JSON’s ConvertToJson. The result is a JSON text string that can be saved or sent to an API, subject to that service’s required schema.

Why does JSON import work on Windows but fail on Mac?
The parser may not be the cause. Windows-only references, COM-based HTTP clients, and ActiveX components can fail on Mac. Review the parser’s Mac setup and replace any Windows-only transport or automation code with a compatible approach.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.