Hi,
I am seeing a very strange problem, which I don't fully understand, which must surely relate to the combined use of Application.Caller and Application.Volatile.
There is a related post here, which summarises what I have done in my function, so I think it's correct: http://www.excelforum.com/excel-prog...ml#post2502204
I have added a UDF to update sheets automatically. Essentially I have pairs of sheets for each country, so that I want Country#2 sheet to refer to Country#1 sheet to calculate certain cells. Previously the country name was hardcoded in the formulae, but I want to make it more generic.
Having given the spreadsheet to a client, he has made multiple copies of the same spreadsheet. What he has observed is that
a) he opens first spreadsheet and UDF updates values correctly in the relevant cells
b) he opens second spreadsheet and the cells with UDF contain nonsensical values
c) he does copy and paste from cells on the second spreadsheet (which forces the recalculate) and the cells with UDF now show the correct values
BUT
d) on the first spreadsheet, the cells with UDF have now been updated with non sensical values
QUESTION: Surely the Application.Caller method should be aware of which is the active spreadsheet, and therefore NOT affect the second spreadsheet? Or am I doing something wrong?
Any help greatly appreciated!
Thanks
My function code is below
Please Login or Register to view this content.
Bookmarks