0

How do I find out the row the function is executed in? Just an example:

Function getRowNumber () as Integer
     getRowNumber = ???
End Function

I need this because I wrote a function that is supposed to return values from the same row (formular gets its own column and then calculates more than 300 different values for these 300+ rows). Or is there another way how I address values in the row?

BigBen
  • 46,229
  • 7
  • 24
  • 40
justuswolff
  • 61
  • 1
  • 7

1 Answers1

1

You can use the Application.Caller property in this case. Something like the following may help you.

Function Tester() As Long
    Dim o As Object
    Set o = Application.Caller
    
    Tester = o.Row
End Function

Perhaps a more structured approach would be to take a Range as a parameter to your function, and then use the row of that input parameter.

Function Tester(rng as Range) as Long
    Dim nRow as long

    nRow = rng.Row 
    'Do other stuff
End function
basodre
  • 5,720
  • 1
  • 15
  • 23
  • @BigBen I edited my post for `Long`. My takeaway from his post was that this was part of a bigger function call, and he wasn't actually writing a `UDF` to get the current row. I might be wrong, but that's how I interpreted it. – basodre Dec 14 '20 at 14:12
  • 1
    Thank you very much. Exactly what I was looking for! – justuswolff Dec 14 '20 at 14:22