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

We know that when the referred range is in a closed w/b, the formul
bar shows the full path of the referred range and the referred value i
read/accessed by the built-in function to give the correct outpu
value. In my case, it doesn't. Can someone help?

The following may help to get to the bottom of the problem tha I a
encountering. Could someone advise me as to what am I supposed to pas
in for the workbook and worksheet reference parameters to access th
referred range in the closed w/b?

I access the range in the closed workbook by using the VBA statement
of the like :-

tempvar
workbooks(referredbooknamewithfullpath).worksheets (referredshee
name).cells(rowref, colref).value

The references to the referred book and sheet names are obtained b
reading the formula as a text string and extracting the relevan
details.

Parts of the codes are shown below :-

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

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

Hence, the value in the variable 'referredbooknamewithpath' i
"D:\Deepak\Book2.xls" and
the value in the variable 'referredsheetname' is "sheet1"

In essence, I am trying to read the value in the cell :-

workbooks("D:\Deepak\Book2.xls").worksheets("sheet 1").cells(rowref
colref).value

I even tried to get this parameter values by using the
range.parent.parent.fullname - for full filename and
range.parent.name - for sheet name

Nothing helped.

Further, when the referred range is in a closed w/b, how can you ste
through the custom function code?


Deepak Agarwa

--
Message posted from http://www.ExcelForum.com

You received an answer from Harlan Grove.

--
Regards,
Tom Ogilvy

"agarwaldvk " wrote in message
...
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.

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?

The following may help to get to the bottom of the problem tha I am
encountering. Could someone advise me as to what am I supposed to pass
in for the workbook and worksheet reference parameters to access the
referred range in the closed w/b?

I access the range in the closed workbook by using the VBA statements
of the like :-

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

The references to the referred book and sheet names are obtained by
reading the formula as a text string and extracting the relevant
details.

Parts of the codes are shown below :-

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

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

Hence, the value in the variable 'referredbooknamewithpath' is
"D:\Deepak\Book2.xls" and
the value in the variable 'referredsheetname' is "sheet1"

In essence, I am trying to read the value in the cell :-

workbooks("D:\Deepak\Book2.xls").worksheets("sheet 1").cells(rowref,
colref).value

I even tried to get this parameter values by using the
range.parent.parent.fullname - for full filename and
range.parent.name - for sheet name

Nothing helped.

Further, when the referred range is in a closed w/b, how can you step
through the custom function code?


Deepak Agarwal


---
Message posted from http://www.ExcelForum.com/

Where is the answer???????????

Deepa

--
Message posted from http://www.ExcelForum.com

Check your post in Google.


Charle

--
Message posted from http://www.ExcelForum.com

how do I do that

--
Message posted from http://www.ExcelForum.com

this is the site.

http://groups.google.com/groups?hl=e...el.programming

You can search for your post there.



Here's the post.

It's not important that you know why. It's only necessary that you kno
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 o
the
Application class, and that won't work in functions called as udfs. Th
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 us
in
SUM, you'd find they both either work or both return the same error.

Charle

--
Message posted from http://www.ExcelForum.com

"Charles " wrote...
this is the site.

http://google.com/groups?group=micro...el.programming

You can search for your post there.
....

Why not just give him the message ID?

http://google.com/groups?as_umsgid=u...gp13.phx .gbl

Anyway, it appears the OP needs to figure out how to page to previous
threads in the excelforum interface.