XLL+ Class Library (6.3)

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.

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.