Functions vs handlers
The preprocessor supports using SQL to define Excel functions as well as event handlers. The
function declarations are mutually exclusive. A script can either declare a
handler or a
function, but not both.
Example function declaration:
Example event handler declaration:
In general, functions fetch data while event handlers change things (e.g. save data to a database, write data into an Excel table).
When saving data to a database, it's clear that an event handler with a SQL
UPDATE/DELETE/INSERT statement is appropriate. When reading data from the database, though, both functions and handlers can be used, since event handlers can push results into Excel tables. So when is one better than the other?
In general, an event handler can only be used for automating a particular workbook, while a function can be defined both in a workbook as well as globally. Defining a function globally allows it to be used in any workbook and even published to other users.
Additionally, there are also two technical limitations to consider:
- Functions cannot output to an Excel table, they can only output to a cell or a spill area.
- Event handlers can output to Excel tables, but they clear the undo stack every time they modify the workbook