1

How do I access this key variable decoded from JSON? I used the example in Excel VBA: Parsed JSON Object Loop. VBA editor keeps changing the case to uppercase even when I change it to lowercase. I also tried commenting both out and uncommenting a lowercase key but it still changes it. I didn't think the case would matter but it is complaining and I'm not sure why, as the field clearly exists.

Imgur

Community
  • 1
  • 1
Chloe
  • 25,162
  • 40
  • 190
  • 357
  • what `Type` of variable is `arr`? I can't see your declarations because of the error message box. – David Zemens Oct 24 '13 at 15:54
  • `Set arr = jsonDecode(jsonString)` – Chloe Oct 24 '13 at 16:14
  • That's the assignment of the variable. What's the `Dim arr as ...`? – David Zemens Oct 24 '13 at 16:15
  • There is no such line for `arr`. – Chloe Oct 24 '13 at 16:16
  • 2
    VBA has problems dealing with certain properties in js objects when their names correspond to VBA "keywords" which have a different case. Best bet is to keep the decoded JSON object in the script control object, and create an "accessor" function(s) in the script control to which you can pass the property name as a string and get back the property value. There are answers in the thread you cite which address this. – Tim Williams Oct 24 '13 at 16:21
  • Check the typename by ?typename(arr) – html_programmer Oct 24 '13 at 16:27

1 Answers1

2

I had to add Private sc As ScriptControl at the top of the module or else it wouldn't work. Even Dim sc as ScriptControl within the function wouldn't work. I also created a Javascript get function to retrieve values.

Private sc As ScriptControl
...
Function jsonDecode(jsonString As Variant)
    Set sc = New ScriptControl
    sc.Language = "JScript"
    sc.AddCode "Object.prototype.get=function( i ) { return this[i]; };"
    Set jsonDecode = sc.Eval("(" + jsonString + ")")
End Function

...

Set arr = jsonDecode(objHTTP.ResponseText)
Debug.Print arr.get("key")
Chloe
  • 25,162
  • 40
  • 190
  • 357