Skip to content

Functions vs handlers

The preprocessor supports using SQL to define Excel functions as well as event handlers. The handler and function declarations are mutually exclusive. A script can either declare a handler or a function, but not both.

Example function declaration:

1
2
{function myFunc(int param1=1, string param2="abc")}
// ...sql code

Example event handler declaration:

1
2
{handler (myNamedCell, Sheet1!MyButton)}
// ...sql code

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