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

Comments

Post a Comment

Popular posts from this blog

Alien User Interface Hell

I tend to like a movie to be playing while I'm working, preferably one I know well so I can ignore it. Today Alien was up on Netflix Instant. While going to make more tea I noticed the early scene where the Nostromo 's captain goes into the command area to commune with Mother. What the hell were the designers thinking when concocting the fake system user interface that is depicted in that scene? There's a million tiny status lights , all white, set into a white background. None of them has a readable label. The whole surface of the pod is encrusted with incomprehensible but significant miniature beacons. It's been frequently pointed out that some errors on our limited space missions so far have been tied back to ambiguous or confusing information displays in spacecraft cockpits. What evolutions did Ridley Scott expect to have happened in a few decades that would allow a standard human pilot to instantly discriminate one white light from ten thousand others and act on ...
1 comment
Read more

Calendar confusion

I finally got around to posting this as an error in iTunes: When calendars are synced between iCal and iPhone via iTunes, iTunes assigns arbitrary colors to the calendars on iPhone. These colors cannot be changed, and do not match the colors chosen in iCal. On occasion, iTunes will assign two calendars the same arbitrarily-chosen color, making them functionally indistinguishable on iPhone. This is terrible user interface design because users become accustomed to the 'meaning' of the color of the calendar and use it in recognition of the calendar layout. The mental 'wrench' involved in translating color recognition between two instances of the same calendar data imposes a unnecessary cognitive load on the user. Solution Allow the user to set the color of iPhone calendars. If the intention is not to allow the user control of the calendar color, for simplicity of implementation on iPhone, then the logical solution is to use the same color as specified in iCal. If the syn...
Post a Comment
Read more