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 self­p;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


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