function with a range as a parameter

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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.
 
=vlookup() can retrieve data from a closed workbook.

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

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