In Excel VBA for Windows, for parsed JSON variables, what is JScriptTypeInfo?

Question

answering my own question here. I did some work with JSON in Excel VBA and a lot of publishing results, which I will do in Q and A format https://stackoverflow.com/help/self-answer http://blog.stackoverflow.com/2011/07/ its-ok-to-ask-and-answer-your-own-questions /

So, somewhere in stackoverflow, you can see questions about parsing JSON in VBA, but they seem to have missed a trick or two.

First, I refuse to use custom JSON parsing libraries and instead use the ScriptControl Eval method as the basis for all JSON code. And also we express preference from our own Microsoft solutions.

Here's the previous question In Excel VBA on Windows, how can I mitigate the JSON parsing issue broken by the IDE capitalization behavior? This question is built on. This shows how the use of VBA.CallByName is more reliable than using the dotted syntax to traverse the parsed JSON object. Also another previous question In Excel VBA on Windows, how to loop through a JSON array? shows how it can also access array elements. But CallByName returns a curious type of variable that appears in the Watch window as Object / JScriptTypeInfo and if one type of Debug.Print in the immediate window (or hangs over the variable) receives an uninformative "object of the object". In another question in the series In Excel VBA on Windows, how to get a compressed JSON representation instead of "[object Object]" for the analyzed JSON variables? I present some debugging "sugars" which allows you to check variables well. In the fourth question In Windows Excel VBA, how do I get the JSON keys for the preliminary check "Runtime Error" 438: The object does not support this property or method "? , While researching how to request a JSON object for a member I find the hasOwnProperty () method, which seems attached to a JScriptTypeInfo object.

So, in this question, I ask what exactly is JScriptTypeInfo anyway?

This is Question 5 from series 5. Here is the complete series

Q1 In Excel VBA on Windows, how can I mitigate the JSON parsing issue broken by the IDE capitalization behavior?

Q2 In Excel VBA for Windows, how to handle a JSON array?

Q3 In Excel VBA on Windows, how to get a compressed JSON representation instead of "[object Object]" for parsed JSON variables?

Q4 In Windows Excel VBA, how do I get JSON keys to pre-check a "Runtime Error" 438: Does the object not support this property or method ??

Q5 In Excel VBA for Windows, for parsed JSON variables, what is JScriptTypeInfo anyway?

Answer 1

One possible place to look is the type library for ScriptControl, as it is the library that emits this type.

Full data of this type on my machine

Libary Name: Microsoft Script Control 1.0 (Ver 1.0) LIBID: {0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC} Location: C:\wINDOWS\SysWOW64\msscript.ocx 

Using both the VBA IDE Object Browser and OLEVIEW.exe, which parse the type library, I cannot track the JScriptTypeInfo interface or the hasOwnProperty method.

But isn't it that the script engine supports language implementations such as VBScript and JScript (Microsoft's name for Javascript).
Therefore, perhaps we should look for a JScript DLL implementation, and indeed there are details

 Libary Name: Microsoft JScript Globals LIBID: {3EEF9759-35FC-11D1-8CE4-00C04FC2B085} Location: C:\wINDOWS\SysWOW64\jscript.dll 

which on my machine is not registered and therefore not included in the list of libraries Tools-> References or in OLEVIEW.exe. I was lucky to find while poking.
Here is the output from OLEVIEW giving a type library exception

 [ uuid(3EEF9758-35FC-11D1-8CE4-00C04FC2B097) ] dispinterface ObjectInstance { properties: methods: [id(0x0000044c)] StringInstance* toString(); [id(0x0000044d)] StringInstance* toLocaleString(); [id(0x0000044e)] VARIANT hasOwnProperty(VARIANT propertyName); [id(0x0000044f)] VARIANT propertyIsEnumerable(VARIANT propertyName); [id(0x00000450)] VARIANT isPrototypeOf(VARIANT obj); [id(0x00000451)] ObjectInstance* valueOf(); }; 

This shows that hasOwnProperty is an IDispatch (or dispinterface) interface method needed to work with a declared VBA object of type Object (e.g. Dim foo as Object ) Registering a type library with regsvr32 does not seem to do anything. To view VBA objects in the browser, go to the file in the tool list.

We can be sure of this JScript.dll file because using Process Explorer we can see how the DLL loads when the oScriptEngine.Language = "JScript" line is oScriptEngine.Language = "JScript" In the absence of a registered type library, I loaded the JScript.dll file in Notepad ++ and looked for .JScriptTypeInfo as a regular expression and found a hit. Bingo!

There is not only an ObjectInstance object that will describe most of the variables found in the VBA program, but also intriguing ArrayInstance, perhaps we can use our own Javascript array functions or at least a subset, as described in the JScript.dll type library. Here is a sample code

 'Tools->References-> 'Microsoft Script Control 1.0; {0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}; C:\Windows\SysWOW64\msscript.ocx 'and FYI/browsing capabilities Microsoft JScript Globals; C:\wINDOWS\SysWOW64\jscript.dll Option Explicit Private Sub TestJSONParsingWithCallByName5() Dim oScriptEngine As ScriptControl Set oScriptEngine = New ScriptControl oScriptEngine.Language = "JScript" Dim sJsonString(0 To 1) As String sJsonString(0) = "{'key1': 'value1' ,'key2': { 'key3': 'value3' } }" sJsonString(1) = "[ 1234, 2345, 3456, 4567, 5678, 6789 ]" Dim objJSON(0 To 1) As Object Set objJSON(0) = oScriptEngine.Eval("(" + sJsonString(0) + ")") Set objJSON(1) = oScriptEngine.Eval("(" + sJsonString(1) + ")") Debug.Assert objJSON(0).hasOwnProperty("key1") Debug.Assert objJSON(0).hasOwnProperty("key2") Debug.Assert CallByName(objJSON(1), "length", VbGet) = 6 Debug.Assert CallByName(objJSON(1), "0", VbGet) = "1234" '* Is objJSON(1) an ArrayInstance? '* does it support the reverse method of the ArrayInstance object? 'Call objJSON(1).Reverse '* reverse gets capitalised into Reverse ... grrrr Call CallByName(objJSON(1), "reverse", VbMethod) '* so use CallByName as solution to "helpful" capitalisation '* Yes, the elements are reversed! Debug.Assert CallByName(objJSON(1), "length", VbGet) = 6 Debug.Assert CallByName(objJSON(1), "0", VbGet) = "6789" Stop '** And now we know objJSON(1) is an ArrayInstance we can have some fun with array operations Dim objSplice As Object Set objSplice = CallByName(objJSON(1), "splice", VbMethod, 2, 1) Debug.Assert CallByName(objJSON(1), "length", VbGet) = 5 Debug.Assert CallByName(objSplice, "length", VbGet) = 1 Dim objSlice As Object Set objSlice = CallByName(objJSON(1), "slice", VbMethod, 2) Debug.Assert CallByName(objJSON(1), "length", VbGet) = 5 Debug.Assert CallByName(objSlice, "length", VbGet) = 3 Stop Call CallByName(objJSON(1), "sort", VbMethod) Debug.Assert CallByName(objJSON(1), "join", VbMethod) = "1234,2345,3456,5678,6789" Debug.Assert CallByName(objJSON(1), "join", VbMethod, " ") = "1234 2345 3456 5678 6789" Stop Debug.Assert CallByName(objJSON(1), "pop", VbMethod) = "6789" Debug.Assert CallByName(objJSON(1), "length", VbGet) = 4 Stop End Sub 

SUMMARY: JScriptTypeInfo is something that needs to be shown in the VBA IDE viewport and returning the VBA TypeName () function, but which really hides several objects that can be found in JScript.dll.
I suppose this can be described as polymorphic, it might be better to describe it as late binding. To view the features, use Tools-References and browse to JScript.dll.