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.

Re: Visual Studio Tools for Office Excel Automation: How to use an external link as an argument in a user-defined function?

Dennis Wallentin


See if the following blogpost can get You started:

No matter what, it will be a slow performance and therefore ADO.NET or better up classic ADO will not look so clumsy after all.