How to force XLL UDF to recalc with F9/Shift+F9 always without marking volatile?

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

How to force XLL UDF to recalc with F9/Shift+F9 always without marking volatile?
 
Jason Rodman




Posts: 40
Joined: 2016-11-01
I used add-in express to replace an old VBA/XLA/COM addin with one that is XLL based to take advantage of multi-threading. One issue we ran into was the old addin would allow the user to recalc UDFs using F9 or Shift+F9 and they have become accustomed to having that ability. For some reason it behaves this way, possibly because each UDF calls a COM addin to get a result and pass it back to excel. Since the addin hits a database for every call, having control over recalculation was absolutely necessary. Our users would often leave their workbooks in manual calc mode to avoid recalculation on open, as happens with VBA UDFs by default. They determine when to refresh with these shortcuts keys. I want to avoid marking anything as volatile because we do not want all UDFs to recalc whenever anything changes, that would bring our database to its knees. I am looking for a way to get the same experience with XLL UDFs, which behave different. They apparently follow the same recalculation rules as everything else, only refreshing when they change or a dependency changes. F9 and Shift+F9 have no affect on them since excel does not think they are dirty. Using the other shortcuts (Crtl+Alt+F9 and Ctrl+Alt+Shift+F9 are not viable alternatives since they recalc all open workbooks. There is no shortcuts to recalc cells in just the active sheet or workbook regardless if they are marked as dirty like those do.

One idea I came up with from reading some of your other posts on here was to give them a pair of buttons to force a recalc by marking all used cells in the active sheet/workbook as dirty and calling the respective Calculate method. I would have to convince the users to abandon using shortcut keys in favor of a button to do the same thing though. Does anyone have any other suggestions on how we could make this work?
Posted 18 May, 2018 10:38:18 Top
Andrei Smolin


Add-in Express team


Posts: 18794
Joined: 2006-05-11
Hello Jason,

I've created a simple XLL add-in containing this UDF:

public static object MyFunc() {
    return DateTime.Now.ToLongTimeString();   
}


Also, I wrote a simple VBA function:

Public Function MyVbaFunc()
    MyVbaFunc = "VBA: " + CStr(Now)
End Function


=MyFunc()
=MyVbaFunc()

I've found that you can't invoke these functions using F9/Shift+F9.

I've modified them as follows:

public static object MyFunc(object arg) {
    return DateTime.Now.ToLongTimeString();   
}


and

Public Function MyVbaFunc(arg As Object)
    MyVbaFunc = "VBA: " + CStr(Now)
End Function


=MyFunc(A1)
=MyVbaFunc(A1)

Both of them are invoked if I modify an arbitrary cell (e.g. B1) and press F9/Shift+F9; Calculation Mode = Manual.

Hope this helps.


Andrei Smolin
Add-in Express Team Leader
Posted 21 May, 2018 04:22:03 Top
Michael Kaden


Guest


Hello Jason, hello Andrei

I had similar discussion with Andrei, who pointed me on the right path, thank you, where Range.calculate did not help as it only calculates the cells in the dependency tree and SendKeys.Send("^%" & "{F9}")did not let me select which cells shall be calculated and which not. I currently the following approaches, If I want to calculate the cells within a specified range, I use:

Dim xlSheet As Excel.Worksheet
Dim xlRange As Excel.Range
xlSheet = ExcelApp.ActiveSheet ' **if necessary loop through all workbooks and worksheets
xlRange = xlSheet.UsedRange
xlRange.Dirty()

the xlRange.Dirty()calculates all cells in the specified range. But only once, as the cells are no more "dirty" after the calculation is executed.

If I want to select a range depending on the formulas in the cell, I combine it with the FIND method which gives me the range(s) where the specified formula resides.

I am not exactly sure if that is what you where looking for Jason, but perhaps it helps.

kind regards

Michael
Posted 21 May, 2018 05:10:37 Top
Jason Rodman




Posts: 40
Joined: 2016-11-01
Thanks for getting back to me. From what I read, excel actually reads the content of your UDF and tries to determine whether it should be recalculated during F9/Shift+F9 regardless of whether its been marked dirty. You would expect something with a non-deterministic function like Now() would behave like that. But there is something about what is done in my project that makes excel run my functions every time you request it. It may have to do with a call to Application.COMAddIns.Item("XYZ").Object that may be triggering it. All I know is that it works that way now and my users demand it without having to change a dependent cell. Makes sense though since the data changes in the database and excel has no way to know that.

What I did end up doing was adding a button using the technique you refer to above, and it seems to work. If its in automatic mode, I simply call UsedRange.Dirty() and that forces a recalc. In manual mode, I do both that and .Calculate on the sheet and it works as well. The one thing I could not get to work right was cycling thru all the sheets in a workbook. It would always only calculate the active sheet no matter how I cycled thru them. I was forced to have to set each sheet active first before doing this to get it to work, but it turned out to be incredibly slow since it would end up causing a lot of duplicate processing with cross sheet references. My hope was there was some sort of configuration that would cause XLL functions to behave this way, but it appears not.
Posted 21 May, 2018 08:46:45 Top
Andrei Smolin


Add-in Express team


Posts: 18794
Joined: 2006-05-11
Thank you, Michael!

Jason, you can probably call {a worksheet}.UsedRange.Calculate().


Andrei Smolin
Add-in Express Team Leader
Posted 21 May, 2018 09:44:26 Top
Jason Rodman




Posts: 40
Joined: 2016-11-01
Yes, I tried that. Looping thru each sheet doing like this does not work:

foreach (_Worksheet sheet in ActiveWorkbook.Sheets)
{
sheet.UsedRange().Dirty();
sheet.Calculate();
}

It would end up recalculating the cells in the active sheet only, and would repeat that same calculation for each loop. Simply adding sheet.Active() to the first line fixed it for some reason. Try it for yourself.
Posted 21 May, 2018 09:52:38 Top
Andrei Smolin


Add-in Express team


Posts: 18794
Joined: 2006-05-11
Would it work differently if you call:

sheet.UsedRange().Dirty();
sheet.UsedRange().Calculate();

?


Andrei Smolin
Add-in Express Team Leader
Posted 21 May, 2018 09:56:20 Top
Jason Rodman




Posts: 40
Joined: 2016-11-01
Had the same effect. Still not sure why. Cells with normal formulas behaved correctly, it was only UDFs that exibited this behavior. I setup a book with 3 sheets, calling same function with 3 different results. This method called the UDF 3 times, all with the data from whichever sheet was active. I tried switching active sheet and got the same behavior. Manual vs automatic calc mode did not seem to matter.
Posted 21 May, 2018 10:50:49 Top
Andrei Smolin


Add-in Express team


Posts: 18794
Joined: 2006-05-11
Hello Jason,

Can I have a sample project and workbook to look into this more closely? You can send them to the support email address; see readme.txt.


Andrei Smolin
Add-in Express Team Leader
Posted 22 May, 2018 06:19:12 Top
Jason Rodman




Posts: 40
Joined: 2016-11-01
I wish I had the time to set something like this up for you. If I free up long enough to throw together a sample project for this I will send it to you. Thanks for your help.
Posted 23 May, 2018 08:57:07 Top