How do I clip an ExcelReference against the UsedRange?

Question

I am using Excel-DNA to develop some UDF's in Excel. One of the arguments getting passed from Excel into my UDF is a range. The UDF works properly when a specific range is used such as "A1:C50". Below is a sample of my function definition:

[ExcelCommand()]
public static object CalcSMA(object[,] range, int num_points) {
    ...
}

However, I get an "Out Of Memory" error when entire column ranges are passed such as "A:C". I can avoid the error by setting the argument attribute AllowReference=true and change the argument type to object as in the example below:

[ExcelCommand()]
public static object CalcSMA([ExcelArgument("Range", AllowReference=true)]object range, int num_points) {
    ExcelReference xref = (ExcelReference)range;
    ...
}

But now I am stuck wondering how many rows are actually needed for the UDF. I could try iterating all of the rows in the worksheet, but this is highly inefficient. Is there a way to clip the ExcelReference (xref) against the used range? I would like to avoid making the function volatile (IsMacroType=true), but will do so if it is required.

Answer 1

In VBA (or COM) you can Intersect the Range parameter with the UsedRange of the Parent of the Range parameter. But in an XLL it is not straightforward to get the used range because the XLL interface does not provide a UsedRange method for a worksheet. So you have to use the COM interface (which is problematic from inside an XLL UDF). I built a routine that uses the AfterCalculate event to cache the used range for each worksheet.

There is some discussion of a way of doing this here https://fastexcel.wordpress.com/2014/09/26/getting-used-range-in-an-xll-udf-multi-threading-and-com/

Note that if you are willing to make your UDF a single-threaded macro-type UDF you could use the GETDOCUMENT(10) XLL api. But the pain may not be worth the gain.