Excel Functional Programming - Explore Another Dimension of Spreadsheet Programming

Abstract

The benefits of functional programming are well known, by looking into the characteristics of spreadsheets and traditional ways of spreadsheet programming, whose risks are popularly accused, we show that a functional approach is especially beneficial. We propose a way to code Excel spreadsheets in a functional style. The method consists in composing Excel built-in functions and taking array as arguments, thus we name it "array formulae programming". We present array formulae solutions of some real tasks of Excel users, which are often recognized as impossible without the help of VBA. We introduce the solutions in an order from basic to advanced expecting that users understand progressively this way of programming. We develop "AF Library Add-In" which allows developers to load many sample solutions into their spreadsheets.

Keywords

Spreadsheet programming, Array formulae, Functional programming, Spreadsheet risks, Microsoft Excel, VBA, Case studies.