Capture cell address in UDF

  • Thread starter Thread starter Jan Kronsell
  • Start date Start date
J

Jan Kronsell

Hi NG!

Is there a way of capturing the cell address of an argument in a UDF in
stead of the value of the argument.

I have a UDF definition like

Function Test(arg)
....
End Function

when I use the function I type =test(A1) and i get the value of A1. But
what I like to captuire is the address A1

Is there any way to achieve this?

Jan
 
Put this into its own module
Function CPosition() As String
'Runs from sheet1 to trigger event
'To get it to work, enter this into the desired cell:
'=CPos()
Application.Volatile
CPosition = "Cursor is in " & ActiveCell.Address(False, False)
End Function

And put this into sheet1 module
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Worksheets("Sheet1").Range("A1").Formula = CPosition()

Me.Calculate
End Sub
 
Thank you, but I need a fucntion not a Sub.

Jan

Mike said:
Put this into its own module
Function CPosition() As String
'Runs from sheet1 to trigger event
'To get it to work, enter this into the desired cell:
'=CPos()
Application.Volatile
CPosition = "Cursor is in " & ActiveCell.Address(False, False)
End Function

And put this into sheet1 module
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Worksheets("Sheet1").Range("A1").Formula = CPosition()

Me.Calculate
End Sub
Click to expand...
 
Back
Top