Excel UDF, volatile and VLOOKUP

Add-in Express™ Support Service
That's what is more important than anything else

Excel UDF, volatile and VLOOKUP
 
Ranjitha Nagendra




Posts: 40
 Joined: 2015-10-26
Hi team,

I have a problem similar to what has been discussed under: https://www.add-in-express.com/forum/read.php?FID=5&TID=10691

I have an XLL UDF that takes in a parameter marked as AcceptAsArray = false, I need that in order to get the parameter as a Range object in my code.
now, due to the fact that when marking a parameter with AcceptAsArray = false it immediately causes the function to become volatile, I've applied the trick [Module.CallWorksheetFunction((ADXExcelWorksheetFunction)237, false);], this works great; the function doesn't get called every time there's a calculation not affecting the aforementioned parameter.

I use Excel's function VLOOKUP in the parameter (e.g. =MyFunction($D6:$AB$6)) - Here each cell from $D6 to $AB$6 have VLOOKUP formula.

My VLOOKUP formula is like this : =VLOOKUP(E$5,Sheet2!$A:$C,2,FALSE)

Now, my problem is, I change the value in E$5, But the input what MyFunction gets is not the new changed value, but old value. Once MyFunction calculation is done ( with old value ofcourse) , E$5 gets the new changed value. So I am thinking, is MyFunction calculating before E$5 has got the new value?
How to overcome this?
Is Excel's sequence of calculation is not how I want?
I did not get it fixed, even after making MyFunction volatile as well, with : Module.CallWorksheetFunction((ADXExcelWorksheetFunction)237, true);

Please advise
Ranjitha
Posted 26 Jul, 2017 07:39:46 Top
Andrei Smolin


Add-in Express team


Posts: 18829
 Joined: 2006-05-11
Hello Ranjitha,

The recalculation logic is described at https://msdn.microsoft.com/en-us/library/bb687891(v=office.15).aspx. Could you send me a sample project and workbook to test this issue?


Andrei Smolin
Add-in Express Team Leader
Posted 26 Jul, 2017 08:21:09 Top