Excel VBA - Use of Application.Caller within UDF
Posted on 2013-01-08
I have writen UDF's that appeared to be working but now I have a problem. The UDF's are are used in different rows and each UDF uses Application.Caller to determine the value of another cell in the same row. Here is an example
x = Cells(Application.Caller.row, 4).Value
Other information I Have -
- The UDF's include the Application.Volatile statement.
- The function name has the following format:
Function test(Optional default_value As Variant) As Variant
If I change a cell's value on another worksheet or in a different workbook I then see multiple instances of #VALUE! in cells that have a UDF.
I put a break in the code and when the function has been called (after changing the cell in another worksheet or workbook) the evaluated value of x is 'empty'.
I would be very grateful if someone could please help me.
Many thanks in advance