JSON and VBA

 
These articles are abridged from my  book on Office to Apps migration. 

Going GASfrom 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 thos 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'}]")

Navigate a cJobect

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 forumfollow the blog or follow me on twitter to ensure you get updates when they are available. 

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.





Comments