Buddy, Can You Paradigm?

(stackoverflow rep: 8998, Project Euler 89/273 complete

(or: “If Paradigm was half as nice”)

I started reading Coders At Work recently. In the style of a few predecessors, the book comprises transcribed (and presumably edited) interviews with fifteen programmers who have made significant contributions of various kinds. It’s a weighty tome, possibly a little overweight if I’m honest, but interesting nonetheless; it makes a change from the kind of technical tome I usually cart around on the daily commute.

Circling a little closer to, and nodding vaguely in the general direction of the point, interviewee number seven, reached just this morning, is Simon Peyton Jones, who was one of the progenitors of Haskell, one of, if not the first name that springs to mind when one hears “functional programming“. For the last decade, Jones has been a researcher at Microsoft Research in Cambridge, which suggests that he would have had at least one finger in the F# pie… There’s a DotNetRocks show number 310 features an interview with the man, by the way. I’m enviously disturbed that despite our very similar ages, he appears considerably less follicly-challenged than me, although there could be some comb-over action going on there.

In the taxonomy of programming paradigms (does that make sense?) we have Declarative and Imperative. They are opposites. In the Declarative fold, we get stuff like SQL, XSLT, all the functional languages and, I submit Excel worksheets. Actually it’s not just me: that Wikipedia link places spreadsheet cell-based programming within the Dataflow subcategory of Functional. Imperative programming, on the other hand, includes the panoply of more “traditional” languages, including good old Visual Basic and its slightly less functional sibling VBA.

Thinking about Excel and VBA developement, I’d say there are several fairly distinct uses for VBA code (usually, but not exclusively: we can write add-ins or perform automation in plenty of other ways) which include:

  • New worksheet functions;
  • Interface extensions (menus, toolbars, ribbons etc);
  • Helpers (or wizards);
  • Control and simulation

…of which I write a lot of the latter two.

(You know how some comedians talk about a string of seemingly unrelated things and then cleverly tie them all together at the end? Well, fingers crossed, stay tuned)

A while back, I was handed a workbook, developed by an external consultancy, that modelled the 20-year evolution of a financial product. We wanted to run it across a significant number of stochastic market data simulations to get a picture of the distribution of possible outcomes. About 30,000 such simulations (and about 16GB of simulated market data) would be sufficient, we thought. The workbook took about 20 minutes to perform one such calculation. We didn’t have a year.

Fortunately, there were any number of classic optimisation failures to rectify: massive VBA/worksheet interaction at the cell level (changed to use large arrays), screen update enabled at all times, lots of less painful VBA-specific stuff, Application.Calculation = xlCalculationAutomatic, you know the drill. Easy stuff. Within a day it was taking about a minute and I could look at having the whole analysis done in under a week (I have a four-CPU machine).

If it all seems straightforward, it wasn’t. I’d wrapped the workbook in a modified (hacked-up from xlUnit) test jacket to check that my changes weren’t affecting the results, pretty much a necessity for any refactoring work, and one change kept breaking. Every time I tried to switch off automatic calculation, triggering it only when I thought it was needed, the final results came out different. I finally realised that the VBA and worksheet were extremely highly-coupled – calculations were occurring as the code pushed values through: change a value, auto-calc, change another value, auto-calc again and so on. Eek. It took almost a week to unravel. Of course, since I actually enjoy doing this kind of work for the most part, it wasn’t a complete disaster, but I could have lived without the time pressure…

As a finance worker (perversely, I’m increasingly inclined to describe myself as a Banker1 these days) I have cause, from time to time, to access The Bloomberg from Excel. At least one of the functions in the (extensive and powerful, let’s be fair) API is a real functional-imperative mess. Here’s one with which I’m not at all happy:

=BDH("MXWO", "PX_LAST", "01/06/2007", "28/12/2009", "Dir=V", "Dts=S", "Sort=A", "Quote=C", "QtTyp=Y", "Days=T", "Per=cd", "DtFmt=D", "cols=2;rows=672", "FX=EUR")

Yes, there are a lot of arguments2, but most are at least not positional, which shows they’re trying. This particular example asks for the last daily quote of the MSCI World Index, in Euro, between the dates given. Even better, I guess, there’s a wizard to generate it all for you. But here’s the nasty bit: the call fills as many rows – without warning about overwrites – as it needs. Oh, and that "cols=2;rows=672" parameter? It’s rewritten if you make any change that would affect the number of cells output. This is not easy to accomplish. Try duplicating the effect in VBA, go on, I double-dare you. I think there’s a separate server process at work here, able to make asynchronous (but timely) modifications to user worksheets.

Yikes. And ugh.

To me, there’s something broken here. They’re using am apparently functional programming model but allowing the code to affect the state of the worksheet in (from the user’s perspective at least) a rather arbitrary (or at least, unpredictable) way. We’re not even getting an array formula here – the function just causes chunks of worksheet to be filled with data. In point of fact, there looks to have been some serious programming work here – I don’t think in the normal course of events that calling a function is allowed to cause areas outside the calling range to be modified. I rather suspect an external process is being asked to pump values into the sheet through some devious means like (but let’s hope it isn’t) DDE.

(This is where I try to pull it all together. A little encouragement wouldn’t go amiss here…)

The fact that we have both paradigms represented as first-class components of a single development platform may be a large part of the reason for Excel’s dominance in the marketplace. But the interface between declarative and imperative code needs to be carefully managed to avoid the kind of unnecessary side-effects that I spent so long untangling.

The messed-up simulation I whined about above failed to recognise that the worksheets should have been, in effect, a great big, super-complex function, which the code used by setting up the input conditions, firing off a Calculate and then reading the output values. Instead, it tried to implement a tightly-coupled declarative-imperative abomination, with unfortunate results.

Let’s try not to repeat that mistake, for the sake of the children.

I’ve been (and remain) grumpy – thank you and good night.

You've been a lovely audience

1 Wanna make something of it?

2 Many Reuters functions do something similar, but push all the key-value pairs into a long string.

[Wi|Ga]dgets – what’s the use?

(stackoverflow rep: 2539, Project Euler 47/227 complete)

Gadgets? We don’t need no stinking gadgets!

Microsoft, Yahoo! and Google, to name but three proponents, each have a desktop widget (or gadget, or whatever) model, either providing stuff spread out all over your Windows (are OSX and other *nix environments similarly afflicted?) desktop or tucked away in a “Sidebar”. These handy-dandy little applets provide information on such vitally otherwise hard-to-get functions such as:

  • The weather (for those not in sight of a window?);
  • RSS feeds (for those who want to read their feeds in a 2″x1″ window);
  • An e-mail notifier (because you still don’t understand the meaning of ‘asynchronous’);
  • The time (for those who can’t read the little digital clock on their taskbar and who don’t own a watch);
  • A calculator (because the world needs another computer desktop calculator);
  • Resource usage monitors (so you can tell your computer isn’t running slow);
  • A mediocre controller for your media player (because media players don’t have minimised controls … oh, yes they do);
  • Teeny-tiny picture viewers (for when flickr is just too much detail)
  • Out-of-date stock tickers (so you can see in delayed real-time how much poorer you are today)

… you get the idea.

Even better, all these are neatly tucked away on a “sidebar” (a sort of non-window window) or even better, spread out all over the shop, which is exactly what you want on a two- or three-screen setup. And they’re all handily concealed beneath the applications you’re running at any time. You know, those stupid time-wasters like Outlook, Excel, Firefox, Word, Visual Studio, a couple of Explorers and a database utility or two.


What we seem to have here are a collection of (sometimes) graphically pleasing little applications that deliver functionality available elsewhere, each having one or more of the following drawbacks:

  • Always-present, seldom needed;
  • Inadequate functionality;
  • Duplicate of something that’s already fit-for-purpose;
  • Pointless eye-candy;
  • Invisible.


Looking at Yahoo!’s programming category, I find that the most popular has been downloaded 80,000 times. It’s a widget that performs geolocation for a given IP address. With a flag. I’m trying to imagine a situation where I (or anyone) would need that often enough to abandon a browser-based function, opting for a desktop-resident applet against a “proper” application because I don’t need it that much. I don’t exactly see the “programming” connection either, come to think.

Yahoo programming widget downloads as at 12-Jan-2009

Data as at 12-Jan-2009

I’m probably not being fair – I thought the most useful stuff would be written for programmers. What does it look like overall? I can’t tell much from Google’s list because they don’t give download stats, although sorting by popularity shows the expected four C’s (clock, climate, calculator, calendar). So back to Yahoo! where the current Number One, with a snappy 4.5 million downloads, is Yahoo! Weather. In fact, as I write this, a whopping six widgets have passed the million mark. Only two are clocks.

Oh look - Microsoft Sidebar does weather too

Oh look - Microsoft Sidebar does weather too

The highlights of a quick-and-dirty breakdown of the top 100 are 19 fun-and-games, 15 system monitor thingies, 14 clocks, 9 calendars, 8 media players, 7 weather reports, 6 post-it notes, 5 gold rings. Very similar to the Google list. I was too depressed to look at Microsoft’s in any detail, but it’s the same ol’ same ol’, although their weather widget claims over 22 million downloads. I’m guessing it’s downloaded automatically when a Vista PC connects to the Internet…

Number one Google gadget - because you can never have too many clocks

Number one Google gadget - because you can never have too many clocks

I’m not getting it. Looks like many others aren’t either. The numbers of people who come back to provide a rating are miniscule: about 7,000 for the weather app. About 0.16%

Apart from the shocking paucity of imagination in the applets themselves, what’s wrong with the whole idea? (IMHO, of course, YMMV).

Real estate is Precious

There are people out there who have enough monitors to be able to allocate space for widgets. Two 1280×1024 screens isn’t enough for me though. Utility drops to almost zero if the things aren’t always available. Google make things worse by allowing applets to live anywhere on the desktop. Stuff needs to go in a sidebar that manages window maximisation to keep itself visible. So it needs to be on the right- or left-most monitor, unless you’re prepared to give up on dual-screen workbooks. For the average user, the bar needs to be a lot narrower than at present to be tolerable. Somewhere between, say, 25 and 50 pixels? I could live with that.

Useful vs Pretty

Useful doesn’t always win. Useful-but-ugly often doesn’t get past “Go”, whereas Pretty at least gets a chance. Long-term, it’s got to have both: too much of what’s on offer seems to be limited to pretty useless.

To offer a compelling argument against rapid deletion, applets have to either provide something in a better way than is currently available or provide something that isn’t available at all elsewhere . Example: there is a Ruby script that allows, from the command line, simple copying of files to an Amazon S3 bucket. Useful. Maybe we could have a widget (maybe it already exists, but I couldn’t find one) that allows upload via drag-and-drop from Explorer. That would be better than what’s already available. Something I can’t do at all except via cut and paste is store stuff in Google Notebook. A drag-and-drop gadget to simplify that would be providing something I can’t do at all.


I don’t know that I have one. There are several similar implementations of a desktop XML/Javascript applet technology that has a lot of money invested in it. Well, I don’t know how much exactly, but I bet I’d be a happy old programmer if you’d given it all to me instead. And you might as well have done exactly that, for all the benefit mankind appears to be accruing. It ought to be good for something, oughtn’t it?


Get every new post delivered to your Inbox.