Solved: User defined function and copy paste

[mikke3141]

When I enter my example user defined function to the cell A1 like =xax() and copy it to other cells the function does not automatically calculate all the values for the function, but gives the same value to alle the cells that get the copied function. I must go from cell to cell and press F2+Enter. What can I do so that the function would always calculate the value eventhough the function has been pasted to the cell.


Function xax()
'example
xax = ActiveCell.Row
End Function

[p45cal]

Given that you don't want
=ROW()
you would expect the formula to give the same result wherever it is in the spreadsheet since it's giving the row of the active cell, wherever that happens to be at the time of the calculation. To make it respond to presses of F9 you could add Application.Volatile to the code.

Alternatively, ditch the Application.Volatile and put this in the sheet's code module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Calculate
End Subso now as you move around the sheet, all cells with that function will update to show the active cell's row.



When I enter my example user defined function to the cell A1 like =xax() and copy it to other cells the function does not automatically calculate all the values for the function, but gives the same value to alle the cells that get the copied function. I must go from cell to cell and press F2+Enter. What can I do so that the function would always calculate the value eventhough the function has been pasted to the cell.


Function xax()
'example
xax = ActiveCell.Row
End Function

[mikerickson]

Since there are no arguments, why should that function return different values when put in different cells?

If you want the row of the cell that holds the function (a ROW() emulator), you could use


Function xax() As Long
If TypeName(Application.Caller) = "Range" Then
xax = Application.Caller.Row
End If
End Function

[mikke3141]

I noticed the error. I used in the function command activecell.row, which caused the error. It should of course have been application.caller.row. Thank you mikerickson for the idea.