Many thanks for the replies.

Charles Williams nailed it. Very simple. My example was just so I could see
how to do it and bears no relation to my actual problem which is now fully
solved. I just couldn't see a 2 line way of getting the actual column of a
defined range that is passed as a parameter.

Jim Cone. Looks clever but I couldn't quite figure it out. Unless the sales
and costs are defined in the function, how can you get them without passing
them as function parameters? I can easily define them in the function but
that limits the function. I wanted a generic way of getting returning the
value from a row in the same column as the cell containing the function -
mimicking the implicit intersection in Excel functions.

In general, the other answer was far too complex and requires the location
of the rows to be set. What if the sales is on Row 20 and the costs is on row
50. Who knows where they are and who cares. The key is that they are in the
same column and that is what Charles returned.

Many thanks to all contributors though. Although there are always multiple
ways of achieving the same goal, the simple generic two liner is most
probably the best solution. I already have a complex way and needed a simple
way.



"James Buist" wrote:

I use defined names in all my workbooks. Sometimes i need to pass a cell
value to a vba function. To do this to an excel function I would just pass a
one dimensional range like an entire column or row and excel will compute for
the value in the same row or column as the function like Gross Profit = Sales
- Cost of Sales and each of those is defined as an entire row.

Now if I do this to a vba function it will not interpret the range as a
cell. I deed to pass it the exact cell reference. I do this just now by using
the index function. However, I'd like to do it entirely in vba and can't find
a simple way to return just the value (not the reference) in the cell in the
same row as the application caller. I'd have to use the application caller
function to return the address of the cell containing the vba function, then
compute the address of the cell in the adjacent row to get its value and used
that. Surely there is an easier way.

Any help would be much appreciated. An example like the one I used above
would be great, i.e. just add two numbers together using add in a vba
function accepting the sales and costs as range inputs.

JMB