Skip to main content

Excel ↔ Numbers roundtripping

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: , , , , , , , , , , , , ,


Popular posts from this blog

Getting It Done?

As someone who has worked at many jobs in which it is part of the job description to be interrupted incessantly, whilst at the same time having, as part of the same job, work that needs careful planning, reflection and sustained concentration to execute correctly, I've had many problems with the usual time-management approaches. Most of them seem to have been conceived in some middle-management Utopia back in the '50s, a place where everyone has an office with a door, the closure of which was sacrosanct; a time before email, pagers, cellphones and Blackberries; a society where "getting up in someone's face" was a social crime rather than a standard business strategy.

That said, you can't let your life be run by random events and dropping everything to work on whatever the customer who shouts the loudest wants. You need some sort of system, and I've found the system popularized by David Allen, which he calls by the arcane, obscure name of Getting Things Don…

Folk Ergonomics: or, it all Fitts

When I was a poor civil servant (plus ça change) and part-time graduate student I longed to own a Mac. I’d read everything available about them, and nothing I read did anything to dissuade me. What I wanted, as well as the crisp, typographic display and the integration between the applications, was the windowing system. I already knew, somehow, that a Proper Computer™ would be able to show more than one program on the screen at once and let you move between them. Lacking the funds to buy the current model Apple was offering, a Mac SE, I made do with an Atari ST 520 STFM. This was a strange machine with a dual personality:a games machine with aspirations to being a workstation, and which used the non-broken version of Digital Research’s GEM windowing environment. This system had been shamelessly copied from the Mac interface, so much so that Apple sued Digital Research and got an injunction that prevented later versions of GEM (used on DOS machines, notably those from Amstrad) from us…