Accessing a Variant/Object/JScriptTypeInfo property in VBA

Question

I have the following code VBA code (for an Excel module)

Function getDesc(ByVal pCode As String) As String
    Dim oRequest As Object
    Set oRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    oRequest.Open "GET", "https://my.url.com/?filter=CODE=" & pCode, False
    oRequest.SetRequestHeader "Accept", "application/json"
    oRequest.Send ""

    Set props = jsonDecode(oRequest.ResponseText)

    getDesc = props.row_data
End Function

Function jsonDecode(jsonString As Variant)
    Set sc = CreateObject("ScriptControl"): sc.Language = "JScript"
    Set jsonDecode = sc.Eval("(" + jsonString + ")")
End Function

The results of props.row_data is as shown

I can't figure out how to assign the property LONG_DESCRIPTION to getDesc =

What's the proper syntax I need? Alternatively, I will accept just about any solution that will allow me to return the String of LONG_DESCRIPTION.

Please post `oRequest.ResponseText` content, at least a relevant part. — omegastripes, Sep 28 '17 at 13:49
Note that using ScriptControl ActiveX such way you might make the system vulnerable for the a malicious JS code within the response. Take a look at [this answer](https://stackoverflow.com/a/30494373/2165759) and [VBA-JSON-parser on GitHub](https://github.com/omegastripes/VBA-JSON-parser). — omegastripes, Sep 28 '17 at 13:57
Using this library might help here https://github.com/VBA-tools/VBA-JSON — Ryan Wildry, Sep 28 '17 at 13:58

score 2 · Accepted Answer · answered Sep 28 '17 at 14:18

Try the below code. Import JSON.bas module into the VBA project for JSON processing.

Option Explicit

Function getDesc(ByVal pCode As String) As String

    Dim oRequest As Object
    Dim sJSONString As String
    Dim vJSON As Variant
    Dim sState As String

    Set oRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    oRequest.Open "GET", "https://my.url.com/?filter=CODE=" & pCode, False
    oRequest.SetRequestHeader "Accept", "application/json"
    oRequest.Send
    sJSONString = oRequest.ResponseText
    JSON.Parse sJSONString, vJSON, sState
    getDesc = vJSON("row_data")(0)("LONG_DESCRIPTION")

End Function

Tested with JSON string {'row_data':[{'LONG_DESCRIPTION':'desc_string'}]}.

score 0 · Answer 2 · answered Feb 20 '21 at 16:44

You don't need external libraries to achieve this:

Option Explicit

Function getDesc(ByVal pCode As String) As String
    Dim oRequest As Object
    Set oRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    oRequest.Open "GET", "https://my.url.com/?filter=CODE=" & pCode, False
    oRequest.SetRequestHeader "Accept", "application/json"
    oRequest.Send ""

    Set props = jsonDecode(oRequest.ResponseText)

    Set sc = CreateObject("ScriptControl"): sc.Language = "JScript"
    sc.AddCode "function jsGetDesc(obj) { return obj[0]['LONG_DESCRIPTION']; }"
    getDesc = sc.Run("jsGetDesc", props.row_data)
End Function

Accessing a Variant/Object/JScriptTypeInfo property in VBA

2 Answers2