No Excel VBA no Windows, como atenuar o problema da passagem de sintaxe de ponto do JSON analisado, interrompido pelo comportamento de capitalização do IDE?

Olá, respondendo minha própria pergunta aqui. Eu já trabalhei com JSON no Excel VBA e descobri muitas descobertas que serão feitas no formato de perguntas e respostashttps://stackoverflow.com/help/self-answer http://blog.stackoverflow.com/2011/07/its-ok-to-ask-and-answer-your-own-questions/

Portanto, em outras partes do stackoverflow, é possível ver perguntas sobre a análise do JSON no VBA, mas elas parecem faltar um truque ou dois.

Para começar, evito usar bibliotecas de análise JSON personalizadas e, em vez disso, uso o método Eval do ScriptControl como base de todo o meu código JSON. E também expressamos uma preferência pelas soluções nativas da Microsoft.

Nesta primeira pergunta, mostrarei que, no Excel VBA, é possível usar a sintaxe de pontos para atravessar uma estrutura JSON, mas que infelizmente isso é interrompido pela "utilidade" do IDE do VBA no que diz respeito à capitalização.

Abaixo está um exemplo de código em que na linha 1: podemos ver o texto "objJSON.key1" e esse código funciona até que alguém descomente

'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

Aqui está uma captura de tela antesE depois de descomentar a Linha 2, a Linha1 é reescrita com o símbolo 'chave1' agora com a letra K 'maiúscula.

Agora, após algumas experiências, parece que o efeito de reescrita é limitado ao escopo do projeto, para que outros projetos não sejam afetados. Isso significa que se pode isolar o problema usando sempre um projeto separado, mas como organizar o objeto para o projeto consumidor e, portanto, acessá-lo, certamente se depara com o mesmo problema novamente. Portanto, o isolamento do projeto não é realmente uma solução.

Uma maneira é garantir que os símbolos não entrem em conflito e dê às chaves JSON algum tipo de prefixo, então aqui está um exemplo

'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 alguma forma, eu não gosto disso, parece estranho ter que mudar o JSON apenas para que o VBA possa acessá-lo. Além disso, pode não haver controle sobre o JSON de origem.

Existem outros métodos, como adicionar javscript ao mecanismo de script para permitir que o Javascript acesse. Com uma gorjeta para o usuário Codohttps://stackoverflow.com/users/413337/codo aqui está uma amostra com base nessa abordagem ...

'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

Eu gosto da técnica de adicionar um script ao mecanismo de script, no entanto, descobri uma técnica mais nativa, que é usar VBA.CallByName e essa é a técnica mostrada na minha resposta.

Eu não selecionei minha própria resposta como definitiva porque acho que (1) parece que a comunidade pode continuar aprimorando nosso conhecimento sobre a análise JSON no Excel VBA e (2) se alguém descobrir como interromper a capitalização, esse é um vencedor óbvio.

Esta é a pergunta 1 da série de 5. Aqui está a série completa

Q1No Excel VBA no Windows, como atenuar o problema da passagem de sintaxe de ponto do JSON analisado, quebrado pelo comportamento de capitalização do IDE?

Q2No Excel VBA no Windows, como fazer um loop através de uma matriz JSON analisada?

Q3No Excel VBA no Windows, como obter uma representação JSON com string em vez de "[objeto Objeto]" para variáveis JSON analisadas?

Q4No Windows Excel VBA, como fazer com que as chaves JSON antecipem "Erro em tempo de execução '438': o objeto não suporta esta propriedade ou método"?

Q5No Excel VBA no Windows, para variáveis JSON analisadas, o que é esse JScriptTypeInfo?