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" <kronsell(nomorespam)@adslhome.dk> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> skrev i en meddelelse
news:C28C9545-1CF5-4CCD-B2DA-(E-Mail Removed)...
>
> 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" <(E-Mail Removed)> skrev i en meddelelse
news:(E-Mail Removed)...
> Function Test(arg As Range)
> Test = arg.Address
> End Function
>
> Mike F
>
> "Jan Kronsell" <kronsell(nomorespam)@adslhome.dk> wrote in message
> news:%(E-Mail Removed)...
>> 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
>>
>
>