User defined function: Address where UDF is used

[Joerg Lensing]

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

 

[Niek Otten]

Application.Caller.Address

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

 

[NickHK]

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

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 Pearson]

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]

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