Hi NG,
how can I detect the address of a userdefined function within the code
of the function?

look at this example-function:

-------snip----------
1 Public Function myExcelFunction(myValue As Integer) As Integer
2
3 myExcelFunction = myValue * 20
4 Debug.Print ThisCell.Address
5
6 End Function

----------snip-------------

in line 4 I want to get the address of the cell the function is used.
But "ThisCell" is no valid Expression. Any tipps or hints?

Tx Jörg

Application.Caller.Address

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Joerg Lensing" wrote in message ups.com...
Hi NG,
how can I detect the address of a userdefined function within the code
of the function?

look at this example-function:

-------snip----------
1 Public Function myExcelFunction(myValue As Integer) As Integer
2
3 myExcelFunction = myValue * 20
4 Debug.Print ThisCell.Address
5
6 End Function

----------snip-------------

in line 4 I want to get the address of the cell the function is used.
But "ThisCell" is no valid Expression. Any tipps or hints?

Tx Jörg

Use
Check the help, you will see Application is required
ErrFunction = Application.ThisCell.Address

Or if you need support for XL2000 and earlier
ErrFunction = Application.Caller.Address

Although I have had some strange situations using this and the Help has some
warning of its use.

NickHK

"Joerg Lensing" wrote in message
ups.com...
Hi NG,
how can I detect the address of a userdefined function within the code
of the function?

look at this example-function:

-------snip----------
1 Public Function myExcelFunction(myValue As Integer) As Integer
2
3 myExcelFunction = myValue * 20
4 Debug.Print ThisCell.Address
5
6 End Function

----------snip-------------

in line 4 I want to get the address of the cell the function is used.
But "ThisCell" is no valid Expression. Any tipps or hints?

Tx Jörg

Application.Caller and Application.ThisCell behave differently when a
formula is array entered into a range of cells. For example,

Function Test() As String
Test = Application.Caller.Address
End Function

Function Test2() As String
Test2 = Application.ThisCell.Address
End Function

Array-enter =TEST() into A1:A3 and array-enter =TEST2() in C1:C3 and you'll
see the difference. Application.Caller returns what I would expect, while
ThisCell does not. More importantly, when array entered into a range of
cells, Application.Caller.Cells.Count return the correct number of cells in
the range array. Application.ThisCell.Cells.Count return 1, even when
array-entered into a range of cells.

I'm not sure what MS was trying to accomplish with the addition of ThisCell,
but I always use Application.Caller. ThisCell simply returns the wrong
answer for UDFs array-entered into a range of cells.



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"NickHK" wrote in message
...
Use
Check the help, you will see Application is required
ErrFunction = Application.ThisCell.Address

Or if you need support for XL2000 and earlier
ErrFunction = Application.Caller.Address

Although I have had some strange situations using this and the Help has
some
warning of its use.

NickHK

"Joerg Lensing" wrote in message
ups.com...
Hi NG,
how can I detect the address of a userdefined function within the code
of the function?

look at this example-function:

-------snip----------
1 Public Function myExcelFunction(myValue As Integer) As Integer
2
3 myExcelFunction = myValue * 20
4 Debug.Print ThisCell.Address
5
6 End Function

----------snip-------------

in line 4 I want to get the address of the cell the function is used.
But "ThisCell" is no valid Expression. Any tipps or hints?

Tx Jörg

Chip,
I was under the impression that ThisCell was equivalent to Caller (from a
worksheet), but as your example shows, it behaves differently.
The Help is certainly not as explicit on ThisCell as Caller regarding this.

NickHK

"Chip Pearson" wrote in message
...

Application.Caller and Application.ThisCell behave differently when a
formula is array entered into a range of cells. For example,

Function Test() As String
Test = Application.Caller.Address
End Function

Function Test2() As String
Test2 = Application.ThisCell.Address
End Function

Array-enter =TEST() into A1:A3 and array-enter =TEST2() in C1:C3 and
you'll
see the difference. Application.Caller returns what I would expect, while
ThisCell does not. More importantly, when array entered into a range of
cells, Application.Caller.Cells.Count return the correct number of cells
in
the range array. Application.ThisCell.Cells.Count return 1, even when
array-entered into a range of cells.

I'm not sure what MS was trying to accomplish with the addition of
ThisCell,
but I always use Application.Caller. ThisCell simply returns the wrong
answer for UDFs array-entered into a range of cells.



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"NickHK" wrote in message
...
Use
Check the help, you will see Application is required
ErrFunction = Application.ThisCell.Address

Or if you need support for XL2000 and earlier
ErrFunction = Application.Caller.Address

Although I have had some strange situations using this and the Help has
some
warning of its use.

NickHK

"Joerg Lensing" wrote in message
ups.com...
Hi NG,
how can I detect the address of a userdefined function within the code
of the function?

look at this example-function:

-------snip----------
1 Public Function myExcelFunction(myValue As Integer) As Integer
2
3 myExcelFunction = myValue * 20
4 Debug.Print ThisCell.Address
5
6 End Function

----------snip-------------

in line 4 I want to get the address of the cell the function is used.
But "ThisCell" is no valid Expression. Any tipps or hints?

Tx Jörg