1

I need to find out cell addresses which have called a specific function.

If my function is MyFunction (arg1, arg2), I should be able to find the cell addreses using name "MyFunction".

Please help me to find out what would be the most efficient way to do this.

Thank You

Carl Manaster
  • 39,912
  • 17
  • 102
  • 155
nimo
  • 95
  • 1
  • 2
  • 5

2 Answers2

1

I asked the same question, but worded differently so I didn't find this one when I searched.

The interesting point is that the answer I got is, I think, better than the looping one for two reasons:

  1. Faster - no looping. Just use Application.Caller
  2. Works if your function is called from more than one cell
Community
  • 1
  • 1
Dan Dascalescu
  • 143,271
  • 52
  • 317
  • 404
  • As noted in the comments on my own answer, I think the OP was looking for something different. Either way, +1 for the link, since I had not heard of `Application.Caller`, and I am sure it will come in handy one day. – e.James Apr 05 '11 at 23:28
0

You can loop through a range of cells, looking for that particular function:

Dim name as String
Dim searchRange as Range
Dim row as Integer
Dim col as Integer

name = "MyFunction" ''// for example
Set searchRange = Range("A1:P:50") ''// for example

For row = 1 to searchRange.Rows.Count
  For col = 1 to searchRange.Columns.Count
    If Left(searchRange.Cells(row, col).Formula, Len(name)) = name Then
      ''// do something with this cell
    End If
  Next col
Next row
e.James
  • 116,942
  • 41
  • 177
  • 214
  • +1 for the good answer and for fooling Markdown into properly handling VBA comments :-) – mechanical_meat Feb 20 '10 at 19:23
  • 1
    Thank You for the answer, thus I found Excel Find() which would be more efficient than a loop. ref: http://www.ozgrid.com/VBA/find-method.htm – nimo Feb 21 '10 at 06:13
  • 1
    @nimo: That's a good find. Why not post it as an answer, and then mark it as accepted? There's nothing wrong with answering your own question `:)` – e.James Feb 21 '10 at 11:06
  • @Adam Bernier: Thank you. Those single-quote VBA comments are tricky! `:)` – e.James Feb 21 '10 at 11:07
  • 1
    What will happen if the function is called from more than one cell? I think this question provides a better answer (use Application.Caller): [Cell from which a VBA Function is called](http://stackoverflow.com/questions/5559279/excel-cell-from-which-a-function-is-called) – Dan Dascalescu Apr 05 '11 at 22:35
  • @dandv: Thank you for taking the time to add new (and very useful) information to an old question. Thank you also for the well-deserved downvote, and for actually posting a comment to explain your reasoning. Much appreciated! – e.James Apr 05 '11 at 23:21
  • @dandv: actually, now that I look at it closer, I think nimo (the OP) was asking a different question. He was looking for a way to read which cells contain a particular function call. – e.James Apr 05 '11 at 23:26