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.

Answer 1

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'}]}.