Tag Archives: Excel

The 7 signs your UI was created by a programmer

Do you suspect a programmer may have put together the terrible user interface on that “enterprise” software you’re forced to use every day? There are some give-away indicators. Look out for them in your software, hunt down the developer and force them to read a book about user interface design. If you’re suitably senior, force them to a) improve it, or even better b) get someone with real UI experience to fix it.

1. Exclamation marks in dialog box messages
Look, it’s probably the 50th time I’ve seen this message today. The fact that this application “Cannot connect to database!” is no longer a surprise. You may have noticed that most professional software uses a neutral tone for its communication with the user. Try that. Also:
1a. Double negatives in confirmation dialogs
“Are you sure you don’t want to lose your changes?” Err… what? No. I mean YES. Oh sh*t. Any dialog that requires you to stop and try to parse the question in order to work out if you’re about to destroy several hours of work is not doing its job. It’s getting in the way of you doing your job. In fact, convoluted messages are such a serious issue that Microsoft even tried to help developers to help their users by introducing a whole new kind of dialog box in Vista: the question/task dialog. Good luck with that.

2. No tab ordering defined\mouse only navigation
Because no-one’s ever going to use the keyboard to navigate the zillion controls in your data entry app, are they? This one actually surprises me, because I’d have thought that developers would’ve needed to navigate quickly through the application while they’re writing it. Well, that doesn’t seem to be the case. Pretty much all commercial apps are good counter examples. I don’t mean to hold up Microsoft Office as a paragon of UI virtue, but they definitely do the “alternate way of navigating everything” thing well. Everything you need can be reached by both keyboard and mouse. Unplug your mouse and try that with your favourite piece of in-house software and see how you get on.

groups3. Group boxes around everything
This is a bit of a WinForms specific one. The clue is in the name: group boxes are for grouping logically related controls, not for providing a kewl recessed border around every single one of the controls in your dialog. Don’t kid yourself that this is doing anything other than using up some valuable screen real estate. (See if you can spot another pet peeve in the example dialog, too).

icon_editor4. Icons created in the IDE
Look, Visual Studio’s a really good integrated development environment, but it ain’t no Photoshop. Don’t try and use it to create icons. And while you’re at it, please don’t make icons consisting solely of the name of your application (inevitably an acronym) in pixel font and primary colours. Oh, and don’t just steal various icons from another application, unless you’re going to steal the whole lot; one of the key visual aspects of a good UI is consistency. Mixing your hand-drawn 4-bit icons with the glorious 32-bit shiny ones you borrowed is going to be jarring. In fact, why not go the whole way and get someone who can actually draw to create your icons for you? After all, you wouldn’t have someone who wasn’t a programmer writing the code, would you…?

5. Data grids
Otherwise known as the “Excel is the pinnacle of user interface design” disease. Break the habit. Generally, the more types of controls that are embedded in your grid, the less likely that it’s the right kind of interface paradigm. Yeah, I’m looking at you, person embedding a calendar control, drop down box, graph, slider and checkbox in each row of a data grid. And whatever your 3rd-party grid provider of choice says, it’s not going to do screen redraw performance any good, either.

6. Not implemented message boxes
Ahh, the GUI equivalent of source code TODO comments. Of course, it’s an in-house software give-away; no commercial (desktop) software would be brazen enough to ship with bits of functionality dangling from the stumps of buttons and menu items. Would it? Feel free to provide counter-examples if you have them.

dialog_dialog7. Excessive use of dialog boxes
Warning: dialog boxes are considered harmful (to usability). They’re the equivalent of restraining your user by force and preventing her from moving until she answers your question. That might be OK for matters of life or death (i.e. data loss), but not otherwise. Every time you find yourself about to add a new message box, stop, take a deep breath and consider whether it’s really necessary.

So, if you’re a victim or one, many, or all of these user interface faux pas, all I can say is: sorry. I’ve been responsible for doing at least one of these things myself over the years. Consider this post repentance for my user interface sins.

Ending the love affair with Excel

I heart spreadsheets
I heart spreadsheets
It’s a well known fact that the financial services industry (where I mean banks, hedge funds, pension providers, fund managers etc) is deeply in love with Excel. But what is it about the Excel ecosystem that makes it so appealing?

Unfortunately, given the ever-increasing focus on accountability and scalability in these domains, Excel is also an increasingly inappropriate platform for delivery of this sort of functionality. How can organisations ease the transition away from this environment to something more easily manageable, while retaining it’s positive benefits?

Continue reading Ending the love affair with Excel

Beware cached IDispatch

I’ve kinda given it away there with the title, but we had an interesting set of symptoms exhibited the other day while trying to call a function in an Excel workbook via F#. It appeared that the function being called would fail depending on what had been called previously. Very odd.

A bit of background: as you may know, if you add functions to the worksheet or workbook code in Excel then they appear as callable methods on the objects themselves. This is achieved with the use of dynamic dispatch and IDispatch. For example, creating a workbook with this function in it’s VBA code:

Public Function Foo() As Double
    Foo = 100
End Function

Means you can call it like this:

MsgBox CStr(ThisWorkbook.Foo)

As well as being able to call it like this from within the Excel session (i.e. in other VBA code in the process), you can also access it externally using the COM object model that Office applications expose. For instance, you can use VBScript:

Dim excel, wkb
Set excel = CreateObject("Excel.Application")
Set wkb = excel.Workbooks.Open("a.xls")
WScript.Echo wkb.Foo

Or, more interesting, using F#:

    let excel = new Excel.ApplicationClass()

    let wkb = excel.Workbooks.Open(@”c:\a.xls”)

    wkb.GetType().InvokeMember(“Foo”, BindingFlags.InvokeMethod, null, wkb, null)

The key part here is that we’re using wks.GetType() to get a managed representation of the unmanaged Excel COM interface. Under the covers this is creating a runtime callable wrapper (RCW) to wrap the worksheet COM object.

However, the problem we were seeing was that opening multiple sheets resulted in failures to call the method in certain situations. Although the VBA signature was exactly the same in all of the sheets, it seemed that opening b.xls after a.xls, would fail; returning null when we expected it to return a value. If we opened c.xls after b.xls, it would fail in a different way; never actually making it to the body of the function. Very odd.

My first suspicion was that it was somehow related to COM object vs .NET object lifetime. This is quite a common problem whens invoking Excel using managed code. It’s bad mixing COM and .NET anyway; generally deterministic, reference-counted lifetime semantics don’t play well with the garbage collector. Throwing an app with a full-blown UI being managed as COM object into the mix just complicates matters further. Anyway, it’s been widely discussed, so I won’t say any more about it here; suffice to say that calling Marshal.ReleaseCOMObject and GC.Collect got us to the point where we could see the Excel process terminating, so we knew that the failure wasn’t due to some state being cached inside there.

So we concentrated on different aspects of the problem:

  • Given the pattern of failures, it seemed that the order of opening the sheets and calling affected the outcome. This hinted that something was persisting betweeen calls, but not in the client (Excel) site.
  • The code had previously worked when written in VBScript, so there was nothing intrinsically wrong with the operations we were performing.

This seemed to strongly indicate that something was being cached at the .NET level. And the major difference between the .NET code and the VBScript was that the former used Type.GetType() on the worksheet object to get it’s managed representation, while the latter used the IDispatch directly.

So it looked like GetType() was caching some information about the particular IDispatch implementation that it encountered first, then reusing that for subsequent worksheet implementations which actually had a slightly different layout, i.e. although they also had the Foo function which we were trying to call, they had a different set of other dynamic functions too.

After a bit of digging about I uncovered this gem: the mapping between interface pointers and runtime callable wrappers. Which seemed to describe exactly what we were seeing. The first time through the loop, the runtime is asked to get the type and is given a COM pointer, for which it creates a RCW that we can use to invoke Foo. The second time through the loop, the runtime thinks it’s seen the object before, so rather than perform the expensive operation of creating the RCW again, it just returns the original one. The problem is, the underlying COM object is different!

So, in order to prevent the runtime for trying to cache the RCW, we need to use Marshal.GetUniqueObjectForIUnknown and that does the trick nicely. We first need to get an IUnknown for our object, than convert that back into an object, which is actually a RCW:

    let wkb = Marshal.GetUniqueObjectForIUnknown(Marshal.GetIUnknownForObject(wkb))

Although it’s less efficent, at least the code works, and it finally allows us to call the dynamic methods on the workbook object from F# .NET.

It will be interesting to see how dynamic in .NET 4.0 addresses this kind of issue.