When exposing a Python function as an Excel Worksheet function using @xl_func, your Python function will be called whenever Excel requires the returned value to be recalculated.
Excel Recalculation
Typically, Excel will only require the return value to be recalculated when an input to the function is changed. For example, if you call your function in cell B1 as "=foo(A1, A2, A3)", then your function foo will be recalculated whenever cells A1, A2 or A3 are changed.
In order to track what cells need calculating, Excel maintains a dependency graph. This keeps track of what cells each cell depends on. For example, above our cell B1 depends on A1, A2, and A3. Whenever a cell is changed, Excel marks all cells that depend on it as dirty including cells that indirectly depend on it. In our example above, suppose cell A1 depended on B2 (eg "=bar(B2)"), then if cell B2 was changed then A1 would be marked as dirty, and then also B1 (remember, we set "B1 = foo(A1, A2, A3)").
When Excel recalculates it will call the functions of all of the dirty cells to get their new value. Excel will do this calculation either automatically if automatic calculation is turned on, or when the user tells Excel to calculate if manual calculation is enable. If a value is not dirty, then Excel will not recalculate it unless doing a full recalculation (which happens if the user requests it).
Volatile and RTD Functions
Excel has a special type of function called a volatile function. These volatile functions are always dirty. If a function depends on a volatile function (either directly or indirectly) then it will be called every time Excel calculates. This is true even if the value of the volatile function hasn't changed. Examples of built-in Excel volatile functions are NOW, TODAY, RANDBETWEEN, OFFSET, INDIRECT, INFO, CELL and SUMIF.
Your own Python functions can be made volatile by passing "volatile=True" to @xl_func. These functions will be called every time Excel calculates, as will all dependent formulas.
PyXLL RTD functions are volatile by default (prior to PyXL 4.5.0). If you have an RTD function then all depenedent formulas will be recalculated each time Excel calculates because of this. This is usually what you want, as RTD functions typically tick fairly frequently and by making the function volatile it will recalculate and start ticking as soon as the sheet is opened. However, if this isn't what you want, you can pass "volatile=False" to the @xl_func used to register your RTD function to make it non-volatile.
Note: As of PyXLL 4.5.0 RTD functions are no longer volatile by default.
Determining Why a Function is Being Recalculated
Now you understand how Excel decides which functions to call when calculating, you may already have realised why your function is being called each time Excel calculates. If not though, there are a few things you can try to determine why it's being called each time.
- Log the inputs to your function. Are any of the inputs changing? If so, that is why your function is being called.
- Check if any of the inputs to your function are volatile. If they are, your function will be called each time Excel calculates even if the actual value hasn't changed.
- Replace each input with a static value. Check your function no longer gets called each time Excel recalculates. Now replace each value one by one with the original reference or formula until you find the one that is causing your function to be called each time - most likely, that argument is itself volatile or depends on a volatile function.
Preventing a Function From Being Called Repeatedly
If you have followed the steps above and identified why the function is being called each time Excel calculates, but you don't want to change any of the inputs and you want to ensure that your function will only ever get called if the inputs genuinely change value, regardless of whether an input is volatile or not, one possible solution is to cache the calculated values.
PyXLL's LRU (Least Recently Used) Cache
PyXLL has a caching feature for exactly this purpose.
It's enabled by passing the lru_cache keyword argument to the @xl_func decorator.
The LRU Cache (or Least Recently Used cache) caches returned values keyed by the function arguments, and when the function is called again with the same arguments the cached result is returned instead of calling the Python function.
The least recently used values are removed from the cache automatically, and the maximum number of results to store in the cache is the value passed as the lru_cache keyword argument.
For example,
from pyxll import xl_func
@xl_func(lru_cache=128)
def slow_function(arg1, arg2, arg3)
# do some slow calculation
return result
Here, "slow_function" will not be called if called twice with the same values of arg1, arg2 and arg3, if the last call with the same values was one of the last 128 calls to that function.
PyXLL's LRU cache feature is available in PyXLL versions from 5.11.0 onwards.
If you are using an earlier version of PyXLL then you would need to upgrade, or you could use the functools.lru_cache from the standard Python library, although there are some differences to be aware of.
For full details of PyXLL's LRU cache feature see the following page from the documentation: https://www.pyxll.com/docs/userguide/udfs/lrucache.html.
Using functools.lru_cache instead of PyXLL's LRU Cache
Python includes a function cache as part of its functools package called lru_cache. This will cache recent calls of the function, and if called again with the same arguments it will return the cached value instead of calling the function again. The least recently used values are removed from the cache. The number of results to store in the cache is set when using lru_cache.
We recommend using PyXLL's LRU cache instead, but if you are using a version of PyXLL prior to 5.11 and are not able to upgrade then using functools.lru_cache can be an alternative solution.
For example,
from pyxll import xl_func
from functools import lru_cache
@xl_func
@lru_cache(maxsize=128)
def slow_function(arg1, arg2, arg3)
# do some slow calculation
return result
Here, "slow_function" will not be called if called twice with the same values of arg1, arg2 and arg3, if the last call with the same values was one of the last 128 calls to that function.
The arguments to functions decorated with lru_cache must be hashable, whereas PyXLL's LRU cache is less restrictive.
Note that the @lru_cache decorator is applied to the function first, and then the @xl_func decorator. The @lru_cache decorator is applied directly to slow_function, and then @xl_func is applied to the decorated function. This is because we want the decorated function to be exposed to Excel.
Additional Resources
You can find more detailed information about how Excel recalculation works in the Microsoft documentation here https://docs.microsoft.com/en-us/office/client-developer/excel/excel-recalculation.
EDIT: The above link has been removed. The next best current resource is https://learn.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-improving-calculation-performance#calculating-workbooks-worksheets-and-ranges