- Rate Projections Functions Walkthrough
- Accruals and Cash
- Repeating Formulae
- Range Names and References
- Optional Parameters
- Using PmtsPerYear
- Modelling Seasonality
- Calculating Interest
- Using Business Functions in VBA (Visual Basic for Applications)
- Array Functions
- Annual Equivalent Rates
- Array Function
- Auto Multi Functions
- Variable Plurality
- The Trace facility is available from the Business Functions menu. Just highlight the cell containing the Business Function you want to investigate and go "Trace" from the BF menu.
- You will then see a window with a number of messages from the function(s), displayed in a tree format. In effect, the function is in "chat" mode, telling you what calculations it is doing, what other functions it is calling, and so on. For complex functions, some of this chat may not be obvious, but you will be clearly made aware of errors in your input data, since this is the first check the function does.
- Click on the little "+" signs to expand the next level of branches (function calls).
- If you have made an input error (nonsensical input, out of range) the Trace facility will report this and the function will return a #NUM! Error.
- So if you see a Business Function with a #NUM! Error, apply Trace - you will soon find the problem because the very final message in Trace will be where the problem arose, and the message will describe what happened.
- Sometimes you just think the function is returning an odd number and don"t quite believe the answer, Again, Trace provides the answer, although sometimes you will have to look through 20-30 messages to understand the calculation. If you"re still flummoxed, load up the example file for the function in question an examine the behaviour.
- When you run a Trace, a copy is written to a text file for perusal later, called bf_Trace.bfl, and that file is located in your current Excel files directory. It can be opened with any text editor, such as notepad.
- With an array function, the output from all calls in every cell in the array is returned, typically you will get a main function call and ancillary information for each timeperiod in the range (if its array-like with respect to timeperiods).
- Notice that, to help you interpret the Trace information, there are a number of color-coded icons and an explanation of what each one means.
- Take notice too, of the Detail box at the bottom. Some of the messages are too big to fot in the tree itself, and you can see these written in full in the Detail box when you click on the relevant item in the message tree.
- TIP: The quick way to bring up Trace (in either Excel 2007 or earlier versions) is to go Alt-B and then T .
|BF"s error tracking and investigation mechanism|
|Business Functions has a Trace facility that enables you to walk through the calculations of all the BF function calls in a cell. It contains a lot of information, and although some of it is hard to interpret without access to the source code, it has improved substantially from early versions and tries to explain every calculation and function call. We, the developers, like to think that Trace is our primary debugging tool. |
The information from Trace is provided in a tree format, with each new branch of the tree a function call or significant peice of calculation logic, such as analysis of inputs or passes through a calculation loop.
Here is some information about Trace:
As of version 1.48 (Jan 2008), we have finally made Trace really useful, so do use it and let us know where it could be improved!