my  block

where uzairs roam

GB at DKIB put together an absolutely fantastic Excel interface for our analytics. A lot of the functionality is probably industry standard at this point, but the quality of GB’s tools was absolutely fantastic, and there were aesthetic touches that really made it a pleasure to use.

One tool that I hadn’t seen before but have since come to rely on was an innocuous looking button with the label ‘Tidy Array’ that would resize an Excel range with an array formula in it. This is probably the biggest PITA in Excel: entering a formula that returns several values. However, unless you know exactly how many values the formula is going to return, you’re left either getting only part of the result back, or with unsightly #N/As framing the result. Tidy Array would evaluate the formula internally, check the size of the returned result, resize the range in which the formula had been entered, and then re-evaluate the formula to fill that resized range with the result.

Since leaving DKIB, I’ve been playing around with ExcelDNA, Govert van Drimmelen’s fantastic library for building XLLs in managed code, to build an Excel-based interface for my trading system. I missed Tidy Array, so I put something similar together (link below). It’s a little different in that after you run the function (from the ‘Pandora’ menu), it just resizes the selected range to the correct size rather than filling that resized range; the reason for this is that this prevents neighbouring cells from being inadvertently overwritten. (Note: GB’s version had logic to keep it from overwriting stuff, but I prefer to limit function behaviour rather than add behind-the-scenes logic.)

I tried to add a keyboard shortcut for it too (Ctrl + Shift + T) but I haven’t managed to get it to work yet.

To use:

1. Unzip to some convenient directory. Note the location of the TidyArray.xll file.
2. Start Excel and add TidyArray.xll as an Excel Add-in (In 2003, it’s under Tools -> Addins; in 2007, it’s under Excel Options -> Add-ins -> Excel Add-ins

TidyArray.zip