VBA Code to handle JSONs


JSON and VBA?

JSON is used everywhere.

By design, the JSON format is independent of any specific programming language. If you want to use the data in a JSON text in a specific programming language, you need a VBA JSON deserializer (or JSON parser) in that programming language. The website www.json.org offers links to JSON deserializers in many different programming languages. Many, but not in VBA.

As we are heavy VBA users and we also want to use the JSON format to exchange data, we have developped a JSON deserializer in VBA.

Would you like more details about the VBA code?
Check the Documentation page


Would you like to get the files with code?
Check the Downloads page


Would you like to see how it works in real life?
Check the Case Study page

The complete source code of our free VBA deserializer

Here you can find the complete VBA source code that will permit you to handle JSON texts. This VBA source code is a result of dozens of hours of work and it represents approximatively 1.800 lines of code. We decided to offer it to the Community as Open Source code. You are thus free to use it according to your needs. Below, you can consult the code directly in your browser. You can also download it as TXT file or as CLS file. Alternatively, you can also visit the Downloads page with all the files available...

If you have remarks, comments or ideas to improve the code, we will be glad to hear from you. You can contact us via our Contact page.


Click to view the complete VBA code

The VBA deserializer in action

Let's deserialize the following simple JSON text.

{"Name":"Value"}

Here is the VBA code that deserializes the JSON text.

The actual deserialization is done within the VBA class cJSON, by calling the method Deserialize. Here is a snippet of the VBA code of the class cJSON. The full code is over 1.800 lines and can be found in the drop-down button above.

Add the JSON deserializer to your VBA project

It is very easy to add the JSON deserializer to your VBA project. There are only two things that you need to do.

  1. Add the VBA class cJSON to your VBA project.
  2. Add the reference to the Microsoft Scripting Runtime library to your VBA project.

How to add the VBA class cJSON to your VBA project

Go to the Downloads page, download cJSON.cls. Push Alt + F11 to open the VBA editor. Import the file cJSON.cls. Check the reference Microsoft Scripting Runtime. Compile to be sure. You are ready to go.

Here are the steps again, illustrated, and in more detail.

  1. Let's assume you are working in Excel. Open your workbook. In this example, we will assume you are starting in a new workbook, in an English version of Excel 2013. If this is not the case, don't worry: the steps are the same in all Excel versions up to date. Only the pictures will differ.
    Image showing how to add the VBA class to an Excel workbook
  2. Open the VBA editor by pushing Alt + F11.
    Image showing how to add the VBA class to an Excel workbook
  3. Go to the Downloads page,  downloadcJSON.cls, i.e. the JSON VBA class in CLS format and save it somewhere. Although the extension of the file is cls, it is a flat text file. Hopefully it passes your firewall without problems. If you cannot download the file, there is an alternative set of steps below that doesn't require a download.
    Image showing how to add the VBA class to an Excel workbook
  4. In the VBA editor, look for the Project Explorer (pushing Ctrl + R will lead you to it) and right click 'VBAProject' (this is the default name of your VBA project). A pop-up menu will open.
    Image showing how to add the VBA class to an Excel workbook
  5. Choose 'Import File..." and browse to the place where you have saved the downloaded file cJSON.cls. Select it and click 'Open'.
    Image showing how to add the VBA class to an Excel workbook
  6. The class cJSON has been added to your VBA project.
    Image showing how to add the VBA class to an Excel workbook

If you have download problems, here is an alternative sequence of steps.

  1. Let's assume you are working in Excel. Open your workbook. In this example, we will assume you are starting in a new workbook, in an English version of Excel 2013. If this is not the case, don't worry: the steps are the same in all Excel versions up to date. Only the pictures will differ.
    Image showing how to add the VBA class to an Excel workbook
  2. Open the VBA editor by pushing Alt + F11.
    Image showing how to add the VBA class to an Excel workbook
  3. Click "Insert" and choose "Class Module"
    Image showing how to add the VBA class to an Excel workbook
  4. This will insert an empty class in your VBA project.
    Image showing how to add the VBA class to an Excel workbook
  5. Push F4 to open the 'Properties Window'.
    Image showing how to add the VBA class to an Excel workbook
  6. Change the name of the class to 'cJSON'. Actually, you can pick any name you like. However, all our examples assume that the name of the class is called cJSON. If you choose another name, you need to replace the name cJSON with your name at the appropriate places (just CNTRL+F for 'cJSON' and replace with your name).
    Image showing how to add the VBA class to an Excel workbook
  7. Copy the VBA code from here and paste it into the class
    Image showing how to add the VBA class to an Excel workbook

How to add the reference to the Microsoft Scripting Runtime library to your VBA project

Push Alt + F11 to open the VBA editor. Click 'Tools' and choose 'References...' and check 'Microsoft Scripting Runtime'. That is all.

Here are the steps again, illustrated, and in more detail.

  1. Let's assume you are working in Excel. Open your workbook. In this example, we will assume you are starting in a new workbook. The steps are the same anyway. Only the pictures will differ.
    Image showing how to add the VBA class to an Excel workbook
  2. Open the VBA editor by pushing Alt + F11.
    Image showing how to add the VBA class to an Excel workbook
  3. Click 'Tools' and choose 'References...'
    Image showing how to add the VBA class to an Excel workbook
  4. Look for 'Microsoft Scripting Runtime'.
    Image showing how to add the VBA class to an Excel workbook
  5. Check it and click OK.
    Image showing how to add the VBA class to an Excel workbook
  6. The reference has been added to your VBA project.

Remarks

  • The method Deserialize in the VBA class that will try to deserialize a JSON text into a VBA Dictionary. The VBA Dictionary is an element of the Microsoft Scripting Runtime library. For this reason, you need to add a reference to the Microsoft Scripting Runtime library.
  • The VBA code is assuming early binding to the Microsoft Scripting Runtime library. As it is, the VBA code will not work if you use late binding. It can be easily adjust. We assume that if you know the difference between early and late binding, you know how to make the changes.
  • The source file of the Microsoft Scripting Runtime library is scrrun.dll. On our machines, this dll is in the folder C:\Windows\SysWow64\. If you do not have this file on your machine, the VBA code will not work because you need the Dictionary object that is defined in the Microsoft Scripting Runtime library. In that case, you can try to download a version of the file scrrun.dll for your Windows version and put it in the folder with the Windows dlls (on our machines C:\Windows\SysWow64\). In this folder, you should also find the file regsvr32.exe. You need this application to register the file scrrun.dll. Copying the file is not enough. Without registration, Windows doesn't know about the dll and the Microsoft Scripting Runtime will not appear in the list of references in VBA. To register the dll, try to run the command regsvr32 scrrun.dll in a DOS window. It should succesfully register the file scrrun.dll. If this doesn't work, you will need to look for help or tips (type 'scrrun.dll missing' in Google).
  • Here is a picture of the typical flow of the VBA code. For more details, check the Documentation page.Image showing mapping the typical flow of the VBA code
  • If you are stuck, check out our site. There are plenty of examples and detailed explanations.

Get me started quickly

Download the file JSONGetMeStartedQuickly.xlsm. Open the workbook (you can safely enable the content, there are no surprises in the VBA code). Push Alt + F11 to open the VBA project. Check if the reference to the Microsoft Scripting Runtime library is checked (Tools > References...). Go to Module1 and run the sub Example. It should work flawlessly. This means you should see two message boxes, one with 'Value' and one with 'String'.

History of the VBA code

VersionReleased atRemark
1.001/09/2017First version