XlCall.Excel(XlCall.xlfReftext, xlref, true) causing ExcelDna.Integration.XlCallException

[Deepak Krishna]

My code goes like this

        private static Range ReferenceToRange(ExcelReference xlref)
        {
            Debug.WriteLine(xlref.RowFirst);
            Application app = (Application)ExcelDnaUtil.Application;
            string refText = (string)XlCall.Excel(XlCall.xlfReftext, xlref, true);
            Range range = app.get_Range(refText, Missing.Value);
            return app.get_Range(xlref);
        }

I first passed xlref like

ExcelReference xlref = (ExcelReference)XlCall.Excel(XlCall.xlfCaller); 

then like 

 ExcelReference xlref = new ExcelReference(1,1)

but it's throwing error at the line where we get refText

My .net version is 4 

[Govert van Drimmelen]

xlfReftext is a "Macro Sheets Only" function, so if you want to call it in a UDF you need to mark the UDF as [ExcelFunction(IsMacroType=true)] which alters the registration.

As an alternative which does not require marking your function as IsMacroType=true, you can get the sheet name with  XlCall.Excel(XlCall.xlSheetNm, xlref) and then build the rest of the reference yourself using the row and column info in the ExcelReference.

-Govert

[John Alexiou]

Here is the code that converts an ExcelReference to a Range, and it can be called from a UDF without errors

private static Range ToRange(ExcelReference reference)
{
    var xlApp = ExcelDnaUtil.Application as Application;
    var item = XlCall.Excel(XlCall.xlSheetNm, reference) as string;
    int index = item.LastIndexOf(']');
    item= item.Substring(index+1);
    var ws = xlApp.Sheets[item] as Worksheet;
    var target = xlApp.Range[
        ws.Cells[reference.RowFirst+1, reference.ColumnFirst+1],
        ws.Cells[reference.RowLast+1, reference.ColumnLast+1] ] as Range;
 
    return target;
}

As you can see, you have to call xlSheetNm that returns something like "[WorkbookFile.xls]Sheet1" and the name of the sheet must be parsed and used to get the worksheet reference. Finally, a range object is built from the table of cells in this worksheet.