Excel VBA: Loop Through JSON Object Arrays Step by Step

VBA & macros · Updated November 21, 2026

To loop through a JSON array of objects in Excel VBA, parse the JSON with a VBA-compatible parser such as VBA-JSON, then use For Each to visit each item in the resulting Collection. Each item is typically a dictionary, so you can read a property with its key, for example record("name").

What VBA receives from a JSON array

JSON is a text format, not a built-in VBA data type. A JSON array is written with square brackets, and each object inside it uses curly braces. For example, [{"id":101,"name":"Notebook"},{"id":102,"name":"Pen"}] is an array containing two objects. The array itself is a sequence; each object holds named properties.

VBA does not include a general-purpose JSON parser in the standard language. You need to convert the JSON text before VBA can iterate through it as structured data. A common choice is the open-source VBA-JSON project, whose JsonConverter.ParseJson function parses JSON into VBA collections and dictionary-like objects. With that conversion done, the usual pattern is straightforward: loop over the collection, then retrieve each object’s properties by key.

This distinction matters when the JSON starts with an object rather than an array. An array at the top level can be looped through directly. An object may instead contain an array in a property such as results or items; in that case, first retrieve that property, then loop over its collection.

Set up a JSON parser in the VBA project

For Excel for Microsoft 365 on Windows, download the VBA-JSON parser from its project repository and import JsonConverter.bas into the workbook’s VBA project. In the Visual Basic Editor, use File > Import File, select the downloaded module, and confirm that it appears in the Project Explorer. The parser is a separate dependency; it is not installed automatically with Excel.

VBA-JSON uses dictionary objects for JSON objects. Depending on the version and configuration you use, the project may require the Microsoft Scripting Runtime reference on Windows, or may use late binding. If the module reports that Dictionary is undefined, open Tools > References in the VBA editor and select Microsoft Scripting Runtime, or follow the setup instructions for the particular parser version. On Mac, use the parser’s documented Mac-compatible setup; some configurations require its companion dictionary implementation. Excel for the web does not run VBA macros.

After importing the parser, save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it contains the macro. Only enable macros in files you trust. The parser converts data; it does not make an API request by itself, so the JSON text must already be in a VBA variable or be obtained by separate code.

A working loop for a top-level array

This example parses a small JSON array, then writes the object properties into columns A through C on a worksheet named Sheet1. It assumes every array member is an object and that each object contains the three shown keys. Add the parser module before running it. Save a copy of the workbook first: macros cannot be undone with Ctrl+Z.

Option Explicit

Sub WriteJsonArrayToSheet()
    Dim jsonText As String
    Dim parsed As Object
    Dim records As Collection
    Dim record As Object
    Dim ws As Worksheet
    Dim rowNumber As Long

    ' Example input: a JSON array containing two objects.
    jsonText = "[{""id"":101,""name"":""Notebook"",""price"":4.5},{""id"":102,""name"":""Pen"",""price"":1.25}]"

    ' Parse the text, then use the returned collection as the array.
    Set parsed = JsonConverter.ParseJson(jsonText)
    Set records = parsed

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ws.Cells(1, 1).Value = "ID"
    ws.Cells(1, 2).Value = "Name"
    ws.Cells(1, 3).Value = "Price"

    rowNumber = 2
    For Each record In records
        ws.Cells(rowNumber, 1).Value = record("id")
        ws.Cells(rowNumber, 2).Value = record("name")
        ws.Cells(rowNumber, 3).Value = record("price")
        rowNumber = rowNumber + 1
    Next record
End Sub

The first parsed object is written to row 2, and the second to row 3. The loop variable is declared as an object because each member is a parsed JSON object. record("id") asks for the value associated with the exact key id; JSON keys should be spelled and capitalized as they appear in the source. If your worksheet has another name, change "Sheet1" to match it.

Loop through an array inside a response object

Many web services return a top-level object containing status information and a separate array. For example, the response might have a count property and a results array. In that shape, ParseJson returns an object, not the array you want to iterate. Extract the array property first:

Dim parsed As Object
Dim records As Collection
Dim record As Object

Set parsed = JsonConverter.ParseJson(jsonText)
Set records = parsed("results")

For Each record In records
    Debug.Print record("name")
Next record

This snippet assumes the JSON has a top-level property called results, that its value is an array, and that each array member has a name property. Replace results and name with the actual keys from the response. If the top-level value is an array, assigning the parsed result to records is the appropriate pattern instead.

Nested JSON follows the same principle. If an object has a nested object, retrieve that property and then its key. If it has a nested array, retrieve the array as a collection and use another For Each loop. Check the response structure before writing the loop: APIs often wrap results in different properties, and a correct loop over the wrong level will either fail or return no records.

Handle missing keys, nulls, and data types

Real responses are less tidy than the short example. A property may be absent, explicitly set to JSON null, or represented by a different type than expected. A missing dictionary key can raise an error when you request it directly. If the parser returns dictionary objects that support Exists, test for the key before reading it. Also check for Null before assigning a value that might be null to a worksheet cell.

For example, a guarded read can follow this pattern inside the loop:

If record.Exists("name") Then
    If Not IsNull(record("name")) Then
        ws.Cells(rowNumber, 2).Value = record("name")
    Else
        ws.Cells(rowNumber, 2).ClearContents
    End If
Else
    ws.Cells(rowNumber, 2).ClearContents
End If

This assumes record is a dictionary object and ws and rowNumber have already been set. It leaves the destination blank when the key is missing or null. The JSON values may also be strings, numbers, Boolean values, arrays, or objects. A scalar such as a number can usually be written to a cell directly, but nested objects and arrays need their own handling; they are not automatically converted into a useful table layout.

Do not assume that every value that looks numeric in JSON should be treated as an Excel number. Identifiers, postal codes, and long account-like values may need to remain text, especially when leading zeroes matter. Large numbers can also exceed Excel’s numeric precision. Decide how each field should be stored based on its meaning, not just its appearance in the JSON.

Common errors and how to diagnose them

  • “Sub or Function not defined” for ParseJson: Check that JsonConverter.bas is imported into the same VBA project and that the call is spelled JsonConverter.ParseJson.
  • “User-defined type not defined” for Dictionary: Review the parser’s Windows or Mac setup and required references. This is a project configuration issue, not a problem with the JSON loop itself.
  • Type mismatch when assigning the result to a Collection: The JSON root may be an object rather than an array. Inspect the first character of the JSON text after whitespace: an array begins with [; an object begins with {. For an object root, retrieve its array-valued property.
  • Subscript or key error: Confirm the exact key spelling and capitalization and check that the key exists. A common cause is expecting name when the service returns fullName.
  • Unexpected type or blank output: Inspect a representative response. The API may return null, a nested object, an empty array, or an error message instead of the expected records.

When the source is an API, also inspect the HTTP status and response text before parsing. A server can return an HTML error page or a JSON error object instead of the requested array. Parsing that response may fail, or it may succeed while producing a structure different from the one your code expects. Keeping a small sample response for debugging makes it easier to distinguish an HTTP issue from a VBA type or key issue.

Write efficiently and protect workbook data

Writing one cell at a time is clear and works well for modest responses. For thousands of records, repeated worksheet writes can be slow. A faster design is to copy the parsed values into a two-dimensional VBA array, then assign that array to a same-sized worksheet range in one operation. The loop still visits each JSON object; only the output step changes. This approach also makes it easier to decide which properties become columns before touching the sheet.

Be deliberate about where the macro writes. The example starts at row 2 and does not clear old rows, so a later response with fewer records can leave stale data below the new results. If you intend to replace prior output, clear only the designated output range after confirming it does not contain other data. A macro’s changes cannot be reversed with Ctrl+Z, which is why a backup copy and a clearly chosen destination are useful safeguards.

JSON itself is data, but a value copied into Excel can still need careful handling. Do not treat external text as trusted instructions, and avoid hard-coding API passwords or tokens in a workbook that may be shared. If a text field beginning with characters Excel interprets as a formula is written to a cell, consider how to preserve it as text. Keep macro security enabled and use trusted sources and files.

When to use VBA and when to choose another tool

VBA is a good fit when the workbook needs a repeatable button-driven import, when parsed values must interact with existing macros, or when the response requires custom processing. Its main trade-off is setup: you need a parser dependency, and you must maintain code that handles the service’s response structure and errors.

For a recurring data import that does not need custom VBA logic, Power Query may be more convenient. In Excel for Microsoft 365 on Windows, the web connector is available through Data > Get Data > From Other Sources > From Web. Power Query can load and transform web data through its interface, though authentication and complex API pagination can require additional configuration. Excel for the web does not run VBA, so a VBA-based workflow must be run in desktop Excel. Choose based on how often the data changes, how much control you need, and who must maintain the workbook.

Frequently Asked Questions

Can Excel VBA parse JSON without a library?
VBA does not provide a general built-in JSON parser. Use a parser such as VBA-JSON, or use another import tool such as Power Query when it fits the task.

How do I loop through a JSON array in VBA?
Parse the JSON, store the top-level array as a Collection, and use For Each item In records. Read each object’s properties by their exact JSON keys.

Why does ParseJson return an object instead of a Collection?
The JSON root is likely an object. Look for the array property inside it, such as results, and assign that property to a collection before looping.

How do I handle a missing JSON property?
Check whether the object’s dictionary contains the key before reading it, then choose a suitable default such as a blank cell. Also handle JSON null separately from a missing key.

Can I use this VBA macro in Excel for the web?
No. Excel for the web does not execute VBA macros. Open the macro-enabled workbook in desktop Excel to run the code, or use a web-compatible alternative for the workflow.

Related

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.