Debug formulas

I’ve been doing a load of conditional compilation in VC++ recently. I have a commercial product out with debug, release and trial versions. I always disliked cc in VBA but in C I find it much more useful. (FWIW I added code signing as a post build step so alt br gets me a signed xll to test.)

Anyway I’d like the same kind functionality in my spreadsheet formulas please. I’d like the ability to set a workbook to be in release or debug mode (or other) and have it behave accordingly.

In debug, lookup functions could return the address of the match rather than the value for example. Formulas that return an error could perhaps provide a bit more info like which argument caused the error. What things would you like in a debug version of a formulas?

I’d also really like a map of the calc tree and the calc order, especially any that bounce as uncalculated, as there could be real scope here to improve performance. I’d particularly like to see which cells calculate the most often, and perhaps how long they take – but perhaps this is more of a tuning/optimisation feature than debugging?

I’d also quite like a way to watch for cell data types changing over a few calc cycles.

I don’t really want to get into the audit tool feature sets here, like unique formula lists, data type/formula maps etc. I’m thinking more on watching it calc it a way we can’t currently do with Excel (but can with Resolver).

What things would you find useful in a debug version of a workbook?



11 Responses to “Debug formulas”

  1. Harlan Grove Says:

    Conditional compilation happens before actual compile time. Different code gets sent to the compiler. How would anything similar work for interpreted languages like Excel formulas that isn’t already provided by IF(..) constructs?

    And using your lookup formula example, if it returned a cell address, presumably as text, in DEBUG mode, wouldn’t that screw up downstream formulas that expected the lookup formula to return a number? Or are you angling for setting formulas in one set of ranges to PRODUCTION mode and formulas in different ranges to DEBUG mode?

  2. Simon Says:

    I was simply meaning the different modes – being able to flick a switch and have a version optimised for testing/checking and then flick it to production.

    good point about downstream effects – what about each formula adds its address, value and (calculated) dependents to a list during the calc, or on some other trigger (TRACE?). 2 modes at once sounds like an interesting idea too, I guess that is heading back to being some pop up audit tool?

  3. sam Says:

    On a Tagent

    “…Lookup functions could return the address of the match rather than the value for example….”

    When the Index Function is used as a stand alone function it points to a cell returns the value of a cell
    Ex =Index(PickRng,Match(WhatToMtch,WhereToMatch,MatchType))

    However when the Index Function is used as a portion of a Range Reference then it Points to a cell and Returns the Address of the Cell

    Ex = A1:Index(A:A,Counta(A:A)) will return A1:A10 (if there are 10 Data elements in Col A without blanks)

    This cannot be seen using the F9 key….but by using the Evaluate button on the Auditing Toolbar

  4. Harlan Grove Says:

    sam – not quite. When the first argument to INDEX is a range reference, INDEX returns a range reference no matter how INDEX is used in the larger formula. So INDEX(A:A,2) returns EXACTLY the same thing as A2. When either INDEX(A:A,2) or A2 appear as final expressions (operands to operators rather than as arguments to functions), Excel dereferences them, returning their values.

  5. Simon Says:

    I seem to recall something like
    Index returns a value, or was it lookups?
    Offset (and indirect) returns a range which is instantly coerced to a value, this may be a factor in them being volatile.

    Excel is generally pretty smart at giving you the data type that is most appropriate for any given use.

  6. Harlan Grove Says:

    Simple way to check.





    As for Excel being pretty smart, explicit range references, the 3 built-in functions which return range references, INDEX, INDIRECT and OFFSET, and udfs returning range objects are passed as range references when used as function arguments, which is why you DON’T get syntax errors when you try to enter formulas calling SUMIF or COUNTIF functions with INDEX calls as 1st arguments whereas you DO get syntax errors when you try to enter the 4th formula above.

    When you use range references as operands to arithmetic or string operators (but not the range manipulation operators space, comma and colon – I should have been precise before), Excel ALWAYS dereferences them. As for the proper data type, Excel ALWAYS (other than in Transition Formula Evaluation mode) converts text and booleans to numbers when they’re used as operands to arithmetic operators, and Excel always converts numbers and booleans to text when they’re used as operands to the concatenation operator &. Even when Excel dereferences blank cells, they’re DETERMINSITICALLY converted to text or numbers syntactically: to the number 0 when used as arithmetic operands, to text “” when used a an operand to &, and to the same type as the other operand of comparison operators.

    The types Excel returns when dereferencing range references is driven exclusively by formula syntax. There’s no semantic decision tree involved.

  7. Simon Says:

    Harlan thanks for that
    you’ve blown my volatility/return type idea out of the water.

    Here is an eg where Excel doesn’t convert from a string to a number, or does it somewhat inconsistently.

    A1 = a
    A2 = 1
    A3 = sum(A1:A2) = 1
    A4 = A1 + A2 = #VALUE!
    In Excel, in OOo A4 = 1 as well

  8. Harlan Grove Says:

    I should also have addressed volatility. INDEX isn’t volatile because it only returns results within its 1st argument range/array. OTOH, OFFSET can return anything in the same worksheet as its 1st argument range reference, and INDIRECT can return any range in any OPEN workbook. Because INDEX’s return result comes from within a static range, no problem adding it to the dependency tree, thus allowing it to be nonvolatile. Note: changes in any cell in INDEX’s 1st argument range trigger recalculation, not just changes in the cell given by its 2nd, 3rd and 4th arguments.

    As for SUM(A1:A2) and A1+A2 not returning the same result, it’s documented in online help, but I don’t have the topic reference. Text in ranges passed to functions like SUM aren’t converted to numbers. Try =SUM(A1,A2). I was careful before when I stated that automatic numeric-text conversion only occurs when expressions are used as OPERANDS to arithmetic or string operators. When they’re passed to functions, those functions have to do their own conversions or not.

    FWIW, if you want =A1+A2 to return 1, set Transition Formula Evaluation. This is the way all versions of Lotus 123 (except 2.0) handle nonnumeric text as arithmetic operators. Also FWIW, if A1 contained =”100″, then A3 would still return 1 in both Excel and OOo Calc, but A4 would return 101 in Excel and still 1 in OOo Calc. Final FWIW, Gnumeric returns the same results as Excel.

  9. Marcus Says:

    Hi Simon – I believe Harlan is correct. Another way to demonstrate this is using INDEX in Code (via WorksheetFunction). If there is no match I believe it returns an error as it’s not able to obtain an object reference, rather than a (numeric) index position. Same with VLOOKUP and Match I think.

    Cheers – Marcus

  10. Miggy Says:

    Hi guys, is there ANY way to get the address of the result from a lookup match?

  11. Harlan Grove Says:

    If you want the address of the cell producing the result of, say,


    it’s given by


Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: