VBA - Using JSON in VBA

A very important skill you will need to know in VBA if you want to write professional applications is to handle JSON text and convert it into VBA objects. JSON is a very popular text-based file format used to transmit data objects. It's very common right now for APIs to return requests using JSON text. If you learn how to handle it, you can make some good use out of it.

To put in an example (very trending right now by the way), imagine you have a Cryptocurrency portfolio you built in Excel where you store all your transactions and the number of Bitcoins you have. To know how much your Bitcoins are worth, you need to go and check the web for the current Bitcoin price. Or, instead, you can have Excel to automatically call an API to request this information. Parse out into an Object in VBA and display the price in the Worksheet to compare how much you Bitcoins are worth right now.

Another example: let's say you want to know the list of the Best Sellers according to the NY Times. By knowing the Server API and using the right parameters, you can get the JSON objects that parsed out can create the desired list in your workbook.

There're hundreds and hundreds of example we can put to reference the importance of knowing how to handle JSON objects from APIs. But the most important one might be that specific Web service in your company that stores information from customers and that you might be able to use to build Excel Applications.

Let's put this in code and see how does it work:

Assume you have a very simple portfolio in Excel that looks like this:

The value of the current Bitcoin price is what's going to give you the value of your portfolio. To get this info, I already found a very simple API you can use: "https://api.coindesk.com/v1/bpi/currentprice.json". If you paste this path in the address bar of your browser, you will see you will get something like:
That's OK, you didn't break anything. That just the JSON text response from the API Path. That's exactly what you are going to get in your VBA JSON request and you going to convert into a VBA Object. Let's see in practice:


Let's stop right here and see what we have so far. With the API Path, we have been able to request the server information and convert the JSON text into an object. If we place a breakpoint just below we set the oJSON object and add a Watch to oJSON we will see that we successfully were able to create the object with the Bitcoin information:

The hard part is done, or at least most of it. Next step is to get from that object, the desired information. In this specific case, we don't need to know the Bitcoin exchange value for EUR or GBP, just USD.



Ok, there you go. That's how the complete code looks like. When the code finishes running, you will see in the range "R_BITCOIN_PRICE" the value extracted from the JSON text. 

Pretty cool, right?

That's all folks,
Cheers



Comments

Post a Comment

Popular posts from this blog

VBA - Copy Sheet from One Workbook to Another

Sometimes we need to insert a Sheet from one Workbook into another one. The solution is really easy. Follow bellow code: That was really simple. Just a few comments: Always make sure to define the workbooks that are going to be used (Source & Target). This will make the code more legible and it will make things easy to understand in the future. You should apply this rule everywhere in your coding experience. Variables are not only valuable when you have no other choice, they can be used to make your code clearer. That being said, note in the code how we are inserting the copied Sheet after the last Sheet in the Workbook Target. That's all folks... Cheers!
Read more

VBA - Change IF Statements for Reference Table

Image
If Statements are a crucial part of the daily VBA Development process. You can't live without conditional statements since they are, in core, the most fundamental decision derivative expression of programming. However, in Excel, where you have to deal with a lot of conditions, it might get tricky to use them. Imagine having 7 different conditions and for each condition 2 sub-conditions where 3 of them don't result in final "ELSE" condition. Hard to see, right? How many lines do you need to code to accomplish this? How about if in the future, you need to add more conditions, will you know where to place them? A lot of If Statements can be replaced for a table that drives all possible conditions. This will help with readability and will also help you to avoid modifying or adding more code in the future. Check this example where you have to return a fruit type based on the arguments passed to a function. (DO NOT PAY ATTENTION TO DESCRIPTIONS, THEY ARE VERY R...
Read more