NickHK
12/11/2006 2:03:00 AM
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" <chip@cpearson.com> wrote in message
news:%23VA7tdvGHHA.1276@TK2MSFTNGP04.phx.gbl...
>
> 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" <TungCheWah@Invalid.com> wrote in message
> news:%234mYDLrGHHA.3616@TK2MSFTNGP02.phx.gbl...
> > 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" <newsgroups@joerg-lensing.de> wrote in message
> > news:1165571355.383891.160520@80g2000cwy.googlegroups.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
> >
> >
>
>