Authors
Thomas A. Grossman & Erik Burd
Abstract
The ability to reuse code is an important element of productivity when using traditional programming languages. Code reuse is rarely done with spreadsheet programming languages. A necessary but not sufficient condition for code reuse is the ability to apply the principles of encapsulation and interchange.
We present the results of experiments using different protocols for establishing encapsulated spreadsheet code modules, and for interchanging a revised encapsulated spreadsheet code module from a sandbox location into a production location. We discovered that Excel worksheet-scope names are unstable under worksheet interchange. We find that encapsulation and interchange are feasible with spreadsheet code, with caveats. Highly disciplined programming practices are mandatory. Scalability to large numbers of code modules is an issue that requires further study.
We explain why source code management systems might be feasible with spreadsheet code modules, and have potential to take care of the scaling challenge. Although much work remains to be done, this is an important step towards the possibility of reusable spreadsheet code.
Sample
Workbook-level encapsulation places all the code of a module in a single workbook. Inputs, calculations, and outputs are on separate tabs. Any number of tabs containing calculations is permissible and may in fact be desirable. Likewise, multiple input tabs and output tabs may be used.
Publication
2015, EuSpRIG
Full article
Towards reusable spreadsheet code: Experiments to create and interchange encapsulated Excel modules
