It can be useful to have worksheet functions (UDFs) that are automatically called when a workbook is opened. Typically this is achieved by making the function volatile, but making a function volatile means that it is recalculated every time Excel calculates and not just when the workbook is opened.
PyXLL functions can be made to automatically recalculate when a workbook is opened by passing
recalc_on_open=True to xl_func.
The recalculating on open feature makes use of Workbook Metadata.
Use-cases for wanting to recalculate a function when a workbook is opened include:
Opening a database connection to be used by other functions.
Loading market data or other data required by the workbook.
Values that depend on the current date or time, but should not constantly update.
Functions returning objects, as the object needs to be created in order for the sheet to calculate.
RTD functions, as they need to be called once to start ticking.
The below function uses the recalc_on_open=True option to tell PyXLL that it should be
recalculated when the saved workbook is opened. For this to work, this function is called
from a cell (eg =recalc_on_open_func()) and then the workbook is saved. The next time
that workbook is opened, the cell containing the function will be marked as dirty and
if automatic calculations are enabled it will be recalculated.
from pyxll import xl_func
@xl_func(recalc_on_open=True)
def recalc_on_open_func():
print("recalc_on_open_func called!")
return "OK!"
Warning
When a workbook is opened cells in that workbook will be recalculated if the function in that cell was marked as needing to be recalculated on open at the time the workbook was saved.
Changing the recalc_on_open option for a function after the workbook has been
saved will have no effect until the workbook has been recalculated and saved again.
Similarly, if opening a workbook saved with a version of PyXLL prior to 4.5, the workbook will need to be recalculated and saved before it will recalculate on being opened.
The default behaviour for non-volatile worksheet functions is not to recalculate on open unless the
recalc_on_open option is set in xl_func.
The recalc_on_open feature is especially useful for RTD functions and for functions returning
Python objects. The default behaviour for these two types of functions can be modified such that
the recalc_on_open feature applies by default.
Real Time Data (RTD) Functions
For RTD functions the option recalc_rtd_on_open can be set in the PYXLL section of the
pyxll.cfg config file. If set, all RTD functions will recalculate on opening unless specifically
disabled by setting recalc_on_open=False in the xl_func decorator.
[PYXLL]
; Enable recalc on open for all RTD functions
recalc_rtd_on_open = 1
Functions Returning Objects
Similarly, any worksheet function that explicitly returns an object can be set to recalculate on opening
via the config setting recalc_cached_objects_on_open.
To enable recalculating all object functions on open set recalc_cached_objects_on_open to 1.
[PYXLL]
; Eenable recalc on open for all functions returning objects
recalc_cached_objects_on_open = 1
With this setting enabled the following function would be recalculated when a workbook using it was opened,
without needing to explicitly set recalc_on_open=True in xl_func.
from pyxll import xl_func
@xl_func("int x: object")
def create_object(x)
obj = SomeClass(x)
return obj
This can be overridden for individual functions by passing recalc_on_open=False to
xl_func.
As with the recalc_on_open setting, these settings only affect what metadata gets saved in the
workbook. Changing the recalc_cached_objects_on_open option after the workbook has been
saved will have no effect until the workbook has been recalculated and saved again.
If you do not want any functions to be recalculated when opening a workbook set disable_recalc_on_open = 1
in your pyxll.cfg file.
This setting prevents any cells marked by PyXLL as needing to be recalculated from being recalculated, regardless of what settings were used at the time the file was saved. It does not prevent Excel from calculating other cells that need recalculating, such as volatile cells.
[PYXLL]
disable_recalc_on_open = 1
