-1

I have a custom macro function, declared in a Module :

Function myMacro(value as String) as String   
    myMacro = "Hello world " & value 
End Function

This macro is inserted in a cell (A1 for example) and take the value of B2 as parameter.

Fx(A1 cell) = "MyMacro(B2)"

So my MyMacro func is triggered each time the value in B2 is changed, which is normal and is exactly what I want.

But i want to improve my macro to be more user friendly because my macro can take a long time (3 or 4 seconds) to execute. I want my macro to display "computing..." inside the resulting cell, before the real result is displayed.

To do that, my macro have to know on wich cell the compute is done. In the previous example, my macro need to know that the macro is triggered on the A1 cell.

The Application.Cell attrib contain the current cell (the one selected with the mouse), which is not always the cell containg the macro formula.

Is there a way for a VBA function to know which cell call the function ?

pnuts
  • 58,317
  • 11
  • 87
  • 139
JayMore
  • 211
  • 5
  • 8
  • 1
    possible duplicate of [Excel cell from which a Function is called](http://stackoverflow.com/questions/5559279/excel-cell-from-which-a-function-is-called) – eirikdaude Feb 18 '15 at 13:46

1 Answers1

1

Application.Caller will return the range that the function is entered into. If the formula is array-entered into multiple cells, it will return the entire range containing the array. If you only want the individual cell containing the formula you can use Application.ThisCell.

Rory
  • 32,730
  • 5
  • 32
  • 35