set rng = Application.Caller

will identify the cell containing a UDF used in a worksheet.

--
Regards,
Tom Ogilvy

"Dennis Allen" wrote in message
...
That worked. Does passing the range also pass which worksheet the
function is executing on? I was thinking we might have to pass
some sort of javascript "this"...Dennis

"Ron Rosenfeld" wrote in message
...
On Mon, 13 Sep 2004 23:02:41 -0400, "Dennis Allen"

wrote:

Hi again. I'm stuck. Added Public Function LastPM(). Went and
highlighted all 400 sheets and added to cell B4=LastPM(). When I
go to the first sheet I see 8/4/2004. When I jump to the next sheet I
still see 8/4/2004. If I add an entry 9/1/2004, B4 will
display it, but jumping to the next sheet still displays 9/1/2004.

How can I get a VBA function to behave like an excel function? As I
said, I don't understand excel inline formulas very
well...Dennis

OK, I think it has to do with how cells are referenced and DIM'd in
Functions,
but I don't understand the logic well enough to really explain it. In
any
event, the following UDF seems to behave sensibly, and still searches
from the
bottom up, which should increase speed as the data gets larger.

You will have to specify the cell range of "y" 's in the function
argument, but
you can specify an entire column, if you wish.

So you would write =LastPM(A:A) for example. Or =LastPM(a11:a1000)

===========================
Function LastPM(rg As Range)
Application.Volatile
Dim i As Long
Dim NumRows As Long
Dim LastRow As Long, FirstRow As Long

NumRows = rg.Count
FirstRow = rg.Row

If rg.Cells(NumRows) = "" Then
LastRow = rg.Cells(NumRows).End(xlUp).Row - FirstRow + 1
Else
LastRow = NumRows
End If

For i = LastRow To 1 Step -1
If rg.Cells(i) = "y" Then
LastPM = rg.Cells(i, 2)
Exit Function
End If
Next i

End Function
==========================


--ron