Daily Dose of Excel

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.

I don’t know many people know this, but I find it extremely useful when I’m designing a form.

If you select a control, you can then press the TAB key to cycle to the next control (and on) to see what the flow of the form is, just like if you were using the form live. It’s very easy to spot errors in the TabIndex properties this way.

But sometimes I wish we also (and note the *also*, I don’t want that to be the *only* way) had a feature like Adobe has for its forms, where you just click the tab order of the controls.

I’m designing a data entry form that uses the dynamic table as described in Professional Excel Development. It basically uses conditional formatting to “open” a data entry line when the one above is has something in it.

I want to put a command button on every line so the user can optionally do a special calculation. It will only be used on less than half of the data entry lines, but it has to appear on all of them. Since more than half of the lines will be hidden initially by conditional formatting, I need a way to hide the buttons for those lines. One of the reasons I don’t like using all those buttons is I don’t want to write the code to manage them and writing code to hide them would only add to the problem.

One solution I came up with was to simulate a command button. I sized the column appropriately and formatted the cell to look like a button. I created a custom color to closely match a button from the Forms toolbar and set that as the pattern. I then used another technique from PED by applying white and black borders to give the raised effect. The top one is the fake button, but I’m sure you could tell.

The last step in completing the simulation was changing the cursor and maintaining the proper cell selection. Since my fake button was really just a cell, that cell would be active when I clicked the “button”. Not good. I made a rectangle that fit the cell exactly and placed it over the cell. Then I set the properties of the rectangle to No Line, Transparency=100%, and Move and Size with Cells. I can then assign a macro to that rectangle and simulation is complete.

The benefit of this is that when conditional formatting is applied to that fake-button cell, it disappears. The rectangle is still there and is still clickable, but I hope the rest of the UI design discourages the mouse from wandering down there. Wishful thinking, I’m sure.

In the end, I’m going to have 60 shapes on my sheet all pointing to the same macro. I’m not too keen on having so many shapes. It’s not that I suspect something will go wrong, it’s that I don’t suspect it. That’s a sure fire way to guarantee something goes wrong.

The other options I considered are using a menu item instead of a command button on the sheet. That’s sure easier to manage, but I like the contextualness of the commandbutton, so there’s a trade-off there. Also, I could use one real command button and move it based on the data entry cell the user is in. Other than having the worksheet’s selectionchange event fire continuously, that’s not too bad of an idea. Since most of the rows will be “unavailable”, the user won’t be selecting those cells so the button won’t be moving to unintended rows.

I’m not particularly happy with any of those solutions. If any of you have dealt with the same issue, I’d love to hear about how you solved it.