Click here to Skip to main content
Click here to Skip to main content

Non-Recursive JSON Parser for VBA

, 27 Jun 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
Sample code for parsing JSON with deep-nested schemas in VBA.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Vinolarium

United States United States
No Biography provided

Comments and Discussions

 
BugBug: Failure when emotes and other chars present PinmemberTonyt317-Oct-14 4:17 
BugBug: Failure on large JSON response PinmemberTonyt317-Oct-14 4:13 
GeneralMy vote of 4 PinmemberTonyt317-Oct-14 4:11 
GeneralMy vote of 2 PinmemberMember 1075866422-Jul-14 4:36 
SuggestionNew JsonParser class member [modified] Pinmemberahtiger5-Dec-12 7:14 
QuestionIterating through a list Pinmemberhaniel17-Oct-12 12:21 
AnswerRe: Iterating through a list PinmemberVinolarium17-Oct-12 12:38 
QuestionParsing tags Pinmemberhaniel17-Oct-12 10:54 
Questionhandeling Types Pinmemberogoto11-Oct-12 13:38 
AnswerRe: handeling Types PinmemberVinolarium2-Oct-12 8:04 
GeneralRe: handeling Types Pinmemberogoto13-Oct-12 13:14 
GeneralRe: handeling Types PinmemberVinolarium3-Oct-12 14:05 
OK, for the JSON you provided, you have:
* Outermost is a dictionary, which contains a "results" entry
* The "results" entry returns a list of lists of decimals (or you could treat them as strings)
 
Here is some code to get you started. Sorry it's not debugged (and I forgot if there is a JsonFloat method you could call instead of JsonString at the end). But it should be pretty close.
 
Dim dictA As Object
Set dictA = tokenizer.JsonDictionary(tokenized, tokens)
 
Dim listResults As Object
Set listResults = tokenizer.JsonList(dictA.Item("results"), tokens)
 
Dim listDecimals As Object
 
Dim outer As Integer
Set outer = 0
 
Dim inner As Integer
Dim dataAsString As String
 
While outer < listResults.Count
    Console.WriteLine("List " + outer.ToString())
    Set listDecimals = tokenizer.JsonList(listResults.Item(outer.ToString()), tokens)
    Set inner = 0
    While inner < listDecimals.Count
        Set dataAsString = tokenizer.JsonString(listDecimals.Item(inner.ToString()), tokens)
        Console.WriteLine(dataAsString)
        inner = inner + 1
    End While
    outer = outer + 1
End While

GeneralRe: handeling Types Pinmemberogoto13-Oct-12 14:16 
GeneralRe: handeling Types Pinmemberogoto13-Oct-12 14:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.1411023.1 | Last Updated 27 Jun 2012
Article Copyright 2012 by Vinolarium
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid