If you're wondering how to extract data from JSON in VBA or how to parse JSON without using external libraries, you've come to the right place. In this post, I'll address all these queries about VBA. So, let's get started!

Today, I'll provide you with all the answers you need regarding VBA. Read this post carefully, and I'm confident you'll find the solution to your question. Let's dive in.

First of all please add reference  of microsoft script control 1.0 object library.

microsoft script control object library

Parse JSON with VBA without external libraries

Option Explicit

Private ScriptEngine As ScriptControl

Public Sub InitScriptEngineForParsing()
    Set ScriptEngine = New ScriptControl
    ScriptEngine.Language = "JScript"
    ScriptEngine.AddCode "function GetPropertyFromJson(jsonObj, propertyName) { return jsonObj[propertyName]; } "
    ScriptEngine.AddCode "function GetKeysFromObject(jsonObj) { var keys = new Array(); for (var i in jsonObj) { keys.push(i); } return keys; } "
End Sub

Public Function DecodeJsonStringFromApiResponse(ByVal JsonString As String)
    Set DecodeJsonStringFromApiResponse = ScriptEngine.Eval("(" + JsonString + ")")
End Function

Public Function GetPropertyFromJson(ByVal JsonObject As Object, ByVal propertyName As String) As Variant
    GetPropertyFromJson = ScriptEngine.Run("GetPropertyFromJson", JsonObject, propertyName)
End Function

Public Function GetObjectPropertyFromJson(ByVal JsonObject As Object, ByVal propertyName As String) As Object
    Set GetObjectPropertyFromJson = ScriptEngine.Run("GetPropertyFromJson", JsonObject, propertyName)
End Function

Public Function GetKeysFromObject(ByVal JsonObject As Object) As String()
    Dim Length As Integer
    Dim KeysArray() As String
    Dim KeysObject As Object
    Dim Index As Integer
    Dim Key As Variant

    Set KeysObject = ScriptEngine.Run("GetKeysFromObject", JsonObject)
    Length = GetPropertyFromJson(KeysObject, "length")
    ReDim KeysArray(Length - 1)
    Index = 0
    For Each Key In KeysObject
        KeysArray(Index) = Key
        Index = Index + 1
    Next
    GetKeysFromObject = KeysArray
End Function


Public Sub TestJsonParse()
    Dim JsonString As String
    Dim JsonObject As Object
    Dim Keys() As String
    Dim strname As Variant
    Dim strcountry As Variant
    Dim j As Variant

    InitScriptEngineForParsing

    JsonString = "{""name"": ""johny"", ""address"": { ""country"": ""USA"",""city"": ""New York City"" } }"
    Set JsonObject = DecodeJsonStringFromApiResponse(CStr(JsonString))
    Keys = GetKeysFromObject(JsonObject)

    strname = GetPropertyFromJson(JsonObject, "name")
    Set strcountry = GetObjectPropertyFromJson(JsonObject, "address")
    MsgBox ("Student Name: " & strname & " country Name:" & strcountry.country)
End Sub

So our use the Javascript engine to get at the information from the JSON string. First of all, there is a function to GetKeysFromObject for getting the keys of a Javascript object.when you know the Javascript object keys, the next code is to access the properties value from the object.

As we know that VBA won’t help either if the name of the key is known at run-time. So we have two methods to access a property of the object, one for access values i.e GetKeysFromObject and the other one for getting objects and arrays i.e GetObjectPropertyFromJson.

InitScriptEngineForParsing() Subroutine:

  • Initializes the ScriptEngine object for parsing JSON.
  • Sets the language to "JScript" to utilize JavaScript syntax.

Defines two JavaScript functions:

  • GetPropertyFromJson: Retrieves a property value from a JSON object.
  • GetKeysFromObject: Retrieves keys from a JSON object.

DecodeJsonStringFromApiResponse() Function:

  • Converts a JSON string to a JavaScript object using the ScriptEngine's Eval method.
  • Returns the JavaScript object.

GetPropertyFromJson() Function:

  • Retrieves a property value from a JSON object using the GetPropertyFromJson JavaScript function defined earlier.
  • Returns the property value.

GetObjectPropertyFromJson() Function:

  • Retrieves an object property from a JSON object using the GetPropertyFromJson JavaScript function defined earlier.
  • Returns the object property.

GetKeysFromObject() Function:

  • Retrieves keys from a JSON object using the GetKeysFromObject JavaScript function defined earlier.
  • Returns an array of keys.

TestJsonParse() Subroutine:

  • Calls InitScriptEngineForParsing to initialize the ScriptEngine.
  • Defines a JSON string containing name and address information.
  • Parses the JSON string using DecodeJsonStringFromApiResponse.
  • Retrieves keys from the JSON object.
  • Retrieves and displays the name and country information using GetPropertyFromJson and GetObjectPropertyFromJson.

This VBA code demonstrates how to parse JSON data in VBA without relying on external libraries, using JavaScript syntax within the ScriptControl object.

VBAparse json array

Option Explicit

Private ScriptEngine As ScriptControl

Public Sub InitScriptEngine()
    Set ScriptEngine = New ScriptControl
    Dim foo As Object
    Dim Jsonobject As Object
    ScriptEngine.Language = "JScript"
    ScriptEngine.AddCode "Object.prototype.myitem=function( i ) { return this[i] } ; "
    Set Jsonobject = ScriptEngine.Eval("(" + "[ 'Mark', 'Alex' ]" + ")") ' JSON array
    MsgBox (Jsonobject.myitem(1))
End Sub

Thanks you so much let me know if you have any query

The short form of Visual Basic for Applications is VBA. Let me tell you that VBA is an event-driven programming language from Microsoft. Which is now mainly Microsoft Office applications; Such as MS-Excel, MS-Word, and MS-Access.

It helps build customized applications and solutions that enhance the capabilities of those applications. The benefit of this feature is that we do not require to install VB on our computer, but installing Office helps us achieve our objective.

We can use VBA in all versions of Office from MS-Office 97 to MS-Office 2013. You can also experiment with more modern versions of Office that are available.

VBA Excel is the most famous programming, using VBA is that we can build a powerful tool using linear programming.

Now let’s know what VBA is used for?

Use of VBA :

VBA is used as follows:

MS-Excel provides only basic built-in functions which are not effective in complex calculations. In all these situations VBA has become the obvious solution.

It is quite difficult to calculate the monthly loan repayment using the built-in function of Excel but the VBA program for this type of calculation is simple.

Now we have learned about the use of VBA, now let us also know about its features.
Features of VBA:

Following are the features of VBA:

(1) Connectivity to the database can be done in VBA.

(2) Many difficult tasks can be simplified by programming through VBA.

(3) VBA provides an environment to work on multiple applications (eg – MS – Excel, MS – Access, etc.).

(4) VBA has built-in functions for stinging mathematical operations etc.

(5) Any user in VBA can create his own user-defined functions.

Now let us know that how many types of windows are there in VBA?

VBA Window:

To access the Visual Basic Editor in Excel is to press the Alt+F11 key .Here are some Windows that appear in the Excel VBA editor. This is controlled through the View menu at the top of the VBA editor window. The following are the names of each window:

1. Project Window

2. Code Window

3. Properties Window

4. Intermediate Window

5. Locals window

6. Watches Window

DataType in VBA

When a variable is declared, we also need to specify its data type. Because the value stored in any variable depends on its data type.

However, VBA handles your data automatically regardless of the data type. As a result, the program executes very slowly.

There are several types of VBA data types, which can be divided into two broad categories:

1) Numeric data type
2) non-numeric data type

Variables

The position in which a value is filled is given the name of that char or variable. It is known by a certain name in the program. Before a variable can be used in a program, it is necessary to define or declare it. The meaning of declaring variables is to specify their name and type.

Rules for Creating Variable

The first character in the name of the variable must be an alphabetical letter.

Spaces, commas ( , ) or any other characters in the names of variables; Like – @, $, # should not be used.

In VBA Variable names must not be greater than 255 characters.

The reserved keywords of Visual Basic should not be used in the names of variables.

Read Similar Articles