Another way...
'--
Function Gross() As Double
Dim arr As Variant
arr = [Sales - Costs]
Gross = arr(Application.Caller.Column)
End Function
--
Jim Cone
Portland, Oregon USA



"James Buist"
wrote in message
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