Non-Recursive JSON Parser for VBA

• Download demo - 33.4 KB

Introduction

This code can be used in Microsoft Office™ applications for parsing JSON query results. Other examples I found (both VBA and Visual Basic™) used recursive algorithms to parse JSON in a single pass and return a tree data structure. When I tried some of these they failed in very strange ways. The symptoms looked like those of a stack overflow problem, so I rewrote this code from scratch so that the calling application could parse just the JSON pieces it needed in a non-recursive way... and it worked!

Another advantage of this code over other JSON parsers I have seen is that it is self-contained and all the macros you need can be included inside the Microsoft Office™ document. You do not need to install an external library.

Background

VBA (Visual Basic for Applications™) is a useful scripting language from Microsoft that can be used to create macros in Microsoft Office™ documents such as Excel™. It is similar to Visual Basic™, but there are significant differences in both syntax and capabilities, so code that works in Visual Basic™ cannot be used directly for VBA. All the sample parsers I found used a one-pass recursive algorithm to parse the JSON data and only worked for data with about three levels. Suspecting that VBA has a very limited ability to use recursion, I rewrote the code using a non-recursive algorithm.

Credits to JuiceAnalytics http://www.juiceanalytics.com/writing/excel-geocoding-tool-v2/ for the original inspiration, though the code supplied is a rewrite from scratch. The stack class example was adapted from an MSDN example http://msdn.microsoft.com/en-us/library/aa227567(v=vs.60).aspx.

Usage Example

The code below gives an example of how to use the API. Better yet, the macro-enabled spreadsheet file has all of the code that you need including a simple subroutine and button to geocode an address string.

The first step is to "tokenize" the JSON stream to be parsed. This step will replace all keys and data in the JSON string with numeric tokens. The actual values of the tokens will be stored in a dictionary (tokens) and are used later by the tokenizer calls to return the actual data you are interested in. Why perform this initial tokenizing step? Because I can get rid of messy things like escaped quotation marks or other special JSON characters inside quoted strings. This makes the later parsing functions much simpler and (I hope) less error prone.

Once tokenized, each call to a specific parsing function (JsonList, JsonDictionary, etc.) peels away just the next single layer of the JSON structure. Eventually, you drill down to the data you want and call the appropriate parsing function (JsonDouble, JsonString, JsonLong) to get the actual data of interest.

//
// Example of using the code to parse Latitude and Longitude from a Google Geocoding call.
//
Dim address As String
address = "98052"

Dim URL As String
URL = "http://maps.googleapis.com/maps/api/geocode/json?address=" & address & "&sensor=false"

'Create Http object
Dim http As Object
Set http = CreateObject("WinHttp.WinHttpRequest.5.1")

'Send request To URL
http.Open "GET", URL

http.send
'Get response data As a string

response = http.responseText

' Tokenize the response '
Dim tokens As Object
Dim tokenized As String
tokenized = tokenizer.JsonTokenize(response, tokens)

' Search for latitude and longitude '
Dim dictA As Object
Set dictA = tokenizer.JsonDictionary(tokenized, tokens)

Dim listB As Object
Set listB = tokenizer.JsonList(dictA.Item("results"), tokens)

Dim dictC As Object
Set dictC = tokenizer.JsonDictionary(listB.Item("0"), tokens)

Dim dictD As Object
Set dictD = tokenizer.JsonDictionary(dictC.Item("geometry"), tokens)

Dim dictE As Object
Set dictE = tokenizer.JsonDictionary(dictD.Item("location"), tokens)

Dim lat As Double
lat = tokenizer.JsonDouble(dictE.Item("lat"), tokens)

Dim lng As Double
lng = tokenizer.JsonDouble(dictE.Item("lng"), tokens)

Points of Interest

Admittedly this is a strange usage model for a modern API that harkens back to the days when developers had to worry about stack overflow problems and other language constraints. To use it you need to know what type of objects you are looking for before each call.

I was surprised at how painful and tedious it was to use the VBA language and tools. For my particular problem, it was the best overall solution, but it took a lot of time to get working. I hope that this code can save you some time and effort.

History

• 6/27/2012 - Initial posting.