UDF in excel updating all tabs to the same number (I'm expecting to see different #s)

Hello -
I have the following UDF set up to calculate the break- even point in excel.

The UDF is referenced on multiple tabs throughout the workbook. When one tab is refreshed, ALL of the tabs that reference the same UDF are updated to the same number (although the cash flows are different on the various tabs, and I'd expect the number to be different as well).

For example, let's say tab1-cellA1 should show "5" and tab2-cellA2 should show "10". If I click on tab 1-cell A1 and refresh, all the tabs now show "5" in cell A1. If I then click on tab 2-cell A1 and refresh, all of the tabs show "10" in cell A1.

Is it possible to update the entire workbook at once and have the correct values be calculated? I can't seem to figure out why excel is updating all tabs to the same value.

Thank you!

Please Login or Register  to view this content.

This forum can be strict about some rules, like putting code in code tags. Edit your post to include these tags to avoid getting the topic locked.

Until that is done, this hint: what does the SumProfit=SumProfit+ActiveSheet.Cells(r,c).Value statement do?

Did you look at that statement? What is going to happen here is that VBA is taking values from ActiveSheet (which may or may not be the same sheet that the function resides in) and adding them up. Since there can only be one active sheet at a time and you have multiple copies of the function in multiple sheets, all of those copies will return the same answer based on whatever the one active sheet is.

Structured like this, you need to use something like Application.Caller to get at the actual sheet each copy of the function resided in. I never use Application.Caller, so I'm not sure exactly what that looks like, but some experimentation should help you discover how that might work.

The way I would structure this would be to pass the actual range with the values into the function:

Please Login or Register  to view this content.

Note that, doing it this way, I should not need application.volatile nor should I need to refer to application.caller or activesheet. The function knows what data it is processing because that data is passed to the function.

Hopefully that helps.

I think solver was invented for this. Unable to test due to lack of sample file