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

In the land of grey and pink

I’m pretty happy with my current-model MacBook Pro 15”. I got the Santa Rosa based 2.2 GHz model via a very good education price deal (I work in a university), and it’s a great all round machine. Because I take lots of photographs , I have calibrated the MacBook Pro’s display as well as my 23” Cinema Display so that I get true colors when preparing photos for web or print. I used the Spyder2 Express from datacolor, which is a great little gadget. The idea is very simple: you use the supplied colorimeter to look at your monitor while an application displays swatches of known colors. The colorimeter reports the actual color appearing on the monitor, and the controlling application creates a table that records the difference between the RGB values in the swatches and the ones recorded by the colorimeter. This is known as a device color profile and the Mac can use its generic factory-shipped ones, or the more accurate ones you create for your own displays with the Spyder2 or similar devic
3 comments
Read more

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