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

Based on the recommendations of Charles and Govert, I ended up implementing the following:

public class UsedRangeCache 
{
    protected static Dictionary<IntPtr, ExcelReference> _usedRanges = new Dictionary<IntPtr, ExcelReference>();
    protected static Application _app;

    /// <summary>
    /// Call this method when the XLL is initialized
    /// </summary>
    public static void Initialize(Application app)
    {
        _app = app;
        for (int i = 0; i < app.Workbooks.Count; i++ )
        {
            app_WorkbookOpen(app.Workbooks[i + 1]);
        }
        app.WorkbookOpen += app_WorkbookOpen;
        app.WorkbookBeforeClose += app_WorkbookBeforeClose;
        app.AfterCalculate += app_AfterCalculate;
    }

    // Refresh references
    static void app_AfterCalculate()
    {
        for (int i = 0; i < _app.Workbooks.Count; i++)
        {
            UpdateCache(_app.Workbooks[i + 1]);
        }
    }

    // Remove references
    static void app_WorkbookBeforeClose(Workbook book, ref bool Cancel)
    {
        for (int i = 0; i < book.Worksheets.Count; i++)
        {
            Worksheet sheet = book.Worksheets[i + 1] as Worksheet;
            if (sheet != null)
            {
                ExcelReference xref = (ExcelReference)XlCall.Excel(XlCall.xlSheetId, sheet.Name);
                if (_usedRanges.ContainsKey(xref.SheetId))
                {
                    _usedRanges.Remove(xref.SheetId);
                }
            }
        }
    }

    // Create references
    static void app_WorkbookOpen(Workbook book)
    {
        UpdateCache(book);
    }

    // Update cache
    private static void UpdateCache(Workbook book)
    {
        for (int i = 0; i < book.Worksheets.Count; i++)
        {
            Worksheet sheet = book.Worksheets[i + 1] as Worksheet;
            if (sheet != null)
            {
                ExcelReference xref = (ExcelReference)XlCall.Excel(XlCall.xlSheetId, sheet.Name);
                ExcelReference xused = new ExcelReference(
                    sheet.UsedRange.Row,
                    sheet.UsedRange.Row + sheet.UsedRange.Rows.Count,
                    sheet.UsedRange.Column,
                    sheet.UsedRange.Column + sheet.UsedRange.Columns.Count,
                    xref.SheetId);

                if (_usedRanges.ContainsKey(xref.SheetId)) 
                { 
                    _usedRanges.Remove(xref.SheetId); 
                }
                _usedRanges.Add(xref.SheetId, xused);
            }
        }
    }


    /// <summary>
    /// Get used range
    /// </summary>
    public static ExcelReference GetUsedRange(ExcelReference xref)
    {
        ExcelReference ret = null; 
        _usedRanges.TryGetValue(xref.SheetId, out ret); 
        return ret;
    }
}

Answer 2

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.