Comparison of CXlOper and CXlArray

It is not always clear which of the Excel data types to use for passing arguments to add-in functions. The table below summarises the various limitations and features of each type.

Item	CXlOper	CXlOper	CXlArray
Passes ranges	By value	By reference	By value
Can contain mixed types	Yes	Yes	No; numeric only
Empty cells	Optionally truncated by input routines	Optionally truncated by input routines	Converted to zeroes by Excel
Pass range references	No	Yes	No
Pass entire columns (e.g. A:A or A:D)	No	Passes only the first 16384 rows	No
Maximum number of cells (Excel 95)	5458	unlimited (if passed by reference); 5458 (if passed by value)	5458
Maximum number of rows (Excel 95)	5458 / number of columns	16384	5458 / number of columns
Maximum number of cells (Excel 97 and above)	limited by available memory	unlimited	limited by available memory
Maximum number of rows (Excel 97 and above) More...	65535	65535	65535

Passing very large arrays

If you pass a very large range as an argument to an add-in function, you should consider the following issues.

If you use a CXlOper by value or a CXlArray to pass the data, Excel 95 may simply reject the argument if it is too large, and consequently not call the add-in function. The cell containing the function call will display #VALUE!.
If your add-in function only looks at a sub-set of the cells in the range, then using a CXlOper will be most efficient. Since only a reference is passed to the add-in, your code can use CXlOper::Coerce() to establish the values of the various sub-sets, and avoid wasting time resolving the whole array.

It is therefore best generally to use a CXlOper reference type to pass very large arrays.

You can find a detailed analysis of large array passing in the demo project, ArgSizes and its test sheet ArgSizes.xls.

Special considerations for Excel 95

The size limitations of CXlArray and COper types are particularly stringent under Excel 95. If you need to support very large array inputs under Excel 95, you should always use the CXlOper type to pass the argument and then call the ReadMatrix() method to extract the data. CXlOper::ReadMatrix() contains code to retrieve the data in chunks from Excel and then put it all back together in the output matrix.