This is quite a coincidence. Last night on one of my infrequent visits to the Office Marketplace http://office.microsoft.com/en-us/marketplace/default.aspx I noticed what looked like a familiar name in the “Popular Downloads” section. Sure enough it was a modeless userform “calculator” that I put together some time ago.

For those interested, here are two direct links: In the Office Marketplace and on my site.

This product has an interesting history and there’s a reason for mentioning the coincidence.

History

I wrote it because I wanted to create a modeless userform that didn’t look like a userform. Essentially, that meant no titlebar and something other than shades of grey. Of course, it still had to retain all the capabilities of an userform including the consumer capability of moving the form.

As it happened, this dovetailed nicely with a long time “back burner” project, which was to put together something that would provide a quick calculation capability within Excel. It had to support the full range of Excel’s formula, including array formulas and, yet, do so without using cells. It also had to have the ability to quickly access previously used formulas.

Two scenarios where I expected this to be useful: (1) create a complex formula, component at a time, and (2) carry out quick calculations, both with an audit trail. Think of an electronic version of the the kind of calculators accountant-types favor: one that prints on a paper roll everything done with the calculator. The advantage of the electronic version is that with copy-and-paste one can quickly reuse previous work.

The result

As happens on occasion, it is the unexpected that becomes popular. This calculator has quickly become one of the more popular downloads on my site. One of the people who provided a comment wrote, “Great idea, especially for younger excell users, 7th 8th graders.”

What I didn’t realize until last night that it was also popular in the Office Marketplace. Of course, the skeptic in me says that for all I know Microsoft simply rotates different items through the “popular downloads” showcase. ;-)

The coincidence

OK, finally, the coincidence part… I’ve been playing with the new 2007 UI for the past few days and wanted do something unusual with it. Obviously, that meant using more of the UI capability than I did with the TM Plot port, in which all that RibbonX did was replace the old commandbar capability.

I thought of a variety of products but finally settled on the calculator as satisfying two key components of the learning process. One, the VBA part was almost trivial. Second, the UI part was different from the norm but not all that complex. Two fields and a handful of buttons.

Over the last few days I’ve been tinkering with the TM Calc 2007, built from the ground up in Excel 2007. The biggest challenge was the audit trail. With a userform it was trivial. There’s no RibbonX component that can contain an arbitrary amount of text information, formatted properly, and copy-able by the user. Of course, there may be one and I just haven’t figured out which. I implemented a limited audit trail capability with the new Gallery component; each entry has just a label (i.e., no image). The contents are shown in 2 columns, one representing the user formula the other the corresponding result.

I also learnt a bit more about VBA add-ins and RibbonX, including some very unwelcome news. Well, it will be unwelcome if it’s “by design” and Microsoft insists on leaving it as is. In any case, there should be at least one post in the near future sharing the experience of building a Excel 2007 “quick calculate” capability from the ground up.

In the meantime, here’s an image of the first-cut TM Calc 2007. The next step is to include a “launcher” in the parent group. This will display the current version.