Hi all,

I am trying to write a function that uses a range as a parameter (see below)

Function Param(MyRange As Range, Aline As Integer) As Integer
Param = 5
End Function

The problem is that when the range specifies cells on a closed workbook,
the function dispays an error (#VALUE)
If the range corresponds to an open workbook there is no error.

Is it possible to get back a Range on a closed workbook (like the function
VLOOKUP does) ?
if yes how ?

Thanks

Jim.

(I am using Excel 2000)

VBA doesn't support referencing closed workbooks.

John Walkenbach documents a method useing the xl4 macro in VBA:

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

I have found it quicker to put a linking formula in a cell to retrieve the
value, then clear it out if necessary. (this wouldn't work in a UDF used in
a worksheet)

--
Regards,
Tom Ogilvy


"nox" wrote:

Hi all,

I am trying to write a function that uses a range as a parameter (see below)

Function Param(MyRange As Range, Aline As Integer) As Integer
Param = 5
End Function

The problem is that when the range specifies cells on a closed workbook,
the function dispays an error (#VALUE)
If the range corresponds to an open workbook there is no error.

Is it possible to get back a Range on a closed workbook (like the function
VLOOKUP does) ?
if yes how ?

Thanks

Jim.

(I am using Excel 2000)

Any function that references a closed workbook (even vlookup) needs to have
the workbook open to get the data. If you need it to work every time, you
probably ought to build a UDF that opens the workbook in question, accesses
the data and then closes the workbook. Of course, every time the workbook is
calculated, all of the external workbooks will need to be opened/closed and
it could take some time.
--
HTH,
Barb Reinhardt



"nox" wrote:

Hi all,

I am trying to write a function that uses a range as a parameter (see below)

Function Param(MyRange As Range, Aline As Integer) As Integer
Param = 5
End Function

The problem is that when the range specifies cells on a closed workbook,
the function dispays an error (#VALUE)
If the range corresponds to an open workbook there is no error.

Is it possible to get back a Range on a closed workbook (like the function
VLOOKUP does) ?
if yes how ?

Thanks

Jim.

(I am using Excel 2000)

=vlookup() can retrieve data from a closed workbook.

Lots of functions can. But you're right, some functions can't (like
=indirect(), =sumif(), =countif()).

Barb Reinhardt wrote:

Any function that references a closed workbook (even vlookup) needs to have
the workbook open to get the data. If you need it to work every time, you
probably ought to build a UDF that opens the workbook in question, accesses
the data and then closes the workbook. Of course, every time the workbook is
calculated, all of the external workbooks will need to be opened/closed and
it could take some time.
--
HTH,
Barb Reinhardt

"nox" wrote:

Hi all,

I am trying to write a function that uses a range as a parameter (see below)

Function Param(MyRange As Range, Aline As Integer) As Integer
Param = 5
End Function

The problem is that when the range specifies cells on a closed workbook,
the function dispays an error (#VALUE)
If the range corresponds to an open workbook there is no error.

Is it possible to get back a Range on a closed workbook (like the function
VLOOKUP does) ?
if yes how ?

Thanks

Jim.

(I am using Excel 2000)



--

Dave Peterson

Thanks Tom,

It's a pity that VBA does not support referencing closed workbook while
VLOOKUPS does.

"Tom Ogilvy" wrote:

VBA doesn't support referencing closed workbooks.

John Walkenbach documents a method useing the xl4 macro in VBA:

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

I have found it quicker to put a linking formula in a cell to retrieve the
value, then clear it out if necessary. (this wouldn't work in a UDF used in
a worksheet)

--
Regards,
Tom Ogilvy