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”<\/a> then naturally you want that to update whenever one of the inputs changes.<\/p>\n\n\n\n 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.<\/p>\n\n\n\n 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<\/p>\n\n\n This function is not volatile<\/a>. 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.<\/p>\n\n\n\n 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<\/a>.<\/p>\n\n\n\n PyXLL’s Cell Formatting<\/a> updates the style or appearance of the range in Excel. Formatting can be applied to any Python Excel worksheet function using the @xl_func<\/a> 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. <\/p>\n\n\n 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.<\/p>\n\n\n\n This technique not only works for single value functions like “timestamp” above but also works for array functions<\/a>. Often an Excel macro<\/a> or ribbon function<\/a> is used to paste data into a Range, but using a single shot Excel function can be a good alternative!<\/p>\n","protected":false},"excerpt":{"rendered":" Single shot functions are functions that only ever get calculated once. Instead of being recalculated if an input changes or the sheet is refreshed the result of a single shot function is fixed.<\/p>\n","protected":false},"author":1,"featured_media":1454,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[12,2],"tags":[648,647,646],"class_list":["post-1443","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-python","category-pyxll","tag-formatting","tag-functions","tag-udfs"],"acf":[],"jetpack_featured_media_url":"https:\/\/www.pyxll.com\/blog\/wp-content\/uploads\/2020\/12\/single-shot-e1607359232303.jpg","jetpack_shortlink":"https:\/\/wp.me\/p7l3LP-nh","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts\/1443","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/comments?post=1443"}],"version-history":[{"count":1,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts\/1443\/revisions"}],"predecessor-version":[{"id":1557,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/posts\/1443\/revisions\/1557"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/media\/1454"}],"wp:attachment":[{"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/media?parent=1443"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/categories?post=1443"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.pyxll.com\/blog\/wp-json\/wp\/v2\/tags?post=1443"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}An example single shot function<\/h2>\n\n\n\n
\nfrom pyxll import xl_func\nimport datetime as dt\n\n@xl_func\ndef timestamp():\n return dt.datetime.now()\n<\/pre><\/div>\n\n\n
Fixing the result<\/h2>\n\n\n\n
\nfrom pyxll import Formatter, xl_func\nimport datetime as dt\n\nclass SingleShotFormatter(Formatter):\n\n def apply(self, cell, *args, **kwargs):\n # Setting cell.value replaces the formula\n cell.value = cell.value\n\nsingle_shot_formatter = SingleShotFormatter()\n\n@xl_func(formatter=single_shot_formatter)\ndef timestamp():\n return dt.datetime.now()\n<\/pre><\/div>\n\n\n