Regular Expressions in JavaScript – thanks MDN docs

Their docs are so useful. I did some handwritten points as reinforcement for me. I know it’s basically the same as any other regex engine, just amused me to do this. I do find it hard to get stylus-handwriting looking quite like the real thing, hence the greyness of the photographed page.



AWS: Basics April 2018

I need to implement Skill synonyms. This is simple on the Skill/Client side – you just alter the json. However on the Lambda function side, you have to have implement logic to handle that.

Before I dive into that, I felt I needed a refresher of Lambda functions and how they sit in AWS, and how you test them (UI based to start). The 2 pictures try to convey where I got. Note that I am now executing in the London region – there was a warning previously that Skills only worked in 2 regions, neither of them London… so we’ll see if that works when I return to the Skill part. Secondly I am now giving Node.js 8.10 a try. I don’t yet know if that will make a mess of my Lambda-tester tests (see posts back in September I think).

For now, 2 pictures which try to convey where I got.

PowerShell: convert a column of data to a delimited string

Given a column of data (e.g. in Excel, Sheets), convert it to a delimited string (using PowerShell)
If I have data like this in a spreadsheet:

I want to create a single delimited string from that:


This is one way:


However, those were numbers/integers, so nice and easy.

If I now make one of them an alphanumeric…

… not so smooth this time:

Neither Sheets nor Excel will surround alphas with the necessary quotes. If we do that by hand…

, then this time no objections, and the quotes have been removed, which for my purposes is what I want anyway:

VBA: TDD…ish

Test Driven Development (TDD) focuses on What you want to achieve before thinking about How. That focus means you define and prepare your (automated) tests before writing a line of production code. Ideally, the code under test, the to-be production code, does not even exist at the point you execute your first test. The principle can be applied to pretty much any language, including VBA – why not? Personally I like it a lot – amongst other things I find it satisfying to have a rigorous, up-front, Definition of Done in the form of a set of tests. Indeed, as you develop your tests, you will often find that the functionality requested by the Product Owner has ambiguities that she had not foreseen, leading to more functional rigour. Sure, it is a leap to suggest that a VBA project could be the subject of a Sprint,  but again, why not, especially in a hobby situation where you are everything from Product Owner to Sprint Master.

Code under test. Test code.

First I need a trivial test framework, for this scope, that is basically some utilities that I can re-use across projects. Quite often, I find a need to have a Sleep function available, and some expected/actual results formatting geared to my preferences is handy:



Having done that, I can then think about the problem I want to solve. Examples I find are always easier than definitions. If I have a “word” (e.g. “Curiosity”), then I want to know how many times it occurs in a “line” (a set of words delimited by a given character), e.g. in “Curiosity:Curiosity;Beagle”, then it occurs 1 time if the delimiter is “:”, and zero times if the delimiter is “;”. (you can disagree… but that’s the joy of functionality… you CAN disagree 🙂 )

So I’ll call my function “CountOfTimesOneWordOccursInALine”… and I’ll call my test TestCountOfTimesOneWordOccursInALine. You will see I have more than 1 test in that er test. Discuss. Or not.

What we have thus far then is a function name:


and a test name:


Write the first test to establish a pattern – first time round it won’t shouldn’t even compile if we are close to TDD, as the vba under test doesn’t exist:


Next time, if we think we have coded it correctly, we deliberately fail it, e.g. by putting in a mad expected result:


… with a predictable outcome:


Then we change the 42 to a zero… and get an OK:



And then write the rest of the tests making sure each one breaks before we allow it to pass:


And sharing the code under test:


In the above, the reference to [Dim uniqueSet As New Scripting.Dictionary] has to be set in Tools.

VBA for Word

Honestly, I have a genuine use-case for this. Yes I feel guilty writing VBA.. but one “I wonder if you can do x?”… (yes), led to a “I wonder if you can do y?” (yes). It would be nice if you could test this stuff confidently – sure I could trawl the final document to make sure it had 23 carriage returns (hm, maybe that’s the way) when before it had 2. At least that’s making a nod towards testing. I mean automated testing, of course.

Gist. Some text you can use for testing.

May 2018: Given a selection, remove all carriage returns (In fact I won’t repaste, but more typically the replacement should be a ” “, not a “”).