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.
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.
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.
from pyxll import xl_func
from functools import lru_cache
def slow_function(arg1, arg2, arg3)
# do some slow calculation
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.
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.
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.