The Lair of the Pythia

Revised at updated: Feb/28/07

Because NativeExcel doesn’t yet handle reading range names I’ve been stuck with still using OLE in my application. Range names are pretty much essential if you are reading spreadsheets, because there’s generally no guarantee how much data you’ll be collecting or where it was put. But now I have a solution, thanks to Marco Cantu.
Read the rest of this entry »

OK, now that it looks like the upgrade path issue is on its way to being sorted, let’s talk about something happier. I have a third party library that I’m very pleased with.
Read the rest of this entry »

I’ve been quiet over the last few days as I’ve been busy coding and haven’t had time to write about it. However, in the process I did find something that may be of interest.

I do a lot of passing data back and fore between my software and Excel. I’ll hopefully write a lot more about this in future, but if anyone is interested Deborah Pate’s pages are a good place to start.

Anyway, as you probably know, OLE is horribly slow, so you need to minimize the number of calls you make. One of the ways you can do this is to transfer an entire range of cells at once, because then you just have one call, not one per cell.

When you do this, the cells that you transfer are in the form of a variant array. That’s normally not a problem. You can easily find out how big the array is and process it. I’m generally picking up a range name and grabbing the range it refers to. But beware, because in the rare case of the range you are transferring being a single cell in size, the variant that you get is no longer an array, it is a single variable, and if you try to refer to it as an array you’ll get an exception.

Variants are wonderful things, but they do also open up the possibility of coding errors like the above example that only show up with particular configurations of the data you are processing, and that can be very hard to test for in advance.