Excel VBA row calling cell on multi cell paste (Not ActiveCell.Row)

Question

I have a VBA script that I use the calling cell to do some processing. It works great when I only paste it into one cell but if I paste into multiple cells, ActiveCell is always the first selected cell in the range and so all the cells get the same value.

How do I get the row of the cell that the function is being called from?

Example VBA - (in one cell do =TestCallCell() then copy and paste into several cells)

        Function TestCallCell() As String
            curRow = ActiveCell.Row
            TestCallCell = curRow
        End Function

Scott Craner Scott Craner · Accepted Answer · 2017-05-25T19:01:34

You do not want to use ActiveCell in a UDF, as it will change as the Active Cell changes.

Use Application.Caller. It will use the cell from which the function is called and not the active one.

Function TestCallCell() As String
    curRow = Application.Caller.Row
    TestCallCell = curRow
End Function

Excel VBA row calling cell on multi cell paste (Not ActiveCell.Row)

1 Answers