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

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

Mike F

"Jan Kronsell" wrote in message
...
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

"Jan Kronsell" wrote:

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

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

Jan

"Mike" skrev i en meddelelse
...

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" wrote:

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

Thank you. It works great.

Jan

"Mike Fogleman" skrev i en meddelelse
m...
Function Test(arg As Range)
Test = arg.Address
End Function

Mike F

"Jan Kronsell" wrote in message
...
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