Automated Formulas Testing

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:

Sub CreateTest()
    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
                    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
        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?

Posted in Uncategorized

9 thoughts on “Automated Formulas Testing

  1. 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.

    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.


  2. 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.

  3. 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!

  4. “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.

  5. 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.

  6. 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.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.