How to stop refreshing ExcelDNA functions when the user hits F9

[Shiva Gowrishetti]

Hi Govert,

 

We have implemented couple of excel UDF functions using ExcelDNA however when the user hits”F9”, it refreshes all the sheets including the sheet with ExcelDNA functions. Is there any way I could disable refresh(F9) function for the excel dna fucntions?

 

Any help is really appreciated.

 

Thanks a lot,

Siva Gowrishetti

[Govert van Drimmelen]

Hi Siva,

If your functions are recalculating with every F9, then they are probably considered "volatile" by Excel.

This can happen even if they are not marked "IsVolatile=true",  in the following case: The function is marked "IsMacroType=true" and has at least one argument marked "AllowReference=true".

Such functions are automatically considered "volatile" by Excel.

You should not use either "IsMacroType=true" or "AllowReference=true" unless you have a specific reason to do so.

The easiest fix is to remove one or the other of these directives.

If your function does require both of these flags, and you are sure it should not be computing with every sheet change, you can override the volatile flag inside your function with a C API call:

    XlCall.Excel(XlCall.xlfVolatile, false);

However, this should be a last resort - it can lead to inconsistent sheets if your function is reading from other parts of the sheet.

-Govert

[Shiva Gowrishetti]

Thanks Govert, Let me try with these changes and see.

[Shiva Gowrishetti]

Thanks Govert,

The directives did help however when I hit "F9" from other worksheet/workbook the function is still getting refreshed. Is there to control the function to get triggered only if I am in the active/current sheet?

Basically I don't want to refresh the functions unless I am in the current/active sheet.

Thanks for you help.

[Govert van Drimmelen]

If the function itself is not volatile, it will not recompute unless an input value changed – maybe you have a volatile input, e.g. a function call of reference to a cell that has a built-in volatile formula like =NOW() or =TODAY().

 

Excel-DNA functions are not different to built-in functions in terms of when they recalculate.

Normal Excel-DNA functions won’t recalculate just because you press F9, whether it’s the active sheet or not.

 

If you switch Excel to Manual calculation mode, you can recalculate the current sheet by Shift+F9.

Note that manual mode can be a bit confusing, as it is a global setting but also saved in the workbook.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Shiva Gowrishetti]

Thanks for the response Govert !

Yes, One of my functions references built-in volatile functions and is the reason for recalculating. But I would like to control the recalculation only when I hit Shift+F9 or F9 irrespective of the references. Is there anyway I could control the recalculation? I am not sure if I could change Excel calculation to Manual mode as the workbook has lot of other sheets which uses built-in functions.

Thanks

[Govert van Drimmelen]

I don’t know of any further calculation flexibility that Excel gives us beyond the manual mode.

 

Internally in your functions you can do things like cache recent results and return the cached value rather than doing a slow calculation again. That way you can control how much work your function does at different times.