=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.
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 adescriptionfor each argument: the editor shows them when you type the function.exampleandhelpUrl: optional. The ScreenTip shows the example. Help on this function opens thehelpUrl, which must start withhttporhttps.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 setmaxAgeMs.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.
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.
Spill rows
Whencompute 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
Whencompute 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
computesees it. - With the function registered, the function comes before the name. Without it,
@reductoai/xlsxcalls the name, as Excel does.
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.