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

excelsior

You 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.exc

This 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.

Written
for Dr. Dobbs