Excel VBA to JSON Macro vs Free Browser Tool — Which Is Faster?

There are two types of Excel users who search for "excel to json vba": people who want to write a VBA macro to automate the conversion, and people who copied a VBA macro from Stack Overflow and it's not working. This guide addresses both — but starts with the question you should ask first: do you actually need VBA for this, or is a no-code browser tool faster?

The VBA JSON Macro Approach

VBA (Visual Basic for Applications) can produce JSON from Excel data, but Excel does not have a built-in JSON serializer. You have two options:

Option 1 — Write a JSON serializer from scratch. Loop through cells, build strings, handle escaping for special characters. Works, but is fragile — missed comma, wrong escape sequence, and the output is invalid JSON that silently breaks things downstream.

Option 2 — Use a VBA JSON library. The most popular is VBA-JSON (github.com/VBA-tools/VBA-JSON). You import the module, then use JsonConverter.ConvertToJson(). This is more reliable but requires importing the module into every workbook where you need it.

Here's a minimal VBA macro using VBA-JSON:

Sub ExportToJSON()
    Dim ws As Worksheet
    Dim arr As New Collection
    Set ws = ActiveSheet

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ' Build array of objects
    ' ... (30+ more lines of code)

    ' Write to file
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    ' ... (more code)
End Sub

The full macro is typically 50-80 lines. Debugging VBA string handling takes time. Macro-enabled files (.xlsm) often trigger security warnings when shared.

When VBA Actually Makes Sense for JSON Export

VBA is the right choice in specific situations:

• Embedded in a shared Excel tool. If you're building a workbook that dozens of colleagues will use, a button that exports JSON without them visiting a website is more user-friendly.
• Complex pre-processing before export. If you need to run calculations, merge sheets, or apply business logic before exporting — VBA can do all of that in one macro.
• Scheduled automatic exports. VBA can be triggered by workbook events (on open, on save) for automated exports without user interaction.
• Legacy Excel environments. If you're in an organization that restricts internet access and can't use browser-based tools, VBA in a locally-installed Excel is your only option.

Sell Custom Apparel — We Handle Printing & Free Shipping

When the Browser Tool Is the Better Choice

For the majority of "I need to convert this Excel file to JSON" situations, the browser tool wins on every practical dimension:

Factor	Browser Tool	VBA Macro
Time to working conversion	10 seconds	1-4 hours
Debugging required	None	Often yes
Works for recipients	Any browser	Needs xlsm + macro trust
Handles type detection	Automatic	Manual logic
Date handling	Automatic	Notoriously tricky in VBA
JSON validity guaranteed	Yes	Depends on your code

If You Must Use VBA — The Simplest Reliable Approach

If you genuinely need VBA, here's the most reliable minimal approach — using XMLHTTP to call a conversion service, or exporting to CSV first and then converting:

Excel CSV save + browser conversion (simplest hybrid):

1. In VBA, save the active sheet as CSV: ActiveWorkbook.SaveAs Filename:="output.csv", FileFormat:=xlCSV
2. Then open the CSV in the CSV to JSON converter for the actual JSON conversion.

This gives you programmatic control over the export step while avoiding the need to write a JSON serializer in VBA.

For direct .xlsx-to-JSON conversion in one click, use the Excel to JSON converter — no VBA required.

Dates: The Biggest VBA JSON Pain Point

If you've debugged a VBA JSON macro, you've almost certainly hit date issues. VBA represents dates as Date type internally, but JSON has no date type — you must serialize as string. And Excel's Date type doesn't have a built-in ISO 8601 formatter, so you end up with custom code like:

Format(dateValue, "yyyy-mm-dd")

This works for date-only cells. For datetime cells, you need to handle both the date and time components. And if your cells are stored as numbers (date serials), you need to convert those first with CDate().

The browser tool handles all of this automatically. For the details on how date conversion works, see our guide on Excel to JSON date format issues.

Frequently Asked Questions

Is there a free VBA-JSON library I can use?

Yes — VBA-JSON by Tim Hall (github.com/VBA-tools/VBA-JSON) is the most widely used. It handles serialization and deserialization. You import the module into your workbook and use JsonConverter.ConvertToJson(). Free and open source.

Can VBA produce nested JSON?

Yes, but it requires manually building Collection and Dictionary objects to represent the nested structure before serializing. It is significantly more complex than flat JSON export. Python is generally easier for nested structures.

What security warnings do users see with macro-enabled Excel files?

When recipients open an .xlsm file, Excel shows a "Macros have been disabled" banner by default. They must click "Enable Content" to run the macro. In some corporate environments, macros are disabled entirely and cannot be enabled by the user.

Can the browser tool be triggered from VBA?

Not directly. But VBA can launch a browser to a URL using the Shell command or CreateObject("InternetExplorer.Application"). For automation, using Python or n8n is more practical than trying to chain VBA with a browser tool.