creating function (vba) with range arguments

[Fredouille]

Hi,

I need to create a new function in VBA (this function is similar to the
vlookup one). This new function will have a range of cells as arguments.

public function myFunction (x as Range) as String

As a formula in one cell, I have
=myFunction('D:\anAnotherWb.xls]aSheet'!$A$1:$T$59)

I have the right result when x points on a range of cells in an already
opened workbook.
If x points to an existing workbook not already opened, I have a value error.

If you run the vlookup function, this problem do not appear. I'm pretty sure
the matter is the function declaration.


What would you do to solve my problem ?


Thx

 

[Bob Phillips]

Why do you need this function, what is wrong with using VLOOKUP from the
worksheet?

 

[Fredouille]

Hi,

The problem is that I have to work with table structures defined years ago :
the table is a correspondance between voyage, ports & call dates defined as
follow :
voyage1, point 1, point 5, point 2, point 3, point 4
'call dates', date1-1, date 1-5, date 1-2, date 1-3, date 1-4
voyage2, point 3, point 5, point 1, point 4, point 2
'call dates', date2-3, date 2-5, date 2-1, date 2-4, date 2-2

This function has as arguments :
a voyage : v (String)
a port : p (String)
the table range : 'D:\anAnotherWb.xls]aSheet'!$A$1:$T$59
And returns the matching date.

My function :
1 - finds the row nb of the voyage v
2 - finds the column nb of the port p (corresponding with the voyage v)
3 - get the date with the row nb & col nb.

I can't use the vllokup function because the port order changes for every
voyage.


I would like to do how Mr Excel did to declare his vlookup function & how he
declared its arguments. I thaught...if only a kind of javadoc for vba would
exist...

Thx

Why do you need this function, what is wrong with using VLOOKUP from the
worksheet?

--
HTH

Bob Phillips

Hi,

I need to create a new function in VBA (this function is similar to the
vlookup one). This new function will have a range of cells as arguments.

public function myFunction (x as Range) as String

As a formula in one cell, I have
=myFunction('D:\anAnotherWb.xls]aSheet'!$A$1:$T$59)

I have the right result when x points on a range of cells in an already
opened workbook.
If x points to an existing workbook not already opened, I have a value error.

If you run the vlookup function, this problem do not appear. I'm pretty sure
the matter is the function declaration.


What would you do to solve my problem ?


Thx