Back in February I did a podcast with Mike Woodhouse, based around testing VBA code. Mike has written a unit testing frame work for VBA code, called xlUnit. Over the last few weeks I’ve been using xlUnit to help write a generic validation class for VBA, so I thought I would put together a little video of how the addin can be used to write tests for your code.
Its in 2 parts because you can only upload videos that are 10 minutes long to you tube. The first part is the basics and in the second part I show some examples from my project.
You can find out more about xlUnit at Mikes blog, grumpy old programer:
Related posts:
Mike,
I am amazed, you leave the ‘Auto Syntax Check’ option set in the VBIDE, do you like VBA shouting at you when you make a mistake?
Shouldn’t you be telling us the strengths of the tool, and the weaknesses, what it does well, what it does poorly, what would be nice extra functionalty rather than how to use it?
Finally, in your example, didn’t you change the code in the test harness to show a test that failed rather than in your Numbers class. If so, that doesn’t really show anything does it?
Hi/Blimey Bob!!!!!
The video was done by me not Mike!
>>I am amazed, you leave the ‘Auto Syntax…
It’s not the Auto Syntax Checking* option in the declare variable option… and you only need this on because of a small “bug”, when the code searches through the worksheet modules – still to be confirmed as a bug, but I guess 99% of people will have option explicit on an anyway.
>>Shouldn’t you be telling us the stre…
Mike has done that on his blog. I was just trying to show people who might not have done unit testing before how they can use the addin? :-)
>>Finally, in your example, didn’t you…
Nope the code changed in the Numbers class MaxValue, Changed to MinValue and the operator change I think too.
So there! :-)))))))))))))
*That might be on in the video – it’s not my production PC…
Sorry Mike, I mean Ross (or do I mean Mike?). I was getting my developers mixed up, thinking Ross but kept hearing the name Mike.
I know you unchecked the ‘Require Variable Declaration’ bx to demonstrate what the tool needed, but I noticed that you also had the ‘Auto Syntax’ box checked (just replayed the video t make sure). This is that daft option that throws up a message that tells you that there is something wrong with the code that it tells you there is something wrong with by highlighting in red.
Believe it or not, I am interested in your opinion. I don’t want Mike’s opinion, he wrote it so he is biased :-). You have tried it, quite a bit it seems, so your opinion will inform me.
If that change was in the class, then I apologise. I wound it back to check, and I was sure it wasn’t. I must admit to being a tad confused as to what those workbooks that xlUnit created were for, why didn’t you point it at your existing workbooks? See what happens when you try to give a tutorial rather than an opinion :-)
Hey Bob!
Yeah it’s on in the video, but that’s ‘cus it a “fresh” install of Excel – well one that I don’t actually use for writing code in. So yes, it should be turned off, although given my unique spelling and typing skills in my case it’s a 50/50 call some times!
I did think of something that would be genuinely useful -(I think)- the other day – now forgotten of course!, but I would say Mike’s done a really good job with it. I’ve tried a few other unit testing addins and Mike’s one is the best – IMHO.
The addin is Alpha at the moment, so there are few bugs, but its not too bad as the audience is dev’s so we can deal with the odd error message here and there, and they are generally unhandled errors so once you work it out it’s fine.
As for the workbooks, it’s actually one of things that’s really good about xlUnit. It gives good separation of the code. The other frameworks did not do that. You can drop in you existing application workbook if you wanted too, you would just need to reference the workbook in the testing one, and then expose the elements you wanted to test. Once you get into it, it becomes much clearer – I guess I need to work on my technical presentation skills!! LOL ;-)
thanks Bob
Ross
It looks useful, but I as I said I was looking for your perceptions (so that I could keep my lazy count up by having you do the work), to convince me to invest time in trying it. I have testing tools, but nothing as thought out and as integrated as this.
As for your presentation skill … no I won’t go there, I have already had my name besmirched on Twitter :-(. On a more serious note, what do you use to create these videos, and your podcasts. I am thinking of trying them, it will be a nice job for some voice over so as not to expose my west country brogue.
I would say defiantly give it a go. It’s a great tool, even if you only use it for regression type testing, it also depends on your whole out look to testing in general but I like it and would encourage others to give it a try. It’s a bit of work to get use to, but once you start using it, I think it sells itself.
I’ve used this a bit too, but after a while you’ll start to see the benefits of Mike approach.
http://www.eurometros.org/gen_report.php?category=distributions&pkey=22&subform=yes
>>I have already had my name besmirched on Twitter :-(
LOL, despicable behaviour on my part old boy!!!!
For podcasts I’ve been using skype and then Audacity, go for the beta 1.3.xx its stable and has some important features, like dragging tracks and stuff.
For the videos I use Cam studio, which is a bit limited but free and still quite good. You can write text if you don’t like voicing over it, but it will happily record your voice at the same time if you like.
The issue I have with videos is getting the screen set up right so it fit correctly in the you tube screen – if I did more videos, I’d take the time to find out how to do them better…eh, but I cant be asked!!!
http://camstudio.org/
http://audacity.sourceforge.net/
I do most things defiantly Ross, so that will be no problem.
LOL, I’m going to edit that and then people will think you’re mad!!!
Nothing new there then.
[...] add-ins, but not much (if at all) the interaction with Excel.Note: I am aware of the existence of a VBA unit testing solution, xlUnit; I found the project conceptually pretty cool, but from a practical standpoint, it [...]