In Windows Excel VBA,how to get JSON keys to pre-empt "Run-time error '438': Object doesn't support this property or method"?

answering my own question here.
I have done some work with JSON in Excel VBA and lots of findings to post which I will do so in Q & A format https://stackoverflow.com/help/self-answer https://blog.stackoverflow.com/2011/07/its-ok-to-ask-and-answer-your-own-questions/

So elsewhere on stackoverflow one can see questions about parsing JSON in VBA but they seem to miss a trick or two.

To begin with, I resile from using custom JSON parsing libraries and instead use the ScriptControl's Eval method as the basis of all my JSON code. And also we express a preference from native Microsoft solutions.

Here is a prior question In Excel VBA on Windows, how to mitigate issue of dot syntax traversal of parsed JSON broken by IDE's capitalisation behaviour? upon which this question builds. It shows how using VBA.CallByName is more robust than using the dot syntax to traverse a parsed JSON object. Also another prior question In Excel VBA on Windows, how to loop through a JSON array parsed? shows how it also can be used to access array elements. But CallByName returns a curious variable type that appears in Watch window as Object/JScriptTypeInfo and if one type Debug.Print in the immediate window (or hovers over the variable) one gets the uninformative "[object Object]". In another question in the series In Excel VBA on Windows, how to get stringified JSON respresentation instead of “[object Object]” for parsed JSON variables? I present some debugging "sugar" that allows nice inspection of variables.

In this question I ask how can we programmatically get a list of members with which I can detect presence of a key, this will help to pre-empt any "Run-time error '438': Object doesn't support this property or method" errors and allow us to write defensive (hopefully "bulletproof") code ?

This is Question 4 of series of 5. Here is the full series

Q1 In Excel VBA on Windows, how to mitigate issue of dot syntax traversal of parsed JSON broken by IDE's capitalisation behaviour?

Q2 In Excel VBA on Windows, how to loop through a JSON array parsed?

Q3 In Excel VBA on Windows, how to get stringified JSON respresentation instead of “[object Object]” for parsed JSON variables?

Q4 In Windows Excel VBA,how to get JSON keys to pre-empt “Run-time error '438': Object doesn't support this property or method”?

Q5 In Excel VBA on Windows, for parsed JSON variables what is this JScriptTypeInfo anyway?

Answers to other stack overflow question that relate to working with parsed JSON objects use a mini-script approach and we can use this approach here. If we say we are running a Microsoft Windows edition of Excel VBA then we can use the Scripting Dictionary as found in the library Microsoft Scripting Runtime.

We can create the Scripting.Dictionary in the Javascript, populate it with the keys of JSON object and also use the values as references to the subelements, and finallly pass back to VBA. In VBA, one can then use the Dictionary's Exists method to defend against missing keys. One can use the Dictionary's Count method to dimension other downstream variables. One can even use Dictionary's Item method to retrieve a subelement (one level down only).

Thus,

'Tools->References->
'Microsoft Scripting Runtime
'Microsoft Script Control 1.0;  {0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}; C:\Windows\SysWOW64\msscript.ocx

Option Explicit

Private Function GetScriptEngine() As ScriptControl
    Static soScriptEngine As ScriptControl
    If soScriptEngine Is Nothing Then
        Set soScriptEngine = New ScriptControl
        soScriptEngine.Language = "JScript"

        soScriptEngine.AddCode "function getKeyValues(jsonObj) { " & _
                              " var dictionary = new ActiveXObject(""Scripting.Dictionary""); " & _
                              " var keys = new Array(); for (var i in jsonObj) { dictionary.add(i,jsonObj[i]); }; return dictionary; } "


    End If
    Set GetScriptEngine = soScriptEngine
End Function


Private Sub TestJSONParsingWithCallByName3()

    Dim oScriptEngine As ScriptControl
    Set oScriptEngine = GetScriptEngine

    Dim sJsonString As String
    sJsonString = "{'key1': 'value1'  ,'key2': { 'key3': 'value3' } }"

    Dim objJSON As Object
    Set objJSON = oScriptEngine.Eval("(" + sJsonString + ")")

    Dim dicKeys As Scripting.Dictionary
    Set dicKeys = oScriptEngine.Run("getKeyValues", objJSON)

    Debug.Assert dicKeys.Count = 2

    Debug.Assert TypeName(dicKeys.Item(dicKeys.Keys()(1))) = "JScriptTypeInfo"
    Stop

    If dicKeys.Exists("foobarbaz") Then

        '*** Next line WOULD throw "Run-time error '438': Object doesn't support this property or method" because "foobarbaz" is not a key
        '*** but is skipped because of defensive code.
        Debug.Assert VBA.CallByName(objJSON, "foobarbaz", VbGet)

    End If

End Sub

However, I have also discovered a wonderful alternative that requires no miniscript or Scripting.Dictionary. It will allow pre-empting of missing keys but has no collection class functionality. It uses a little known property of hasOwnProperty(). Thus,

'Tools->References->
'Microsoft Script Control 1.0;  {0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}; C:\Windows\SysWOW64\msscript.ocx

Option Explicit

Private Sub TestJSONParsingWithCallByName4()

    Dim oScriptEngine As ScriptControl
    Set oScriptEngine = New ScriptControl
    oScriptEngine.Language = "JScript"

    Dim sJsonString As String
    sJsonString = "{'key1': 'value1'  ,'key2': { 'key3': 'value3' } }"

    Dim objJSON As Object
    Set objJSON = oScriptEngine.Eval("(" + sJsonString + ")")

    Debug.Assert objJSON.hasOwnProperty("key1")
    Debug.Assert objJSON.hasOwnProperty("key2")

    Dim objKey2 As Object
    Set objKey2 = VBA.CallByName(objJSON, "key2", VbGet)

    Debug.Assert objKey2.hasOwnProperty("key3")


    If objJSON.hasOwnProperty("foobarbaz") Then

        '*** Next line WOULD throw "Run-time error '438': Object doesn't support this property or method" because "foobarbaz" is not a key
        '*** but is skipped because of defensive code.
        Debug.Assert VBA.CallByName(objJSON, "foobarbaz", VbGet)

    End If

End Sub

Recommended topics

Hot tags