2

Referring to answered Jun 27 '13 at 7:43 by totymedli and answered Apr 13 '16 at 0:53 by Meir Shachar

I do prefer the VBA solution although the OP did not ask for one. However, I am able to parse and understand most of the linked responses above, other than the line:

distance = Range(Application.Caller.Address).row - rng.row

It appears that the posters are using this construct to determine if the resultant range (or array?) is greater than zero length to avoid creation of an empty Variant return value. If so, I fail to see the need for this and am able to create useful output without references to the integer distance.

I have attempted my due diligence, however, the usually cryptic Microsoft reference was less than useful for me. If someone could please explain the use/nature of the Application.Caller.Address construct (even better - in this particular instance) I could better judge if I actually need to use it.

halfer
  • 19,824
  • 17
  • 99
  • 186
McScreech
  • 23
  • 3
  • `distance = Application.Caller.Row - rng.row` is more concise - there's no need to extract the Address and then turn it back into a Range... Note also you can use `Application.ThisCell` in place of `Application.Caller` – Tim Williams Oct 26 '18 at 16:21

1 Answers1

1

If you use Application.Caller.Address in a UDF (User Defined Function), it returns the address of the cell that contains the formula that is calling the UDF.

For example if cell B2 contains formula =listUnique(A1:A10) then Application.Caller.Address would return the address B$2$.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Thank you. Confirms that for my intent of using within a UserForm code module I can safely ignore/work-around the references to `distance`. Many thanx. – McScreech Oct 26 '18 at 12:39