UniServe*Science News, Vol. 5, November 1996
Creating CAL courseware with Microsoft Excel 5
Joanna Tidball, CTI Centre for Land Use and Environmental Sciences, University
of Aberdeen, UK.
Spreadsheets provide an easy way to develop computer assisted learning (CAL)
courseware based on simulation models or databases. Studentsí learning
can be greatly enhanced by investigating models of real life systems. These
models can be of biological, chemical, physical, and socio-economic systems.
Spreadsheets can also get students actively involved in problem solving.
In 1993 the CTI Centre for Land Use and Environmental Sciences produced
a self-teaching tutorial on developing CAL courseware using the spreadsheet
Microsoft Excel (A practical introduction to creating courseware with
Microsoft Excel by Mary L Cuttle, Clive P L Young and Simon B Heath).
This covered Excel versions 3 and 4. It showed how to use Excelís
built-in features to create an effective user friendly interface so that
the resulting CAL courseware was as educationally effective as CAL courseware
developed in other software environments (see Young, Heath and Cuttle, 1994,
The CTISS File 17, 54). The great attraction of this approach
for lecturers, who have existing spreadsheet skills, is that with a small
investment to enhance these skills they are able to develop educationally
effective courseware from their own existing spreadsheet models and databases.
The tutorial described how to improve the formatting of a worksheet, add
text boxes and other graphic objects, create charts, and use dialogue boxes.
It also demonstrated how to write simple Excel macros that can be run by
clicking on a graphic object, such as a button.
Excel 5 has many new features that make it a much more powerful CAL development
tool than previous versions. A new tutorial on creating CAL courseware with
Excel 5 has recently been published. An introduction to creating CAL
courseware with Microsoft Excel 5 has been developed under Project
LoCAL (funded under the Teaching and Learning Technology Programme). This
new tutorial is an update on the previous tutorial and is based around a
new example. It shows how to take advantage of the following new features
of Excel 5:
On-sheet controls
In previous versions of Excel, the only type of control that could be placed
on a worksheet was a command button or a graphic object with a macro attached
to it. Also, no buttons or graphic objects could be added to a chart sheet.
In Excel 5, a variety of controls can be placed on a worksheets and chart
sheets. These include option buttons, check boxes, drop-down lists, list
boxes and spinner buttons. Macros can easily be attached to these controls.
Spinner buttons for varying parameter values
Easier custom dialogue boxes
In Excel 3 and 4, custom dialogue boxes are defined by a dialogue definition
table on an Excel macro sheet. However, editing a dialogue box or linking
macros to the controls on a dialogue box through the dialogue definition
table is not very straightforward. In Excel 5, graphical representations
of custom dialogue boxes are saved in an Excel workbook as dialogue sheets,
and making changes to a dialogue box or attaching macros to the controls
is much easier.
Menu editor
Excel 5 has a Menu Editor which makes customisation of the menu bar far
easier than in previous versions. It is straightforward to add or remove
menus and menu items, and a customised menu bar can now be saved with a
file.
Visual Basic for Applications programming language
Excel 5 has Visual Basic for Applications (VBA) as its macro language. This
is a powerful programming language which can be used to control Excel objects,
such as cells, buttons and charts. It is great advance on the original Excel
macro language and can be used to develop very sophisticated custom applications.
VBA procedures are entered on module sheets. These are far more convenient
to use than the previous Excel macro sheets where code had to entered into
spreadsheet cells.
New workbook structure
All Excel 5 files are workbooks which can contain several worksheets, chart
sheets, module sheets and dialogue sheets. The names of the sheets in a
workbook can be displayed on tabs at the bottom of the screen and users
can move to a different sheet in a workbook by clicking on its sheet tab.
Sheets can also be renamed so that their function is more selfp;explanatory.
In previous versions of Excel, sheets had to be created separately and then
grouped and saved as a workbook.
On completing the tutorial, An introduction to creating CAL courseware
with Microsoft Excel 5, the learner will have created a CAL courseware
module designed to help students learn about the normal distribution. The
self-teaching tutorial takes the learner step-by-step through the development
of the module. Learners will then be able to develop their own applications
using and adapting the ideas and features learnt with the aid of the tutorial.
The tutorial is divided into the following five chapters:
1. Building the Spreadsheet - shows how to set up the main worksheet
for the example and embed a chart in it.
2. Improving the Interface - shows how to improve the appearance
of your worksheet by using graphic objects.
3. Using Controls and Macros - introduces ways of customising
an Excel application with on-sheet controls and simple macros.
4. Adding Dialogue Boxes - gives examples of more advanced macros
and shows how to use different types of dialogue box.
5. Distributing Courseware - gives details of ways that you can improve
the useability of an Excel application and includes several useful macros.
Joanna Tidball
clues@abdn.ac.uk
Macros attached to options buttons switch between two charts
An Introduction to Creating CAL Courseware with Microsoft Excel 5
Cost: £25 (including postage and packing).
You can obtain your copy by sending an order to:
Centre for Computer Based Learning in Land Use and Environmental
Sciences (CLUES),
MacRobert Building,
University of Aberdeen,
Aberdeen AB24 5UA, UK
Tel: +44 1224 273755 Fax: +44 1224 273752
Email: CLUES@abdn.ac.uk