PDA

View Full Version : How to do Unit testing in Excel as part of Test Driven Development?



psionic
05-14-2008, 11:02 PM
Hi there, me again! :slingshot

I will soon be working on an proposed Excel application that does more than looking at fields or cells, checking they are correct or to standards and formatting cells according to colours, etc based on the data received from a database once the talks gets started. : pray2:

With the buzz of recent software development (including object oriented data modelling from UML and class modules available to VBA) I have been reading all over the place in the last few months. :reading:

I would like to do Test Driven Development process within Excel so I know I am testing as I go along, has anyone done this or did benefit doing this themselves? Am I in the right place to ask these question? :thinking:

Could the VBA experts around this forum want to put their cards on the table about or on this issue? :bow:

The only thing I can add is that I have seen is EUnit from metrology doing testing on spreadsheets.

http://scicomp.npl.co.uk/eurometros/gen_report.php?category=distributions&pkey=22&subform=yes

Hope there is a generated discussion on this.... :bonk:

Til then :hi: ,
Psionic

Oorang
05-15-2008, 09:00 AM
You do this a lot in banking, because you need to make sure you have accounted for every possible scenario. There is a little extra startup time while you build your test harness but it is a good way to CYA.
Typically we would set things up so every specified case would be caught and handled and anything not accounted for would be thrown into an exception report for a human to analyze and if need be create a new case.

The trick is learning to write good specific cases that don't catch up other things in the net. For instance if you wanted to auto bill clients via a fax program you would want to make sure you had all the information available before throwing a record into the "fax" case. Does the company have a fax? Do we have the fax number? Did they agree to recieve the bills in that fashion? Is their fax on 24/7? Does our fax auto redial? Is it encrypted? etc. etc. The idea being to narrow it down to only cases where a faxed bill has the maximum potential to be paid. And then you would need to set up cases for success/fail. Should it be logged? Where? If it failed, why? Do we throw it to exception report? Do we keep trying? How long do we keep trying? etc.

I think the biggest benefit (to the programmer) is that it forces the people with business knowlede to really flush out what they want. And as long as your code meets the test cases you agreed on, if something comes up, then root cause is the spec not the code.

Bob Phillips
05-15-2008, 09:35 AM
Just an additional thought. I know of no decent (any) automated regression testing tools for Excel/VBA. If anyone does, I would love to hear about it.