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;
    }
}