Embedded Files
Description
5g functions must be thoroughly tested before releasing to others. Releasing a function that can fail can spell disaster for those who use our functions and for our reputation. Thorough testing is one of the best ways for ensuring our functions won't fail.
Learning Objectives
At the end of this course, learners will know:
Strategies for testing
Prerequisites
RAND()
RANDARRAY()
RANDBETWEEN()
Discussion
Testing is a proven technique for driving out errors. There are many things, and many ways to test. Let us discuss a few.
Inline Help Test
One of the first things, and easiest things to test is our function's inline help. Just type = followed by the function's name and don't enter any arguments. Examine the displayed inline help text.
Is it for the correct function (we didn't copy paste it from another function without changing it did we)?
Is it aligned (did we forget a delimiter somewhere)?
Does it include everything?
Is everything spelled correctly?
Scenario Tests
It is impossible to test for all scenarios for any problem that involves just a handful of numeric or date variables. What we can do is use sampling to create random values that reasonably represent the values a user might enter. An automated version of this approach is shown in the video at right and the add-in used to automate the tests is available here: BXL Tests.xlam.
The basic approach is to use one of Excel's random number generators: RAND, RANDARRAY, and RANDBETWEEN, to create sample values for each of our function's arguments and place the randomize values where they can be accessed by our 5g function and a non-LAMBDA, non-dynamic array solution that does the same thing. Compare results. If both methods produce identical results, the function may be correct. Repeat many times to confirm.
Creating Tests
Tests can be created in many ways but the basics are the same.
Inline Help
Do not neglect testing inline help. In a separate worksheet, enter the 5g function without arguments and check the display:
Is it for this function?
Is it properly aligned?
Does the first line contains the function's name and its syntax?
Does the second line contain the Webpage address (if any exists)?
Does the next line contain the correct version date.
Do the next lines contain each function argument name with either (Required) or (Optional) and a description.
Does the last line contain an example (if practical).
Function Tests
This is the most common method I use for setting up tests. I am always humbled by this process as it seems my tests always fail at least once for simple functions, and dozens of times for complex functions. When my functions finally do pass at least a thousand tests, I feel assured it is probably right. To setup test with BXL Tests:
Identify the function to test. Catalog its arguments and decide what the range of possible values are. If the function needs data, consider randomizing the values and dimensions using something like RANDARRAY(). And if we decide to randomize the data with something like RANDARRAY(), catalog its arguments and decide what its range of values should be.
Open a blank workbook and load BXL Tests add-in. Enable macros/VBA.
Click BXL Tests' Import Config Table icon. This will add worksheet Config and the configuration table labeled as Inputs and named tblInputs.
Enter all arguments in the Table along with:
The type of randomization (INTEGER, NUMBER, PERCENTAGE, DATE, CHOOSE, and EVALUATE).
The range of values
An initial value for setting up the test
The units of measure or some comment to help explain what the values mean.
Go back the workbook's first worksheet and import tblInputs' Argument and Value columns by placing these formulas in cell A3 and B3:
=tblInputs[[#All],[Argument]]
=tblInputs[[#All],[Values]
In cell D3, add a label for the data then add the data starting in cell F3. If using a formula for the data, connect the formula to the imported values for its arguments.
Add the 5g function beneath the data and connect it oto the imported values for its arguments.
Add an alternative non-dynamic array, non-LAMBDA formula that does whatever the 5g function is supposed to do.
If needed, aggregate, shift, and/or crop step 3's results to line up with timelines or period counters.
Enter formulas to compare the 5g function's results to the results in step 4. Use a formula that takes into account tolerances like: =ABS( G5 - G19) <= .0001
AND() step 5's results and name that cell "Pass". BXL Tests looks for a named range called Pass to tell it if the test succeeded or not.
If step 11 equates to FALSE (results do not match), figure out what went wrong. Is the 5g function in error? Are the alternative formulas wrong? Is the test designed poorly? Fix then repeat.
Once step 11 equates to TRUE, set the number of tests in BXL Test ribbon Test Count then click Run Tests.
Run hundreds of tests. If any fail, figure out what went wrong, fix then rerun the tests. Continue until the tests never fail.
Additional Tests
Additional Tests
Edge Tests
These tests check for things the end user might do, and probably shouldn't. Examples include invalid dates, negative amounts where only positive amounts are allowed, text entered for numeric inputs, codes entered that are not valid, etc. Create tests for these cases. For invalid inputs, 5g Functions should fail with one of Excel's error conditions.
Orientation Test
We should remember that dynamic arrays can spill horizontally and vertically. If we want our function to work no matter which way the array spills, we must test it against both orientations.
Array vs Range Test
Dynamic arrays can spill into cells, or create an array within our function. In general, 5g functions are for ranges. But some can be used inside formulas and never spill into a range. This can cause some Excel functions to fail. The xIFS functions (SUMIFS, COUNTIFS, etc.) only work over ranges. If we intend our function to work inside a formula, we should test that it will work in that environment.
Summary
Testing is one of the best ways to uncover errors in our functions. The hard part of testing is thinking about all the ways our function might be used. When testing functions in timelines, we should consider how our function should respond when the start date is before the timeline start and the end date is after the timeline end.
Good testing requires that we not only think about what the user should do, but what the user shouldn't do, but could.
Page updated
Google Sites
Report abuse