Example of use of JSON in VBA: Books checker

Objective

In PowerPoint, while presenting, we would like to have the functionality to look up book information based on an ISBN number. Say, for instance, that our PowerPoint slide looks as follows.

The objective is to build a routine in VBA, say GetGoogleBooksInfo() that, after clicking the button 'Get info from Google Books', takes as input the ISBN number and fills in the information about the corresponding book in the text boxes on the slide. If something goes wrong (e.g. the ISBN number does not exist), a short message should be shown on the slide. The 'Reset' button simply resets the slide.

Solution, the principle

Thanks to Google, the solution is easy. All we need to do is to send the following URL to the Google Books API.

The Google Maps API will respond, and the response will be a JSON text.

Then, it is up to us to parse out the required information and show it on the PowerPoint slide.

Solution, the PowerPoint slide

The top three objects on the slide are ActiveX controls.

To add an ActiveX control in PowerPoint, go to the 'Developper' tab in the ribbon and then use the 'Controls' part to drag and drop an ActiveX control on the slide.

Here are the names of the ActiveX controls.

• TBNumberISBN
• CBGetBookInfo
• CBReset

To name an ActiveX control in PowerPoint, select the Active X control and click right. A menu will pop up.

Select 'Property Sheet'. Now you can set the name in the textbox next to '(Name)'.

The first ActiveX control is a textbox. While presenting the slide, an ISBN number can be typed into it. The other two ActiveX controls are buttons. As suggested by its caption, the first ActiveX button will try to get the information from Google Books, based on the ISBN number inputted in the textbox. The second ActiveX button will simply reset the textboxes to their original state. Both buttons can be pushed while presenting.
All the other objects on the slide are shapes.

To facilitate communication between VBA and the slides, it is good habit to give each shape a name. Here are the names of the shapes (that will be used in the VBA code). Just fyi, the shape with name 'Message' is the shape with no border, next to the ActiveX controls, that shows the error message. You can't see it in the picture.

• Message
• Title
• Authors
• Publisher
• Pages
• Categories
• Rating
• Description
• Picture

Here is how to name a shape in PowerPoint.

• Select one of the shapes on the slide, e.g. the one for the title.



• Go to the 'Format' tab in the ribbon and look for the 'Selection Pane' icon.



• Click on the 'Selection Pane' icon to open up the pane, on the right of the screen.



• Click on the name of the shape to change it.

Solution, the VBA code

First, let's try to send the URL with VBA and obtain the response.
Open the VBA editor of the workbook (push Alt + F11). Add the following code into 'Slide1'. This code links the ActiveX control events (i.e. pushing the button) to the VBA code.

Then, add a module (in the VBA editor, choose 'Insert' and then 'Module') and copy the following VBA code into it.

The MSXML2.XMLHTTP60 object is part of the Microsoft XML reference (see here for details). To make the code work, the reference must be checked (in the VBA editor, choose 'Tools', choose 'References...', search in the list and check it). The JSON text that the Google Books API returns will be in oHTTPRequest.responseText and is in fact a simple String.
Try the code by going back to the slides (leave the VBA editor), push F5 (to start presenting), move the mouse until you see the pointer and click the button 'Get info from Google Books'. The JSON text should be shown (partly) in a messagebox. If you see this, it means that your communication with Google works!

Now, let's extract what we need from the JSON text.
Let's do this in separate routine, called ExtractBookInfoFromResponseText. Change the VBA code as follows.

There are several possible ways to extract the distance from the JSON text. One option is to use basic string search functions, like Instr(). After a bit of trying, we will get there for sure. There is nothing wrong with that approach, however, there may be an easier solution. Let's try the JSON class 🙂
First, add the code of our JSON class to the VBA project. Either download the class (cJSON.cls) from the website and insert the file in the VBA project. Or, add an empty class to the VBA project, name it 'cJSON', copy the VBA code of the class from the website and paste it into the empty class.

Now replace the empty routine ExtractBookInfoFromResponseText by this one.

All the code is standard VBA coding in PowerPoint. First, we are putting the data that is in the JSON text into a VBA Dictionary. Second, the code extracts (aggresively) data from the VBA Dictionary and puts it inside the shapes on the slide. To keep the example small, we have omitted most of the safety coding. Of course, before extracting an item from the dictionary, it is better to test first if it exists. For those interested, we have added the more safe version of the routine ExtractBookInfoFromResponseText further.
To end the VBA coding, also add these extra routines to the VBA project.

The Reset routine handles resetting the slide. The function DoesShapeExist simply checks whether a shape with a given name exists on a sheet. This function is needed to handle the shape that will hold the picture on the cover. Admitted, there may be other and better ways to handle pictures (like by using a picture placeholder on the master slide). To focus on the JSON, we wanted to keep the pure PowerPointing in the example as simple as possible.
Let's now try the VBA code again (start the presentation, click the button 'Get info from Google Books'). You should obtain this. Objective achieved!

Solution, the VBA code revisited

Case 1: non existing ISBN number

Let's see what happens when the ISBN number does not exist. Try to look for '9780140177375' (changing the 4 at the end into a 5).

The VBA code generates and error ('No books found') and shows it on the slide. Here is the JSON text that the Google Books API has sent back in this case.

The Google Maps API has sent a valid JSON text, i.e. it matches the JSON grammar rules. However, the content is different. In this case, there are no items in the JSON text, which makes sense. By giving an error message, the VBA code handles this case fine.
Let's take another case.

Case 2: empty ISBN number

Delete the content of textbox containing the ISBN number. Push the button 'Get info from Google Books'.

The error message on the slide ('Multiple books found') suggests that multiple books have been found. Multiple books? For an empty IBSN number? Yes. Let's have a closer look at the JSON text that the Google Maps API has sent back. Here is a part of it.

In this case, the Google Books has sent back a JSON text that contains 10 items (although the 'totalItems' is set to 1062). The JSON text is valid. As the slide assumes output for exactly one book, the VBA code returns an error message ('Multiple books') and shows it on the slide. This is ok, however, in this case, it would probably make more sense to catch the error before sending the request to the Google Books API and return an error like 'Missing ISBN number'.
The two cases presented so far can both be avoided by validating the ISBN number before sending it to Google Books. For help on how to do that, look here.
Here is another case.

Case 3

Let's see what happens when you try the ISBN number '9780131103627', which is the ISBN number of the book 'C Programming Language, 2nd Edition' by Brian W. Kernighan and Dennis M. Ritchie.

Note that there is only one author on the slide. Here is the JSON text that the Google Books API has sent back.

In the JSON text that Google Books has sent back, both authors are mentionned. Hence, the error must be in the VBA code (can you find it?). The JSON value associated to the name 'authors' is a JSON array. The error is not in the data exchange with VBA: the item in the VBA dictionary that is associated to the key 'authors' is a VBA Dictionary that contains both authors. The first one has key "0", the second one has key "1". The expression TypeOf D.Item("items").Item("0").Item("volumeInfo").Item("authors") Is Dictionary evaluates to True. The error is in the way that the author data is extracted from the VBA dictionary. By doing oSlide.Shapes("Authors").TextFrame.TextRange.Text = D.Item("items").Item("0").Item("volumeInfo").Item("authors").Item("0") only the first item (i.e. the first author) is extracted. To have all the authors, you need to run through all the keys in the VBA Dictionary that are associated to the key 'authors'. Actually, we have made the error on purpose. We wanted to keep the VBA code as easy as possible, even at the expense of a small error 🙂
Let's now improve the code.

Better code

To write better code, you need to spend some moments studying the structure of the JSON text that is returned. As a book can have multiple authors, Google Books creates a JSON array to contain the authors. Similarly, as a book can be in multiple categories, the categories are also in a JSON array. To improve the VBA code, we should take this JSON text design into account. Another improvement consists of testing whether or not an item exists in the VBA Dictionary before extracting it. It makes the VBA code longer, but safer.
Replace the content of the function ExtractBookInfoFromResponseText by this.

The extra lines of VBA code add more safety and mirror the design of the JSON text in a better way. The resulting code is considerably longer, but, still remains quite readable, no?
Now rerun case 3. The result looks better now.

Closing remark

Would we obtain the same result in the browser? Go to https://books.google.com and search for the ISBN number '9780140177374'.

At the moment of writing, Google Books returns us this.

Note that ISBN number in the url https://books.google.be/books?isbn=014017737X is sligtly different from the one that you have inputted. ThThe ISBN number 0140177737X is the 10-digit version (with X being the check digit) while 9780140177374 is the 13-digit version (with the 4 at the end being the check digit). Now click 'The Pearl' to see more details. The page you get looks a bit like the powerpoint slide. You will certainly recognize some elements. Of course, there is more info on the webpage.