Pester: New-Fixture

While I might not always use it, when you’ve been away for a while and forgotten syntax, New-Fixture scaffolds up a PowerShell script (the script under test – SUT), and a PowerShell pester script to test the SUT. The wiki is very good and is here.

I don’t like the idea of having the test and production script in the same folder, but that is easy enough to adjust (see posts passim).




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.

PowerShell: given an owner, find services

I’ll wrap this up another time, but this is the essence:




You could in fact then use Out-GridView to display and filter, or just use the filter in the Grid, and remove the [where-Object] from the original query. However, that would be less manageable if you had a farm of VMs to query. But it’s there, if needed.

SqlServer/PowerShell: simple test of backup and restore

The SQLServer package has taken over from SQLPS. Basic backup and restore examples on my GitHub, and the data I used to create the tables (just create the databases by hand for now).

And the screenshots as I got the hang of it…