Strong Typing is an add-in that allows formulas to be augmented with type meta data to help ensure formulas work as intended or to highlight logic issues with formulas in a spreadsheet.
- Define any number of types
- Create types automatically from existing worksheets
- Create rules to define how types should be validated
- Validate a whole worksheet or a smaller range
- Report validation results
- Export and share rule sets
Huge numbers of people use Excel and it’s a testament to Excel’s ease of use that so many people feel able to use it. The problem is, Excel is easy to use because it only sees a list of numbers. Moreover, it makes assumptions on the user’s behalf which makes it really easy to do stupid things like add apples and oranges.
Suppose the purpose of a spreadsheet is to record and sum the sales of different types of apples and oranges: Bramley, Cox, Gala, Satsuma, Tangerine, etc. Excel relies on the user entering the numbers and then creating a formula to sum total apples and total apples. The intention of the user may be to create a formula to total all apple sales but Excel offers no help selecting the values to sum instead the user has to know. Of course in a simple example like this, there really is no issue. But in non trivial spreadsheets it has been shown that even highly skilled spreadsheet users will make at least one error. If in this trivial case the person who creates the formula total sales of apples also includes a value for the sales of a type of orange Excel will offer no alert to warn the user.
Excel also employs a technique called ‘coercion’. It may sound like something involved in subterfuge but the technique allows Excel to make a best guess at the user’s intent. The most obvious example is when a cell containing text is included in a range of cells that is summed. Excel does not try to add a number and text to report an error. Instead is ‘coerces’ the string value to an implicit value of zero. In many cases this is harmless – even preferred. But if the text looks like a number the user will be unaware the ‘number’ is not included in the total. To be fair modern versions of Excel will include a green triangle in the upper left corner when text looks like as number. But disabling this safeguard or defeating the safeguard, either accidentally or deliberately is trivial.
Now imagine being able to define types in Excel and assign them to cells so Excel is able to report when apples are added to oranges. That is strong typing.
It’s strong typing to distinguish it from the value type system that Excel, like dynamic languages has. Value types are ‘number’. ‘text’ and ‘yes/no’. These types cannot be extended in dynamic language.