Capture cell address in UDF

[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

 

[Mike Fogleman]

Function Test(arg As Range)
Test = arg.Address
End Function

Mike F

 

[Mike]

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

 

[Jan Kronsell]

Thank you, but I need a fucntion not a Sub.

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

 

[Jan Kronsell]

Thank you. It works great.

Jan

Function Test(arg As Range)
Test = arg.Address
End Function

Mike F