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.

1. Download a piece of Web authoring software — 20 minutes.

2. Think about what you want to write on your Web page — 6
   weeks.

3. Download the same piece of Web authoring software,
   because they have released 3 new versions since the first time you
   downloaded it — 20 minutes.

4. Decide to just steal some images and awards to put on
   your site — 1 minute.

5. Visit sites to find images and awards, find 5 of them
   that you like — 4 days.

6. Run setup of your Web authoring software. After it fails,
   download it again — 25 minutes.

7. Run setup again, boot the software, click all toolbar
   buttons to see what they do — 15 minutes.

8. View the source of others’ pages, steal some, change a
   few words here and there — 4 hours.

9. Preview your Web page using the Web Authoring software —
   1 minute.

10. Try to horizontally line up two related images — 6
    hours.

11. Remove one of the images — 10 seconds.

12. Set the text’s font color to the same color as your
    background, wonder why all your text is gone — 4 hours.

13. Download a counter from your ISP — 4 minutes.

14. Try to figure out why your counter reads “You are
    visitor number -16.3 E10” — 3 hours.

15. Put 4 blank lines between two lines of text — 8 hours.

16. Fine-tune the text, then prepare to load your Web page
    on your ISP — 40 minutes.

17. Accidentally delete your complete web page — 1 second.

18. Recreate your web page — 2 days.

19. Try to figure out how to load your Web page onto your
    ISP’s server — 3 weeks.

20. Call a patient friend to find out about FTP — 30
    minutes.

21. Download FTP software — 10 minutes.

22. Call your friend again — 15 minutes.

23. Upload your web page to your ISP’s server — 10 minutes.

24. Connect to your site on the web — 1 minute.

25. 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.