JSON and VBA

These articles are abridged from my  book on Office to Apps migration. Going GAS, from VBA to Google Apps Script. Now available  from O’Reilly or Amazon

VBA does not naturally support JSON, but to be able to use this API (and many others), we need to work with JSON. It’s not just a question of encoding to and from JSON, but also being able to navigate the structure of JavaScript-like objects is not straightforward in VBA.

 

cJobject

 

In most of the VBA projects on those site, I use the  cJobject class to deal with JSON and JavaScript like objects in VBA. There are tutorials in the mechanics of cJobject elsewhere, but to be able to follow the examples, here are a few basics.

Create a cJobject by parsing JSON.

Set args = JSONParse( _ "[{'id':'1f4zuZZv2NiLuYSGB5j4ENFc6wEWOmaEdCoHNuv-gHXo','sheetName':'lookup'}]")
End Function

Navigate a cJobject

for each job in args.children ‘ navigate the array
  for each prop in job.children ‘ navigate the properties
    debug.print prop.key, prop.value ‘ print the key and the value
  next prop
next job

Convert a cJobect to JSON

debug.print args.stringify

Access a property value

debug.print args.child(‘1.sheetName’).value

Create a cJobject from scratch

with arg.init (Nothing)
  .add “name”, “Methuselah”
  .add “age”,969
end with

This is a fairly complex and powerful class with many methods and properties, but the above should give the flavor of how it works.

All code samples can be found on Github. Note that that VBA samples have been developed for Office for Windows and may need some tweaking for Office for Mac.

Other items in this section

For more like this, see Execution API and Office to Apps migration. Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available.