Of Maps and Macros: Object Oriented Spreadsheet 'GIS'
in Environment and Planning B, Planning and Design, 1998, 25 pages 227-243.
This exercise is carried out in the Planning Program Decisions Support and GIS class.
This paper explains how object oriented mapping may be carried out in spreadsheets, using simple user-created maps and macros. The method has uses for teaching, research, and policy. The approach is illustrated for Quattro Pro for Windows and Excel with Visual Basic, using two classroom applications, and extensions to decision support, animation and cellular modeling.
The procedures given in the paper provide an elementary set of instructions for users to be able to create their own maps from scratch - that is to draw maps, and to write a simple program to color the map objects so as to display spatial data. The macros have been kept as simple as possible - on the grounds that spreadsheet aficionados will easily create their own extensions once they are aware of the possibilities, and novices are best served by a bare-bones exposition of working examples, which they can then elaborate. Thus, a "minimalist" set of instructions is provided, that nevertheless may be used to develop satisfying outcomes.
The overall process consists of four steps:
Step One: Draw map objects into a graph box using a scanned bitmap image of a regular map as a template. Name the objects appropriately.
Step Two: Record a macro to copy selected data to the mapping worksheet. This provides the list of geographic names (Region) and a scaled color code (RegionIndex) based on the selected data.
Step Three: Write a macro to color the map. For this, the main instruction is recorded by selecting any of the region objects and changing its color using the Format.Object.Patterns options. The resulting command then is edited to read ActiveSheet.DrawingObjects(Region).Interior.ColorIndex = RegionIndex, and enclosed within a For..Next loop.
Step Four: Create a user interface using spreadsheet dialog boxes and macro buttons. Add additional macros to create time simulations and moving objects.
The advantage over current add-in mapping is that the macros can be written by users with basic spreadsheet skills, the procedures are relatively fast, new maps may be drawn or imported, and the macros can be adapted to a variety of applications such as real-time animation, or used in planning situations where 'time is of the essence'. The paper raises a number of issues as to the inflexibility of some GIS modeling capabilities and spreadsheet mapping capability. Spreadsheets provide a tool to explore ideas for novel interfaces or operations, and avoiding some of the continuing frustrations of mainstream GIS, but which might subsequently be implemented within GIS as part of students' own research or professional kitbag. Important here is the direct link to the other facilities of spreadsheet for data processing and model construction. For less advanced students, the method provides an introduction to some GIS concepts and operations, and also a means of de-mystifying the subject, explaining that full-blown GIS is much the same process applied in more detail to vaster amounts of data.