return the current row number for use in a formula
 

How do I return the row number of the current (not necessarily active) cell
for use in a custom formula? =Row() in a cell returns that cell's row number,
but I can't figure out how to use that in a custom formula. For example, code
such as this doesn't work because Excel gives me a compile error on "=Row()".

Public Function myFunction()
Dim Variable As Integer, Result As Integer
Variable = Row()
Result = Variable + 10
myFunction = Result
End Function

Thanks very much.

return the current row number for use in a formula
 

Option Explicit
Public Function myFunction() As Long
Dim Variable As Long
Dim Result As Long
Variable = application.caller.Row
Result = Variable + 10
myFunction = Result
End Function

or just:

Option Explicit
Public Function myFunction() As Long
myFunction = application.caller.row + 10
End Function



CuriousMark wrote:

How do I return the row number of the current (not necessarily active) cell
for use in a custom formula? =Row() in a cell returns that cell's row number,
but I can't figure out how to use that in a custom formula. For example, code
such as this doesn't work because Excel gives me a compile error on "=Row()".

Public Function myFunction()
Dim Variable As Integer, Result As Integer
Variable = Row()
Result = Variable + 10
myFunction = Result
End Function

Thanks very much.


--

Dave Peterson

return the current row number for use in a formula
 

Thanks very much Dave. I tried to find the answer in the Discussion groups,
in my books and on-line, and it was frustratingly difficult. I knew it would
be simple.

"Dave Peterson" wrote:

Option Explicit
Public Function myFunction() As Long
Dim Variable As Long
Dim Result As Long
Variable = application.caller.Row
Result = Variable + 10
myFunction = Result
End Function

or just:

Option Explicit
Public Function myFunction() As Long
myFunction = application.caller.row + 10
End Function



CuriousMark wrote:

How do I return the row number of the current (not necessarily active) cell
for use in a custom formula? =Row() in a cell returns that cell's row number,
but I can't figure out how to use that in a custom formula. For example, code
such as this doesn't work because Excel gives me a compile error on "=Row()".

Public Function myFunction()
Dim Variable As Integer, Result As Integer
Variable = Row()
Result = Variable + 10
myFunction = Result
End Function

Thanks very much.


--

Dave Peterson