在Windows中的Excel VBA中，对于解析的JSON变量，这个JScriptTypeInfo是什么？ [英] In Excel VBA on Windows, for parsed JSON variables what is this JScriptTypeInfo anyway?
问题描述
在这里回答我自己的问题。
我已经在Excel VBA中完成了一些JSON工作，还有很多结果可以在Q&格式
https://stackoverflow.com/help/self-answer http://blog.stackoverflow.com/2011/07/its-ok-要求和答案 - 你自己的问题/
所以在stackoverflow的其他地方，可以看到有关在VBA中解析JSON的问题，但他们似乎错过一两招。
首先，我使用自定义JSON解析库，而不是使用ScriptControl的Eval方法作为所有JSON代码的基础。
我们还从本地Microsoft解决方案中表达了偏好。
这是一个先前的问题在Windows中的Excel VBA中，如何减轻分析JSON的点语法遍历问题由IDE的大小写行为破坏，这个问题构建在这个问题上。它显示了如何使用VBA.CallByName比使用点语法遍历已解析的JSON对象更强大的
。另一个先前的问题在Excel中VBA在Windows上，如何循环解析JSON数组？显示如何使用
来访问数组元素。但是CallByName返回一个好奇的变量类型，它在Watch窗口中显示为Object / JScriptTypeInfo
，如果在立即窗口（或悬停在变量上）中的一个类型Debug.Print可以获取不知情的对象Object。在另一个问题
系列在Windows中的Excel VBA中，如何获取JSON格式的JSON代码，而不是解析JSON变量的[object Object]？我提供了一些调试糖，允许对变量进行很好的检查。在第四个问题在Windows Excel VBA中，如何获取JSON密钥以避免运行时错误438'：对象不支持此属性或方法？，同时调查
如何查询成员的JSON对象，我发现似乎附加到JScriptTypeInfo对象的hasOwnProperty（）方法。
所以在这个问题中，我问这个JScriptTypeInfo无论如何？
这是系列5的问题5。这是完整的系列
Q1 < a href =https://stackoverflow.com/questions/37710995/in-excel-vba-on-windows-how-to-mitigate-issue-of-dot-syntax-traversal-of-parsed>在Excel VBA中在Windows上，如何缓解由IDE的大小写行为破坏的解析JSON的点语法遍历问题？
Q2 在Windows中的Excel VBA中，如何循环解析JSON数组？
VBA在Windows上，如何获得JSON格式的代码，而不是[object Object]来解析JSON变量？Q4 在Windows Excel VBA中，如何获取JSON键以预先运行运行时错误438'：对象不支持此属性或方法？
Q5 < a href =https://stackoverflow.com/questions/37711112/in-excel-vba-on-windows-for-parsed-json-variables-what-is-this-jscripttypeinfo>在Windows中的Excel VBA中，解析JSON变量这个JScriptTypeInfo是什么？
在ScriptControl的类型库中可以看到可见的地方，因为这是发布此类型的库。
我机器上的这种类型的完整详细信息是
Libary名称：Microsoft Script Control 1.0（Ver 1.0）
LIBID：{0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}
位置：C：\wINDOWS\SysWOW64\msscript.ocx
同时使用VBA IDE的对象浏览器和OLEVIEW.exe，它反汇编类型库我无法跟踪JScriptTypeInfo或方法hasOwnProperty。
但是不是这样的脚本引擎主机语言实现，如VBScript和JScript（用于Javascript的Microsoft名称）。
所以也许我们应该寻找一个JScript实现DLL，确实有一个是细节
Libary名称：Microsoft JScript Globals
LIBID：{3EEF9759-35FC-11D1-8CE4-00C04FC2B085}
位置：C：\wINDOWS\SysWOW64\jscript.dll
我的机器上没有注册，所以不在我的工具 - >引用库或OLEVIEW.exe列表中。我很幸运地找到了，而捅了一下。
这是OLEVIEW的一些输出，给出了类型库的一个excert。
uuid（3EEF9758-35FC-11D1-8CE4-00C04FC2B097）
]
dispinterface ObjectInstance {
属性：
方法：
[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（）;
};
以上显示了hasOwnProperty是一个必须使用的IDispatch接口（或dispinterface）的方法VBA对象被声明为Object（例如 Dim foo as Object ）
使用regsvr32注册类型库似乎什么都不做。我们可以非常确定这个JScript.dll文件，因为使用Process Explorer，我们可以看到执行行时，加载dll oScriptEngine.Language =JScript
在缺少注册类型库的情况下，我将文件JScript.dll加载到记事本++中，并进行搜索对于.JScriptTypeInfo作为正则表达式并发现命中。 Bingo！
不仅有一个ObjectInstance，它将描述VBA程序遇到的大多数变量，而且还有一个ArrayInstance是有趣的，也许我们可以使用Javascript自己的数组函数
或至少在JScript.dll类型库中记录的子集。以下是一些示例代码
'Tools-> References->
'Microsoft Script Control 1.0; {0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}; C：\Windows\SysWOW64\msscript.ocx
'和FYI /浏览功能Microsoft JScript Globals; C：\wINDOWS\SysWOW64\jscript.dll
选项显式
私有子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
设置objJSON（0）= oScriptEngine.Eval（（+ sJsonString（0）+））
设置objJSON（1）= oScriptEngine.Eval（（+ sJsonString（1）+））
Debug.Assert objJSON（0）.hasOwnProperty（key1）
调试。 assert objJSON（0）.hasOwnProperty（key2）
Debug.Assert CallByName（objJSON（1），length，VbGet）= 6
Debug.Assert CallByName（objJSON ），0，VbGet）=1234
'*是objJSON（1）ArrayInstance？
'*是否支持ArrayInstance对象的反向方法？
'调用objJSON（1）.Reverse'* reverse被大写为Reverse ... grrrr
调用CallByName（objJSON（1），reverse，VbMethod）'*所以使用CallByName作为有用的大写的解决方案
'*是的，元素是相反的！
Debug.Assert CallByName（objJSON（1），length，VbGet）= 6
Debug.Assert CallByName（objJSON（1），0，VbGet）=6789
Stop
'**现在我们知道objJSON（1）是一个ArrayInstance，我们可以对数组操作有一些乐趣
Dim objSplice As对象
设置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
设置objSlice = CallByName（objJSON（1），slice，VbMethod，2）
Debug.Assert CallByName（objJSON（1），length，VbGet）= 5
Debug.Assert CallByName（objSlice，length，VbGet）= 3
停止
调用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
摘要：JScriptTypeInfo是在VBA IDE监视窗口中显示的内容，并返回VBA函数TypeName（），但是真正隐藏了可以在JScript.dll中找到的一些对象。
我认为它可以被描述为多态的，也许更好地将其描述为晚期绑定。要查看功能，请使用工具引用并浏览到JScript.dll。
answering my own question here.
I have done some work with JSON in Excel VBA and lots of findings to post which I will do so in Q & A format
https://stackoverflow.com/help/self-answer http://blog.stackoverflow.com/2011/07/its-ok-to-ask-and-answer-your-own-questions/
So elsewhere on stackoverflow one can see questions about parsing JSON in VBA but they seem to miss a trick or two.
To begin with, I resile from using custom JSON parsing libraries and instead use the ScriptControl's Eval method as the basis of all my JSON code. And also we express a preference from native Microsoft solutions.
Here is a prior question In Excel VBA on Windows, how to mitigate issue of dot syntax traversal of parsed JSON broken by IDE's capitalisation behaviour? upon which this question builds. It shows how using VBA.CallByName is more robust than using the dot syntax to traverse a parsed JSON object. Also another prior question In Excel VBA on Windows, how to loop through a JSON array parsed? shows how it also can be used to access array elements. But CallByName returns a curious variable type that appears in Watch window as Object/JScriptTypeInfo and if one type Debug.Print in the immediate window (or hovers over the variable) one gets the uninformative "[object Object]". In another question in the series In Excel VBA on Windows, how to get stringified JSON respresentation instead of "[object Object]" for parsed JSON variables? I present some debugging "sugar" that allows nice inspection of variables. In a fourth question In Windows Excel VBA,how to get JSON keys to pre-empt "Run-time error '438': Object doesn't support this property or method"?, whilst investigating how to query a JSON object for a member I discover a hasOwnProperty() method that seems attached to a JScriptTypeInfo object.
So in this question I ask, what exactly is this JScriptTypeInfo anyway?
This is Question 5 of series of 5. Here is the full series
Q2 In Excel VBA on Windows, how to loop through a JSON array parsed?
Q5 In Excel VBA on Windows, for parsed JSON variables what is this JScriptTypeInfo anyway?
One possible place to look is in the type library for the ScriptControl as this is the library which emanates this type.
The full details of this type on my machine are
Libary Name: Microsoft Script Control 1.0 (Ver 1.0)
LIBID: {0E59F1D2-1FBE-11D0-8FF2-00A0D10038BC}
Location: C:\wINDOWS\SysWOW64\msscript.ocx
Using both VBA IDE's Object Browser and OLEVIEW.exe which disassembles type library I can no trace of the interface JScriptTypeInfo or the method hasOwnProperty.
But isn't it the case that the script engine hosts language implementations, such as VBScript and JScript (Microsoft name for Javascript).
So perhaps we should hunt for a JScript implementation DLL and indeed there is one here 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 so not in my list of Tools->References libraries or in OLEVIEW.exe. I was lucky to find whilst poking around.
Here is some output from OLEVIEW giving an exceprt of the type library
[
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 above shows the hasOwnProperty to be a method of a IDispatch interface (or dispinterface) necessary to work with VBA object's declared of type Object (e.g. Dim foo as Object)
Registering the type library with regsvr32 appears to do nothing. One must browse to the file in Tools References to view in VBA's object browser.
We can be pretty sure about this JScript.dll file because using Process Explorer we can see the dll being loaded when executing the line oScriptEngine.Language = "JScript"
In the lack of a registered type library I loaded the file JScript.dll into Notepad++ and searched for .J.S.c.r.i.p.t.T.y.p.e.I.n.f.o as a regular expression and found a hit. Bingo!
Not only is there an ObjectInstance which would describe most of the variables a VBA program encounters but also there is an ArrayInstance which is intriguing, perhaps we can use Javascript's own array functions or at least a subset as documented in JScript.dll's type library. Here is some 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 to show in the VBA IDE watch window and the return of the VBA function TypeName() but which really hides a number of objects that can be found in JScript.dll.
I suppose it can be described as polymorphic, perhaps better to describe it as late binding. To view capabilities use Tools-References and browse to JScript.dll.
这篇关于在Windows中的Excel VBA中，对于解析的JSON变量，这个JScriptTypeInfo是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
