How do I access a JSON variable in lowercase when VBA keeps changing to uppercase?

Question

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

what Type of variable is arr? I can't see your declarations because of the error message box. — David Zemens
– David Zemens, Commented Oct 24, 2013 at 15:54
That's the assignment of the variable. What's the Dim arr as ...? — David Zemens
– David Zemens, Commented Oct 24, 2013 at 16:15
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
– Tim Williams, Commented Oct 24, 2013 at 16:21

Chloe · Accepted Answer · 2013-10-24 17:51:38Z

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

Collectives™ on Stack Overflow

How do I access a JSON variable in lowercase when VBA keeps changing to uppercase?

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related