2

There are many posts here on Stackoverflow and its related sites that center around the question of how a UDF can get ahold of the cell from which the UDF was called.

Many of the accepted answers involve the use of the Range object returned by Application.ThisCell. Several accepted answers include language like "you can use either Application.ThisCell or Application.Caller."

However, the documentation for Application.ThisCell (Application.ThisCell property (Excel)) contains this remark:

Users should not access properties or methods on the Range object when inside the user-defined function.

What does this mean? What is the point of providing a property that returns the cell from which the UDF is called if you're not supposed to use that property within the UDF? Can anyone shed more light on this remark? And... should I be using Application.Caller while actively avoiding using Application.ThisCell?

mbmast
  • 960
  • 11
  • 25
  • 1
    What actually are you trying to do ... i.e. how will you use `Application.ThisCell`? – BigBen Jan 11 '22 at 18:21
  • 1
    They were probably overly zealous with that warning. You're not supposed to [make changes](https://stackoverflow.com/a/3622544/11683) and some properties may be [unavailable](https://stackoverflow.com/q/6470263/11683), but otherwise you are free to use it. Even their suggested "cache the Range object for later use" is *using* the Range. – GSerg Jan 11 '22 at 18:31
  • @BigBen Generally, the UDF is invoked and then it pokes around nearby cells looking for stuff. It never attempts to modify anything in those other cells. So it gets the Cell from which it was called (currently, by Application.ThisCell) and then goes and navigates nearby cells from there. – mbmast Jan 11 '22 at 19:25
  • That doesn't seem to be problematic to me. – BigBen Jan 11 '22 at 19:27
  • 1
    FYI one difference between `Caller` and `ThisCell` is when a UDF is called from >1 cell using an array formula, `Caller` gets the whole range, whereas `ThisCell` only gets the first cell in the calling range. – Tim Williams Jan 11 '22 at 21:15

0 Answers0