Saturday 12 August 2023

Quick program to download and parse UK Parliament Petition JSON data onto a worksheet

It's nice to pop onto my own blog and take some working code and re-apply it for a current problem. My JSON parsing posts are quite popular. In this instance I wanted to download and parse JSON data from the United Kingdom Parliamentary Petitions website.

The petition data gives two drill downs: (i) signature count by region and (ii) signature count by constituency.

The clever JSON parsing code is given to us by Douglas Crockford from his github site, you can see it being downloaded in the function SC (stands for Script Control).

Parsing returns a JSON object that is scriptable from VBA upon which we use VBA.CallByName(jsonObject, <property name>, VbGet) to drill down through the tree nodes.

If the scriptable JSON object is an array that it will support VBA's For Each .. Next syntax and so looping through is a breeze.

All the remaining code is simply plain vanilla that pastes the results on to the worksheet.

Don't forget to go to Tools->References and add the dependencies listed in the top comments or it will not compile. Also the code expects a worksheet with two sheets with codenames Sheet1 and Sheet2.

Enjoy!

Option Explicit

'* Tools->References
' MSScriptControl      Microsoft Script Control 1.0    C:\Windows\SysWOW64\msscript.ocx
' MSXML2               Microsoft XML, v6.0             C:\Windows\SysWOW64\msxml6.dll

Private Enum enuConstituencyColumns
    eCCMP = 0
    eCCName
    eCCONSCode
    eCCCountry
    eCCSigCount
    eCCFirst = eCCMP
    eCCLast = eCCSigCount
End Enum

Private Enum enuRegionColumns
    eRCName = 0
    eRCONSCode
    eRCSigCount
    eRCFirst = eRCName
    eRCLast = eRCSigCount
End Enum

Private Const clConsituencyCount As Long = 650
Private Const clRegionCount As Long = 12

Private Sub TestGetPetitionData()
    Const cl_petition_endchildfoodpoverty As Long = 554276 'no child should be going hungry  #endchildfoodpoverty
    GetPetitionData cl_petition_endchildfoodpoverty
End Sub

Private Sub GetPetitionData(ByVal lPetitionNumber As Long)
  
    Dim sPetitionJSON As String
    sPetitionJSON = RunHttpRequest("https://petition.parliament.uk/petitions/" & lPetitionNumber & ".json")
    
    Dim oSC As ScriptControl
    Set oSC = SC
    
    'Stop
    Dim objSafelyParsed As Object
    Set objSafelyParsed = SC.Run("JSON_parse", sPetitionJSON)
   
    Dim objDataAttributes As Object
    Set objDataAttributes = VBA.CallByName(VBA.CallByName(objSafelyParsed, "data", VbGet), "attributes", VbGet)
    

    
    '********** do the constituencies ***********
    Dim objConstituencySignatures As Object
    Set objConstituencySignatures = VBA.CallByName(objDataAttributes, "signatures_by_constituency", VbGet)
    
    
    Dim vConstituencies(0 To clConsituencyCount - 1, eCCFirst To eCCLast) As Variant
    
    Dim objConstituencyLoop As Object
    Dim idxConstituencyLoop As Long: idxConstituencyLoop = 0
    For Each objConstituencyLoop In objConstituencySignatures
    
        vConstituencies(idxConstituencyLoop, eCCMP) = CallByName(objConstituencyLoop, "mp", VbGet)
        vConstituencies(idxConstituencyLoop, eCCName) = CallByName(objConstituencyLoop, "name", VbGet)
        Dim sONSCode As String
        sONSCode = CallByName(objConstituencyLoop, "ons_code", VbGet)
        
        vConstituencies(idxConstituencyLoop, eCCONSCode) = sONSCode
        vConstituencies(idxConstituencyLoop, eCCCountry) = Left$(sONSCode, 1)
        
        vConstituencies(idxConstituencyLoop, eCCSigCount) = CallByName(objConstituencyLoop, "signature_count", VbGet)
        'Stop
        idxConstituencyLoop = idxConstituencyLoop + 1
    Next
    Sheet1.Cells.Clear
    WriteConstituencies Sheet1, vConstituencies
    
    
    '********** do the regions ***********
    Dim objRegionSignatures As Object
    Set objRegionSignatures = VBA.CallByName(objDataAttributes, "signatures_by_region", VbGet)
    
    Dim vRegions(0 To clRegionCount - 1, eRCFirst To eRCLast) As Variant
    Dim objRegionLoop As Object
    Dim idxRegionLoop As Long: idxRegionLoop = 0
    For Each objRegionLoop In objRegionSignatures
    
        vRegions(idxRegionLoop, eRCName) = CallByName(objRegionLoop, "name", VbGet)
        vRegions(idxRegionLoop, eRCONSCode) = CallByName(objRegionLoop, "ons_code", VbGet)
        
        vRegions(idxRegionLoop, eRCSigCount) = CallByName(objRegionLoop, "signature_count", VbGet)
    
        idxRegionLoop = idxRegionLoop + 1
    Next
    Sheet2.Cells.Clear
    WriteRegions Sheet2, vRegions
    
End Sub


Private Sub WriteConstituencies(ByVal ws As Excel.Worksheet, vConstituencies)
    Dim lVerticalCursor As Long
    lVerticalCursor = lVerticalCursor + 1
    
    ws.Cells(lVerticalCursor, eCCMP + 1) = "MP"
    ws.Cells(lVerticalCursor, eCCName + 1) = "Name"
    ws.Cells(lVerticalCursor, eCCONSCode + 1) = "ONS Code"
    ws.Cells(lVerticalCursor, eCCCountry + 1) = "Country"
    ws.Cells(lVerticalCursor, eCCSigCount + 1) = "Signature Count"
    lVerticalCursor = lVerticalCursor + 1
    
    Dim rngConstituencyPaste As Excel.Range
    Set rngConstituencyPaste = ws.Range(ws.Cells(lVerticalCursor, eCCFirst + 1), ws.Cells(lVerticalCursor + clConsituencyCount - 1, eCCLast + 1))
    
    'Debug.Assert rngConstituencyPaste.Columns.Count = 5
    'Debug.Assert rngConstituencyPaste.Rows.Count = 650
    
    rngConstituencyPaste.Value = vConstituencies
End Sub


Private Sub WriteRegions(ByVal ws As Excel.Worksheet, vRegions)
    Dim lVerticalCursor As Long
    lVerticalCursor = lVerticalCursor + 1
    
    ws.Cells(lVerticalCursor, eRCName + 1) = "Name"
    ws.Cells(lVerticalCursor, eRCONSCode + 1) = "ONS Code"
    ws.Cells(lVerticalCursor, eRCSigCount + 1) = "Signature Count"
    lVerticalCursor = lVerticalCursor + 1
    
    Dim rngRegionPaste As Excel.Range
    
    Set rngRegionPaste = ws.Range(ws.Cells(lVerticalCursor, eRCFirst + 1), ws.Cells(lVerticalCursor + clRegionCount - 1, eRCLast + 1))
    
    'Debug.Assert rngRegionPaste.Columns.Count = 3
    'Debug.Assert rngRegionPaste.Rows.Count = 12
    
    rngRegionPaste.Value = vRegions
End Sub


Private Function SC() As ScriptControl
    Static soSC As ScriptControl
    If soSC Is Nothing Then

        Set soSC = New ScriptControl
        soSC.Language = "JScript"
        
        '* https://stackoverflow.com/questions/45015/safely-turning-a-json-string-into-an-object
        soSC.AddCode RunHttpRequest("https://raw.githubusercontent.com/douglascrockford/JSON-js/master/json2.js")
        soSC.AddCode "function JSON_parse(sJson) { return JSON.parse(sJson); } "
        

    End If
    Set SC = soSC
End Function

Private Function RunHttpRequest(ByVal sURL As String) As String

    Dim xHTTPRequest As MSXML2.XMLHTTP60
    Set xHTTPRequest = New MSXML2.XMLHTTP60
    xHTTPRequest.Open "GET", sURL, False
    xHTTPRequest.send
    RunHttpRequest = xHTTPRequest.responseText

End Function

Wednesday 1 September 2021

CIW - Web Developer and other certification

So twenty years ago I passed an exam by the Certified Internet Webmasters (CIW). Whilst I've never been asked for it in a job application or a job advert the fact that someone has chosen to define a syllabus worthy of study is useful for those of us who want to structure our learning by means of certification exam passes. So here I give notes summarising what CIW vertification is curently offered.

First, we need to know the extent of CIW certication. Quoting from their page the CIW has 4 certification series which I give along with supposed salary ranges:

The CIW Web Foundations Associate is an omnibus of 3 individuals course and they are: Internet Business Associate (1D0-61A, 30 mins), Site Development Associate (1D0-61B, 30 mins), Network Technology Associate (1D0-61C, 30 mins), the aggregated course outlines are here. You can take the exams separately, or as an omnibus exam (1D0-610, 90 mins).

The CIW Web Design Professional certification consist of two exams: CIW Web Design Specialist (1D0-520, 75 mins) and CIW E-Commerce Specialist (1D0-525, 75 mins) with their respective course outlines here Web Design and E-Commerce.

The CIW Web Development Professional certification consists of three specialist courses and corresponding certification exams: CIW Javascript Specialist (1D0-735, 90 mins), CIW Perl Specialist (1D0-437, 75 mins) and Database Design Specialist (1D0-541, 90 mins) with their respective course outlines here JavaScript, Perl, Database.

The CIW Web Security Associate is the entry level CIW Security certification with a 90 minute exam, 1D0-671, here is link to course objectives. The higher security certifications imply requiring co-opted exam pasess from third parties but if you read closely then the CIW own CIW Network Technology Associate and CIW Web Foundations Associate seem to qualify so this implies an exam and money saving.

The new CIW Web and Mobile Design series consists of seven proposed certifications though only four are live. They are:

So now we have identified all the exams we can tabulate and provide some learning resource links

CourseCertification(s)ExamLearning Links
Internet Business AssociateWeb Foundations Assoc1D0-61A, 30 mins, £108Google
Site Development AssociateWeb Foundations Assoc1D0-61B, 30 mins, £108Google
Network Technology AssociateWeb Foundations Assoc1D0-61C, 30 mins, £108Google
Web Foundations Associate (Bundle)Web Foundations Assoc1D0-610, 90 mins, £108Google, Certlibrary.com, Google Books - Exam Q & A
Advanced HTML5 & CSS3 SpecialistWeb and Mobile Design1D0-620, 75 mins, £108Google
User Interface DesignerWeb and Mobile Design1D0-621, 75 mins, £108Google, Certlibrary.com, Google Books - Exam Q & A
CIW Data AnalystWeb and Mobile Design1D0-622, 75 mins, £108Google
CIW Social Media SpecialistWeb and Mobile Design1D0-623, 90 mins, £108Google
Web Design SpecialistWeb Design Pro1D0-520, 75 minsGoogle, Certlibrary.com
E-Commerce SpecialistWeb Design Pro1D0-525, 75 mins, £108Google, Certlibrary.com
Database Design SpecialistWeb Development Pro1D0-541, 90 mins, £180Google, Certlibrary.com, Amazon E-book
Perl SpecialistWeb Development Pro1D0-437, 75 minsGoogle, Certlibrary.com
Javascript SpecialistWeb Development Pro1D0-735, 90 mins, £108Google
Web Security AssociateWeb Security1D0-671, 90 mins, £108Google, Google Books Q & A (571 version)

Friday 27 August 2021

Map friendly application names to localhost and port numbers

So, we'll all familiar with running various local web servers/services on our own computer and running them on the 127.0.0.1 ip address and various port numbers (because we cannot share an ipaddress and port number combination). So, it is the case that for local web servers one types into a browser address bar addresses like localhost:3002 or 127.0.0.1:4005. It would be better if we could give our local web servers/services friendly names and also drop the port number. That is the goal of this post.

Let us try to give two friendly names to two web services, we'll call them Luke and Leia.

When one types an ipaddress or a network name into a web browser address bar the port numner is assumed to be port 80 (for HTTP) unless specified othwerwise. So if I type in Luke into a web browser address bar then that actually signifies Luke:80.

It turns out that the necessary configuration to do map friendly names occurs in two places, the name to ipaddress occurs in the HOSTS file but the port routing is handled by an application called netsh.

Use the HOSTS file to map hostname to Ip Address

We'll do the hosts part first which is to amend the HOSTS file which (on my machine at least) can be found at C:\Windows\System32\drivers\etc\hosts. Note the hosts file has no file extension and so looks like another folder in the address but it really is a file. Take a backup of this file because if you mess up then your system will malfunction, ***this is your responsibility not mine***. You will need Administrator rights to amend the contents of this file and the whole directory.

If you are satisfied in taking responsibility then amend your hosts file to include the following lines...

# to allow friendly names for localhost web servers/services
127.0.0.2     luke
127.0.0.3     leia

You can test if this change has been applied by pinging Luke then pinging Leia so I get the output below and you can see the respective ip addresses in the output...

C:\>ping luke

Pinging luke [127.0.0.2] with 32 bytes of data:
Reply from 127.0.0.2: bytes=32 time<1ms TTL=128
...

C:\>ping leia

Pinging leia [127.0.0.3] with 32 bytes of data:
Reply from 127.0.0.3: bytes=32 time<1ms TTL=128
...

I still have the web servers running from the previous blog article (please read at least bottom part) and so I can now type into a browser and get the following...

http://luke:3002/
Bonjour le monde de port 3002 (2021-08-27 15:07:54)
http://leia:3003/
Hello world from port 3003 (2021-08-27 15:07:45)

It ought to be noted that ip address 127.0.0.1 is not the only loopback/localhost address, every ip address that begins with octet 127, i.e. 127.*.*.*, is a loopback/localhost address. So our ip addresses for Luke and Leia of 127.0.0.2 and 127.0.0.3 are both localhost addresses and so no network packets leave the machine they are always handled locally. It should be noted that 127.*.*.* means there are 256^3 loopback/localhost addresses, or above 16 million. So that should be enough! Especially when you consider we have yet to factor in all the combinations with port numbers!

So I am happy to have abolished the ip addresses and given the friendlier names of luke:3002 and leia:3003 but the port numbners need to go away so we will do that next.

Port Proxying

I am indebted to this stack overflow answer Using port number in Windows host file - Stack Overflow which highlighted the use of the tool netsh (and the hosts file above). In my example I going to use the following command to port forward for Luke and Leia...

netsh interface portproxy add v4tov4 listenport=80 listenaddress=127.0.0.2 connectport=3002 connectaddress=127.0.0.2
netsh interface portproxy add v4tov4 listenport=80 listenaddress=127.0.0.3 connectport=3003 connectaddress=127.0.0.3

You can use the command netsh interface portproxy show v4tov4 to display what you have just registered...

PS C:\Windows\System32> netsh interface portproxy show v4tov4

Listen on ipv4:             Connect to ipv4:

Address         Port        Address         Port
--------------- ----------  --------------- ----------
127.0.0.2       80          127.0.0.2       3002
127.0.0.3       80          127.0.0.3       3003

And now in the web browser I can abolish the port number...

http://luke/
Bonjour le monde de port 3002 (2021-08-27 15:07:54)
http://leia/
Hello world from port 3003 (2021-08-27 15:07:45)

You remove an entry with a command line like...

netsh interface portproxy delete v4tov4 listenport=80 listenaddress=127.0.0.2

Warning, I have come across some posts that say the Apache web server can hog all the localhost/loopback addresses and so if this is not working for you then you might want to stop Apache, the same applies to IIS Express.

One interesting point to note is that is appears that the entries are stored in the registry at Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\PortProxy\v4tov4\tcp here is a screenshot...

Installing PowerShell 7

I installed PowerShell 7 because I wanted to use the Background operator & when running node web servers. The background operator & will run the command line in the backgroud and so not tie up a command window; it is a feature borrowed from the UNIX shell. It means I can run several web servers or other long running (or permananent) executables from a single command window.

The other feature I want is the option to open a PowerShell command window from the context menu when in a Windows Explorer window. This already is working for my current version of PowerShell (5.1). I'd want it for PowerShell 7 as well.

It seems if you want the latest version of PowerShell, version 7, then you must deliberately install it. That is to say the Windows service packs and Windows updates did not upgrade my PowerShell from version 5.1 automatically. Here is the Microsoft web page for downloading and installing PowerShell 7.1.

The installer throws a series of dialog boxes which I have shown below, I took the defaults with the exception of the feature labelled Add 'Open here' context menus to Explorer. If I didn't take this option then I'd have to fiddle around in the Registry to get that feature working. Much better to turn on this feature at the install stage. The final screenshot shows the context menu feature working.

Multiple Background Jobs with Powershell

So now I am ready to run some instances of a node.js web server on different ports. Firstly, let me give some node.js source code. The following listing will return either Hello world or Bonjour le monde depending on the command line arguments on an ip address and port number also specified on the command line.

var args = process.argv.slice(2);

const http = require('http');

const hostname = args[0] || '127.0.0.1';
const port = args[1] || 3001;
const lang = args[2] || 'en';

const server = http.createServer((req, res) => {
  res.statusCode = 200;
  res.setHeader('Content-Type', 'text/plain');
  var timeStamp = new Date().toISOString().replace(/T/, ' ').replace(/..+/, '');
  var greet = (lang ==='fr') ? 'Bonjour le monde de port': 'Hello world from port' ;
  res.end(`${greet} ${port} (${timeStamp})`);
});

server.listen(port, hostname, () => {
  console.log(`Server running at http://${hostname}:${port}/ language:${lang}`);
});

So to run the above I'd run the following example commands ...

node.exe .\server.js 127.0.0.2 3002 fr
node.exe .\server.js 127.0.0.3 3003 en

Below is a screenshot of PowerShell 7 where I run these commands with the ampersand & operator which forces them into the background. You can see that Powershell reports then as a background running job. These jobs can then be terminated using the stop-job command supplying the job number.

Thursday 26 August 2021

CSS Grid's 'grid-template-areas' are wonderfully intuitive

Finally, I have found a decent CSS grid layout technology. Twenty years ago, I used to use HTML tables to structure a page. Then we were told not to use tables and switch over to CSS instead but the CSS techniques at the time were inadequate and so I and many other programmers carried on with HTML tables. Now, I am happy to blog about CSS Grid's grid-template-areas which are wonderfully intuitive way to layout a page.

A really good YouTube video is Easily Structure your Layout with CSS Grid's 'grid-template-areas' and I have given the source code for this video below. I have also embedded the sample page into this blog entry, converting as required. You should find that this is a responsive page that will reduce to a column/stack if the browser's width is made narrow. The CSS has a 'mobile first' design in that the default declaration is for the reduced screen mobile stack whilst the media queries further down are where to find the full window declarations.

You really should watch the video in full but for those in a hurry the real essence is in the following extracts, first we have this grid-template-areas CSS property...

grid-template-areas:
    "sidebar header header header"
    "sidebar sect1  sect2  sect3"
    "sidebar main   main   main"
    "sidebar footer footer footer";

Then we have the HTML...

<body>
    <aside></aside>
    <header></header>
    <section></section>
    <section></section>
    <section></section>
    <main></main>
    <footer></footer>
</body>

Then these are tied together by specifying the grid-area property in each HTML element's CSS ...

aside { grid-area: sidebar; }
header { grid-area: header; }
section:nth-of-type(1) { grid-area: sect1; }
section:nth-of-type(2) { grid-area: sect2; }
section:nth-of-type(3) { grid-area: sect3; }
main { grid-area: main; }
footer { grid-area: footer; }

And that's it, full listing below. Speaking personally this will be my go to page when drawing up a web page from scratch. Enjoy!

Sample Page

Code Listings

index.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
    <link rel="stylesheet" href="main.css">
</head>
<body>
    <aside></aside>
    <header></header>
    <section></section>
    <section></section>
    <section></section>
    <main></main>
    <footer></footer>
</body>
</html></html>

main.css

body,
html {
    height: 100vh;
}

body {
    margin: 0;
    display: grid;
    grid-template-columns: 100%;
    grid-template-rows: repeat(5, auto);
    grid-template-areas:
        "sect1"
        "sect2"
        "sect3"
        "main"
        "footer";
}

aside {
    grid-area: sidebar;
    background-color: #007fff;
}

header {
    grid-area: header;
    background-color: #71b8eb;
}

section:nth-of-type(1) {
    grid-area: sect1;
    background-color: #B3D8FD;
}

section:nth-of-type(2) {
    grid-area: sect2;
    background-color: #5E86AF;
}

section:nth-of-type(3) {
    grid-area: sect3;
    background-color: #6D9FD2;
}

main {
    grid-area: main;
    background-color: #7DA9D5;
}

footer {
    grid-area: footer;
    background-color: #588EC3;
}

@media only screen and (min-width: 768px) {
    body {
        margin: 0;
        display: grid;
        grid-template-columns: auto 27% 27% 27%;
        grid-template-rows: 8% 30% auto 10%;
        grid-template-areas:
            "sidebar header header header"
            "sidebar sect1  sect2  sect3"
            "sidebar main   main   main"
            "sidebar footer footer footer";
    }
}

Links

Sunday 22 August 2021

How do web servers tell the (Windows) operating system which port to listen on?

So I chanced upon a beautiful piece of sample C++ whilst wondering around the Microsoft website. Essentially the code creates an http server sample application. If we browse the code we can see that there is a line to register interest in a URL of which the port is a segment by calling HttpAddUrl.

Before we call HttpAddUrl we have to call first HttpInitialize and then httpcreatehttphandle; the latter passes a structure that we can pass into HttpAddUrl.

But now we can get to the heart of the issue: how to register interest in a port. Here is the method signature of HttpAddUrl.

HTTPAPI_LINKAGE ULONG HttpAddUrl(
  HANDLE RequestQueueHandle,
  PCWSTR FullyQualifiedUrl,
  PVOID  Reserved
);

The second parameter is a string, a URLPrefix string to be precise. The syntax and examples are given below.

"scheme://host:port/relativeURI"

https://www.adatum.com:80/vroot/
https://adatum.com:443/secure/database/
https://+:80/vroot/

To start receiving requests the sample code gives a function which handles each request, in this code there is yet another Windows API call this time to HttpReceiveHttpRequest.

And that is enough code, although we should tidy up and this is given in the code. Hopefully this clarifies the relationship between a web server and the (Windows) operating system.

There are some details about upgrading the code when using HTTP Server API Version 2.0. Other than that the code stands

All of this is for Microsoft Windows obviously, but I should imagine the process is similar for Linux and Mac OS.

Previously I have given code (twice!) that allows Excel to run as a web server: once in C# and once in Python. So, it would appear that for the adventurous some C++ Excel addin could also implement an HTTP web server! So that's a third way!

Saturday 24 July 2021

JavaScript Notes and Queries: JavaScript's Prototypical Inheritance is simpler than TypeScript implies

In other posts on this blog I can write with authority rooted in deep experience. Lately, I have been revisiting JavaScript and cannot write on this topic with such confidence but I am keen to master web development. So I begin a new series called JavaScript Notes and Queries and the first topic is prototypical inheritance...

TypeScript, harbinger of change

I cannot write about all the previous versions of JavaScript nor give key milestones, I can only write as to my previous encounters. Decades ago, I remember JavaScript being the language used in web pages to handle mouse clicks etc. and little more. At my clients (Banks etc.) nobody was writing large scale projects in JavaScript. But Microsoft were and they found it wanting, so Microsoft invented TypeScript. Here's John Papa, Principal Developer Advocate with Microsoft ...

TypeScript is a language for application-scale JavaScript development. It’s a typed superset of JavaScript that compiles to plain JavaScript and was originally created out of a need for a more robust tooling experience to complement JavaScript language developers.

So, Microsoft concluded that ordinary JavaScript couldn't cope with large scale projects. The TypeScript tooling, specifically the online transpilers, that arose could be used to compare and contrast syntax patterns between TypeScript and JavaScript, of most interest to me was classes and inheritance.

TypeScript transpilers reveal JavaScript prototypical equivalence of TypeScript classes

Typescript 'transpiles' to plain JavaScript, and you can find tools on line to demonstrate such as TypeScript playground. I find this to be the best tool because you can change the target version of JavaScript. When I first encountered these JavaScript was still at ECMAScript 5, i.e. before the class keyword had arrived. We can play a game of time travel and restrict the target language to ECMAScript 5 by using the options menu. Then, we can write a TyepScript class and see the old school JavaScript equivalent.

Below is a simple class in Typescript (on the left) and its transpiled into old school JavaScript, specifically ECMAScript 5 (because ECMAScript 6 has its own class keyword) equivalent (on the right). Here is the link which you must paste in full because the source code is encoded in the url.

class Animal {
  name: string;
  constructor(theName: string) {
    this.name = theName;
  }
  move(distanceInMeters: number = 0) {
    console.log(`${this.name} moved ${distanceInMeters}m.`);
  }
}
"use strict";
var Animal = /** @class */ (function () {
    function Animal(theName) {
        this.name = theName;
    }
    Animal.prototype.move = function (distanceInMeters) {
        if (distanceInMeters === void 0) { distanceInMeters = 0; }
        console.log(this.name + " moved " + distanceInMeters + "m.");
    };
    return Animal;
}());

The JavaScript on the right is cryptic compared to the Typescript on the left but if you play around (add some more methods etc.) you'll discover the syntax pattern. The pattern is that methods are appended to the prototype property of the Animal object, this means all instances of Animal created will get a move() method, just like it was a class. Other lines in the JavaScript implement the contructor function. A class's modularity is reproduced in JavaScript by using the 'module pattern', this is established with an IIFE (Immediately Invoked Function Expression).

JavaScript's Prototypical Inheritance is simpler than TypeScript implies

So far so good. Now that we know how to append methods to the prototype we could just skip the Transcript and write the JavaScript directly. But what about inheritance? In Typescript you use the extends keyword. I've given a code listing example below and this link will take you again to Typescript playground but brace yourself for the transpiled equivalent for it is very, very scary.

class Animal {
  name: string;
  constructor(theName: string) {
    this.name = theName;
  }
  move(distanceInMeters: number = 0) {
    console.log(`${this.name} moved ${distanceInMeters}m.`);
  }
}

class Dog extends Animal {
  bark() {
    console.log("Woof! Woof!");
  }
}

So the transpiled listing is given below in all its scariness. Don't forget, we're deliberately choosing to target a version of JavaScript before the class keyword arrived. We're doing this in the name of investigation! All the really scary code is in the top code block which defines the __extends function.

"use strict";
var __extends = (this && this.__extends) || (function () {
    var extendStatics = function (d, b) {
        extendStatics = Object.setPrototypeOf ||
            ({ __proto__: [] } instanceof Array && function (d, b) { d.__proto__ = b; }) ||
            function (d, b) { for (var p in b) if (b.hasOwnProperty(p)) d[p] = b[p]; };
        return extendStatics(d, b);
    };
    return function (d, b) {
        extendStatics(d, b);
        function __() { this.constructor = d; }
        d.prototype = b === null ? Object.create(b) : (__.prototype = b.prototype, new __());
    };
})();
var Animal = /** @class */ (function () {
    function Animal(theName) {
        this.name = theName;
    }
    Animal.prototype.move = function (distanceInMeters) {
        if (distanceInMeters === void 0) { distanceInMeters = 0; }
        console.log(this.name + " moved " + distanceInMeters + "m.");
    };
    return Animal;
}());
var Dog = /** @class */ (function (_super) {
    __extends(Dog, _super);
    function Dog() {
        return _super !== null && _super.apply(this, arguments) || this;
    }
    Dog.prototype.bark = function () {
        console.log("Woof! Woof!");
    };
    return Dog;
}(Animal));

I took this code and then I started to remove lines to see what breaks, you might like to do the same as an exercise. I believe the variable b stands for base, d stands for derived and p stands for property. Much of this code is 'polyfill' code which acts to retofit modern features, so a lot could be removed.

My classes are simpler than others and so I could remove loads of code. My classes hold no state which is my preference these days. Without state, my classes have parameterless constructors and the need to call base class constructors is obviated; this also simplified matters.

I had thought that I had completely boiled down the __extends function to one line that uses the Object.create method ...

Dog.prototype = Object.create(Animal.prototype);

An alternative line of code is to the use

Object.setPrototypeOf( Animal.prototype, Creature.prototype )

I asked a pertinent Stack Overflow question while constructing a deeper class hierarchy. That question links to other relevant SO questions.

All this means we can arrive at a much less scary code listing below.

    var Animal = (function () {

        function Animal() {
            return this;
        }

        Animal.prototype.move = function (distanceInMeters) {
            if (distanceInMeters === void 0) {
                distanceInMeters = 0;
            }
            console.log("Animal moved " + distanceInMeters + "m.");
        };

        return Animal;
    }());

    var Dog = (function () {
        
        Object.setPrototypeOf( Dog.prototype, Animal.prototype )

        function Dog() {
            return this;
        }
        Dog.prototype.bark = function () {
            console.log("Woof! Woof!");
        };
        return Dog;
    }());

    var dog = new Dog();
    dog.bark();
    dog.move(10);
    dog.bark();

If we look in the Console in the Chrome Developer Tools then we can see out program's output. If we also type console.info(dog) and expand the nodes then we can see our desired inheritance tree ...

Our target inheritance tree

Speed Warning

Unfortunately that is not the end of the story because during my research I cam across this MDN article which says that the Object.setPrototypeOf technique is ...

Ill-performing. Should be deprecated. Many browsers optimize the prototype and try to guess the location of the method in memory when calling an instance in advance; but setting the prototype dynamically disrupts all those optimizations. It might cause some browsers to recompile your code for de-optimization, to make it work according to the specs.

Clearly, I need to do more research. I will return to this topic...

Links

Other links that maybe useful...

Wednesday 21 July 2021

Use Python to write file tags from Excel VBA

This post was prompted by a bounty on a Stack Overflow question which details VBA code that can read file tags; questioner wants to know if VBA can write these tags as well. Sorry to say that I do not know of any current VBA referencable type libraries (there was once a DSOFiles.dll but not on my current machine). Instead we can leverage the comprehensive Python ecosystem and create a Python class callable from Excel VBA, below is a Python listing for the FilePropertiesSetter.py script.

The Python COM gateway class pattern is one I give dozens of times on this blog so I will skip details of that. The key magic is in the FilePropertiesSetter.changeSingleProperty() method which I give now without error handling ...

1 properties = propsys.SHGetPropertyStoreFromParsingName(self.filename, 
                            None, shellcon.GPS_READWRITE, propsys.IID_IPropertyStore)
2 propKey = propsys.PSGetPropertyKeyFromName(propName)
3 newValuesType = (pythoncom.VT_VECTOR | pythoncom.VT_BSTR) if isinstance(newValue,list) else pythoncom.VT_BSTR
4 propVariantNewValue = propsys.PROPVARIANTType(newValue, newValuesType)
5 properties.SetValue(propKey,propVariantNewValue)
6 properties.Commit()

So, essentially this code opens up the file's tag property store (line 1). On line 2 we find the property's key from the property's friendly name (e.g. "System.Category"). Line 3 determines if we're dealing we an array or not to help construct the new property. Line 4 constructs the new value in a form acceptable to that particular library. Line 5 actually makes the change. Line 6 commits the change and closes the file store.

The rest of the script is essentially helper methods.

The scripts has a number of libraries and so be prepared for some installation, some pip install commands. One pitfall I had to code for is when user passes in a two dimensional variant array such as if they had lifted values off a block of cells (I think the StackOverflow questioner was wanting this), the code to handle this is in FilePropertiesSetter.ensureList() which also split a comma separated string into an array (list).

FilePropertiesSetter.py needs to run once, and will request Administrator privileges to register the class with the COM registry.

FilePropertiesSetter.py

import os
import pythoncom
from win32com.propsys import propsys, pscon
from win32com.shell import shellcon
from win32com.server.exception import COMException
import winerror
import numpy

class FilePropertiesSetter(object):	
    _reg_clsid_ = "{5ED433A9-C5F9-477B-BF0A-C1643BBAE382}"
    _reg_progid_ = 'MyPythonProject3.FilePropertiesSetter'
    _public_methods_ = ['setFilename','setTitle','setKeywords','setCategory','setSubject','setComment']
	
    def __init__(self):
        pass

    def setFilename(self, filename: str):
        if not os.path.exists(filename):
            raise COMException(description="Specified file '" + filename + "' does not exist!  Subsequent calls will fail!", 
                    scode=winerror.E_FAIL, source = "FilePropertiesSetter")
        else:
            self.filename = filename

    def ensureList(self, arg):
        # despite hinting that one pass a string it seems Python will accept a VBA array as a tuple
        # so I need to convert tuples to a list, and split csv strings to a list
        # and flatten any 2d tables to a list

        try:
            if type(arg) is tuple:
                list2 = list(arg)
                
                if numpy.ndim(list2)>1:
                    # flatten any two dimension table to a one dimensional list
                    return [item for sublist in list2 for item in sublist]
                else:
                    return list2
            else:
                if isinstance(arg,list):    
                    return arg
                else:
                    return arg.split(",")

        except Exception as ex:
            raise COMException(description="error in ensureList for arg '" + str(arg)  + "'\n" + (getattr(ex, 'message', repr(ex))) ,
                    scode=winerror.E_FAIL, source = "FilePropertiesSetter")

    def setTitle(self, title: str):
    	# https://docs.microsoft.com/en-us/windows/win32/properties/props-system-title
    	self.changeSingleProperty( "System.Title", title)

    def setKeywords(self, keywords: str):

        # https://docs.microsoft.com/en-us/windows/win32/properties/props-system-keywords
        self.changeSingleProperty( "System.Keywords", self.ensureList(keywords))

    def setCategory(self, category: str):
        # https://docs.microsoft.com/en-us/windows/win32/properties/props-system-category
        self.changeSingleProperty( "System.Category", self.ensureList(category)  )

    def setSubject(self, subject: str):
        # https://docs.microsoft.com/en-us/windows/win32/properties/props-system-subject
        self.changeSingleProperty( "System.Subject", subject)

    def setComment(self, comment: str):
        # https://docs.microsoft.com/en-us/windows/win32/properties/props-system-comment
        self.changeSingleProperty( "System.Comment", comment)

    def changeSingleProperty(self,  propName: str, newValue):
        propKey = None 
        if hasattr(self,'filename') and self.filename is not None:      

                try:
                    properties = propsys.SHGetPropertyStoreFromParsingName(self.filename, 
                            None, shellcon.GPS_READWRITE, propsys.IID_IPropertyStore)
                except Exception as ex:
                    raise COMException(description="Could not open properties for file '" + self.filename + "'\n" + (getattr(ex, 'message', repr(ex))) ,
                            scode=winerror.E_FAIL, source = "FilePropertiesSetter")
            
                try:

                    propKey = propsys.PSGetPropertyKeyFromName(propName)
                except Exception as ex:
                    raise COMException(description="Could not find property key for property named '" + propName + "'\n" + (getattr(ex, 'message', repr(ex)))  , 
                            scode=winerror.E_FAIL, source = "FilePropertiesSetter")
                    
                if propKey is not None:
                    try: 
                        newValuesType = (pythoncom.VT_VECTOR | pythoncom.VT_BSTR) if isinstance(newValue,list) else pythoncom.VT_BSTR
                        propVariantNewValue = propsys.PROPVARIANTType(newValue, newValuesType)
                        properties.SetValue(propKey,propVariantNewValue)
                        properties.Commit()
                    except Exception as ex:
                        raise COMException(description="Error whilst setting value ...'\n" + (getattr(ex, 'message', repr(ex)))  , 
                                scode=winerror.E_FAIL, source = "FilePropertiesSetter")

def RegisterThis():
    print("Registering COM servers...")
    import win32com.server.register
    win32com.server.register.UseCommandLine(FilePropertiesSetter)

if __name__ == '__main__':
    RegisterThis()	
    pass

def Test():
    propsSetter=FilePropertiesSetter()
    propsSetter.setFilename(r"N:\FileProps\Newbook.xlsx") # use your own filename (obviously)
    propsSetter.setKeywords("python,keywords")
    propsSetter.setCategory("python,category")
    propsSetter.setTitle("python title")
    propsSetter.setSubject("python subject")
    propsSetter.setComment("python comment")

After FilePropertiesSetter.py is run once and correctly registered (don't forget you may need some installation) then it is scriptable from VBA with code like the following ...

Option Explicit

Sub TestPythonClass()

    Dim objPropsSetter As Object
    Set objPropsSetter = VBA.CreateObject("MyPythonProject3.FilePropertiesSetter")
    
    objPropsSetter.setFilename "N:\FileProps\Newbook.xlsx"
    objPropsSetter.setTitle "VBA Title"
    objPropsSetter.setSubject "VBA Subject"
    objPropsSetter.setComment "VBA Comment"
    objPropsSetter.setKeywords "Foo2,bar"
    'objPropsSetter.setKeywords Array("Foo1", "bar1")
    objPropsSetter.setCategory Sheet1.Range("A1:B2").Value2

End Sub

You will need to call setFilename because that is how the script knows which file to operate upon.

Enjoy!

Saturday 31 October 2020

Internationalise Dates in VBA

VBA does Date Internationalisation! Here find VBA code to generate and interpret dates in foreign languages. In this post I give new code to interpret dates in foreign languages as well as signpost existing code to format dates in foreign languages written by Stack Overflow user GSerg.

A while back I blogged about how VBA can interpret foreign currencies by calling into the COM runtime like other Windows API calls. Dates are another internationalisation problem and the same trick works, we call into oleaut32.dll the COM runtime.

First, let's introduce GSerg's VBA code to write dates in foreign languages. We need this first to generate test data for my code which interprets foreign language dates.

I won't replicate the code to respect intellectual property rights but I give here some test code that calls in to GSerg's FormatForLocale function...

Public Function TestFormatForLocale() As String
    
    '*
    '* for list of locale ids (LCID) see this
    '* https://docs.microsoft.com/en-us/openspecs/office_standards/ms-oe376/6c085406-a698-4e12-9d4d-c3b0ee3dbc4a
    '*
    Const EN_US As Long = 1033
    Const DE_DE As Long = 1031
    
    '*
    '* FormatForLocale written by GSerg
    '* https://stackoverflow.com/users/11683/gserg
    '*
    '* Find source code at https://stackoverflow.com/questions/8523017/excel-format-value-mask/8523219#8523219
    '*
    Debug.Print FormatForLocale(CDate("12/May/2020"), "dd/mmm/yyyy", , , EN_US, DE_DE)
    Debug.Print FormatForLocale(CDate("12/Oct/2020"), "dd/mmm/yyyy", , , EN_US, DE_DE)
    Debug.Print FormatForLocale(CDate("12/Mar/2020"), "dd/mmm/yyyy", , , EN_US, DE_DE)
    
End Function

The code aboves prints out three dates in German...

12.Mai.2020
12.Okt.2020
12.Mrz.2020

GSerg's code cleverly calls into VarFormatFromTokens and VarTokenizeFormatString to format a date in a foreign language. You can read GSerg's explanation at his StackOverflow answer. For the moment I am content he has generated some good test input for my foreign language date interpretation code.

Next is code to interpret foreign dates, it's far fewer lines because I don't have to build a string buffer instead I supply a string and get a date back. There is some test code at the bottom demonstrating the German dates are being interpreted correctly (I have thrown in a French date as well).

Option Explicit

'* https://docs.microsoft.com/en-us/previous-versions/windows/embedded/aa519031(v=msdn.10)
Private Declare Function VarDateFromStr Lib "oleaut32" (ByVal strIn As Long, ByVal lcid As Long, _
            ByVal dwFlags As Long, ByRef pdateOut As Date) As Long

Public Function VarDateFromStr2(ByVal sInDate As String, ByVal lLCID As Long) As Date
    Dim hres As Long
    Dim pdateOut As Date
    hres = VarDateFromStr(StrPtr(sInDate), lLCID, 0, pdateOut)
    
    If hres = 0 Then
        VarDateFromStr2 = pdateOut
    Else
        Debug.Print "warning error: " & hres
    End If
End Function

Sub TestVarDateFromStr2()
    '*
    '* for list of locale ids (LCID) see this
    '* https://docs.microsoft.com/en-us/openspecs/office_standards/ms-oe376/6c085406-a698-4e12-9d4d-c3b0ee3dbc4a
    '*
    Const EN_US As Long = 1033
    Const DE_DE As Long = 1031
    Const FR_FR As Long = 1036

    Debug.Print VarDateFromStr2("12.Mai.2020", DE_DE) = CDate("12-May-2020")
    Debug.Print VarDateFromStr2("12.Okt.2020", DE_DE) = CDate("12-Oct-2020")
    Debug.Print VarDateFromStr2("12.Mrz.2020", DE_DE) = CDate("12-Mar-2020")
    Debug.Print VarDateFromStr2("12/mars/2020", FR_FR) = CDate("12-Mar-2020")
    
End Sub

I was inspired to write this post because of a StackOverflow question which uses the MonthName function which will be tied to the VBA installation language. With the couple of programs demonstrated it is possible to break away from VBA's installation language and truly go international.

Wednesday 30 September 2020

ConnectToConnectionPoint offers low-level alternative wiring for VBA events

I really like when I chance across some low-level technical wizardy to help out when VBA hits its limits ( it is what this blog does best!) Today, on StackOverflow a reward bounty of 500 is being awarded to a cracking answer which uses the ConnectToConnectionPoint Win32 API function call to sink events without using the WithEvents keyword.

So, I came across this StackOverflow Q & A where the questioner is asking how to reduce WithEvent declarations and subs with VBA and ActiveX and the responder provides a solution which uses ConnectToConnectionPoint to acquire events without using WithEvents.

The responder says they found the original code on a Japanese website and indeed I believe they are referring to this from Keiichi Tsunoda: Implementation of the event handling by API : ConnectToConnectionPoint. ConnectToConnectionPoint is defined in shlwapi.h which is part of the Windows Shell API (so it's not part of the original COM runtime API).

Googling a little more and I found a VBFormus post, a Mr Excel post and a GitHub Gist which I have placed in the Links section below.

How significant is this for Excel VBA? I do believe it is already possible to reduce the number of WithEvent declarations by introducing a class and holding an array of instances of those classes. Each class instance would be instantiated with the reference to a ActiveX control acquired using OLEObjects() for a worksheet or Controls() for a UserForm. However, the fact that the implementation of ConnectToConnectionPoint is in the Windows Shell library which is what Windows Desktop and the Windows Explorer use suggests that its use for sinking events from other Windows processes may have a more dramatic potential.

However, Mathieu Guindon who runs the RubberDuck project thinks this is a key technology to solving a glitch that had been an obstacle in implementing MVVM for VBA, here is his blog post Making MVVM Work in VBA Part 2 - Event Propagation

Links

Soft Links assist with Hard Link Hell

If you build an Excel application of any size then you will probably use more than one workbook. To access information in another workbook the standard way is to link. However, having workbooks linked to one another often leads to problems managing the opening and closing of linked workbooks. In this post I offer a 'soft link' which aims to break the hard links which come as a default and let your code take control.

There is such a thing as dependency hell where it required to gather antecedent code or data. A specific instance on Windows is DLL Hell concerning the loading of correct executable libraries. In Excel, we have our own form which I am calling 'Hard Link Hell'.

I call Hard Link Hell the mess that VBA coders can encounter when we build a VBA application of size that spans multiple workbooks. In my opinion, a VBA coder ought to exercise as much control as possible over the opening and closing of workbooks. Have a cell in one workbook link to another raises the spectre of Excel opening linked workbooks when we were not expecting it.

Admittedly, there is some control over the behaviour. So from the Data ribbon if I select Edit Links then I get the following dialog box...

... where we can see in the bottom right corner the Startup Prompt button which if pressed raises the following dialog ...

Nevertheless, I have had Hard Link Hell in the past where I have had the break links and relink to a new workbook. I remember it being a nightmare. So, in this post I give some code called Soft Links which means VBA code can take control of when to open linked workbooks. The code ships two functions to be called from a worksheet, SoftLink(workbookName, sheetName, rangeName) which actually return an Excel.Range object but Excel is clever enough to call the Value property; but this works only for single cell references. So for multiple cells use SoftLinkValue(workbookName, sheetName, rangeName). Be aware that the source cell(s) must be named using a range name. Also in the listing are some test procedures

Note, you will have to write code to open the source workbooks or you will get a #VALUE!, but we wanted to take control and so comes the responsibility to ensure the source workbook is loaded when this function is calculated. Enjoy!

Option Explicit

'* Use this for a source comprising multiple cells
Public Function SoftLinkValue(ByVal sWorkbookName As String, ByVal sSheetName As String, ByVal sRangeName As String)
    Dim rng As Excel.Range
    Set rng = SoftLink(sWorkbookName, sSheetName, sRangeName)
    SoftLinkValue = rng.Value
End Function

'* Use this for a source comprising single cell, also useful in other VBA code
Public Function SoftLink(ByVal sWorkbookName As String, ByVal sSheetName As String, ByVal sRangeName As String) As Excel.Range
    Dim wb As Excel.Workbook
    Set wb = OernColItem(Application.Workbooks, sWorkbookName)

    If Not wb Is Nothing Then

        Dim ws As Excel.Worksheet
        Set ws = OernColItem(wb.Worksheets, sSheetName)
        
        If Not ws Is Nothing Then
            Set SoftLink = OernWorksheetRange(ws, sRangeName)
        End If
    End If
End Function

Private Function OernWorksheetRange(ByRef ws As Excel.Worksheet, ByVal sRangeName As String) As Excel.Range
    On Error Resume Next
    Set OernWorksheetRange = ws.Range(sRangeName)
End Function

Private Function OernColItem(ByRef col As Object, ByVal idx As Variant) As Object
    On Error Resume Next
    Set OernColItem = col.Item(idx)
End Function

'**** TEST ****

Sub TestVBACallingSoftLink_LocalSheet()

    Const csSHEET1 As String = "Sheet1"

    Dim rng As Excel.Range
    Set rng = SoftLink(ThisWorkbook.Name, csSHEET1, "A1")
    
    Debug.Assert Not rng Is Nothing
    If Not rng Is Nothing Then
        Debug.Assert rng.Address = "$A$1"
        Debug.Assert rng.Worksheet.Name = csSHEET1
    End If
End Sub


Sub TestVBACallingSoftLink_ExternalWorkbook()
    Const csSHEET1 As String = "Sheet1"

    '*** test setup: create new workbook, add a name
    Dim wbNew As Excel.Workbook
    Set wbNew = Application.Workbooks.Add
    
    Const csNAME_FOO As String = "Foo"
    Dim ws As Excel.Worksheet
    Set ws = wbNew.Worksheets.Item(1)
    ws.Names.Add Name:=csNAME_FOO, RefersToR1C1:="=Sheet1!R4C8"
    ws.Range(csNAME_FOO).Value2 = 42
    '*** end of test setup:

    '*** now we can call our function to get a link to an external workbook
    Dim rng As Excel.Range
    Set rng = SoftLink(wbNew.Name, csSHEET1, csNAME_FOO)
    
    Debug.Assert Not rng Is Nothing
    If Not rng Is Nothing Then
        Debug.Assert rng.Address = "$H$4"
        Debug.Assert rng.Worksheet.Name = csSHEET1
        Stop
    End If
    wbNew.Close False
    Stop
End Sub

Wednesday 26 August 2020

VBA - Use Dom.SelectNodes and double slash XPath to jump in anywhere in an Xml Document

Don't be tempted to loop through an Xml structure algorithmically when you can jump in using some double slash prefixed XPath.

A question arose on StackOverflow which sadly had been closed by the moderators, so I solved it myself and published the answer here. The questioner says

I want to have flexible code so that I can just point to the nodepath of the financial value and then simply go up or down in the XML tree to find all the other data I need.

So the questioner would prefer not to write code for every Xml document structure instead find the a key node and expect to find the supplementary data in elements not far away. They have given some test data and I have given some code to handle both.

A key feature of the code is to use the SelectNodes() method of the DomDocument object which will give a list of multiple matches. To avoid specifying fixed paths use double slash!

Enjoy!

Option Explicit

Private Sub TestListSingleFinancialValueItems()
    ListSingleFinancialValueItems TestData1
End Sub

Private Sub TestListMultipleFinancialValues()
    ListMultipleFinancialValues TestData2
End Sub

Private Sub ListMultipleFinancialValues(ByVal dom As MSXML2.DOMDocument60)

    Dim nodesFinancialValues As MSXML2.IXMLDOMNodeList
    Set nodesFinancialValues = dom.SelectNodes("//financialvalues")
    
    Dim nodeFinVal As MSXML2.IXMLDOMElement
    For Each nodeFinVal In nodesFinancialValues
        
        Dim sCurrency As String: sCurrency = ""
        
        Dim nodsChildVals As MSXML2.IXMLDOMNodeList
        Set nodsChildVals = nodeFinVal.SelectNodes("value")
        
        If nodsChildVals.Length > 0 Then
            sCurrency = ReadCurrency(nodeFinVal.PreviousSibling)
            Debug.Assert sCurrency <> ""
            
            Dim vals As MSXML2.IXMLDOMElement
            For Each vals In nodsChildVals
                Debug.Print sCurrency & " " & vals.Text
            Next
        End If
    Next

End Sub


Private Sub ListSingleFinancialValueItems(ByVal dom As MSXML2.DOMDocument60)

    Dim nodesFinancialValues As MSXML2.IXMLDOMNodeList
    Set nodesFinancialValues = dom.SelectNodes("//financialvalue")
    
    Dim nodeFinVal As MSXML2.IXMLDOMElement
    For Each nodeFinVal In nodesFinancialValues
        
        Dim sCurrency As String: sCurrency = ""
        sCurrency = ReadCurrency(nodeFinVal.NextSibling)
        Debug.Assert sCurrency <> ""
        Debug.Print sCurrency & " " & nodeFinVal.Text
    Next
End Sub

Private Function ReadCurrency(ByVal xmlElement As MSXML2.IXMLDOMElement) As String
    If Not xmlElement Is Nothing Then
        If xmlElement.BaseName = "currency" Then
            ReadCurrency = xmlElement.Text
        End If
    End If
End Function

Function TestData1() As MSXML2.DOMDocument60
    Dim s
    s = _
    "<transactions>" & _
    "    <transaction>" & _
    "        <transactionID>5</transactionID>" & _
    "        <lines>" & _
    "            <line>" & _
    "                <financialvalue>100.00</financialvalue>" & _
    "                <currency>USD</currency>" & _
    "            </line>" & _
    "            <line>" & _
    "                <financialvalue>200.00</financialvalue>" & _
    "                <currency>USD</currency>" & _
    "            </line>" & _
    "         </lines>" & _
    "    </transaction>" & _
    "</transactions>"
    Dim dom As MSXML2.DOMDocument60
    Set dom = New MSXML2.DOMDocument60
    Debug.Assert dom.LoadXML(s)
    Set TestData1 = dom
End Function

Function TestData2() As MSXML2.DOMDocument60
    Dim s
    s = _
    "<transactions>" & _
    "    <transaction>" & _
    "        <currency>USD</currency>" & _
    "        <financialvalues>" & _
    "            <value>100.00</value>" & _
    "            <value>200.00</value>" & _
    "        </financialvalues>" & _
    "    </transaction>" & _
    "    <transaction>" & _
    "        <currency>USD</currency>" & _
    "        <financialvalues>" & _
    "            <value>300.00</value>" & _
    "            <value>400.00</value>" & _
    "        </financialvalues>" & _
    "    </transaction>" & _
    "</transactions>"
    Dim dom As MSXML2.DOMDocument60
    Set dom = New MSXML2.DOMDocument60
    Debug.Assert dom.LoadXML(s)
    Set TestData2 = dom
End Function


Friday 24 July 2020

VBA - Writing code in the Immediate window

You can write mini-programs in the Immediate window but you'll need to change how you write code. If you want a VBA code challenge this morning this will challenge you.

So a Stack Overflow answer to this question is due to be awarded a bounty. The prize winning answer doesn't actually solve the question which is how to print a two dimensional array to the Immediate window; instead, the answer highlights the Locals window (it's a favorite of mine as well). Nevertheless this question and its answers caught my attention and amongst them was a mini-program meant to be run entirely from the Immediate window, the code and output is show below.

arr = [ {"A",1; "B",2; "C",3 } ]: _
For r = LBound(arr, 1) To UBound(arr, 1): _
        For c = LBound(arr, 2) To UBound(arr, 2): _
            Debug.Print arr(r, c): _
       Next c: _
Next
A
 1 
B
 2 
C
 3 

I have seen code for the Immediate window before and wondered 'Why bother?' when you can write a small function and so didn't register the pattern and syntax required but today for whatever reason I embraced this syntax.

I wasn't happy with the output of the given code and wanted to amend it and then I hit the syntactical challenges therein. A small list of bullet points is appropriate here.

  • You can't use Dim statements; so write code as if Option Explicit is commented out
  • You can't use a For statement with a Next statement so you are obliged to use multi-line statements
  • If statements cannot be multi-line version they must be single line version. I.e. Don't use End If
  • Feel free to use VBA.IIf instead of an If statement.

So I successfully amended the code to give output with which I'm happy.

    arr = [ {"A",1; "B",2; "C",3 } ]: _
    sAcc = "": _
    For r = LBound(arr, 1) To UBound(arr, 1): _
            For c = LBound(arr, 2) To UBound(arr, 2): _
                bRowEnd = (c = UBound(arr, 2)): _
                sAcc = sAcc & CStr(arr(r, c)) & VBA.IIf(bRowEnd, ";" & vbNewLine, ","): _
           Next c: _
    Next r: _
    Debug.Print sAcc
A,1;
B,2;
C,3;

Be mindful that your variables in the 'ether' of the Immediate window will hang around until either (a) you suffer a state loss or (b) you type End which deliberately causes a variables wipedown. This is why I clear down sAcc at the start of the mini-program, otherwise repeated execution makes it build up.

You can instantiate classes in the Immediate window but the Intellisense didn't work (Intellisense did work for standard modules).

This is all a long way from Python's REPL I must say. If you any comments, suggestions or tips for working with the Immediate window do please drop me a line below.