En Excel VBA en Windows, ¿cómo mitigar el problema del recorrido de sintaxis de puntos de JSON analizado roto por el comportamiento de capitalización de IDE?

Hola, respondiendo mi propia pregunta aquí. He realizado algunos trabajos con JSON en Excel VBA y muchos resultados para publicar, lo que haré en formato de preguntas y respuestashttps://stackoverflow.com/help/self-answer http://blog.stackoverflow.com/2011/07/its-ok-to-ask-and-answer-your-own-questions/

Entonces, en otra parte de stackoverflow, uno puede ver preguntas sobre el análisis de JSON en VBA, pero parece que pierden uno o dos trucos.

Para empezar, evito usar bibliotecas de análisis JSON personalizadas y en su lugar uso el método Eval de ScriptControl como base de todo mi código JSON. Y también expresamos una preferencia de las soluciones nativas de Microsoft.

En esta primera pregunta, mostraré que en Excel VBA, de hecho, se puede usar la sintaxis de puntos para atravesar una estructura JSON, pero que desafortunadamente esto se rompe por la "utilidad" del IDE de VBA con respecto a la capitalización.

A continuación se muestra un código de ejemplo en la línea con la etiqueta 1: podemos ver el texto "objJSON.key1" y este código funciona hasta que se descomenta

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

Option Explicit
Option Private Module

Private Sub TestJSONParsingWithVBACallByName()

    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 + ")")
1:  Debug.Assert objJSON.key1 = "value1"
    Debug.Assert objJSON.key2.key3 = "value3"

    '**** BUT IF UNCOMMENT NEXT LINE THIS AFFECTS ALL CAPITALISATION INSTANCES OF KEY1 INCLUDING LINE 1 WHICH THENCE BREAKS
2:  'Dim Key1 as Long

End Sub

Aquí hay una captura de pantalla antesY después de descomentar la Línea 2, la Línea 1 se reescribe con el símbolo 'clave1' ahora con una 'K' mayúscula.

Ahora, después de algunos experimentos, parece que el efecto de reescritura se limita al alcance del proyecto, por lo que otros proyectos no se ven afectados. Esto significa que uno podría aislar el problema utilizando siempre un proyecto separado, pero entonces, ¿cómo podría ordenar el objeto al proyecto consumidor y luego acceder a él? Seguramente uno vuelve a encontrar el mismo problema. Entonces, el aislamiento del proyecto no es realmente una solución.

Una forma es asegurarse de que los símbolos no entren en conflicto y dar a las teclas JSON algún tipo de prefijo, así que aquí hay un ejemplo

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

Private Sub TestJSONParsingWithDotSyntaxAndKeyPrefixesToAvoidNameClash()

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

    Dim sJsonString As String
    sJsonString = "{'kKey1': 'value1'  ,'kKey2': { 'kKey3': 'value3' } }"


    Dim objJSON As Object
    Set objJSON = oScriptEngine.Eval("(" + sJsonString + ")")
1:  Debug.Assert objJSON.kKey1 = "value1"
    Debug.Assert objJSON.kKey2.kKey3 = "value3"

    '**** SAFE TO UNCOMMENT AS SYMBOLS DO NOT CLASH NOW
2:  'Dim Key1 As Long
End Sub

De alguna manera no me gusta esto, parece extraño tener que cambiar el JSON solo para que VBA pueda acceder a él. Además, uno no puede tener el control de la fuente JSON.

Existen otros métodos, como agregar algunos javscript al motor de scripts para permitir que Javascript acceda. Con una punta de sombrero para el usuario Codohttps://stackoverflow.com/users/413337/codo Aquí hay una muestra basada en este enfoque ...

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

Private Sub TestJSONParsingWithMiniScript()
    'hat tip to Codo https://stackoverflow.com/users/413337/codo
    'Based on https://stackoverflow.com/questions/5773683/excel-vba-parsed-json-object-loop#7300963  
    Dim oScriptEngine As ScriptControl
    Set oScriptEngine = New ScriptControl
    oScriptEngine.Language = "JScript"
    oScriptEngine.AddCode "function getProperty(jsonObj, propertyName) { return jsonObj[propertyName]; } "


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

    Dim objJSON As Object
    Set objJSON = oScriptEngine.Eval("(" + sJsonString + ")")
    Debug.Assert oScriptEngine.Run("getProperty", objJSON, "key1") = "value1"
    Debug.Assert oScriptEngine.Run("getProperty", oScriptEngine.Run("getProperty", objJSON, "key2"), "key3") = "value3"

End Sub

Sin embargo, me gusta la técnica de agregar un script al motor de script, descubrí una técnica más nativa y es usar VBA.CallByName y esta es la técnica que se muestra en mi respuesta.

No he seleccionado mi propia respuesta como definitiva porque creo que (1) parece que la comunidad puede continuar mejorando nuestro conocimiento sobre el análisis JSON en Excel VBA y (2) si alguien descubre cómo detener la capitalización, entonces es un ganador obvio.

Esta es la pregunta 1 de la serie de 5. Aquí está la serie completa

Q1En Excel VBA en Windows, ¿cómo mitigar el problema del cruce de sintaxis de puntos de JSON analizado roto por el comportamiento de capitalización de IDE?

Q2En Excel VBA en Windows, ¿cómo recorrer un conjunto JSON analizado?

Q3En Excel VBA en Windows, ¿cómo obtener una representación JSON en cadena en lugar de “[objeto Object]” para las variables JSON analizadas?

Q4En Windows Excel VBA, ¿cómo obtener claves JSON para evitar el "error de tiempo de ejecución '438': el objeto no admite esta propiedad o método"?

Q5En Excel VBA en Windows, para las variables JSON analizadas, ¿qué es este JScriptTypeInfo de todos modos?