We have developed an implementation of sheet-de ned functions, a mechanism that allows spreadsheet users to de ne their own functions, using only spreadsheet concepts such as cells, formulas and references, and no external programming languages. This position paper presents the motivation and vision of this work, describes the features of our prototype implementation, and outlines future work.
We have implemented this idea in the form of sheet-de ned functions. A user may de ne a function F simply by declaring which (input) cells will hold F's formal parameters and which (output) cell will compute F's result, as a function of the input cells. The function de nition may involve arbitrary additional cells, spreadsheet formulas, calls to built-in functions, and calls to other sheet-de ned functions. Figure 1 shows an example. In the example, values are referred to by cell (such as B25). A mechanism that allows for symbolic names (such as \periods") instead could be added, but Nardi speculates that end user developers would not necessarily nd that better [7, page 44].
Augustsson et al. from Standard Chartered Bank provide
further support for the utility of such abstraction
mechanisms, saying about the traditional combination of Excel
and externally de ned functions that \change control, static
type checking, abstraction and reuse are almost completely
lacking" [
The ultimate goal of this work is to allow spreadsheet users themselves to develop and evolve libraries of user-de ned functions to support sophisticated spreadsheet models. De ning a function requires only well-known spreadsheet concepts such as cell, cell reference and function, and no external programming languages. Therefore experimentation and adaptation of user-de ned functions remain under the control of the spreadsheet users and domain experts, who need not wait for an IT department to understand, describe, implement and test the desired changes.
Any spreadsheet computation can be turned into a sheetde ned function. This ensures conceptual and notational simplicity. Moreover, it means that new user-de ned functions may arise by refactoring of a spreadsheet model as it evolves. As a spreadsheet model becomes more re ned and complex, it may be observed that the same cluster of formulas appears again and again. Such a cluster of formulas may then be encapsulated in a sheet-de ned function, and each formula cluster replaced by a call to that function. This both simpli es the spreadsheet model and improves its maintainability, because a bug- x or other improvement to the function will automatically a ect all its uses, unlike the traditional situation when there are multiple copies of the same cluster of formulas.
Sheet-de ned functions may be shared with other users in the same department or application domain, without preventing them from making their own improvements | because the domain knowledge is not locked into the notation of a \real" programming language, but one that presumably is familiar to users and that they are (more) comfortable experimenting with.
Sheet-de ned functions support end-user \tinkering" to
develop models and work ows that are appropriate within
their application domain [
We have created a prototype implementation of sheet-de ned
functions, called Funcalc. The implementation is written in
C#, is quite compact (12,000 lines of code) and compiles
sheet-de ned functions to .NET bytecode [
Funcalc features include:
a \normal" interpretive spreadsheet implementation;
a compiled implementation of sheet-de ned functions;
recursive functions and higher-order functions;
functions can accept and return array values in
addition to numbers and string;
automatic specialization, or partial evaluation [
Function values are built by applying a sheet-de ned function to only some of its arguments, the absent arguments being given as NA(); the resulting function value will display as NOMINAL(#NA,4) or similar.
Such a function value may be specialized, or partially
evaluated, with respect to its available (non-#NA) arguments.
The result is a new function value with the same behavior
but potentially better performance because the available
argument values have been inlined and loops unrolled in the
underlying bytecode. For more information, see [
A forthcoming book [
A comprehensive list of US spreadsheet patents is given in
a forthcoming report [
In ongoing work [
The main downside is that calling a sheet-de ned function
from Excel is much slower than from the Funcalc
implementation (yet apparently faster than calling a VBA function);
see Table 2. However, the sheet-de ned function itself will
execute at the same speed as in Funcalc. This work uses the
Excel-DNA runtime bridge between Excel and .NET [
So far we have focused mostly on functionality and good
performance. We emphasize performance because we want
sheet-de ned functions to replace not only user-de ned
functions written in VBA, C++ and other external languages,
but to replace built-in functions also. Domain experts in
nance, statistics and other areas of rather general interest
should be able to develop well-performing high-quality
functions themselves and not have to rely on Microsoft or other
vendors to do so.
However, a well-performing implementation of sheet-de ned
functions is just the beginning: one should investigate
additional infrastructure and practices to support their use.
For instance, how can we extend the natural collaboration
around spreadsheet development [
Our concept of sheet-de ned functions should be subjected
to a systematic usability study; the study conducted by
Peyton-Jones, Blackwell and Burnett [
Finally, sheet-de ned functions lend themselves well to
parallelization, because they are pure (yet strict, an unusual
combination) so that computations can be reordered and
performed speculatively, and often exhibit considerable
explicit parallelism. In fact, they resemble data ow languages
such as Sisal [
We have presented a prototype implementation of sheetde ned functions and outlined some future work. Our hope is that such functionality will become available in widely used spreadsheet programs, or via a full-featured version of the plugin described in Section 4, and will enable spreadsheet users to develop their own computational models into reusable function libraries, without loss of computational e ciency and without handing over control to remote IT departments or software contractors. Moreover, there seems to be a technological opportunity to harness the power of multicore machines through spreadsheet programming.