User defined function: Address where UDF is used  
Author Message
audeyt





PostPosted: Fri Dec 08 03:54:37 CST 2006 Top

Excel Programming >> User defined function: Address where UDF is used

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 =3D 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=F6rg

Excel63  
 
 
Niek





PostPosted: Fri Dec 08 03:54:37 CST 2006 Top

Excel Programming >> User defined function: Address where UDF is used 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





PostPosted: Fri Dec 08 04:22:35 CST 2006 Top

Excel Programming >> User defined function: Address where UDF is used 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





PostPosted: Fri Dec 08 12:33:56 CST 2006 Top

Excel Programming >> User defined function: Address where UDF is used
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)





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


 
 
NickHK





PostPosted: Sun Dec 10 20:02:56 CST 2006 Top

Excel Programming >> User defined function: Address where UDF is used 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



>
> 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)
>
>
>


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