Calc2Code Tips of the Day

Day 4 Part 2

So you've got a spreadsheet, you tell Calc2Code which cells are the inputs and outputs, push the builder button and create your new function. Then, you test it, and find out sometimes it returns 0 when you know that's not the right answer. What's going on?

If the return type of the function is a double or an integer, there's a good chance the function is trying to return an error, but it can't because the return type is not XLOPER. You can change the return type, and then run it again, at which point you'll likely discover the answer is #VALUE, #NA, or #DIV/0. But why? Where is the error occurring?

You should still have the spreadsheet that was used to build the function.

TIP: ALWAYS SAVE THE SPREADSHEET THAT IS USED TO BUILD A FUNCTION!

You can find out which inputs were passed in to get it to return the 0 (or any other unexpected value), put those into the input cells of the spreadsheet, and then examine the spreadsheet to see where it went wrong. Typically, there will be a cell that is dividing by 0 when the author never checked to be sure they were about to divide by 0, or something similar to that. The spreadsheet needs to be revised to fix the problem, and then Calc2Code needs to be rerun to create the improved DLL.

But what if it's inconvenient or impossible to get the inputs that led to the problem? Fortunately, Calc2Code contains a cell log feature that makes this much easier.

To enable cell logging, edit the turboexcel.ini file to turn it on by adding the line "GenerateCellLog=Y". Then, rebuild the DLL, and replace the one that generated the error with the cell log version. Run the system again, and this time a file will be created which contains the contents of every cell that Calc2Code used to calculate the final value. This can then be used in conjunction with the spreadsheet to see which cells have errors, or you can trace back from the result cell to see which cells it is dependent on that have unexpected values.

While this can be a tedious process, it is far more direct and efficient than debugging in a language like C++ or VBA. Why? Because you are able to look at the value of every variable simultaneously: you can start at the inputs and work forward until you find a problem, or start at the result and work backwards, of jump to certain important intermediate cells to see if the problem occurs before or after them. All without needing to start the program up over and over again or step one pass at a time through a loop.

So let's see an example. Our spreadsheet is calcrate.xls:

This spreadsheet calculates the rate of interest earned on an investment from one date to another. The investment, which is an input, will grow to be worth 1 on the second date. The formula for this is quite simple, but without any error checking, it's possible to create inputs that will cause problems. For one thing, if the two dates are the same, we will get a divide by zero error. Also, if the price is negative, we'll get #NUM. And in either case, if we make the return type for Calc2Code a double, the function will return 0.

If turn on cell logging, we can get this cell log with good inputs:

But when the first two dates are the same, we would get this cell log:

Here we see that J12 contains an error, and a look at the cells J12 depends on (J9 and J10) reveals J9 is zero, which causes the error.

We don't recommend having cell logging turned on all the time, because writing to the file does slow down the execution of the generated function. it should be reserved for those times that the results of the function don't make sense.

To ensure that happens rarely if ever, we recommend that after creating a function you use Excel to test it under a wide variety of inputs.

One type of test could entail creating a VBA macro which puts either a predefined set of values into the input cells, recalculates the original spreadsheet, and also contains a cell which calls into the new function, and then checks that the result cell matches the one with the call into the new function.

Another type of test, appropriate for small spreadsheets like calcrate.xls, it to calculate the result in a single row, based on values in the leftmost columns. Then, call into the new function in a cell to the right, and copy it all down. This is demonstrated in calcrate_test.xls:

This lets you take advantage of Excel to both write the function, and to test it. With Calc2Code, you can have your cake, and eat it too.