StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>After Googling around not quite finding what I wanted, I remembered the <a href="http://www.mvps.org/emorcillo/en/code/vb6/index.shtml" rel="nofollow noreferrer">Edanmo</a> site which got me thinking about TLBINF32.DLL, downloading Microsoft's <a href="http://support.microsoft.com/kb/224331" rel="nofollow noreferrer">TLBINF32.CHM</a> and reading up on GetMembersWithSubStringEx. Below is the implementation of it (done in VB6 with a reference to TLBINF32.DLL), some demo VBScript and output, and the wrapping of that functionality in some VBA. </p> <pre><code>Public Function SearchTLIMethodsAndProperties(sTypelib As Variant, sSymbol As Variant) As Variant Dim SI As SearchItem Dim aResults As Variant Dim bFound as boolean Dim Groups(1) As InvokeKinds Groups(0) = INVOKE_FUNC Or INVOKE_PROPERTYGET Or _ INVOKE_PROPERTYPUT Or INVOKE_PROPERTYPUTREF ReDim aResults(0) bFound = False With TypeLibInfoFromFile(sTypelib) .SearchDefault = tliStClasses Or tliStEvents For Each SI In .GetMembersWithSubStringEx(sSymbol, Groups) bFound = True arr.AAdd_PostIncrement aResults, SI.Name Next End With if bFound then ReDim Preserve aResults(UBound(aResults) - 1) end if SearchTLIMethodsAndProperties = aResults End Function </code></pre> <p>VBScript demo. The above code was included in my StdLib DLL in the Registry coclass.</p> <pre><code>Dim O, R Set O = CreateObject("Std.Registry") Set R = CreateObject("Std.Arrays") WScript.Echo R.ShowStructure( O.SearchTLIMethodsAndProperties( "MSSCRIPT.OCX","")) </code></pre> <p>Output from the demo (script was run in SciTE).</p> <pre><code>>cscript "C:\foo\foo.vbs" {Add,AddCode,AddObject,AllowUI,Clear,CodeObject,Column,Count,Description,Error,Eval,ExecuteStatement,HasReturnValue,HelpContext,HelpFile,Item,Language,Line,Modules,Name,NumArgs,Number,Procedures,Reset,Run,SitehWnd,Source,State,Text,Timeout,UseSafeSubset} >Exit code: 0 </code></pre> <p>Finally, the VBA code. A cell has a symbol in it and this routine finds it or returns an error string. </p> <pre><code>Public Function LookupSymbol(sSym As String) As String Dim aRes As Variant aRes = reg.SearchTLIMethodsAndProperties("MSSCRIPT.OCX", sSym) Dim i As Integer LookupSymbol = "!!NotFound!!" For i = 0 To UBound(aRes) If LCase$(aRes(i)) = LCase$(sSym) Then LookupSymbol = sSym Exit For End If Next End Function </code></pre> <p>Looking back on it now, I think I might pass in the path to the DLL/OCX as the first parameter.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload