When I test formulas on a spreadsheet, it s a very manual process. I’m trying to figure out a way to automate this. Here’s what I have so far.
I want to identify some input cells and output cells. Then record some known good values for the inputs and outputs and store them. I’m using my spreadsheet from my amortization table post as an example. I would select the cells I want to include in the test and run a macro that creates a text file with the cells’ values. If a selected cell has a formula, I’ll assume it’s an output test. Presumably, I would select all input cells on a spreadsheet, but there may be instances where I don’t.
This sheet only has three inputs, so I select D1:D3 for sure. I may or may not want to select every output cell. Maybe I only test a sample of the output. For this example, let’s assume I select D4, E7, E27, E107, and E367. Then I run this macro:
Dim rCell As Range
Dim sFname As String
Dim lFnum As Long
Dim sInput As String
Dim sOutput As String
If TypeName(Selection) = “Range” Then
‘Pick a name for the test file
sFname = Application.InputBox(“Enter test file name to create.”, “File Name”)
If sFname <> “False” Then ‘if inputbox not canceled
‘Add file extension if not there
If Right$(sFname, 4) <> “.txt” Then
sFname = sFname & “.txt”
End If
sInput = “[Input]” & vbNewLine
sOutput = “[Output]” & vbNewLine
‘Build input and output strings based on selection
For Each rCell In Selection.Cells
If rCell.HasFormula Then
sOutput = sOutput & ActiveSheet.Name & “|” & _
rCell.Address & “|” & rCell.Value2 & vbNewLine
Else
sInput = sInput & ActiveSheet.Name & “|” & _
rCell.Address & “|” & rCell.Value2 & vbNewLine
End If
Next rCell
sOutput = Left$(sOutput, Len(sOutput) – 2) ‘delete new line
‘Create the test file
lFnum = FreeFile
Open sFname For Output As lFnum
Print #lFnum, sInput
Print #lFnum, sOutput
Close lFnum
End If
Else
MsgBox “Please select one or more cells and try again”
End If
End Sub
And I get this in my text file:
I haven’t written the procedure that actually runs the test, but I think it will be pretty trivial (famous last words). I thought I would get some opinions on the methodology first. Is anyone automating testing of formulas? What do you think of this method?
Hi Dick
I’ve been working in this field for quiet a while
started off with what-if’s, but the validation part is just as important
I generally setup several Data Tables and use the built in Data , Table command
The Table Command only allows 2 inputs and monitors a single output.
To get around this I wrote a 4D data table function which will take 8 inputs and monitor 4 outputs.
http://www.ianeva.info/Excel_Diversions/4D_Data_Table/4D_Data_Table.html
Always interested in any feedback
Back to Testing,
My other preference is to use graphical validation, wherein I will setup a chart and either adjust input variables or change using a slider the output variables.
This way several variables can be monitored, or comparison between variables ie No. Cars Produced, Total Cost of Production and Cost per Car.
The benefit of this is that hundreds of output variables can be scanned very quickly just by moving the slider up/down changing the key output variable.
Hui…
Dick, it can be done very simply using a one variable data table, which I explain here (I talk about scenarios, but it works just as well for test data sets)
http://excelusergroup.org/blogs/dermot/archive/2008/01/22/an-awesome-powertool-buried-in-excel.aspx
Dick,
I created a couple of simple test routines that I always use:
Firstly, I run a routine that changes the cell background and pattern to one I would never use for every unlocked cell – I use this to verify that only the cells that are allowed input are unlocked.
Then, once this has been checked and agreed, I can run 1 of 2 other routines:
A. this just enters a 1 into every unlocked cell, which is used to test any sub-total and total functions; OR
B. in each worksheet data input into every unlocked cell starts at a selected number (default is 1), and is incremented by 1 for each subsequent unlocked cell within the worksheet. I use this to check where values are referenced in other cells in different worksheets, plus it is more useful to verify ratios and validations.
I specifically don’t state expected output values, as I want to be certain that I am getting the correct results. I know that I have to check the maths involved, but it is useful, as it stops you assuming that all is well if you get the number you want..
Low level, and very simple I know, but I find it extremely effective for what I do.
Why not use scenarios?
Limit to 32 cases, but.
Oh, I just see DermotH has just mentioned them.
I don’t know how to use scenarios. If I screw up a formula and run the scenario, what is it telling me?
Do people actually use Excel Scenarios?
I used them a few times, I think a few lines of VBA is much more transparent. (or even a simple INDEX into a table (like Dermots suggestion)). I prefer to keep my test harness out of the live model so as not to complicate things.
Dick I think your approach makes sense. I tend to use a separate wb (rather than a text file) so I can read and write inputs/outputs easier, and check proportions are understandable. And the test runner is trivial – assign input values…calc all…assign outputs…repeat for x test cases.
The key factors are to get decent real world test cases, and to know what you are looking for. The classic is to forget to test negatives, in fact I’m going to have to go and check something I did the other day right now!
“The key factors are to get decent real world test cases,”
Ah yes – data sign, size, type and boundary cases for IFs and Lookups are classics.
For a simple build-a-wall calculation, I give 20 tests on pages 181-182 of my book.
I do something similar to Simon – using a separate workbook with multiple worksheets for different test cases – the test case workbook has separate sheets for input and output. The input sheets are a copy of just the input cells (unlocked) and when I modify the model, I use a diff feature to compare the old input test sheet to a new one – and then I move the old cells around so the old test can be applied to the new model without having to re-enter all the test data – in most cases, if the model has stabilised, the moving around is trival and the old test cases are retained.
You could look at a tool called Junit (Java).
It’s a different platform yes – but this utility is used to do test driven development, and the concepts principles it applies may well be useful / transferable to Excel.