Click here to Skip to main content
15,867,594 members
Articles / Productivity Apps and Services / Microsoft Office
Tip/Trick

Non-Recursive JSON Parser for VBA

Rate me:
Please Sign up or sign in to vote.
3.67/5 (3 votes)
27 Jun 2012CPOL3 min read 56.4K   1.5K   5   14
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.

VB
//
// 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)


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
BugBug: Failure when emotes and other chars present Pin
Tonyt317-Oct-14 3:17
Tonyt317-Oct-14 3:17 
BugBug: Failure on large JSON response Pin
Tonyt317-Oct-14 3:13
Tonyt317-Oct-14 3:13 
GeneralMy vote of 4 Pin
Tonyt317-Oct-14 3:11
Tonyt317-Oct-14 3:11 
GeneralMy vote of 2 Pin
Member 1075866422-Jul-14 3:36
Member 1075866422-Jul-14 3:36 
SuggestionNew JsonParser class member Pin
ahtiger5-Dec-12 6:14
ahtiger5-Dec-12 6:14 
QuestionIterating through a list Pin
haniel17-Oct-12 11:21
haniel17-Oct-12 11:21 
AnswerRe: Iterating through a list Pin
Vinolarium17-Oct-12 11:38
Vinolarium17-Oct-12 11:38 
QuestionParsing tags Pin
haniel17-Oct-12 9:54
haniel17-Oct-12 9:54 
Questionhandeling Types Pin
ogoto11-Oct-12 12:38
ogoto11-Oct-12 12:38 
AnswerRe: handeling Types Pin
Vinolarium2-Oct-12 7:04
Vinolarium2-Oct-12 7:04 
As an example, the call for One Microsoft Way looks like this
http://maps.googleapis.com/maps/api/geocode/json?address=One+Microsoft+Way,Redmond,WA&sensor=false

and results I get start out like this:
{
   "results" : [
      {
         "address_components" : [
            {
               "long_name" : "157th Ave NE",
               "short_name" : "157th Ave NE",
               "types" : [ "route" ]
            },
            {
               "long_name" : "King",
               "short_name" : "King",
               "types" : [ "administrative_area_level_2", "political" ]
            },
            {
               "long_name" : "Washington",
               "short_name" : "WA",
               "types" : [ "administrative_area_level_1", "political" ]
            },
            {
               "long_name" : "United States",
               "short_name" : "US",
               "types" : [ "country", "political" ]
            }
         ],


I am not totally sure what you are asking, but I think what you want is to see what would be necessary to grab the "United States" and "US" part.

There are two ways you can handle this. The simplest is to assume that the piece you want will always be the 4th element of the "address_components" list. A more robust approach is to specifically look for the "country", "political" piece and not assume it is always the 4th. I would personally choose the more robust approach.

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

' Search for country '
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)

' Semi-robust search for which index has country. Certainly not production-quality. '
' I did not test this code, so use it as a general guide. '
Dim listComponents As Object
Set listComponents = tokenizer.JsonList(dictC.Item("address_components"), tokens)

Dim dictComponent As Object
Dim listTypes As Object
Dim firstType As String
Dim country As String
Dim longName As String
Dim shortName As String

Dim index As Integer
Set index = 0

While index < listX.Count
    Set dictComponent = tokenizer.JsonDictionarylistComponents.Item(index.ToString()), tokens)
    Set listTypes = tokenizer.JsonList(dictComponent.Item("types"), tokens)
    Set firstType = listTypes.Item("0", tokens)
    If firstType = "country" Then
        Exit While
    End If
    Set index = index + 1
End While

' If index >= number of entries, then type "country" never found '
If index < listX.Count Then
    ' dictComponent will contain the address component we want '
    Set longName = tokenizer.JsonString(dictComponent.Item("long_name"), tokens)
    Set shortName = tokenizer.JsonString(dictComponent.Item("short_name"), tokens)
End If

GeneralRe: handeling Types Pin
ogoto13-Oct-12 12:14
ogoto13-Oct-12 12:14 
GeneralRe: handeling Types Pin
Vinolarium3-Oct-12 13:05
Vinolarium3-Oct-12 13:05 
GeneralRe: handeling Types Pin
ogoto13-Oct-12 13:16
ogoto13-Oct-12 13:16 
GeneralRe: handeling Types Pin
ogoto13-Oct-12 13:14
ogoto13-Oct-12 13:14 

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

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