Accepting Range Parameters in UDFs

[yan]

First, express thanks to the author and the community.

Next, the main topic:

Assume there are two files, namely:  
"C:\Users\admin\Desktop\1.xlsx"  
"C:\Users\admin\Desktop\2.xlsx".  

I use my UDF in 1.xlsx. One of the parameters of this UDF is a reference to the A1:C3 range on Sheet1 of the 2.xlsx file. I have correctly set AllowReference=true.

In debugging, I checked the parameter and found that:  
- When 2.xlsx is open, the parameter value is [1.xlsx]Sheet1!$A$1:$C$3.  
- When 2.xlsx is closed, the function directly returns an error. I checked the input parameter value as 'C:\Users\admin\Desktop\[2.xlsx]Sheet1'!$A$1:$C$3. I suspect this is a bug.

My thought process:  
Set the ExcelReference structure as 'C:\Users\admin\Desktop\[2.xlsx]Sheet1'!$A$1:$C$3, which includes file path + file name + sheet name + cell range.

Another issue, while using the code section from Accepting Range Parameters in UDFs:

static Range ReferenceToRange(object xlInput)  
{  
    ExcelReference reference = (ExcelReference)xlInput;  
    Application app = (Application)ExcelDnaUtil.Application;  
 
    string sheetName = (string)XlCall.Excel(XlCall.xlSheetNm, reference);  
    int index = sheetName.LastIndexOf("]");  
    sheetName = sheetName.Substring(index + 1);  
    Worksheet ws = (Worksheet)app.Sheets[sheetName];  
    Range target = app.Range[ws.Cells[reference.RowFirst + 1, reference.ColumnFirst + 1], ws.Cells[reference.RowLast + 1, reference.ColumnLast + 1]];  
 
    for (int iInnerRef = 1; iInnerRef < reference.InnerReferences.Count; iInnerRef++)  
    {  
        ExcelReference innerRef = reference.InnerReferences[iInnerRef];  
        Range innerTarget = app.Range[ws.Cells[innerRef.RowFirst + 1, innerRef.ColumnFirst + 1], ws.Cells[innerRef.RowLast + 1, innerRef.ColumnLast + 1]];  
        target = app.Union(target, innerTarget);  
    }  
 
    return target;  
}

I found that Application app = (Application)ExcelDnaUtil.Application; prevents the program from running.

[Govert van Drimmelen]

As far as I know, you can’t reference workbooks that are not open from a UDF like this.

This is an Excel limitation.

 

In which context are you running into trouble with ExcelDnaUtil.Application ?

Is this from a UDF, or otherwise?

Can you post a small example that I can try to run?

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/9bf1a2c3-44a0-4f29-bbac-077bf2c21d66n%40googlegroups.com.

[yan]

Once again, I'd like to express my gratitude to the author and all the friends in the community.
I have now found a solution to the problem.
1. Limit the references to the current workbook
2. Use the correct Microsoft.Office.Interop.Excel namespace