Using VSTO2005SE, I created an automation add-in that contains a user-defined function.
For the sake of simplicity, let's assume the UDF takes a single cell address as its argument and then multiplies its value by 1000.
Public Function MyFunc(ByVal Range As Object) As Double Dim dblY As Double = CType(Range, Excel.Range).Value * 1000 Return dblY End Function
When entered into a worksheet cell (e.g., A1: =MyFunc(B1) ), the UDF obediently takes the value in B1 and returns it, times 1000. The UDF also works fine if the value shown in B1 originates from an external link to another workbook.
However, the UDF returns a #VALUE! error, if the external link is used as an argument within the function, while the other workbook is not open:
=MyFunc('X:\[ExtLinkSource.xls]Tabelle1'!$A1) --> #VALUE!
If the other workbook is open, the UDF throws no error:
=MyFunc([ExtLinkSource.xls]Tabelle1!$A1) --> returns correct result
Incidentally, this is also the standard behavior for some of Excel's built-in worksheet functions, such as OFFSET or INDIRECT - these are known to not work with external links, unless the other workbook is open, unlike SUM or INDEX.
Does anybody know how a user-defined function can adopt the behavior of the SUM function and the like, i.e. retrieving a cell value from a closed external workbook
I know that cell contents can be retrieved from a closed workbook using ADO.NET, but I'm looking for something not quite so clumsy.
