troubleshooting Question

Excel VBA - Use of Application.Caller within UDF

Avatar of alisonthom
alisonthom asked on
Microsoft Excel
19 Comments2 Solutions2463 ViewsLast Modified:

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
Join our community to see this answer!
Unlock 2 Answers and 19 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 19 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros