Skip to main content
A custom function is a function that you write in JavaScript and that a formula calls by its name, such as =ACME.QR(A2). It can return a number, text, a logical value, an error, a picture or rows that spill. It can also return a promise, to get data from a server. Microsoft Excel does not need your code to open the file. When you save, each result that a formula uses goes into the file with the formula. Excel shows these results with no add-in. Press Run. Then change a link in column A: its QR code in column B changes.
On this page, other packages load from esm.sh. In your app, run npm install uqr.

Define a function

defineFunction checks a definition and gives it back.
  • name: a namespace, a dot and a name, as Excel add-ins name their functions: ACME.QR. The namespace keeps the name apart from Excel’s own functions, now and in the future. Formulas call the function in any case.
  • description, and a description for each argument: the editor shows them when you type the function.
  • example and helpUrl: optional. The ScreenTip shows the example. Help on this function opens the helpUrl, which must start with http or https.
  • args: the arguments, in order.
  • compute(args): gives the result, or a promise of it. The same arguments must give the same result, unless you set maxAgeMs.
  • maxAgeMs: optional. A result this old computes again. See Wait for a result.

Register it

A function belongs to the workbook. A formula that calls a function that is not registered shows #NAME?. When you register the function, the formula calculates.
The editor registers its functions on each workbook that it opens:
compute runs once for each different set of arguments. A second cell with the same arguments uses the first result.

Help in the editor

The editor shows the help of a registered function, as it does for Excel’s own functions:
  • Type =ACME. in a cell. AutoComplete lists the functions, with the description of the one that you select.
  • Type =ACME.QR(. The ScreenTip shows the arguments, the example and the help link.
  • Press Shift+F3 for Insert Function. The User Defined category lists the functions, and the search finds them by their descriptions.
The help belongs to the workbook. Two editors on one page with different functions each show their own.

Spill rows

When compute gives rows, the result spills into the cells below and to the right, as in Excel. The file keeps the rows too, so Excel shows the same spill. Press Run. Then change the text in column A: the spill changes size.

Wait for a result

When compute returns a promise, the cell shows #BUSY!, as Excel does for an add-in function. When the promise settles, the cell and each formula that reads it update. Two cells with the same arguments share one call. Press Run. The rates come from Frankfurter, a free exchange-rate service. Then type another currency in column B, such as JPY.

In Excel

On save, the workbook gets a very hidden sheet, _FunctionCache. It holds the arguments and the result of each call that a formula uses. For rows, it holds the number of rows and columns and each value. Each function also gets a defined name with the same name, as ACME.QR. The name holds a LAMBDA that looks the arguments up on that sheet. For rows, the LAMBDA builds them again with MAKEARRAY. Excel calls this name when a formula calls ACME.QR, so the formula and its results stay the same in Excel, pictures too.
  • A link that you change in Excel to one that the sheet does not hold gives #N/A. Open the file here again and save it, and the new result goes into the file.
  • The lookup matches text in the same case only, as compute sees it.
  • With the function registered, the function comes before the name. Without it, @reductoai/xlsx calls the name, as Excel does.
This was checked with Excel for Windows: Excel opened the saved file, showed the text, picture and spilled results, and kept them when it saved the file again. An empty value in spilled rows shows 0, here and in Excel.
Calls with a range or a picture argument do not go into the file. Excel calculates the file when it opens it, so such a call shows #VALUE! in Excel, not the result from here. Excel does not get a new result: the file keeps the last one that arrived here.