Monday, August 13, 2007

Frequent spreadsheeters are used to constructing formulae by pointing with the arrow keys. It's part of the spreadsheet conditioned reflex set to whack equals, arrow left, hit star, point left left, whack enter, to construct a typical "cost times quantity" cell formula. This has been braided into the finger muscles and the spinal cord of spreadsheet workers since VisiCalc, through Lotus, SuperCalc, Multiplan and Excel. Numbers retains the equals operator to introduce a formula (much more intuitive than Lotus's plus sign and less confusing than SuperCalc's complete lack of formula signifier) but forces you to mouse around the relevant cells to assemble your formula. It's a bit of a drag, honestly. I hope this becomes something the end user can change.

Technorati Tags: , ,

Friday, August 10, 2007
I’ve just checked a couple of things about round-tripping spreadsheet documents between Excel and Numbers. Firstly, Numbers adds a ‘cover sheet’ showing the Numbers table name and which exported Excel worksheet it relates to (see figure). This isn’t a showstopper but it could confuse people with whom you’re exchanging documents. Update: Another blogger user has discovered the preference to turn this behavior off.

Secondly, Excel VBA macros are not preserved by a round trip from Excel into Numbers and back to Excel again. The entire VBA OLE stream within the file is just removed in any Excel file exported from Numbers. This is something to be aware of when collaborating with Office users who make any use of Excel macros.

That prompts the question “how can you have a serious spreadsheet that doesn’t have a macro language?” but honestly, I’m very far from convinced we really need one. I’ve seen countless examples of Excel sheets with macros that could be very easily replaced by a couple of ranges using some of the many hundreds of powerful Excel worksheet formulæ, if only the sheet’s constructor knew them. In part I blame Microsoft’s switch to online documentation for this ignorance of the true power of the app. I learned Excel by taking the thick, brown-and-white (Mac) or blue-and-white (Windows) perfect-bound Function Reference book home and reading it from cover to cover, so I got a complete picture of what sorts of functions were available and how to use them. It’s so much harder to get a good overview of the capabilities of the application by browsing the unattractive online help and reference. Excel’s calculation engine is optimized for ripping through huge arrays of formulæ like a shark is for swimming and catching prey. It does incredibly fast matrix operations. Asking it to sit around and wait while some large interpreted, procedural script runs is giving it an enormous handicap, and in most cases a totally unnecessary one.

Anyway, it barely matters now, as - like I’ve made much of here before - Microsoft can’t devote the necessary resources to bringing the VBA engine to Intel Macs. You can still write scripts in AppleScript that access and animate the Office VBA object model, but their code statements are horribly verbose and hard to parse compared to their (scarcely elegant) VBA equivalents. If you’re interested, April 2007’s MacTech published a supplement on converting Excel VBA to AppleScript. I suspect a lot of spreadsheet users will move to a more formula-based spreadsheeting model as time passes, and to me, that’s a good thing.

Technorati Tags: , , , , , , , , , , , , ,