September 2010 Archives
I have been writing a spreadsheet to help those teaching the International Baccalaureate. In coding the part of its user interface that teachers use to describe the exercises they set and mark, my problems with menus recalled the concept of orthogonal programming languages, and showed me that Excel isn't.
A programming language is "orthogonal" if every feature works with every other feature. One of the first truly orthogonal languages was Algol68. (Others were Lisp and, I suspect, CPL.) For example, Algol68 had arrays. It had structures, functions, and unions. And it allowed you to make arrays of structures, arrays of functions, arrays of unions, structures with arrays in, unions which were sometimes arrays, functions that took and yielded arrays, and even arrays of arrays of functions that yielded structures composed of arrays of structures containing unions of arrays, arrays, and functions that took and yielded arrays.
Excel is not orthogonal. One thing I needed for the user interface was a menu of exercises. Teachers identify these by number, so I needed a menu of numbers. Now, Excel has a feature called "data validation". This enables you to restrict the values that a user can type, and to check what they do type. One way you can restrict them is to allow only values from a specified list. So I needed the data validator to put a menu of exercise numbers into my input cell.
When making the menu, you specify its list by
typing it into the "Source" field on Excel's
data-validation-setup form. The list can be either
a list of string constants, or a formula that returns
a range of cell addresses. Thus, if I want my menu
to have as options the contents of cells A2:A8,
I type =A2:A8.
But that's only if they're on the same sheet as the menu.
When a formula refers to cells on a different sheet,
you normally qualify them with the sheet name, like this:
'Task Metadata'!A2:A8. But, I scream
at my uncaring screen, Excel will not
let me use this as a data-validation source. No matter
how many times I type 'Task Metadata'!A2:A8 into
the "Source" field, Excel retaliates with a dialogue box saying
You may not use references to other worksheets or
workbooks for Data Validation criteria. The only
good thing about this is that its author at least realised that
"criteria" is plural and avoided writing "a criteria".
You have to wonder about the person who implemented data validation. If you've ever written a compiler, you'll know that its code generator defines two vital functions. One generates code that yields the value of an expression. The other generates code that yields its address. You would use the latter, for example, to calculate the base address of an array-valued expression before subscripting it. Whoever specifies the code generator would ensure that it exports these two functions. Everybody would then be able to call them from anywhere in the compiler.
Excel's equivalents to value and address are value and cell address. So why doesn't Excel have an "evaluate to cell address" function? And if it does, why didn't the data-validation implementor call it?
It isn't that cells on
different sheets from the menu are impossible to
use. I say this because Excel can be fooled
into using them. You define an identifier
such as
MyOptions
that refers to 'Task Metadata'!A2:A8. (This
is what Excel knows as a "named range".) You
can then type it into the "Source" field and
everything will just work. Though because users can delete
and add cells at any time, care is needed to make
sure the named range always points where it
should. Mistargeted named ranges are a common cause of errors.
I don't spend my time flaming Microsoft. I run Windows; and I am happy to believe a friend who once kept a Linux-resource Web site but who has abandoned Linux for Windows, when he tells me Windows is simpler to use, has better graphics, has more software written for it, and is easier to install because you don't have to fight through a junkyard of incompatible and complicated distributions, nor fritter away time hunting for drivers. But I don't see any excuse for the non-generality of Excel's data validation, or that the way round it makes your spreadsheet less safe. Nor do I see an excuse for other non-generalities, such as that when putting an array formula into a cell from a Visual Basic for Applications routine, the formula can be at most 255 characters. On the 22nd of September, the BBC announced that Bill Gates was the richest person in the US. More pertinently, because my Excel is Excel 2003, the BBC told us he has been the richest person in the US for the 17th year in a row. To design orthogonal languages takes time, because you have to consider lots of interactions. But Bill's programmers don't have to scrimp and sweat like the rest of us cash-flow-haunted slaves. They can afford the luxury of taking their time, coding calmly, and turning out an elegant and orthogonal design. Why spoil the ship for a ha'p'orth of tar?
g-c-c, g-c-c,
see how it runs, see how it runs,
it ran all over the swap device,
they freed all its ram with a kill -9,
did you ever see such a thrash in your life
as g-c-c
This is one of the songs from Petter's Computer Science Songbook, a collection by Petter Reinholdtsen. Many of them, Petter says, appeared on Usenet; lots were found at www.poppyfields.net/filks/ ("overflowing with parodies of popular songs, set with techy lyrics") and www.netspace.org/~dmacks/internet-songbook/ (now defunct). I found the songbook accidentally while Googling for information about C. As the first song in the book advises:
When I find my code in tons of trouble,
Friends and colleagues come to me,
Speaking words of wisdom:
"Write in C."
As the deadline fast approaches,
And bugs are all that I can see,
Somewhere, someone whispers
"Write in C."
The main page for Petter's Songbook links to versions in PDF and PostScript. It also links to #tut og hyl, which appears to be a Trondheim singing group devoted to songs about computing and science. They have a list of songs at www.pvv.org/~raaness/tut_og_hyl/root.php . Some are in Norwegian, and some aren't. Indeed, I don't know what the middle part of Demona's Java Jive is in. Can anyone enlighten me?
Here's a joke from www.computerjokes.net. It's spot on, especially the bit about lining up images. Though in my case, the problem usually comes with GIMP and invisible layers.
Download a piece of Web authoring software — 20 minutes.
Think about what you want to write on your Web page — 6 weeks.
Download the same piece of Web authoring software, because they have released 3 new versions since the first time you downloaded it — 20 minutes.
Decide to just steal some images and awards to put on your site — 1 minute.
Visit sites to find images and awards, find 5 of them that you like — 4 days.
Run setup of your Web authoring software. After it fails, download it again — 25 minutes.
Run setup again, boot the software, click all toolbar buttons to see what they do — 15 minutes.
View the source of others' pages, steal some, change a few words here and there — 4 hours.
Preview your Web page using the Web Authoring software — 1 minute.
Try to horizontally line up two related images — 6 hours.
Remove one of the images — 10 seconds.
Set the text's font color to the same color as your background, wonder why all your text is gone — 4 hours.
Download a counter from your ISP — 4 minutes.
Try to figure out why your counter reads "You are visitor number -16.3 E10" — 3 hours.
Put 4 blank lines between two lines of text — 8 hours.
Fine-tune the text, then prepare to load your Web page on your ISP — 40 minutes.
Accidentally delete your complete web page — 1 second.
Recreate your web page — 2 days.
Try to figure out how to load your Web page onto your ISP's server — 3 weeks.
Call a patient friend to find out about FTP — 30 minutes.
Download FTP software — 10 minutes.
Call your friend again — 15 minutes.
Upload your web page to your ISP's server — 10 minutes.
Connect to your site on the web — 1 minute.
Repeat any and all of the previous steps — eternity.
I heard this on the BBC's Front Row programme, as they were interviewing Steven Moffat, the latest head writer for Doctor Who:
The sonic screwdriver, for example, can do anything you want. Unless it's really vital to the plot, in which case it can't! Then he either forgets it or leaves it in the TARDIS, or there's a deadlock or a triple deadlock or something. He can never get through a door that's really important!So now you know how to write a science fiction story. Real hardware, of course, never behaves like this.
