"agarwaldvk " wrote...
I have written a custom function (which I eventually want to make as an
AddIn) - one of the argument is a range reference. Seems to work ok
when the referred ranges are in any of the open workbooks. But when the
referred workbook is closed, then, it comes up with an error probably
indicating that it is not able to access the referred range. I can't
understand why. However, all Excel built-in functions, e.g in the SUM()
function are able to do just that.

It's not important that you know why. It's only necessary that you know that
this is so. You can't reference closed workbooks except using literal
external reference links, like

'C:\foo\[bar.xls]Junk'!$X$99

in worksheet cell formulas or using the ExecuteExcel4Macro method of the
Application class, and that won't work in functions called as udfs. The only
work-around is creating another Application instance, and calling the
ExecuteExcel4Macro method in that instance.

Also, if you used the same reference syntax in your function as you use in
SUM, you'd find they both either work or both return the same error.

We know that when the referred range is in a closed w/b, the formula
bar shows the full path of the referred range and the referred value is
read/accessed by the built-in function to give the correct output
value. In my case, it doesn't. Can someone help?
....
I access the range in the closed workbook by using the VBA statements
of the like :-

tempvar =
workbooks(referredbooknamewithfullpath).worksheet s(referredsheet
name).cells(rowref, colref).value

The Workbooks collection *ONLY* contains *OPEN* workbooks. If the workbook
is closed, it's not in the Workbooks collection, and there's no way to
reference it within the Excel object model.

This is part of the formula in the formula bar :-

,'D:\Deepak\[Book2.xls]Sheet1'!$B$21:$E$49,
....

In the formula bar this resolves to the values in that 'range' returned as
an *ARRAY*. Strictly speaking, it's not a range, and there are no range
objects in closed files. It's an object oriented version of a tree falling
in a forest with no one around to hear.

There is *NO* equivalent in VBA to this cell formula syntax for references
into closed workbooks. There should be, but there just isn't.

See

http://j-walk.com/ss/excel/tips/tip82.htm