在Windows Excel VBA中，如何获取JSON键以预先运行“运行时错误”438'：Object不支持此属性或方法“？ [英] In Windows Excel VBA,how to get JSON keys to pre-empt "Run-time error '438': Object doesn't support this property or method"?

问题描述

在这里回答我自己的问题。

我已经在Excel VBA中完成了一些JSON工作，还有很多结果可以在Q&格式
https://stackoverflow.com/help/self-answer http://blog.stackoverflow.com/2011/07/its-ok-要求和答案 - 你自己的问题/

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 http://blog.stackoverflow.com/2011/07/its-ok-to-ask-and-answer-your-own-questions/

所以在stackoverflow的其他地方，可以看到有关在VBA中解析JSON的问题，但他们似乎错过一两招。

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

首先，我使用自定义JSON解析库，而不是使用ScriptControl的Eval方法作为所有JSON代码的基础。
我们还从本地Microsoft解决方案中表达了偏好。

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.

这是一个先前的问题在Windows中的Excel VBA中，如何减轻分析JSON的点语法遍历问题由IDE的大小写行为破坏，这个问题构建在这个问题上。它显示了如何使用VBA.CallByName比使用点语法遍历已解析的JSON对象更强大的
。另一个先前的问题在Excel中VBA在Windows上，如何循环解析JSON数组？显示如何使用
来访问数组元素。但是CallByName返回一个好奇的变量类型，它在Watch窗口中显示为Object / JScriptTypeInfo
，如果在立即窗口（或悬停在变量上）中的一个类型Debug.Print可以获取不知情的对象Object。在另一个问题
系列在Excel中的Excel VBA中，如何获取JSON格式的代码，而不是解析JSON变量的[object Object]？我提供了一些调试糖，可以很好地检查变量。

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.

在这个问题中，我问我们如何以编程方式获取一个可以检测到密钥存在的成员列表，这将有助于
预先释放任何运行 - 时间错误'438'：对象不支持此属性或方法错误，并允许我们写防御（希望防弹）代码？

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 ?

这是问题4系列的系列。这是全系列

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

Q1 在Windows中的Excel VBA中，如何减轻点语法trav的问题被解密的JSON破坏了IDE的资本化行为？

Q2 在Windows中的Excel VBA中，如何循环解析JSON数组？

Q3 在Excel中的Excel VBA中，如何获取被解析的JSON变量而不是[object Object]的字符串JSON表示？

Q4 在Windows Excel VBA中，如何获取JSON键以预先运行运行时错误438'：对象不支持此属性或方法？

Q5 在Windows中的Excel VBA中，对于已解析的JSON变量，这个JScriptTypeInfo是什么？

推荐答案

与使用解析的JSON对象相关的其他堆栈溢出问题使用迷你脚本方法，我们可以在此使用此方法。
如果我们说我们正在运行一个Microsoft Windows版本的Excel VBA，那么我们可以使用库文件 Microsoft Scripting Runtime 中的脚本字典。

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.

我们可以在Javascript中创建Scripting.Dictionary，使用JSON对象的关键字填充它，并使用这些值作为子元素的引用，
并完全传回VBA。在VBA中，可以使用Dictionary的Exists方法来保护缺少的键。可以使用Dictionary的Count方法
对其他下游变量进行维度。甚至可以使用Dictionary的Item方法来检索一个子元素（仅限一个级别）。

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).

因此，

'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

然而，我也发现了一个很好的选择，不需要miniscript或Scripting.Dictionary。它将允许预先删除键，但
没有集合类功能。它使用hasOwnProperty（）的一些已知属性。因此，

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

这篇关于在Windows Excel VBA中，如何获取JSON键以预先运行“运行时错误”438'：Object不支持此属性或方法“？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！