In this series of postings, I'm going to explain how I modularise Excel spreadsheets. That is, how I break them into parts that can be separately documented, tested, and debugged; and how I share the parts between different spreadsheets, thus easing code reuse. My research on spreadsheet modularity takes ideas from a mathematical discipline called category theory, as well as from the work of Joseph Goguen and colleagues about how to modularise algebraic specification languages. This isn't as forbidding as it may sound, because much of it boils down to simple notions of mapping, text inclusion, and replacement of identifiers. But one important point is that I represent spreadsheets as text rather than graphically, coding them in a programming language that resembles the reverse-engineered listings I displayed in my recent posting How to Reveal Implicit Structure in Spreadsheets. To turn the text into spreadsheets, I use a tool called Excelsior. I've made Excelsior freely available, and to start the series, I'll show you how to install it for Windows.
First, download Excelsior from my Spreadsheet Parts download page and unzip it into a clean directory. Hereon, I'll assume you've named this c:\excelsior\ . The zip file contains several subdirectories: make sure your unzip command preserves their structure, and doesn't flatten them into the top-level directory.
You must now set three environment variables. I ask you to do so manually, rather than via an installer; and I don't change the Windows Registry. That way, you know exactly what the install will do to your computer.
This is how I set environment variables in Windows. If you already know how to set them, skip the rest of the paragraph. I click on Start, then on Control Panel halfway down the righthand column in the resulting pop-up. This opens a window with icons labelled "Accessibility Options", "Add Hardware", and so on: I am viewing it in what Microsoft call Classic View, rather than Category View. I double-click on the System icon, then on the Advanced tab in the window this opens, and then on the Environment Variables button near the bottom. This makes another pop-up, with two fields headed "User Variables" and "System variables". I click the "New" button below the "System variables" field, and a pop-up appears with a "Variable name" and a "Variable value" field. Then I type (e.g.) EXCEL_HOME into the "Variable name" field, and (e.g.) c:\Program Files\Microsoft Office\Office11 for "Variable value". I then click "OK", and then either add another variable, or click "OK" on the pop-up below. And that's it.
So now, set the first environment variable EXCEL_HOME to the directory your Excel is in. On my laptop, that's c:\Program Files\Microsoft Office\Office11 . Every time Excelsior successfully compiles a program, it will write out an intermediate file containing the formulae that make up the spreadsheet to be generated from it. It then runs excel.exe from EXCEL_HOME, passing it the name "interface.xls" as a command-line argument. This starts Excel and makes it open the file interface.xls which comes with Excelsior. A VBA macro in interface.xls then reads the intermediate file and copies it into a new spreadsheet, which will magically appear before you.
Second, set EXCELSIOR_HOME to the directory you unzipped into, namely c:\excelsior\ . Excelsior will look in here to find interface.xls and other files.
Third, set EXCELSIOR_TEMP to a scratch directory where Excelsior can put files such as the intermediate formula file. I use c:\windows\temp\ .
Having set these variables, you are ready to test Excelsior. Open a new MS-DOS window, and change directory to c:\excelsior\ . Then type the command
excelsiorYou should get a summary of Excelsior's command-line syntax. If it doesn't work, perhaps the unzip has failed to copy some of the files, possibly DLLs that Excelsior uses.
But if it does work, you are now ready to compile a test spreadsheet. Type this:
excelsior -t c:\excelsior\template.xlt source_tests\test1.excThis should display the messages:
Compiling source_tests\test1.exc. Compiled source_tests\test1.exc.
Excelsior will then launch Excel as I've described. Excel will run its VBA macro to copy the formula, and may ask you to enable macros (because of the VBA code in interface.xls). There'll probably be a bit of delay caused by those irritating virus scans. And then your new spreadsheet should appear in front of you. It will contain the number 2000 in cell A1. In the rest of the series, I'll show you how to build spreadsheets that are more interesting, and how to use modules.