How to Parse JSON with VBA (MS Access/Excel)

We work with JSON a fair amount (and honestly I can’t say I care for it too much for anything outside of the JavaScript realm), and when it comes to VBA, there’s next to no support for it. Tim Hall has a great set of tools in his github repo, one in specific called VBA-JSON that tends to be a first hit when people have to go searching. I used this for a while myself, until I ran into a case that it fails on, which put me back on the drawing board.

The issue seems to be in the parsing algorithm used: some structures of valid JSON raise errors using the above code (see some examples here – and maybe watch the issue to see if there’s a fix at some point?). Furthermore, there’s a number of other “hand-made” parsers that seem to suffer the same faulty algorithm.

In any case, after numerous hours and at least a dozen different parsers being tested, I found the one that seems to work across the board. As well it should, is it’s tapping into the Microsoft JavaScript Engine to do the work. (credit to StackOverflow member Codo for the original source, as near as I can tell)

Toss the following into a standalone module. Following that is another code example for how to use this.

Module JsonParser:

To use it, do something like this:

Admittedly, it could be easier to use. This effectively parses the values (for any valid JSON – I tested loads of it and gave a presentation at the annual PAUG conference last year without an egg on my face), but working with the values when you’re done is a bit of a headache. Essentially loop through the keys and determine if it’s a Property or Object, and recurse as required. If only JSON has an equivalent query language like XPath for XML!

One important thing to bear in mind is that this method does make use of JavaScript’s Eval() function. That function basically says “take any string and execute it as if it were JavaScript”, which has some significant security implications. Thus, we ought to ensure we’re working with a trusted source if we’re going to be using it (but this holds true for just about any external source, I think).

In any case – while Tim Hall’s interface is a bit more friendly to use and works in most cases, this one works in all cases.

If you’re interested in some more information on how to consume web services with VBA, check out the Web Work with VBA blog post. Have fun!
-jack

(NOTE: NZ() in Excel – the NZ() function is an Access function and not available in excel. If you’re trying to use this code in excel, you’ll need another way to handle nulls. NZ() takes any value and tests it for null, converting it to a number or string if the value is null – google can help)

Share on:

Share on facebook
Share on twitter
Share on linkedin

Reader Interactions

Comments

  1. Mor says

    Many thanks for this – works great in Excel VBA for me.

    One thing I don’t understand, though.

    You’re passing the plain JSON string to the JS engine with the Eval method.
    How come JS knows what to do with a plain string without any preceding statement (I’d expect JSON.Parse, for example)?

    • Jack D. Leach says

      Offhand, I’d guess it’s because Eval() is responsible to “resolve an expression” so to speak (e.g., Eval(“1 + 2”) = 3). Because the string we pass to it is json, it knows to convert it to an object (so, an internal thing that the script engine happens to know how to handle). That’s my guess anyway.

  2. MATT DOUGLAS says

    I liked what you shared here. The code reminded me of how much fun vba variants and array functions are. So I wrote a simple little json parser method just to play. Pretty cool how flexible variants are and how weird you can get with array functions in vba, thought I’d share. With a class container and little key search method this might be useful for simple api grabs. Regex on swirly braces, recursion, etc.. variant arrays ought to build out pretty nicely to contain nested json??

    Public Function json_to_vbar(json As Variant) As Variant

    Dim l As Long, vbar As String, keys As Variant, vals As Variant

    If Len(json) > 4 Then

    ‘ trim edges, protect delimiters, kinda lame
    json = Mid$(json, 3, Len(json) – 4)
    json = Replace$(json, “\””,\”””, “||”)
    json = Replace$(json, “\””:\”””, “.:”)

    ‘ minimal esc char handler
    While l < Len(json)
    l = l + 1
    If Mid$(json, l, 1) = "\" Then
    ' skip backslashes
    l = l + 1
    ' cpy next char to vbar string
    ' unless it's an esc'd dbl-quote
    If Not Mid$(json, l, 1) = """" Then _
    vbar = vbar + Mid$(json, l, 1)
    Else
    vbar = vbar + Mid$(json, l, 1)
    End If
    Wend

    ' split on major delim, run through array
    keys = Split(vbar, "||"): ReDim vals(UBound(keys))

    ' split on minor delim, cheaty redims
    For l = 0 To UBound(keys)
    vals(l) = Split(keys(l), ".:")(1)
    keys(l) = Split(keys(l), ".:")(0)
    Next l

    ' pack into new array and return
    json_to_vbar = Array(keys, vals)

    Else
    json_to_vbar = Null
    End If

    End Function

    ' SIMPLE TEST
    Public Sub json_vbar_test2()

    Dim res As Variant, vbar As Variant, l As Long

    ' res holds responseBody of a GET request . . . here's a test value . . .
    ' "{\"Version\":\"3.3.9.2\",\"Updated\":\"7\/16\/2019 3:11:12 PM\"}"

    res = """{\""Version\"":\""3.3.9.2\"",\""Updated\"":\""7\/16\/2019 3:11:12 PM\""}"""

    vbar = json_to_vbar(res)

    Debug.Print "Key", "Value"
    For l = 0 To UBound(vbar): Debug.Print vbar(0)(l), vbar(1)(l): Next l

    ' Key Value
    ' Version 3.3.9.2
    ' Updated 7/16/2019 3:11:12 PM

    End Sub

    • Jack D. Leach says

      Thanks for this Matt. Parsing is certainly an issue (how I wish there was an x-path equivalent for json) and the code I’d posted really only handles the transformation to VBA and not the parsing after. I’m sure someone will find this handy for shallow data handling.

  3. Jack Wagner says

    Thank you so much for posting! This saved my bacon. I am using it in Excel. Here is a function that replicates the Access Nz() function for anyone looking in the future:

    http://dailydoseofexcel.com/archives/2007/02/25/nz-function/

    Public Function Nz(p1, Optional p2) As Variant
    Select Case True
    Case Not IsNull(p1): Nz = p1
    Case IsMissing(p2): Nz = Empty
    Case Else: Nz = p2
    End Select
    ‘ Nz(Null) return Empty in MS Access, so the following Excel vba matches MS Access perfectly.

    ‘ to test it open vba immediate window and type ?(nz(null) = 0) & ” ” & (nz(null) = “”)
    ‘ You will get True True

    End Function

Leave a Reply

Your email address will not be published. Required fields are marked *

For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.

Recent articles