Speaking of numbers...

One of the features in Excel I'm in charge of testing is "Calculation, Functions, and Formulas", which (surprise!) is exactly what it sounds like. Being a big math and numbers geek, I was very excited to learn that I would inherit these features when I started. The way that we test these this area is a nice snapshot of how we utilize AppleScript and our automation system.

Functions are the heart of Excel, even the most novice Excel users are familiar with things like SUM() and AVERAGE(). Excel has somewhere around 320 functions all told, many of which are very specialized and not used frequently. In order to make sure we regularly test every single functions, we've designed a system in which a single script and test file exists for each one. These files consist of functionality, boundary, error condition, and other tests, usually about one per row. Each row (this is row 11 in the SUM.XLS test file) looks something like this:

Column A is a description of the test

Column B contains the formula itself

Column C is a hard-coded expected result

Column D is set to "=C11=B11", which returns TRUE if the values equal each other, and FALSE if they do not.

Columns E-I are the arguments used for the formula in Column B

At the top of each file, cell $D$1 is set to “=AND(StatusArea)” with StatusArea being a name defined as whatever part of Column D is filled with tests. It all boils down to one cell on one sheet; If any of the results are incorrect we will know right away.   

For each test file, we have a little piece of AppleScript that opens the file with help from our automation framework, forces a re-calc by replacing “=” with “=”, logs the value of $D$1, and closes the file.

set myusedrange to active sheet's used range
replace myusedrange what "=" replacement "=" look at part search order by rows without match case
Log (LocalizedTrueString, range ("$D$1")'s string value, "String Value of $D$1 (expect: TRUE/(pass)) ")
close every workbook saving no

Lather, rinse, repeat, 321 times.

Each of these scripts runs every time there is a new build, on both CPU platforms. Failures appear right away, and we can compare the results over time to make sure that PPC and Intel are doing the same math. We can also quickly fire off a test run if we want to check out the latest OS patch, or critical check-in to Excel’s code. Scripts like this and our automation system enable us to test across a huge part of the product almost immediately. The simplicity of it all means that anyone can check on the state of our calc engine, and update the test files with new cases they find at any time.