[ Jocelyn Ireson-Paine's Home Page | Module Expressions for Modularising Spreadsheets and Sharing Code between Them ]

Demonstrations for Calco-Tools 2011

This page links to demonstrations for my paper Module Expressions for Modularising Spreadsheets and Sharing Code between Them submitted to the Calco-Tools 2011 workshop on tools based on algebraic and coalgebraic principles. Reviewers and delegates, and anyone else interested, can download them from here. The paper is about modularising spreadsheets using a module system based on ideas from OBJ and other algebraic-specification languages, and there are two sets of demonstrations. The first set demonstrates my Excelsior spreadsheet-description language, and the use of modules. The second set demonstrates how to insert modules into already-written spreadsheets. The point of both is to show that spreadsheets can be modularised in a way that has good mathematical foundations, and that doing so makes code-reuse easier. As well as examples of modularisation, the demonstrations include the examples from my paper with which I introduce Excelsior. Because there are several of these, I've written a section for each, and linked to it from the contents just below. There is also a section on how to install Excelsior. If you have problems, please mail me at popx@j-paine.org .

Contents

Spreadsheet modularisation by generating everything in Excelsior

Excelsior is a compiler that reads text files and generates Excel spreadsheets. A program can be in one piece or split between several files, and can import modules.

What to run Excelsior on

The current version of Excelsior runs only on Windows. If anyone is interested, I can make a Unix version, but because Unix doesn't have Excel, it won't be able to launch Excel with the generated spreadsheet in as the Windows version does. To demonstrate Excelsior, you need a Windows system, and a copy of Excel. I have only tried it with Windows XP and Excel 2003, but I expect it to work with other modern Windows systems, including 64-bit, and with Excel 2007.

How to install Excelsior

To install Excelsior, please visit my download page. The page links to a zip file and some installation instructions. Read the notes at the end of this section, then download the zip file and follow the instructions. These should be straightforward if you know Windows and the DOS command line. You'll be asked to create a directory for Excelsior to live in, unzip into it, set some environment variables, run some tests, and then put Excelsior on your PATH if you want to run it from a different directory. There are also installation instructions in my feature Gliders, Hasslers, and the Toadsucker: Writing and Explaining a Structured Excel Life Game. They're in the section called Compiling Life, and explain in more detail how to set environment variables.

Notes

The installation instructions ask you to create a directory for Excelsior to live in. I suggest making this a top-level directory called excelsior, e.g. c:\excelsior\ . In the rest of this page, I'll assume that's what you've called it.

Excelsior tells you when it starts compiling a program, and again when it has finished. The messages from a successful compilation will look like this, in the DOS window:

Compiling micawber1.exc.
Compiled micawber1.exc.
If Excelsior detects errors, it will report them in the same window.

If Excelsior does manage to compile your program, it will generate a temporary file holding Excel formulae, then launch Excel and make it run a Visual Basic for Applications (VBA) initialiser program that copies these formulae into the spreadsheet being generated. Because Excel is running VBA, it may ask you whether you want macros enabled. You do, otherwise the VBA program can't run.

Once Excel is launched, it will briefly display the spreadsheet that I holds the initialiser program. This has one sheet, showing a table of the cell formats for Excelsior array-element types. These will then disappear and be replaced by your spreadsheet. The initialiser will give this a temporary name, something like wb__2011_3_28_7_25_38_218 . If you want to keep the spreadsheet, go to Excel's File menu and use the Save As option to save it under a name of your choice. Excelsior doesn't delete or overwrite the Excel windows holding generated spreadsheets, so if you do lots of compilations and don't delete them either, you'll end up with lots of Excel windows.

Excel scans for viruses when launching the initialiser. While doing so, it displays "Running virus scan..." in the bottom-left of its window. Be prepared for some delay while this is happening.

How to run Excelsior, and where to get examples

You run Excelsior from the DOS command line, passing it the input file or files, and various control options. These are described in the installation instructions. I also give the command lines for each example in my paper, below. The installation comes with a few examples, plus a number of test programs. You can find more examples in my paper; in the Life-game feature cited above; in my Dr. Dobbs blog posting How to Document a Spreadsheet: an Exercise with Cash-Flow and Loans; in my EuSpRIG 2008 paper Spreadsheet Components For All; and in the presentation accompanying it, Less Excel, More Components.

Examples for Calco-Tools

Here are links to the examples in my paper. These link to files with the extension .exc, which should display as plain text in your browser. You can capture this by selecting the text with the mouse, then pasting it into a text editor, or by saving the page. The entire collection of examples is available in this zip file.

micawber1.exc

Simple one-row spreadsheet calculating Micawber's net income. Compile it with this command line:

        excelsior -t c:\excelsior\template.xlt micawber1.exc
        

micawber2.exc

Multi-row spreadsheet calculating Micawber's net income for each of nine years. Compile it with this command line:

        excelsior -t c:\excelsior\template.xlt micawber2.exc
        

micawber3.exc

Like micawber2, but with years running horizontally rather than vertically. Demonstrates how easy it is to change layout. Compile it with this command line:

        excelsior -t c:\excelsior\template.xlt micawber3.exc
        

test_split.exc
split.exc

Demonstration of modules. The first file imports the module in the second file twice, to split the strings in two different tables. The tables, their base type or size, and the delimiter to split at, are parameters to the module. Compile it with this command line:

        excelsior -t c:\excelsior\template.xlt test_split.exc
        

test_split2.exc
split2.exc

Demonstrates a bigger version of the module, omitted from the paper because of lack of room. This splits the strings into "before" and "after" segments, rather than just "before". Compile it with this command line:

        excelsior -t c:\excelsior\template.xlt test_split2.exc
        

micawber2_data.exc

Test data which can be compiled together with micawber2 by using the nearest construct Excelsior has so far to a module-sum operator. This demonstrates how you can write test data separately from the code being tested, then combine them without having to edit the latter. Compile the two files with this command line:

        excelsior -t c:\excelsior\template.xlt micawber2.exc micawber2_data.exc
        

Spreadsheet modularisation by glueing compiled modules into existing spreadsheets

How it works

Consider an existing spreadsheet with a "hole" we want to put a module into. As you can see from the examples above, the module-inclusion statement can parameterise modules; and a module's parameters affect the shape, size, and position of its arrays. For example, test_split creates two instances of module split. In one instance, the tables have two cells each; in the other, three. In one instance, they hang downwards from row 2; in the other, from row 7.

So imagine writing a "stub" program that includes the module but does nothing else, then compiling it to a spreadsheet. This will contain only the module, and by choosing suitable parameters, we can shape it to fit any hole in the existing spreadsheet. The examples below demonstrate this: split_spacer.exc is such a stub, and computer_scientists.xls is a spreadsheet with a hole. Other examples can be found in the Try for yourself section of my EuSpRIG 2008 presentation Less Excel, More Components; and with screen shots, and automated for Google's online spreadsheet as well as Excel, in my feature Spreadsheet Components, Google Spreadsheets, and Code Reuse.

Examples for Calco-Tools

split_spacer.exc
computer_scientists.xls

First, compile split_spacer with this command line:

        excelsior -t c:\excelsior\template.xlt split_spacer.exc
        
This makes a spreadsheet that holds a copy of the module shaped so that its inputs are in D3:D7, and its outputs in F3:F7. Next, open computer_scientists.xls. This is a spreadsheet with the names of famous computer scientists in D3:D7. Keep a copy, or be prepared to download it again, because you'll need it unchanged for the next demo. Now, go to the spreadsheet you compiled from split_spacer, and select and copy cells E3:F7, i.e. the non-input-cell portion of the module. (If you don't know how to select and copy: drag the mouse over the cells, then press Control-C.) Go back to computer_scientists and paste these cells into it at the same location. (Drag the mouse over the cells, then press Control-V.) This pastes the formulae that do the splitting into computer_scientists, and you should see the computer scientists' Christian names appear in F3:F7. E3:E7 hold intermediate results, the positions of the space. This demonstrates inserting a compiled module into an existing spreadsheet, though you had to insert it manually.

split.etf

This is an intermediate file of Excel formulae. It's the file that Excelsior emits and that the VBA initialiser program then reads to generate the spreadsheet. The file here is one I compiled from split_spacer. I'll now demonstrate how to insert it automatically. Go to this page, called How to copy your component into Excel. It's one of the pages on my Spreadsheet Parts Repository site. Download the VBA program that it refers to near the top of the page (actually, this is a spreadsheet with VBA in it), and save it in c:\excelsior\spreadlet_copier.xls . Also make sure that you've downloaded split.etf from the link above. Open spreadlet_copier.xls. You should see a sheet with a grey "Go" button on it. Press the button. You'll get a file-selection dialogue box titled "Please select a target spreadsheet". Select computer_scientists.xls . You'll then get another file-selection dialogue box titled "Please select a component ETF file". Select split.etf. The VBA will then copy the formulae from it into computer_scientists. As with the previous demo, Christian names should appear.

Generating the ETF file

This shows how split.etf was generated. First, compile split_spacer with this command line:

        excelsior -t c:\excelsior\template.xlt -no_excel true split_spacer.exc
        
The no_excel option tells Excelsior not to launch Excel. Normally, Excelsior would delete the ETF file after doing so. But with no_excel, it leaves it. You should find it in the temporary-files directory you set the EXCELSIOR_TEMP environment variable to when installing Excelsior, under the name excelsior.etf. Open it and split.etf in a text editor. The formulae should be the same.

Jocelyn Ireson-Paine
30th March 2011