VBA - Types - The Object Browser

I intend to post about Types and Type Libraries etc. and low-level COM interfaces that facilitate their discovery, just like reflection. Before I do I need to cover the basics of Types using the tools that a VBA developer has to hand. The first of these is the VBA IDE's Object Browser.

The two screenshots of the object browser above should be familiar and they show two separately libraries, the VBA library on the left and the Microsoft Scripting Runtime library on the right. I prefer to work with the Microsoft Scripting Runtime for the examples in this series of posts.

Showing Hidden Members

It is a little known feature of the VBA IDE Object Browser that it will reveal some hidden members. The following screenshot shows the menu option selected for the Scripting library and its hidden members revealed.

Some elements are more hidden than others

I will give a sneak preview of some of the Interface Definition Language (IDL) that is used to define the Scripting type library. Below there are two methods, one highlighted in blue and the other in red. The method in red HashVal was displayed after we selected Show Hidden Members' from the menu. However, the method in blue, _NewEnum, remains hidden, that is because it is marked with the restricted attribute.

    interface IDictionary : IDispatch {
        ...
        [id(0xfffffffc), restricted]
        HRESULT _NewEnum([out, retval] IUnknown** ppunk);
        [id(0x0000000a), propget, hidden]
        HRESULT HashVal(
                        [in] VARIANT* Key, 
                        [out, retval] VARIANT* HashVal);
    };

If you are wondering what _NewEnum does then I can tell you it is what drives VBA's For Each statement. So you can see that sometimes it is necessary to restrict some methods from VBA developers. Here is some sample code that relies on the restricted _NewEnum method.

Option Explicit

Sub Test()

    Dim dic As Scripting.Dictionary
    Set dic = New Scripting.Dictionary
    
    dic.Add "Red", "FF0000"
    dic.Add "Green", "00FF00"
    dic.Add "Blue", "0000FF"

    Dim v As Variant
    For Each v In dic
        Debug.Print v, dic(v)
    Next v

End Sub

What's Next?

So I do not intend to show the Object Browser again as I have demonstrated how it does not tell the full story. Instead I will work with and only show OLEVIEW.exe which I will introduce in the next post.

VBA - Inheritance ... or as close as you will get ... better in fact.

So far, this blog has not opined on VBA inheritance or more accurately the simulation of inheritance; time to rectify that here. We could use composition along with a standard naming convention to mimic inheritance as found in languages such as C++ and C#. But we can use the default member trick to make the syntax even tighter.

C# and C++ Inheritance and the Fragile Base Class Problem

Before people complain that follows is not like the true inheritance found in C# and C++ I would say that what follows has some superior benefits. C++ and C# inheritance breaks encapsulation because derived classes can gain access to the base class's members. Also, once derived classes are written it becomes difficult to re-engineer the base class without breaking the derived classes, this is known as the fragile base class problem. Indeed, languages such as C# have to invent keywords such sealed to prevent derived classes inspecting a base class's private variables.

VBA can use Composition and the Default Member Trick to simulate Inheritance

What follows is VBA composition dressed up as inheritance by using the default member trick to tighten the syntax. So it has all the benefits of composition over inheritance

Code Listings

In the following listings, to effect Class1 and Class2 as intended it is not sufficient to cut and paste into VBA environment, it is also required to export to disk, load into editor, edit the file, save and then re-import; details are given in the code.

Class1 Listing

Option Explicit

Private moBase As Class2

'* To do the default member trick
'* 1) Export this module to disk;
'* 2) load into text editor;
'* 3) uncomment line with text Attribute Item.VB_UserMemId = 0 ;
'* 4) save the file back to disk
'* 5) remove or rename original file from VBA project to make room
'* 6) Re-import saved file


Private Sub Class_Initialize()
    Set moBase = New Class2
End Sub

Public Function Base() As Class2
    'Attribute Item.VB_UserMemId = 0
    Set Base = moBase
End Function

Public Function Foo() As String
    Foo = "Class1.Foo:" & 23
End Function

Public Function Common() As String
    Common = "Class1.Common"
End Function

Class2 Listing

Option Explicit

Private moBase As Class3

'* To do the default member trick
'* 1) Export this module to disk;
'* 2) load into text editor;
'* 3) uncomment line with text Attribute Item.VB_UserMemId = 0 ;
'* 4) save the file back to disk
'* 5) remove or rename original file from VBA project to make room
'* 6) Re-import saved file

Private Sub Class_Initialize()
    Set moBase = New Class3
End Sub

Public Function Base() As Class3
    'Attribute Item.VB_UserMemId = 0
    Set Base = moBase
End Function


Public Function Bar() As String
    Bar = "Class2.Bar:" & 42
End Function

Public Function Common() As String
    Common = "Class2.Common"
End Function

Class3 Listing

Option Explicit

Public Function Baz() As String
    Baz = "Class3.Baz:" & -5
End Function

Public Function Common() As String
    Common = "Class3.Common"
End Function

Test Module

So hopefully you have imported the above classes and edited Class1 and Class2 necessarily to effect the default member trick. So now one can see how this works using the test code below. One can access a class's base class simply by adding .Base qualifier but we can tighten the syntax to just a pair of round brackets using the default member trick.

Option Explicit

Sub Test()
    Dim oClass1 As Class1
    Set oClass1 = New Class1

    '* without using default member trick
    '* access common method method
    Debug.Print oClass1.Common              '* prints Class1.Common
    Debug.Print oClass1.Base.Common         '* prints Class2.Common
    Debug.Print oClass1.Base.Base.Common    '* prints Class3.Common

    '* using default member trick (Attribute Item.VB_UserMemId = 0)
    '* access common method method
    Debug.Print oClass1.Common      '* prints Class1.Common
    Debug.Print oClass1().Common    '* prints Class2.Common
    Debug.Print oClass1()().Common  '* prints Class3.Common

    '* access a base class's unique method (using default member trick)
    Debug.Print oClass1.Foo      '* prints Class1.Foo:23
    Debug.Print oClass1().Bar    '* prints Class2.Bar:42
    Debug.Print oClass1()().Baz  '* prints Class3.Baz:-5

End Sub

No need for Virtual or Overides keywords

Of note is the test code above that calls the Common method. So you see how without the virtual and overrides keywords found in other languages we can in fact easily determine which implementation of Common in the 'inheritance' chain to call by changing the number of bracket pairs.

Final Thoughts

I felt the need to post this because whilst investigating COM Type libraries, I thought I had found a secret way to aggregate a VBA class. (Aggregation is COM's reuse feature.) That turned our to be a mirage (details to follow in a separate post, maybe). I wanted somewhere on this blog to demonstrate how VBA developers can use something very conceptually close to inheritance in their designs.

VBA - JSON - REST APIs - Atomic vs Document-Driven

Very interesting article, Replace RESTful APIs with JSON-Pure saying that REST Apis as strictly described by Roy Fielding are probably not a good idea. Definitely worth a read and has influenced my design.

I want to write a web service that takes information scanned for a VBA project and uploads it to a web server for analysis. Initially, I had thought I'd make a network call for each method I'd find but then that adds up to tons of ntwork calls, also I'd have to expose my URLs to the user and finally I'd have to force fit my data to a REST url paradigm which I found quite challenging. This initial approach could be described as an atomic approach making many individual network calls. An alternative approach is to build up a whole document and post this document all in one network call.

In case you're interested the code that was used as the subject of the experiment is available here, Creating an SVG file with VBA .

So the following is a program that will use the Microsoft Visual Basic for Applications Extensibility 5.3 (VBIDE) library to scan a VBA project attached a workbook for classes and modules and procedures found therein.

The code uses simple string concatenation to build the JSON document but still uses the ScriptControl to debug malformed JSON.

The resultant JSON document is found below as well as the list of atomic calls for comparison. I'm opting for document driven approach for now on.

Option Explicit
Option Private Module

'* Tools->References
' MSScriptControl  Microsoft Script Control 1.0                        C:\Windows\SysWow64\msscript.ocx
' MSXML2           Microsoft XML, v6.0                                 C:\Windows\SysWOW64\msxml6.dll
' Scripting        Microsoft Scripting Runtime                         C:\Windows\SysWOW64\scrrun.dll
' VBIDE            Microsoft Visual Basic for App's Extensibility 5.3  C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB

'******************************************************************************
'* This block implements the debug boolean (plus a default)
Private Const mbDEBUGJSONDEFAULT As Boolean = True
Private mvDebugJSON As Variant
Private Property Let DebugJSON(ByVal bRHS As Boolean)
    mvDebugJSON = bRHS
End Property
Private Property Get DebugJSON() As Boolean
    If IsEmpty(mvDebugJSON) Then mvDebugJSON = mbDEBUGJSONDEFAULT
    DebugJSON = mvDebugJSON
End Property
'******************************************************************************


Private Sub TestConvertArtefactsToModellerJSON()
    '*
    '* Main entry point, tests the code and shows the JSON parsed into an object
    '* of course one does not have to parse it here, one can just pass JSON string on to a webservice
    '*
    
    Dim wb As Excel.Workbook
    Set wb = Workbooks.Item("SVG.xlsm") '<--- put your own workbook in here!
    
    DebugJSON = False  '* this controls interim parsing, useful for debugging the document's components
    
    Dim sJSON As String
    sJSON = ReadComponentsWithVBIDE(wb)

    Debug.Print sJSON

    Dim objParsed As Object
    Set objParsed = SC.Run("JSON_parse", sJSON)
    
    Stop '* inspect objParsed in Locals Window
    
End Sub

Private Function ReadComponentsWithVBIDE(ByVal wb As Excel.Workbook) As String
    '*
    '* This function scans through all the classes and modules and reports on procedures found therein
    '*

    If wb Is Nothing Then Err.Raise vbObjectError, , "#Null wb!"
    
    Dim vbp As VBIDE.VBProject
    Set vbp = wb.VBProject
    
    Dim sJSON As String
    sJSON = VBA.Replace("{'projectName':'%foo%'", "%foo%", vbp.Name)
    
    Dim sJSONClasses As String
    sJSONClasses = "["
    
    Dim sJSONModules As String
    sJSONModules = "["
    
    Dim vbcLoop As VBIDE.VBComponent
    For Each vbcLoop In vbp.VBComponents
        
        If vbcLoop.Type = vbext_ct_ClassModule Or vbcLoop.Type = vbext_ct_StdModule Then
        
            Dim sJSONComponent As String
            sJSONComponent = ScanComponent(vbcLoop)
            
            Select Case vbcLoop.Type
            
            Case vbext_ct_ClassModule:
                sJSONClasses = sJSONClasses & VBA.IIf(Len(sJSONClasses) > 1, ",", "") & sJSONComponent
                
            Case vbext_ct_StdModule:
                sJSONModules = sJSONModules & VBA.IIf(Len(sJSONModules) > 1, ",", "") & sJSONComponent
            End Select

        End If
    Next
    
    sJSONClasses = sJSONClasses & "]"
    sJSONModules = sJSONModules & "]"
    
    Call ParseAndStringify(sJSONClasses)
    Call ParseAndStringify(sJSONModules)
    
    sJSON = sJSON & ",'classes':" & sJSONClasses
    sJSON = sJSON & ",'modules':" & sJSONModules
    sJSON = sJSON & "}"
        
    ReadComponentsWithVBIDE = ParseAndStringify(sJSON)
    
End Function

Private Function ScanComponent(ByVal vbc As VBIDE.VBComponent) As String
    '*
    '* This function will scan the source code of a component and report on any procedures found therein
    '*
    Dim dicProcs As Scripting.Dictionary
    Set dicProcs = New Scripting.Dictionary
    
    Dim dotnetlistProcs As Object
    Set dotnetlistProcs = VBA.CreateObject("System.Collections.ArrayList") '* need for sorting
    
    Dim cm As VBIDE.CodeModule
    Set cm = vbc.CodeModule
    
    Dim lLineLoop As Long
    For lLineLoop = 1 To cm.CountOfLines
        Dim sProc As String
        
        Dim eProcKind As VBIDE.vbext_ProcKind
        
        sProc = cm.ProcOfLine(lLineLoop, eProcKind)
        
        If Not dicProcs.Exists(sProc) Then
            dicProcs.Add sProc, VBA.Switch(eProcKind = vbext_pk_Get, "get", eProcKind = vbext_pk_Let, "let", _
                                           eProcKind = vbext_pk_Set, "set", eProcKind = vbext_pk_Proc, "proc")
        End If
        
        If Not dotnetlistProcs.contains(sProc) Then
            dotnetlistProcs.Add sProc
        End If
        
    Next lLineLoop
    
    dotnetlistProcs.Sort
    
    Dim sJSON As String
    sJSON = VBA.Replace("{'compName':'%foo%','procs':[", "%foo%", vbc.Name)
    
    Dim l As Long
    For l = 0 To dotnetlistProcs.Count - 1
        sJSON = sJSON & "{'procName':" & "'" & VBA.IIf(Len(dotnetlistProcs.Item(l)) = 0, "(Declarations)", dotnetlistProcs.Item(l)) & "'," 
        sJSON = sJSON & "'procKind':" & "'" & dicProcs.Item(dotnetlistProcs.Item(l)) & "'}" & VBA.IIf(l <> dotnetlistProcs.Count - 1, ",", "")
        
    Next l
    
    sJSON = sJSON & "]}"
    
    ScanComponent = ParseAndStringify(sJSON)

End Function

Private Function ParseAndStringify(ByVal sJSON As String) As String
    '*
    '* this function callable by any code that concatenates JSON will parse and restringify (to reformat) if the module level debug
    '* flag is set to True
    '*
    If DebugJSON Then

        Dim oSC As ScriptControl
        Set oSC = SC
        
        Dim objParsed As Object
        Set objParsed = oSC.Run("JSON_parse", VBA.Replace(sJSON, "'", """")) '* JSON strictly has double quotes not single quotes
        
        Dim sReStringified As String
        ParseAndStringify = oSC.Run("JSON_stringify", objParsed)
        Debug.Print ParseAndStringify
    Else
        ParseAndStringify = VBA.Replace(sJSON, "'", """")
    End If

End Function

Private Function SC() As ScriptControl
    '*
    '* This ScriptControl hosts javascript fragments, some added here, some downloaded from web
    '*
    Static soSC As ScriptControl
    If soSC Is Nothing Then

        Set soSC = New ScriptControl
        soSC.Language = "JScript"

        soSC.AddCode GetJavaScriptLibrary("https://raw.githubusercontent.com/douglascrockford/JSON-js/master/json2.js")
        soSC.AddCode "function JSON_stringify(jsonObj) { return JSON.stringify(jsonObj); } "
        soSC.AddCode "function JSON_parse(sJson) { return JSON.parse(sJson); } "
        soSC.AddCode "function deleteValueByKey(obj,keyName) { delete obj[keyName]; } "
        soSC.AddCode "function setValueByKey(obj,keyName, newValue) { obj[keyName]=newValue; } "
        soSC.AddCode "function enumKeysToMsDict(jsonObj,msDict) { for (var i in jsonObj) { msDict.Add(i,0); }  } "
        

    End If
    Set SC = soSC
End Function

Private Function GetJavaScriptLibrary(ByVal sURL As String) As String
    '*
    '* This assists the ScriptControl to download javascript library
    '*
    Dim xHTTPRequest As MSXML2.XMLHTTP60
    Set xHTTPRequest = New MSXML2.XMLHTTP60
    xHTTPRequest.Open "GET", sURL, False
    xHTTPRequest.send
    GetJavaScriptLibrary = xHTTPRequest.responseText

End Function

So the output of the program is a JSON string, if I pretty print this with the help of https://jsonformatter.curiousconcept.com/ .

 
{  
   "projectName":"SVGHelper",
   "classes":[  
      {  
         "compName":"Point",
         "procs":[  
            {  
               "procName":"(Declarations)",
               "procKind":"proc"
            },
            {  
               "procName":"SetPoint",
               "procKind":"proc"
            },
            {  
               "procName":"x",
               "procKind":"get"
            },
            {  
               "procName":"y",
               "procKind":"get"
            }
         ]
      },
      {  
         "compName":"Points",
         "procs":[  
            {  
               "procName":"(Declarations)",
               "procKind":"proc"
            },
            {  
               "procName":"AddPoint",
               "procKind":"proc"
            },
            {  
               "procName":"Class_Initialize",
               "procKind":"proc"
            },
            {  
               "procName":"Count",
               "procKind":"proc"
            },
            {  
               "procName":"CreatePoint",
               "procKind":"proc"
            },
            {  
               "procName":"Item",
               "procKind":"proc"
            },
            {  
               "procName":"LastPoint",
               "procKind":"proc"
            }
         ]
      },
      {  
         "compName":"SVGPath",
         "procs":[  
            {  
               "procName":"(Declarations)",
               "procKind":"proc"
            },
            {  
               "procName":"AddPoint",
               "procKind":"proc"
            },
            {  
               "procName":"Class_Initialize",
               "procKind":"proc"
            },
            {  
               "procName":"ClosePath",
               "procKind":"proc"
            },
            {  
               "procName":"D_Attribute",
               "procKind":"proc"
            },
            {  
               "procName":"ReflectInBothXAndY",
               "procKind":"proc"
            },
            {  
               "procName":"ReflectInX",
               "procKind":"proc"
            },
            {  
               "procName":"ReflectInY",
               "procKind":"proc"
            },
            {  
               "procName":"SetMove",
               "procKind":"proc"
            }
         ]
      },
      {  
         "compName":"UnionJack",
         "procs":[  
            {  
               "procName":"(Declarations)",
               "procKind":"proc"
            },
            {  
               "procName":"BlueTriangle",
               "procKind":"proc"
            },
            {  
               "procName":"Class_Initialize",
               "procKind":"proc"
            },
            {  
               "procName":"EnglishCross",
               "procKind":"proc"
            },
            {  
               "procName":"MyLargerBlueTriangle",
               "procKind":"proc"
            },
            {  
               "procName":"MySmallerBlueTriangle",
               "procKind":"proc"
            },
            {  
               "procName":"StPatricksCrossBlade",
               "procKind":"proc"
            }
         ]
      },
      {  
         "compName":"SVGTextMessageLengthCalculator",
         "procs":[  
            {  
               "procName":"(Declarations)",
               "procKind":"proc"
            },
            {  
               "procName":"CalculateChunks",
               "procKind":"proc"
            },
            {  
               "procName":"Class_Initialize",
               "procKind":"proc"
            },
            {  
               "procName":"CycleThroughTextMessage",
               "procKind":"proc"
            },
            {  
               "procName":"Initialise",
               "procKind":"proc"
            },
            {  
               "procName":"NavigateToTextMessageAndMeasureWidth",
               "procKind":"proc"
            },
            {  
               "procName":"Terminate",
               "procKind":"proc"
            },
            {  
               "procName":"WriteSVGTextFile",
               "procKind":"proc"
            }
         ]
      },
      {  
         "compName":"SVGTextMessageHeightCalculator",
         "procs":[  
            {  
               "procName":"(Declarations)",
               "procKind":"proc"
            },
            {  
               "procName":"ComputeHeight",
               "procKind":"proc"
            }
         ]
      }
   ],
   "modules":[  
      {  
         "compName":"modUnionJack",
         "procs":[  
            {  
               "procName":"(Declarations)",
               "procKind":"proc"
            },
            {  
               "procName":"CreateFromScratch",
               "procKind":"proc"
            }
         ]
      },
      {  
         "compName":"tstSVGTextMessageCal",
         "procs":[  
            {  
               "procName":"(Declarations)",
               "procKind":"proc"
            },
            {  
               "procName":"TestHeight",
               "procKind":"proc"
            },
            {  
               "procName":"TestLengthCalculator",
               "procKind":"proc"
            }
         ]
      },
      {  
         "compName":"Module1",
         "procs":[  

         ]
      }
   ]
}

The previous iteration of code took each class method and converted into to a REST url but this means (i) tons more network calls; (ii) exposing URLs ; (iii) forcing into REST url paradigm. (Code not given).

http://localhost:1337/VBAModeller/SVGHelper/Classes/Point/
http://localhost:1337/VBAModeller/SVGHelper/Classes/Point/SetPoint
http://localhost:1337/VBAModeller/SVGHelper/Classes/Point/x_propget
http://localhost:1337/VBAModeller/SVGHelper/Classes/Point/y_propget
http://localhost:1337/VBAModeller/SVGHelper/Classes/Points/
http://localhost:1337/VBAModeller/SVGHelper/Classes/Points/AddPoint
http://localhost:1337/VBAModeller/SVGHelper/Classes/Points/Class_Initialize
http://localhost:1337/VBAModeller/SVGHelper/Classes/Points/Count
http://localhost:1337/VBAModeller/SVGHelper/Classes/Points/CreatePoint
http://localhost:1337/VBAModeller/SVGHelper/Classes/Points/Item
http://localhost:1337/VBAModeller/SVGHelper/Classes/Points/LastPoint
http://localhost:1337/VBAModeller/SVGHelper/Classes/SVGPath/
http://localhost:1337/VBAModeller/SVGHelper/Classes/SVGPath/AddPoint
http://localhost:1337/VBAModeller/SVGHelper/Classes/SVGPath/Class_Initialize
http://localhost:1337/VBAModeller/SVGHelper/Classes/SVGPath/ClosePath
http://localhost:1337/VBAModeller/SVGHelper/Classes/SVGPath/D_Attribute
http://localhost:1337/VBAModeller/SVGHelper/Classes/SVGPath/ReflectInBothXAndY
http://localhost:1337/VBAModeller/SVGHelper/Classes/SVGPath/ReflectInX
http://localhost:1337/VBAModeller/SVGHelper/Classes/SVGPath/ReflectInY
http://localhost:1337/VBAModeller/SVGHelper/Classes/SVGPath/SetMove
http://localhost:1337/VBAModeller/SVGHelper/Classes/UnionJack/
http://localhost:1337/VBAModeller/SVGHelper/Classes/UnionJack/BlueTriangle
http://localhost:1337/VBAModeller/SVGHelper/Classes/UnionJack/Class_Initialize
http://localhost:1337/VBAModeller/SVGHelper/Classes/UnionJack/EnglishCross
http://localhost:1337/VBAModeller/SVGHelper/Classes/UnionJack/MyLargerBlueTriangle
http://localhost:1337/VBAModeller/SVGHelper/Classes/UnionJack/MySmallerBlueTriangle
http://localhost:1337/VBAModeller/SVGHelper/Classes/UnionJack/StPatricksCrossBlade
http://localhost:1337/VBAModeller/SVGHelper/Classes/SVGTextMessageLengthCalculator/
http://localhost:1337/VBAModeller/SVGHelper/Classes/SVGTextMessageLengthCalculator/CalculateChunks
http://localhost:1337/VBAModeller/SVGHelper/Classes/SVGTextMessageLengthCalculator/Class_Initialize
http://localhost:1337/VBAModeller/SVGHelper/Classes/SVGTextMessageLengthCalculator/CycleThroughTextMessage
http://localhost:1337/VBAModeller/SVGHelper/Classes/SVGTextMessageLengthCalculator/Initialise
http://localhost:1337/VBAModeller/SVGHelper/Classes/SVGTextMessageLengthCalculator/NavigateToTextMessageAndMeasureWidth
http://localhost:1337/VBAModeller/SVGHelper/Classes/SVGTextMessageLengthCalculator/Terminate
http://localhost:1337/VBAModeller/SVGHelper/Classes/SVGTextMessageLengthCalculator/WriteSVGTextFile
http://localhost:1337/VBAModeller/SVGHelper/Classes/SVGTextMessageHeightCalculator/
http://localhost:1337/VBAModeller/SVGHelper/Classes/SVGTextMessageHeightCalculator/ComputeHeight
http://localhost:1337/VBAModeller/SVGHelper/Modules/modUnionJack/
http://localhost:1337/VBAModeller/SVGHelper/Modules/modUnionJack/CreateFromScratch
http://localhost:1337/VBAModeller/SVGHelper/Modules/tstSVGTextMessageCal/
http://localhost:1337/VBAModeller/SVGHelper/Modules/tstSVGTextMessageCal/TestHeight
http://localhost:1337/VBAModeller/SVGHelper/Modules/tstSVGTextMessageCal/TestLengthCalculator

Chrome Extension - Stop Stray CORS requests

If I type in a web address such as the British Newspaper theguardian.com then I might naively expect all resources to be delivered from that domain name. But the modern web page has all sorts of cross network calls to web analytics and constant delivery networks (CDNs). Web analytics are a fact of modern life; they help firms reach their customers with better targeted adverts. No more scatter-gun adverts, we can now have pertinent products pitched to us individually. This helps allocative efficiency which is a good thing.

If web analytics were restricted to economic transactions then I'm confident there would be no problem. Sadly, some web analytics have been put to political use which is naughty. How does one opt out of a naughty analytics provider?

Use Hosts file to Block WebAnalytics

In the past I have altered my computer's hosts file at C:\Windows\System32\drivers\etc\hosts so a name resolves to loopback interface 127.0.0.1 meaning data destined for an address never leaves your computer. But this is like a sledgehammer to crack a nut.

Chrome Extension CORS filter

The precise technical term for cross network calls is Cross-origin resource sharing (CORS). CORS requires an exchange and interaction between (browser) client and (web) server.

On the server side, by default web servers disallow CORS and programmers have to actively change their code to permit CORS requests and actually even on this blog you'll find an example enabling CORS.

But the loopholes opened on the server side can be closed on the client side.

On the client side, we can write a Chrome Extension to disable CORS requests. I have given Chrome Extension examples before on this blog. This post's extension is slightly different in that it runs as a background script instead of a context script.

So in our example we are going block requests to Facebook domains because at the time of writing they are 'on the naughty step', being criticized by a British Parliamentary Oversight committee. U.S. Congressional oversight committees' reports are currently pending. But the code could be tweaked to apply to all manner of naughtiness.

manifest.json

Here is the manifest.json file. Create a directory, I called mine N:\CORS Chrome Extension\ and copy this there. This is a standard manifest file, it asks for permissions to block web requests.

  {
    "name": "Cross Origin Filter",
    "version": "0.0.6",
    "description": "Helps you stop stray CORS requests.",
    "permissions": [
      "webRequest",
      "webRequestBlocking",
      "*://*/*"
    ],
    "background": {
      "scripts": [
        "bgp.js"
      ],
      "persistent": true
    },
    "manifest_version": 2
  }

bgp.js

Below is the background page script, I called mine bgp.js (it must match entry in manifest.json) and saved this again in folder N:\CORS Chrome Extension\

The code adds a listener to the event onBeforeSendHeaders but whilst other events are available we need to scan through the request headers looking for the Referer so we can establish if the request is cross domain.

The code parses URLs using the URL object ;we only need the hostnames e.g. www.theguardian.com, www.facebook.com so we throw away the parameter string. Once we have the hostnames we can compare them against a list of domain names to block. There are two list matching sections, one compares exactly and the other compares the tail of the domain name.

If a domain matches one we want to block then we create a blockingResponse object and set its cancel property to true. This cancels the webrequest. We print to the console when we've blocked a domain.

chrome.webRequest.onBeforeSendHeaders.addListener(function (details) {
  
  var myVars = {};
  myVars.urlsPresent = false;

  try {
    myVars.requestURL = (new URL(details.url)).hostname;
    for (var i = 0, l = details.requestHeaders.length; i < l; ++i) {
      if (details.requestHeaders[i].name == 'Referer') {
        referer = details.requestHeaders[i].value;
        myVars.refererURL = (new URL(referer)).hostname;
        myVars.crossOrigin = (myVars.refererURL !== myVars.requestURL);
        myVars.urlsPresent = true;
        break;
      }
    }
  }
  catch (err) {
    console.log("Error whilst determining URLs, err.message: " + err.message);
  }

  if (myVars.urlsPresent === true) {
    try {
      myVars.block = false;

      if (myVars.crossOrigin === true) {

        {
          var aBlockCrossOriginEndsWithList = [".fbcdn.net"];
          for (var i = 0, l = aBlockCrossOriginEndsWithList.length; i < l; ++i) {
            if (myVars.requestURL.endsWith( aBlockCrossOriginEndsWithList[i])) {
              //debugger;
              myVars.block = true;
              break;
            }
          }
        }

        {
          var aBlockCrossOriginList = ["connect.facebook.net", "www.facebook.com"];
          for (var i = 0, l = aBlockCrossOriginList.length; i < l; ++i) {
            if (aBlockCrossOriginList[i] == myVars.requestURL) {
              myVars.block = true;
            }
          }
        }


      }
    }
    catch (err) {
      console.log("Error whilst determining blocking, err.message: " + err.message);
    }
  }

  if (myVars.block === true) {
    try {
      console.log("CORS Filter v.0.0.6, blocking " + myVars.requestURL + " from " + myVars.refererURL);
      //debugger;
      blockingResponse = {};
      blockingResponse.cancel = true
      return blockingResponse;
    }
    catch (err) {
      console.log("Error whilst returning blocking response, err.message: " + err.message);
    }
  }

}, { urls: ["*://*/*"] }, ['requestHeaders', 'blocking']);

Here is the console output showing how a web page from the Guardian is having cross domain calls to Facebook blocked.

Python - Java - Nu Html Checker - Running an HTML validator on old help pages

So in previous post I showed how to use HTMLTidy to restructure old HTML help pages in that case decompiled from a help file (*.chm) but could apply to any old HTML files. To raise compliance to HTML5, it is still necessary to further triage them. In its output messages, HTMLTidy recommends validating at http://validator.w3.org/nu/ but in this post we show how one can run this logic locally by downloading the java jar that drives that web site.

The Nu Html Checker

So HTML Tidy recommends the useful web site Nu Html Checker, https://validator.w3.org/nu/#textarea but before you feel tempted to write code to script against this page be advised you can run your own copy of the Nu Html Checker from a command line so long as you have Java installed.

Install Java

Do please install Java before attempting the code below

Install Nu Html Checker

Instructions as to how to get your own copy of the tool are here. So I navigated to Nu Html Checker version 18.7.23 and downloaded vnu.jar_18.7.23.zip . When the download completed, I unzipped it and extracted contained files to a subdirectory in my Downloads folder. For later use, I defined an environment variable %vnu% to point to vnu.jar's parent folder, %userprofile%\Downloads\vnu.jar_18.7.23\dist . A better long run place to install would be somewhere in Program Files.

Running Nu Html Checker from command line

With the environment variable %vnu% defined I can test the install is working (both java and the downloaded jar file) with ...

C:\>java -jar %vnu%\vnu.jar --version
18.7.23

You can see the Nu Html Checker version number is returned, so all is installed correctly.

Running Nu Html Checker from command line on a single file

Installation confirmed, we can confidently advance to running the tool on an HTML file, I have some files resulting from a previous post. So I will try this file

C:\>java -jar %vnu%\vnu.jar --no-langdetect --format xml %Temp%\HelpFileDecompiler\VBLR6\vblr6.hhc.tidied.html
<?xml version='1.0' encoding='utf-8'?>
<messages xmlns="http://n.validator.nu/messages/">
<error url="file:/C:/Users/Simon/AppData/Local/Temp/HelpFileDecompiler/VBLR6/vblr6.hhc.tidied.html" last-line="8" last-column="15" first-column="8">
<message>Element <code xmlns="http://www.w3.org/1999/xhtml">title</code> must not be empty.</message>
<extract>-&gt;
&lt;title&gt;<m>&lt;/title&gt;</m>
&lt;/hea</extract>
</error>

</messages>

C:\>

So we get a report. In this case one message only complaining about an empty title element; the message carries text file co-ordinates (line, column) so we can locate easily. Some of the message is itself entitized HTML and so reads a little cryptically but the other output formats are not much better.

Running Nu Html Checker from command line on a directory

Running on a whole directory created a huge massive file. I'd prefer a report file per HTML file. Fortunately we can write some Python code to do this.

Python Script to walk a folder and run Nu Html Checker on each file

If you have been reading my Python posts then the next script follows a familiar pattern. The script has a COM callable class so Excel VBA can call into it but it also stands alone and is callable by running Python from the command line. This is an Excel blog and I feel obliged to tie non VBA code back to VBA. In fact, there are two classes, I am working on a series of posts and would like to reuse the naming logic so that explains the HTMLTidiedChmFileNamer class.

The ValidatorReporter class runs the Nu Html Checker validation checker. It walks a folder as found in previous scripts. It shells a process using subprocess as in previous scripts. One thing that is new here is that we are shelling to java. Another things that is new here is that we are capturing the stderr by specifying PIPE in subprocess.run() arguments; this allows us to read the stderr stream and then we write it to a file.

import os
import subprocess
from subprocess import PIPE
import codecs


class HTMLTidiedChmFileNamer(object):
    _reg_clsid_ = "{8807D2B9-C83F-4AEB-A71D-15DBE8EFED9A}"
    _reg_progid_ = 'PythonInVBA.HTMLTidiedChmFileNamer'
    _public_methods_ = ['TidiedFilenameWin32Dict']

    def TidiedFilename(self, subdir, file):
        file2 = file.lower()
        tidiedFile = ""
        errorfile = ""
        validationErrorsFile = ""

        if ".tidied." not in file:
            if file2.endswith((".hhc", ".hhk")):
                tidiedFile = subdir + os.sep + file + ".tidied.html"
                errorfile = subdir + os.sep + file + ".tidied.errors.txt"
                validationErrorsFile = (subdir + os.sep + file +
                                        ".tidied.validationErrors.txt")

            if file2.endswith((".htm", ".html")):
                tidiedFile = (subdir + os.sep +
                              file.split('.')[0] + ".tidied.html")
                errorfile = (subdir + os.sep +
                             file.split('.')[0] + ".tidied.errors.txt")
                validationErrorsFile = (subdir + os.sep +
                                        file.split('.')[0] +
                                        ".tidied.validationErrors.txt")
        return (tidiedFile, errorfile, validationErrorsFile)


class ValidatorReporter(object):
    _reg_clsid_ = "{321F338F-75AE-460B-85A2-5C553A39CDE1}"
    _reg_progid_ = 'PythonInVBA.ValidatorReporter'
    _public_methods_ = ['ValidateBatch']

    def ValidateBatch(self, rootDir):

        if "vnu" not in os.environ:
            raise Exception(
                "vnu environment variable not defined, "
                "please define as vnu jar's parent folder")

        sVNUExe = os.path.join(os.environ["vnu"], "vnu.jar")
        FileNamer = HTMLTidiedChmFileNamer()

        for subdir, dirs, files in os.walk(rootDir):
            for file in files:
                tidiedFile, errorfile, validationErrorsFile = 
                        FileNamer.TidiedFilename(subdir, file)
                if not tidiedFile == "":
                    # https://github.com/validator/validator#user-content-usage
                    args = ['java', '-jar', sVNUExe, '--no-langdetect',
                            '--format', 'xml', tidiedFile]
                    proc = subprocess.run(args, stderr=PIPE)

                    file = codecs.open(validationErrorsFile, "w", "utf-8")
                    file.write(proc.stderr.decode("utf-8"))
                    file.close()

if __name__ == '__main__':
    print ("Registering COM servers...")
    import win32com.server.register
    win32com.server.register.UseCommandLine(ValidatorReporter)
    win32com.server.register.UseCommandLine(HTMLTidiedChmFileNamer)
    
    rootdir = os.path.join(os.environ["tmp"], 'HelpFileDecompiler', "vblr6")
    test = ValidatorReporter()
    test.ValidateBatch(rootdir)

The portion of code that registers the COM classes require administrator rights. You can comment them out and run the script from command line instead in a purely Pythonic way.

The code assumes you have a folder with HTML files in it. For me I have given the code a folder of HTML files extracted from a decompiled *.chm file and the code takes a good while.

Client VBA Code

To prove we can call this Python script from VBA here is the client code

Sub TestValidatorReporter()
    
    Dim objValidatorReporter As Object
    Set objValidatorReporter = VBA.CreateObject("PythonInVBA.ValidatorReporter")
    
    objValidatorReporter.ValidateBatch Environ$("tmp") & "\HelpFileDecompiler\VBLR6\"

End Sub

Final Thoughts

For me, the resultant output is huge and will take time to comb through but it looks like I'll need to load HTML files into Xml parsers and rearrange attributes etc. More Python code to come in this series. So look out for that.

Python - HTML Tidy - Script to restructure old help pages

So previously, I gave a Python script that decompiles a compiled help file (*.chm) into its constituent HTML help pages. Many of the resultant pages were very dated, poorly structured and had absolutely no hope of being well-formed enough to be parsable by an Xml parser (HTML 3.2 is to blame). I did write some VBA code to rewrite the files some time ago but I have found a much better technology called HTMLTidy. I came across HTMLTidy during my Python travels because there is a Python veneer library but actually I found that simple shelling a subprocess to be a much better approach.

The script below follows on from help file (*.chm) decompiler and it assumes that script has been run and has deposited files into a subdirectory of the Temp folder. This script also will form part of an overall workflow/pipeline.

Install HTMLTidy

Do please install HTMLTidy before attempting to run the code below. And set an environment variable %HTMLTidy% to point to the executable's parent folder. My installation is still in the Downloads folder (as determined by my browser) which shows how unfussy the install is (good).

Returning Tuples To VBA

As usual, the code is callable from Excel VBA by virtue of its COM registration but there is a slight problem and that is intrinsic Python types such as tuples do not return to VBA correctly. Attempting to return a tuple to VBA only returns the first element and not the whole list. I am happy to report that copying the values from a tuple into a Scripting.Dictionary (COM Type Library: Microsoft Scripting Runtime) and returning the dictionary to VBA solves the problem.

The Python pattern of returning a tuple is lovely and I would like to use it without restriction but if I want to make a method callable from VBA then I need to ship a second method that converts tuple to Scripting.Dictionary. I imagined wanting to do this in so many scenarios that it felt appropriate to write a little helper class...

class Win32DictConverter(object):
    def ConvertTupleToWin32Dict(self, tup):
        import win32com.client
        win32dict = win32com.client.Dispatch("Scripting.Dictionary")

        n=0
        for x in tup:
            win32dict.Add(n,x)
            n=n+1
        return  win32dict

And here is a usage example taken from this post's script.

    def TidiedFilenameWin32Dict(self, subdir, file):
        return  Win32DictConverter().ConvertTupleToWin32Dict(self.TidiedFilename(subdir, file))

Other than this little trick the rest of the code here is straightforward.

Code Walkthrough

So the code below essentially shell's to HTML Tidy in a manner similar to previous posts. [I have not used the Python veneer library to HTMLTidy as it gave counter-intuitive (to me at least) defaults.]

The only design decision to highlight is that I have broken out the code for the renaming of files into separate class, HTMLTidiedChmFileNamer, as I will probably need to call the logic therein from a later script. This is because this is meant to be part of a workflow/pipeline application.

You can see the code passing arguments to HTMLTidy from a reference of potential arguments see http://tidy.sourceforge.net/docs/quickref.html There are plenty to choose from

The key class only ships with one method, TidyBatch(), which takes a directory; this directory is recursively walked (a nice feature in Python) and each file that meets the naming rules will be tidied.

This script is part of a larger pipeline/workflow application which will decompile compiled help files (*.chm), tidy them and do further triage so meet the HTML5 standard and become convertible to ebooks version 3 (which is strict about HTML5). So we need some logic to handle compiled help file artefacts such as content (*.hhc) files and index (*.hhk) files. I will probably need to call that logic later so I put it in a class of its own, HTMLTidiedChmFileNamer.

import subprocess
import os
import os.path


class HTMLTidiedChmFileNamer(object):
    _reg_clsid_ = "{8807D2B9-C83F-4AEB-A71D-15DBE8EFED9A}"
    _reg_progid_ = 'PythonInVBA.HTMLTidiedChmFileNamer'
    _public_methods_ = ['TidiedFilenameWin32Dict']

    def TidiedFilename(self, subdir, file):
        file2 = file.lower()
        tidiedFile = ""
        errorfile = ""

        if ".tidied." not in file:
            if file2.endswith((".hhc", ".hhk")):
                tidiedFile = subdir + os.sep + file + ".tidied.html"
                errorfile = subdir + os.sep + file + ".tidied.errors.txt"

            if file2.endswith((".htm", ".html")):
                tidiedFile = (subdir + os.sep +
                              file.split('.')[0] + ".tidied.html")
                errorfile = (subdir + os.sep +
                             file.split('.')[0] + ".tidied.errors.txt")
        return (tidiedFile, errorfile)

    def TidiedFilenameWin32Dict(self, subdir, file):
        return Win32DictConverter().ConvertTupleToWin32Dict(
            self.TidiedFilename(subdir, file))


class Win32DictConverter(object):
    def ConvertTupleToWin32Dict(self, tup):
        import win32com.client
        win32dict = win32com.client.Dispatch("Scripting.Dictionary")

        n = 0
        for x in tup:
            win32dict.Add(n, x)
            n = n + 1
        return win32dict


class HTMLTidyChmFiles(object):
    _reg_clsid_ = "{20C361FF-1826-4673-A30D-FABA87FF7910}"
    _reg_progid_ = 'PythonInVBA.HTMLTidyChmFiles'
    _public_methods_ = ['TidyBatch']

    def TidyBatch(self, rootDir):

        if "HTMLTidy" not in os.environ:
            raise Exception(
                "HTMLTidy environment variable not defined, "
                "please define as HTMLTidy's bin folder")

        sHTMLTidyExe = os.path.join(os.environ["HTMLTidy"], "tidy.exe")
        FileNamer = HTMLTidiedChmFileNamer()

        for subdir, dirs, files in os.walk(rootDir):
            for file in files:
                tidiedFile, errorfile = FileNamer.TidiedFilename(subdir, file)
                fullPath = os.path.join(subdir, file)
                if not tidiedFile == "":
                    # http://tidy.sourceforge.net/docs/quickref.html
                    subprocess.run([sHTMLTidyExe, '-output', tidiedFile,
                                    '--doctype', 'html5', '--clean', 'yes',
                                    '--error-file', errorfile, fullPath])


if __name__ == '__main__':
    print ("Registering COM servers...")
    import win32com.server.register
    win32com.server.register.UseCommandLine(HTMLTidyChmFiles)
    win32com.server.register.UseCommandLine(HTMLTidiedChmFileNamer)

    rootdir = os.path.join(os.environ["tmp"], 'HelpFileDecompiler', "vblr6")
    test = HTMLTidyChmFiles()
    test.TidyBatch(rootdir)

The above script needs running at least once with administrator rights in order to register the COM classes. Once that is run you can call from VBA with the code below. The main() function also runs a test.

VBA Client Code

So as this is an Excel blog I should show you some client VBA code. This helped testing. Also, I want to demonstrate how it is fine to write code in Python (and other languages) and make them callable from VBA, this helps to expand the horizons of the VBA developer.

Sub TestHTMLTidyChmFiles()
    
    '* assumes vblr6.chm has been decompiled previously (TestHelpFileDecompiler)
    
    Dim objHTMLTidyChmFiles As Object
    Set objHTMLTidyChmFiles = VBA.CreateObject("PythonInVBA.HTMLTidyChmFiles")
    
    Call objHTMLTidyChmFiles.TidyBatch(Environ$("tmp") & "\HelpFileDecompiler\VBLR6\")
    
End Sub


Sub TestHTMLTidiedChmFileNamer()
    
    Dim objFileNamer As Object
    Set objFileNamer = VBA.CreateObject("PythonInVBA.HTMLTidiedChmFileNamer")
    
    Dim dictResults As Scripting.Dictionary
    Set dictResults = objFileNamer.TidiedFilenameWin32Dict(Environ$("tmp") & "\HelpFileDecompiler\VBLR6\", "vblr6.hhc")
    
    Dim vRet As Variant
    vRet = dictResults.Items
    
    Debug.Print vRet(0)
    Debug.Print vRet(1)
    'Stop

End Sub

Final Thoughts

It is with joy that I found HTMLTidy restructures the mal-formed HTML 3.2 files buried in some *.chm files. There were some howlers such as duplicate opening <BODY>l tags etc and I am glad that the resulting files can now be further triaged by loading into an Xml parser. This is necessary because there is more work to be done to get these files up to HTML5 standard.

HTMLTidy does the restructuring for me and no doubt if I looked long enough in the documentation some command line options could help but the next task is to further validate the files to give a schedule of further triage options. HTMLTidy in its output recommends an HTML validator and I will look at that next.

I thoroughly recommend HTMLTidy over any other known VBA compatible solution for mal-formed HTML files (yes even HTML Agility pack!)

Python - HTML - pytidylib does not install HTML Tidy

So last post I wrote Python class to decompile a *.chm compiled help file. Found within is what looks like HTML 3.2 that be should upgraded to either XHTML or HTML5. I had written some VBA code to do this but since Python month on this blog I am keen to find out what a Python developer would do. They would (I should imagine) use the library https://pypi.org/project/pytidylib/ which wraps the venerable HTML Tidy.

pip install pytidylib does not install HTML Tidy

So one installs pytidylib from a command window with admin rights using

pip install pytidylib

C:\Users\Simon\source\repos\foo\bar>pip install pytidylib
Collecting pytidylib
  Downloading https://files.pythonhosted.org/packages/2d/5e/4d2b5e2d443d56f444e2a3618eb6d044c97d14bf47cab0028872c0a468e0/pytidylib-0.3.2.tar.gz (87kB)
    100% |████████████████████████████████| 92kB 1.4MB/s
Installing collected packages: pytidylib
  Running setup.py install for pytidylib ... done
Successfully installed pytidylib-0.3.2

And Using Visual Studio I run a small example program to test the install

from tidylib import tidy_document
document, errors = tidy_document('''<p>fõo <img src="bar.jpg">''',
options={'numeric-entities':1})
print (document)
print (errors)

But unfortunately it complains of not being able to find libtidy which indicates HTML Tidy is not installed for you.

Here is the stack trace

OSError
  Message=Could not load libtidy using any of these names: libtidy,libtidy.so,libtidy-0.99.so.0,cygtidy-0-99-0,tidylib,libtidy.dylib,tidy
  StackTrace:
C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python36_64\lib\site-packages\tidylib\tidy.py:99 in Tidy.__init__
C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python36_64\lib\site-packages\tidylib\tidy.py:234 in get_module_tidy
C:\Program Files (x86)\Microsoft Visual Studio\Shared\Python36_64\lib\site-packages\tidylib\tidy.py:222 in tidy_document
C:\Users\Simon\source\repos\CompiledHelpToEbookPythonApp\CompiledHelpToEbookPythonApp\HtmlTidy.py:3 in 

Install HTML Tidy Binaries

It is required to install the HTML Tidy Binaries separately. I got mine from http://binaries.html-tidy.org/. Initially, I took the 32-bit edition which was a mistake and the error persisted. So I took the 64-bit edition, I downloaded tidy-5.6.0-vc14-64b.zip, extracted it and then added the extracted bin folder to my path. Don't forget to restart processes for the environment variables changes to be picked up.

After Successful Install

After successful install this is what is output from the sample program above.

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">
<html>
  <head>
    <title></title>
  </head>
  <body>
    <p>fõo <img src="bar.jpg">
  </body>
</html>

line 1 column 1 - Warning: missing <!DOCTYPE> declaration
line 1 column 1 - Warning: plain text isn't allowed in <head> elements
line 1 column 1 - Info: <head> previously mentioned
line 1 column 1 - Warning: inserting implicit <body>
line 1 column 1 - Warning: inserting missing 'title' element

Press any key to continue . . .

Python - VBA - HTML Help - COM Callable Python class runs *.chm decompiler.

I have many old *.chm compiled help files on my computer and for some reason the help viewer is bust. I am considering writing (Python) code which will upgrade these *.chm files into ebooks. The first module is given here, it will run the Microsoft HTML Help executable (hh.exe) to decompile a *.chm file into its constituent html files all within working subdirectories in the %temp% folder.

Submitting to Code Review

I might very well complete a full application and place in github. This means I need to raise my Python standards and so I have submitted this module to codereview.stackexchange.com

The HelpFileDecompiler Python class

Here is the Python code

class HelpFileDecompiler(object):
    _reg_clsid_ = "{4B388A08-8CAB-4568-AF78-47032744A368}"
    _reg_progid_ = 'PythonInVBA.HelpFileDecompiler'
    _public_methods_ = ['DecompileHelpFileMain']

    def DecompileHelpFileMain(self, sHelpFile):
        import os.path
        eg = ", e.g. 'c:\\foo.chm'"
        if not isinstance(sHelpFile, str):
            raise Exception("sHelpFile needs to be a string" + eg)
        if len(sHelpFile) == 0:
            raise Exception("sHelpFile needs to be a non-null string" + eg)
        if sHelpFile.lower()[-4:] != ".chm":
            raise Exception("sHelpFile needs to end with '.chm' " + eg)
        if not os.path.isfile(sHelpFile):
            raise Exception("sHelpFile " + sHelpFile + " not found")

        self.BuildTmpDirectory()
        sCopiedFile = self.CopyChmFileToTempAppPath(sHelpFile)
        self.DecompileHelpFile(sCopiedFile)

    def BuildTmpDirectory(self):
        import os
        import os.path
        sTempAppPath = os.path.join(os.environ['tmp'], 'HelpFileDecompiler')

        if not os.path.isdir(sTempAppPath):
            os.mkdir(sTempAppPath)

    def CopyChmFileToTempAppPath(self, sHelpFile):
        import shutil
        import os.path
        sDestFile = os.path.join(os.environ['tmp'], 'HelpFileDecompiler',
                                    os.path.basename(sHelpFile))
        shutil.copyfile(sHelpFile, sDestFile)
        return sDestFile

    def DecompileHelpFile(self, sHelpFile):
        import win32api
        import subprocess
        import os.path

        if not os.path.isfile(sHelpFile):
            raise Exception("sHelpFile " + sHelpFile + " not found")

        sDecompiledFolder = os.path.join(
            os.environ['tmp'],
            'HelpFileDecompiler',
            os.path.basename(sHelpFile).split(".")[0])

        if not os.path.isdir(sDecompiledFolder):
            os.mkdir(sDecompiledFolder)

        sHELPEXE = "C:\Windows\hh.exe"
        if not os.path.isfile(sHELPEXE):
            raise Exception("sHELPEXE " + sHELPEXE + " not found")

        # Not allowed to quote arguments to HH.EXE
        # so we take the short path to eliminate spaces
        sHelpFileShort = win32api.GetShortPathName(sHelpFile)
        sDecompiledFolderShort = win32api.GetShortPathName(sDecompiledFolder)

        # so now we can run the decompiler
        subprocess.run([sHELPEXE, '-decompile',
                        sDecompiledFolderShort, sHelpFileShort])


if __name__ == '__main__':
    print ("Registering COM servers...")
    import win32com.server.register
    win32com.server.register.UseCommandLine(HelpFileDecompiler)

    helpFile=("C:\\Program Files\\Microsoft Office 15\\root\\vfs\\"
                "ProgramFilesCommonX86\\Microsoft Shared\\VBA\\VBA7.1\\"
                "1033\\VBLR6.chm")
    test = HelpFileDecompiler()
    test.DecompileHelpFileMain(helpFile)

Here is some test VBA client code

Option Explicit

Sub Test()

    Dim obj As Object
    Set obj = VBA.CreateObject("PythonInVBA.HelpFileDecompiler")
    
    obj.DecompileHelpFileMain "C:\Program Files\Microsoft Office 15\root\vfs\" & _
              "ProgramFilesCommonX86\Microsoft Shared\VBA\VBA7.1\" & _
              "1033\VBLR6.chm"

End Sub

OLEDB - C++ - Solved: ATL's OLEDB Provider sample crashes Excel (uncaught exception from msado15.dll)

Happy Conclusion

This post initially did not reach a happy conclusion, I investigated compiling the ATL OLEDB Provider samples but reached an impasse as the ADO runtime throws an exception. I wrote up the problem as a blog post nevertheless because sometimes defeats can be as revealing as victories. I asked on StackOverflow and they came through. Especially Simon Mourier on StackOverflow solved it and I am thankful. I have not changed the main body of the original post, instead I have posted solution at the bottom here.

ADO Recordsets as a marshalling vessel

So I was pondering how to get data from Python to VBA without using COM's SAFEARRAY which can often but not always be acquired with tolist(). One alternative is to create an ADO recordset, it is possible to create an ADO recordset by creating the an Xml representation, i.e. to concatenate a correctly formatted string.

In the era of Visual Basic 5 (upon which VBA is based) code marshalled tabular data from one execution process to another (perhaps even on a different computer as part of an N-tiered scalable architecture, DNA) by transmitting a two dimensional array (SafeArray) housed in OLE Variant. In VB6, the recommended vessel for marshalling tabular became the disconnected ADO recordset. Marshalling means serializing in one process and then deserializing in another. For recordsets, the ADO run-time would do the marshalling where as for OLE Variants it was the COM run-time. They have similar speeds but an ADO recordset packs so much more functionality that one should opt for it.

ADO Recordsets come from OLE DB Providers

The vast majority of ADO recordsets are created by an OLE DB Provider such as the Microsoft SQL Server OLE DB Provider. Can we create our own OLE DB Provider? In theory yes, ATL has had for almost two decades the ability to create OLE DB Providers. I never tried until now. My attempt to get the sample working have got stuck. I'll try not to replicate the documentation, instead I will give links plus some extra info where pertinent.

The ATL Sample OLEDB Provider that Finds Files from a directory

ADO and OLEDB were meant to be an advance on the previous generation of data access technology by allowing a single object model to access not just database tables but also non-tabular data sources such as email stores.

In the case of the ATL Sample OLEDB Provider I have read enough code to say that it (when if works, if ever) actually creates a recordset where each row is a file in a given directory. This is useful as a sample because everyone has a file system and this obviates the need to install a database. It is also illustrative of a non-database source.

The sample reads files by calling API functions such as FindFirstFile.

When running the ATL OLEDB Provider wizard one can customise the name, I will choose "FindFiles".

Running Visual Studio 2017 ATL Wizards to create Find File OLEDB Provider Sample

I will try not to recreate the documentation but will give some screenshots as the official documentation does not (I suspect it is not being maintained).

Because we are creating a COM component you will need to run Visual Studio with admin rights

New Project -> Visual C++ -> ATL -> ATL Project

Click OK on the next screen without entering anything

In the solution explorer you'll find two projects, focus on the top project, FindFiles. On the FindFiles project icon or or any folder icon in the FindFiles project right-click mouse button and then select Add -> New Item . You'll get the following screen from which you should select ATL -> ATL OLEDB Provider and then click Add

The next screen is the wizard, enter FindFiles into the ShortName and (nearly) all the other fields are updated to reflect the short name. Also, add FindFiles to the ProgID, whilst not strictly necessary this will help debugging later. Click finish to commit and the wizard will write the code for you.

Save all the files and compile (don't forget you'll need admin rights).

Enumerating OLE DB Providers

I forget where I found this code (somewhere on Microsoft.com) but it enumerates all the OLE DB Providers installed, after a successful compilation FindFiles should appear

using System;
using System.Data;
using System.Data.OleDb;

namespace OldDbEnumerator
{

    class Program
    {
        static void Main()
        {
            OleDbDataReader reader = OleDbEnumerator.GetRootEnumerator();

            DisplayData(reader);

            Console.WriteLine("Press any key to continue.");
            Console.ReadKey();
        }

        static void DisplayData(OleDbDataReader reader)
        {
            while (reader.Read())
            {
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    Console.WriteLine("{0} = {1}",
                     reader.GetName(i), reader.GetValue(i));
                }
                Console.WriteLine("==================================");
            }
        }
    }
}

And in the output of that is the new Provider, your GUID will be different (it is randomly selected)

SOURCES_NAME = FindFiles Class
SOURCES_PARSENAME = {E387836C-6248-4319-92E8-BCD070844D86}
SOURCES_DESCRIPTION = FindFiles Class
SOURCES_TYPE = 1
SOURCES_ISPARENT = False
SOURCES_CLSID = {E387836C-6248-4319-92E8-BCD070844D86}

Excel VBA client code CRASHES EXCEL

Sorry for caps shouting but it is important to save your Excel session before you play with the next chunk of code. Here is some client VBA code. It crashes Excel VBA.

We'll use for debugging later so give your self a new workbook called TestClient.xlsm, in a new standard module paste the following code

Sub TestOleDbProvider()
    
    On Error GoTo ErrHand
    
    
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    
    cn.Open "Provider=FindFiles;Server=foo;Database=bar" '* this works
    
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    
    Set cmd.ActiveConnection = cn   '* this works
    
    cmd.CommandText = "*.*"   '* this works
    
    Stop
    Dim rs As ADODB.Recordset
    Set rs = cmd.Execute  '* crashes here
    
    
    
    Exit Sub
ErrHand:
    Debug.Print Err.Description & " (" & Err.Number & ")"
    'Stop

End Sub

Before you run this code, goto ThisWorkbook and enter the following helpful code which will always navigate to the above code upon workbook opening...

Private Sub Workbook_Open()
    Application.GoTo "TestOleDbProvider"
End Sub

Then save the workbook before you run TestOleDbProvider because it will crash and you will not have an opportunity to save it!

So running the above code crashes Excel, later I will show that an uncaught exception is being thrown by msado15.dll. Now this maybe to do with the fact I do not know how to yet supply a query. Peaking ahead I can show some of the C++ code (CFindFilesRowset::Execute) where it is assumes *.* if supplied an empty string

  CW2TEX<_MAX_PATH> szDir(m_strCommandText == L"" ? L"*.*" : m_strCommandText);

Time to investigate.

Debugging the OLEDB Provider with breakpoints

So we must investigate. I set the project's properties to start Excel and load a workbook called TestClient.xlsm as part of the debug properties. So on the FileFind project icon right-click and select properties (last entry on the menu) to display FindFiles Property Pages within which select the Debugging entry on the left hand side.

Running the code I get an unhandled exception and the call stack is firmly in msado15.dll..

 msado15.dll!CQuery::SetSQL(unsigned short *) Unknown Non-user code. Symbols loaded.
  msado15.dll!CQuery::SetCommandText(long,unsigned long,unsigned char,unsigned char) Unknown Non-user code. Symbols loaded.
  msado15.dll!CQuery::Execute(enum ExecuteTypeEnum,char,unsigned long,bool,unsigned long,unsigned long,long,struct tagVARIANT *,unsigned long,void *,long *,struct _ADORecordset * *) Unknown Non-user code. Symbols loaded.
  msado15.dll!CCommand::_Execute(enum ExecuteTypeEnum,char,unsigned long,bool,unsigned long,unsigned long,long,long,struct tagVARIANT *,unsigned long,void *,long *,struct _ADORecordset * *) Unknown Non-user code. Symbols loaded.
  msado15.dll!CCommand::ExecuteWithModeFlag(struct tagVARIANT *,struct tagVARIANT *,long,struct _ADORecordset * *,int) Unknown Non-user code. Symbols loaded.
  msado15.dll!CCommand::Execute(struct tagVARIANT *,struct tagVARIANT *,long,struct _ADORecordset * *) Unknown Non-user code. Symbols loaded.
  VBE7.DLL!1e813579() Unknown No symbols loaded.
  [Frames below may be incorrect and/or missing, no symbols loaded for VBE7.DLL]  Annotated Frame
  VBE7.DLL!1e7cff4b() Unknown No symbols loaded.
  VBE7.DLL!1e829d13() Unknown No symbols loaded.
  VBE7.DLL!1e82fea2() Unknown No symbols loaded.
  VBE7.DLL!1e82bcb5() Unknown No symbols loaded.
  [External Code]  Annotated Frame

And with that I am stuck. I guess I could ask Stack Overflow. I've never bothered Microsoft for support before, might do so on this occasion. I'll try a SO bounty first I think.

Update: Solution given by Simon Mourier

I am delighted to say that Simon Mourier at StackOverflow solved this by adding another interface ICommandText to the interface map.

BEGIN_COM_MAP(CFindFilesCommand)
    ...
    COM_INTERFACE_ENTRY(ICommandText) 
    ...
END_COM_MAP()

Apparently, the ADO runtime was querying for this interface and not finding it and thus calling on a null pointer. I'm surprised the ADO runtime doesn't check for null pointers but never mind. The cased is solved.

VBA - Python - OCR - Optical Character Recognition

It was Python month on this blog last month but still plenty of ideas of how to leverage the huge Python ecosystem and bring functionality to the feet of VBA Developers. In this blog I play with Optical Character Recognition (OCR) and get it callable from VBA using a COM gateway class.

Tesseract

The OCR Python library I use here is Tesseract which has a long pedigree and happily has Python bindings. But it needs some care to install properly.

Tesseract Installation

I found that using  pip install pytesseract  falsely reported success. Instead, what was necessary was the following steps...

1. Find a site with a Tesseract Windows binary installer. I found Tesseract at UB Mannheim
2. Run the Tesseract Windows binary installer. I ran https://digi.bib.uni-mannheim.de/tesseract/tesseract-ocr-setup-3.05.02-20180621.exe
3. Add the Tessearcht directory (for me 'C:\Program Files (x86)\Tesseract-OCR') to PATH environment variable
4. Close down and restart all potential client processes, Visual Studio, Excel, Command Windows (cmd.exe), Powershell, any process.

We'll also use an Image library, Pillow, to load the image file, so use  pip install pillow 

OCRBatch Com Gateway Class

I have given this pattern many times over the last month here is a Python class with enough extra code to allow it to be registered as a COM class and thus callable from VBA. Here is the code, which must be run at least once under admin privileges to enable registration (thereafter not required).

## pip install pillow      ## succeeded

## https://github.com/UB-Mannheim/tesseract/wiki
## https://digi.bib.uni-mannheim.de/tesseract/tesseract-ocr-setup-3.05.02-20180621.exe
## add C:\Program Files (x86)\Tesseract-OCR to PATH
## restart Visual Studio so it picks up changes to PATH



class OCRBatch(object):
    _reg_clsid_ = "{A7C1275F-7ABD-4AA2-90E5-462392D821DF}"
    _reg_progid_ = 'PythonInVBA.OCRBatch'
    _public_methods_ = ['RunBatch', 'RunOCR']

    def RunBatch(self, rootDir):
        import os
        for subdir, dirs, files in os.walk(rootDir):
            for file in files:
                #print os.path.join(subdir, file)
                filepath = subdir + os.sep + file

                if file.endswith(".jpeg") and file.startswith("File_"):
                    ocrFile = filepath + ".txt"
                    self.RunOCR (filepath,ocrFile)

    def RunOCR(self,imageFile, ocrFile):
        import io
        from PIL import Image
        import pytesseract

        img = Image.open(imageFile )
        text = pytesseract.image_to_string(img)

        with io.open(ocrFile,'w', encoding="utf-8") as f:
            f.write(text)
            f.close()

if __name__ == '__main__':
    print ("Registering COM servers...")
    import win32com.server.register
    win32com.server.register.UseCommandLine(OCRBatch)

    rootdir="C:\\Users\\Simon\\Downloads\\ocr"
    test = OCRBatch()
    test.RunBatch(rootdir)

The code has two methods RunBatch and RunOCR. The latter operates on a single file whilst the former operates on a folder and its subfolders. The folder is scanned for filenames that start with "File_" and end with ".jpeg" because that is the naming convention of the input device, you should change it to suit your input device.

When run on each file Tesseract will scan the Image and generate some text, the text is then saved to a file in the same directory with very similar name (simply suffixed with ".txt"). For me, the output can be quite jumbled and typically needs cleaning up in a text editor but it is better than typing in from scratch.

Test Script

Just quickly point out that I've added some test script code to the tail of the script, this is where the __main__ procedure runs the COM registration. I find this a useful place to write some test code and will probably continue this convention.

OS Walk

Again, quickly point another (smaller) Python nicety in that os.walk recursively walks a directory structure and its subfolders. In VBA one would need to write some code to do that. Here the Python ecosystem delivers another time saving.

Client VBA Code

So thanks to COM registration the client VBA is trivial...

Option Explicit

Sub Test()

    Dim obj As Object
    Set obj = VBA.CreateObject("PythonInVBA.OCRBatch")
    
    obj.RunBatch "C:\Users\Simon\Downloads\ocr"

End Sub

Node.js - Using HTTPS + CORS

So, on this blog I've given a Node.js simple web service example before but it was plain vanilla and didn't handle either secure connections with HTTPS or handle this curious protocol called CORS (see below). I remedy that here by given some working code which allows POSTing of json payloads.

So to make your Node.js web service handle https you need to use

var https = require('https');
var fs = require('fs');

var options = {
    // create the following two files beforehand with openssl
    // https://stackoverflow.com/questions/12871565/how-to-create-pem-files-for-https-web-server#answer-12907165
    key: fs.readFileSync('key.pem'),
    cert: fs.readFileSync('cert.pem')
};

where you are specifying the private key and the SSL certificate which you must have created before. Switch to your node.js source files directory so openssl.exe creates the files in the right place. A good stack overflow Q&A here shows how to create these files.

CORS Cross-Origin Resource Sharing

Ideally all files and content are delivered from one single web domain (+port). Sometimes a use case is not that simple. Sometimes a web resource needs to be accessed from a different origin. CORS allows this cross origin request. As you can imagine you are opening up a security hole; so in production think carefully about where cross-origin requests might originate and tie down as much as possible. In production, don't use wildcards like in sample code given below! A naughty person could launch a denial of service attack if you allow them! A good CORS guide is here.

Pre-flight request

Today I learnt that some client will send an HTTP OPTIONS request before sending a HTTP POST request; this is known as a "pre-flight request". If you are not using a web server framework then you will need to handle this manually by setting HTTP response headers like the following...

            // IN PRODUCTION DO NOT USE WILDCARDS!!!
            response.setHeader("Access-Control-Allow-Origin", "*");
            response.setHeader('Access-Control-Allow-Methods', 'POST, OPTIONS');
            response.setHeader("Access-Control-Allow-Headers", "Origin, X-Requested-With, content-type, Accept");

So in the above code I have not tied down Origin but you dear reader must do in production! But this is a blog sample. I have tied down the HTTP methods, only allowing POST and OPTIONS. I have also tied down the headers.

Whilst one should should tie down options as much as possible if you tie down too much your client will assume it has been refused permission and not send the follow-on POST request (in my use case). I had to debug CORS today because my POSTs were not coming through. If the preflight OPTIONS request is not implemented then yes any POST request will not follow on.

Anyway, here is a full working example. The following code runs an HTTPS service at 127.0.0.1:8000 and only allows OPTIONS and GET. Don't forget in production to further restrict the origin and not use wildcards...

'use strict';

var https = require('https');
var fs = require('fs');

var options = {
    // create the following two files beforehand with openssl
    // https://stackoverflow.com/questions/12871565/how-to-create-pem-files-for-https-web-server#answer-12907165
    key: fs.readFileSync('key.pem'),
    cert: fs.readFileSync('cert.pem')
};

const port = 8000;

console.log('nversion 0.001n');

//https://stackoverflow.com/questions/5998694/how-to-create-an-https-server-in-node-js#answer-21809393
https.createServer(options, function (request, response) {

    switch (request.method) {
        case "OPTIONS":
            // IN PRODUCTION DO NOT USE WILDCARDS!!!
            response.setHeader("Access-Control-Allow-Origin", "*");
            response.setHeader('Access-Control-Allow-Methods', 'POST, OPTIONS');
            response.setHeader("Access-Control-Allow-Headers", "Origin, X-Requested-With, content-type, Accept");
            response.end();
            break;
        case "GET":
            console.log(request.url);
            response.end('Hello Node.js Server!');
            break;
        case "POST":
            // IN PRODUCTION DO NOT USE WILDCARDS!!!
            response.setHeader("Access-Control-Allow-Origin", "*");
            response.setHeader('Access-Control-Allow-Methods', 'POST, OPTIONS');
            response.setHeader("Access-Control-Allow-Headers", "Origin, X-Requested-With, content-type, Accept");
            
            //https://nodejs.org/en/docs/guides/anatomy-of-an-http-transaction/

            let body = [];
            request.on('data', (chunk) => {
                body.push(chunk);
            }).on('end', () => {
                body = Buffer.concat(body).toString();
                // at this point, `body` has the entire request body stored in it as a string
                console.log('nbody received:nn' + body);

            });

            response.end();
            break;
    }
}).listen(port);

Excel - VBA - Parsing International Currency Amounts in VBA

VBA Currency Parsing is limited to one's own currency

So parsing currencies in ordinary VBA can work so long as one sticks to one's own currency, so the following, CCur can handle commas and minus signs and on my machine Sterling Pound symbol but trips up on dollars.

Sub Test()

    Debug.Assert VBAParseCCur("1000") = 1000
    Debug.Assert VBAParseCCur("-1000") = -1000
    Debug.Assert VBAParseCCur("-10,000") = -10000
    
    Debug.Assert VBAParseCCur("£-10,000") = -10000 '* works on my (UK) machine
    Debug.Assert VBAParseCCur("$-10,000") = -10000  '* fails on my (UK) machine

End Sub

Function VBAParseCCur(v)
    On Error Resume Next
    VBAParseCCur = CCur(v)
End Function

COM System Parsers

So typically C++ programmers have greater access to the system functions than VBA programmers. A C++ programmer can call VarParseNumFromStr to parse a number out of a string.

HRESULT VarParseNumFromStr(
  LPCOLESTR strIn,
  LCID      lcid,
  ULONG     dwFlags,
  NUMPARSE  *pnumprs,
  BYTE      *rgbDig
);

One can see that one can specify the locale id as second parameter. If you know what you want, in this case I want a currency from a string then you can chose a more specific parsing function, VarCyFromStr

HRESULT VarCyFromStr(
  LPCOLESTR strIn,
  LCID      lcid,
  ULONG     dwFlags,
  CY        *pcyOut
);

With the help of VBFormus.com expert Olaf Schmidt we can call VarCyFromStr from VBA. And now we have the ability to parse multi-currency strings, see this code.

Option Explicit


'* http://www.vbforums.com/showthread.php?762443-VB6-Tabulator-Crosstab-Class
'* https://docs.microsoft.com/en-gb/previous-versions/windows/desktop/api/oleauto/nf-oleauto-varcyfromstr
Private Declare Function VarCyFromStr& Lib "oleaut32" (ByVal sDate&, ByVal LCID&, ByVal Flags&, C As Currency)

Private Enum lcidLocaleId
    lcidEN_US = 1033    '* US
    lcidEN_EN = 2057    '* UK
    lcidFR_FR = 1036    '* Eurozone
    lcidRU = 1049       '* Russia
    lcidJA = 1041       '* Japan
End Enum

Private Enum chrwCurrencies
    chrwEuro = 8364     '* for euros
    chrwRouble = 8381   '* for roubles
    chrwYen = 165       '* for yen
End Enum

Private Function CCurLA(ByVal sAmount As String, ByVal LCID As lcidLocaleId) As Currency
    Dim HRes As Long
    
    HRes = VarCyFromStr(StrPtr(sAmount), LCID, 0, CCurLA)
    If HRes Then Err.Raise HRes
End Function

Sub TestVarCyFromStr_Yen()

    Dim sYen As String
    sYen = ChrW(chrwYen) & "-1000"
    Dim curYen As Currency
    
    curYen = CCurLA(sYen, lcidJA)
    Debug.Assert curYen = -1000
End Sub

Sub TestVarCyFromStr_Roubles()

    Dim sRoubles As String
    sRoubles = ChrW(chrwRouble) & "-1000"
    Dim curRoubles As Currency
    
    curRoubles = CCurLA(sRoubles, lcidRU)
    Debug.Assert curRoubles = -1000
End Sub

Sub TestVarCyFromStr_Euros()

    Dim sEuros As String
    sEuros = ChrW(chrwEuro) & "-1000"
    Dim curEuros As Currency
    
    curEuros = CCurLA(sEuros, lcidFR_FR)
    Debug.Assert curEuros = -1000
End Sub

Sub TestVarCyFromStr_Dollars()

    Dim sDollars As String
    sDollars = "$-10,00"
    Dim curDollars As Currency
    
    curDollars = CCurLA(sDollars, lcidEN_US)
    Debug.Assert curDollars = -1000
End Sub

Sub TestVarCyFromStr_Pounds()

    Dim sPounds As String
    sPounds = "£-10,00"
    Dim curPounds As Currency
    
    curPounds = CCurLA(sPounds, lcidEN_EN)
    Debug.Assert curPounds = -1000
End Sub

Python - VBA - Com Interop - Factory Method Pattern

Python can interop with VBA via COM. One can create a Python class and expose it to VBA for scripting, this allows VBA developers to access the massive Python ecosystem. Sometimes, if you want to build an object hierarchy then you want some classes to be only creatable via another class, this is sometimes called the Factory Method Pattern. It can frequently occur in parent-child object relationships. In this post I show how to do this in Python.

Here is the Python code which must be run at least once with admin privilege to ensure COM registration (thereafter not required).

import win32com.client


class MyParent(object):
    _reg_clsid_ = "{C61A7C6E-B657-4D55-AD36-8850B2E501AC}"
    _reg_progid_ = 'PythonInVBA.MyParent'
    _public_methods_ = ['Greet', 'GetChild']

    def __init__(self):  # Rules of Com say parameterless constructors
        self.child = win32com.client.Dispatch("PythonInVBA.MyChild")
        self.child.SetName("foo")

    def Greet(self):
        return "Hello world"

    def GetChild(self):
        return self.child


class MyChild(object):
    _reg_clsid_ = "{15DAAEE2-3A37-4DE1-9973-CCD011DF4888}"
    _reg_progid_ = 'PythonInVBA.MyChild'
    _public_methods_ = ['Initialize', 'GetName', 'SetName']

    def __init__(self):  # Rules of Com say paramerless constructors
        pass

    def GetName(self):
        return self.name

    def SetName(self, sName):
        self.name = sName

if __name__ == '__main__':
    print ("Registering COM servers...")
    import win32com.server.register
    win32com.server.register.UseCommandLine(MyParent)
    win32com.server.register.UseCommandLine(MyChild)

The trick is to create the child class with win32com.client.Dispatch("PythonInVBA.MyChild") and not the standard constructor MyChild() . My thanks to Stackoverflow user Kunif for solving this.

And so the VBA client code looks like this

Sub Test_MyParent_Returning_MyChild()
    On Error GoTo ErrHand:

    Dim objMyParent As Object
    Set objMyParent = VBA.CreateObject("PythonInVBA.MyParent")

    Dim objMyChild As Object

    Set objMyChild = objMyParent.GetChild()
    Debug.Print objMyChild.GetName  '* prints foo

    Exit Sub
ErrHand:
    Debug.Print Err.Description
End Sub