JSON and JavaScript Demo in VBA under Excel 2016

Based on the Code Provided by the Excel File pvd402aaX.xlsm

Nickantony Quach
Feb 12 · 3 min read

This article explains how JSON works using JavaScript in VBA under Excel 2016. If you do not have access to the Excel 2016 file pvd402aaX.xlsm, ask Nick the author for a copy. This article and the companion Excel file show you how to do all of the following tasks:

  • Include JavaScript logic as part of the VBA code in Excel
  • Convert JSON data into an object in memory
  • Extract keys and values from a JSON structure
  • Use a VBA function to encapsulate a JavaScript function
  • Create ArrayLen as a generic way to count array elements in VBA
  • Safely evaluate an expression without using the method Eval from ScriptControl
  • Get the component ScriptControl to work in 64Bit Excel
Nick in Providence, Rhode Island

A | Demo Procedure

[2] Do the following to see the interactive portion of the demo:

  1. Open the Excel 2016 file pvd402aaX.xlsm.
  2. Press the button [Dashboard 404 Code Samples].
  3. The previous step will execute the subroutine Dashboard404ActiveXButton_Click, which will display the form Dashboard404TestCode
  4. Select the third item on the list, “03 — JavaScript and JSON”.
  5. Press the button [Take the selected example].
  6. The previous step will execute the subroutine DoSelectedExample which in turn will execute the subroutine DoCodeSample03_JsonDemo1 which in turn will execute the subroutine DoTheFirstJsonDemo.

[3] The demo code DoTheFirstJsonDemo will present the following interactive message:

  • Demo Step 1 | ObjectAsAStringInJsonFormat holds the following, in JSON format but without double quotes: {key1:val1, key2:{key3,val3}}
  • Demo Step 2 | PropertyNames has 2 items: key1 and key2
  • Demo Step 3 | ValueOfTheFirstProperty is val1
  • Demo Step 4 | PropertyNames has 1 item: key3
  • Demo Step 5 | ValueOfProperty1OfProperty2 is val3

B | Inside the Demo Subroutine DoTheFirstJsonDemo

[4] The subroutine DoTheFirstJsonDemo carries out the following steps:

  • DCS1 | Initialize the JavaScript Engine
  • DCS2 | Use a text string in JSON format to express a major object with two properties, the second of which contains a minor object with only a single property.
  • DCS3 | Turn the JSON object into a major object in memory
  • DCS4 | Get the names of all top-level properties in the major object. The result is key1 and key2.
  • DCS5 | Get the value of the first property of the major object. The result is val1.
  • DCS6 | Get the content of the second property of the active object. The result is a minor object in memory. To be sure, the minor object is the content of the major object’s second property, namely key2.
  • DCS7 | Get the names of all top-level properties in the minor object. The result is key3.
  • DCS8 | Get the value of the first property of the minor object. The result is val3.

C | How to Create a JavaScript Engine

[5] There are five steps in creating and using a JavaScript Engine in VBA under Excel 2016. The first step is to reserve a block of memory large enough to hold the object ScriptControl which is defined in the the library Microsoft Script Control 1.0. As a reminder, use the menu command [Tools\References].

Private JavaScriptEngine As ScriptControl

[6] The second step is to activate the script control using the public domain source code as defined by the two subroutines CreateObjectx86 and CreateWindow. They are required because the following statement cannot be executed in 64Bit version of Excel 2016:

Set JavaScriptEngine = New ScriptControl

[7] The third step is to let the script control know that we want to use JavaScript and not VBScript.

JavaScriptEngine.Language = “JScript”

[8] The fourth step is using the script control’s method AddCode to add as many custom JavaScript functions as necessary.

[9] The fifth step is using the script control’s method Run to invoke the added JavaScript code.

CoalMont

Our offense on a great Black Mountain

Nickantony Quach

Written by

Founder of Ri4CTV, a channel on YouTube, Creator of Thumoslang, the nomenclature for social life, and Father of One, who works for the NFL

CoalMont

CoalMont

Our offense on a great Black Mountain

More From Medium

More on Pvd402 from CoalMont

More on Pvd402 from CoalMont

GdriveTest101

More on Pvd402 from CoalMont

More on Pvd402 from CoalMont

More on Pvd402 from CoalMont

Google Drive API First Attempts

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade