Range as Variant Array

FLX TMS FlexCel for VCL & FMX
1

Hi Adrian


Is there some way of getting a reference to the values in a range directly as a variant array? I need a variant array to pass to a function, but would rather not copy all the cell values into a new array each time. 

Thanks, Bob
2

I forgot to mention that the function should have no knowledge of Flexcel so passing a reference to the xls isn't an option.


Thanks, Bob
3

Bob,


The thing is: internally FlexCel doesn't use variants anywhere, and cells aren't stored as arrays of variants. Variants make a poor match for Excel's cell types: They can hold a lot of datatypes Excel can't (like a bob) and they can't hold all the datatypes Excel can (like a formula, which you wouldn't be able to store in a variant)

Besides, variants use too much memory: FlexCel is optimized a lot to consume as little memory as possible, so for example a small integer like 57 will be stored in 4 bytes, while a variant would use 16. And there is all the OLE automation stuff like ref counting which also make variants slow.

So if we were going to implement a function that returns an array of variants, we would have to internally copy the cells from their internal representation to the variant array. There would be no performance gain against the function that you can code yourself. Since there isn't an array of variants anywhere in FlexCel, we can't give you a reference to it: we need to copy the values to give you the array. For the record, Excel does the same: it internally uses its own structures to store the cells, and when you ask for an array of variants it will just copy the values into that array.

And really, even if internally we had that array of variants, we would most likely have to give you a copy anyway. If we let you access the internals at that level, we would forever doom us to not change them. Storage of cells is a critical part of FlexCel: It changed a lot from FlexCel 5 to 6 (in order to reduce memory usage) and we have plans to change them again in FlexCel 7. If we made that structure public, we wouldn't be able to change it anymore.
4

Thanks Adrian - I thought that would probably be the case.


Kind regards, Bob