What is a “single shot” function?
Excel worksheet functions recalculate automatically whenever an input changes or when the user recalcultes the workbook. This is true of all Excel worksheet functions or UDFs (User Defined Functions), including those written in Python with PyXLL. Most times this is what you want; if you calculate the sum of the numbers in a column using “=SUM” then naturally you want that to update whenever one of the inputs changes.
What if you want to call a function once and then fix the result so it won’t update, even if some inputs change or the user recalculates the sheet? A function that works like this is what we mean in this article by a “single shot” function. This article explains how to write such a single shot Excel worksheet function in Python using PyXLL.
An example single shot function
Imagine you wanted to insert the current time into a sheet. You might use “=NOW()”, but then the result will update each time the sheet is calculated. Let’s add a new function “timestamp” that will return the current time using Python and PyXLL
from pyxll import xl_func
import datetime as dt
@xl_func
def timestamp():
return dt.datetime.now()
This function is not volatile. The returned current time and date does not update each time the sheet is calculated. However, if we fully recalculate the sheet or change an input (if the function had one!) then the function is called again, which is not what we want.
To make the function a true “single shot” function where once the result is returned it becomes completely fixed we need to replace the formula with its calculated value. For that we can use PyXLL’s cell formatting feature.
Fixing the result
PyXLL’s Cell Formatting updates the style or appearance of the range in Excel. Formatting can be applied to any Python Excel worksheet function using the @xl_func decorator. Here we will use it to actually set the cell value to the returned value. This replaces the formula and stops the function from being called again. You can think of it as doing the same thing as “Copy/Paste Value”, but automatically whenever you use the function.
from pyxll import Formatter, xl_func
import datetime as dt
class SingleShotFormatter(Formatter):
def apply(self, cell, *args, **kwargs):
# Setting cell.value replaces the formula
cell.value = cell.value
single_shot_formatter = SingleShotFormatter()
@xl_func(formatter=single_shot_formatter)
def timestamp():
return dt.datetime.now()
When the timestamp function is called from Excel the formatter is used to update the cell appearance. Our custom “SingleShotFormatter” sets the cell value to the current value. This fixes the value and removes the formula from the cell.
This technique not only works for single value functions like “timestamp” above but also works for array functions. Often an Excel macro or ribbon function is used to paste data into a Range, but using a single shot Excel function can be a good alternative!