Economics Network CHEER Virtual Edition

Volume 10, Issue 2, 1996

Creating CAL courseware with Microsoft Excel 5

Joanna Tidball
University of Aberdeen

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 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 spreadsheets 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 dialog 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.

Click to download image
Spinner buttons for varying parameter values. Click to see full size image.

Easier custom dialog boxes

In Excel 3 and 4, custom dialog boxes are defined by a dialog definition table on an Excel macro sheet. However, editing a dialog box or linking macros to the controls on a dialog box through the dialog definition table is not very straightforward. In Excel 5, graphical representations of custom dialog boxes are saved in an Excel workbook as dialog sheets, and making changes to a dialog 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.

Click to download image
Macros attached to options buttons switch between two charts. Click to see full size image.

New workbook structure

All Excel 5 files are workbooks which can contain several worksheets, chart sheets, module sheets and dialog 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-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 main screen of the example is shown in the Figure below. 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 Dialog Boxes - gives examples of more advanced macros and shows how to use different types of dialog box.

5. Distributing Courseware - gives details of ways that you can improve the usability of an Excel application and includes several useful macros.

An introduction to creating CAL courseware with Microsoft Excel 5 costs £20.00 (including postage and packing). Staff in institutions funded by the UK higher education funding bodies, HEFCE, HEFCW, SHEFC and DENI may purchase the tutorial at the discounted price of £12.50.

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

Tel: 01224 273755
Fax: 01224 273752
Email: CLUES@abdn.ac.uk

What's Related:
  · Other articles on the use of spreadsheets in teaching economics
Top | CHEER Home

Copyright 1989-2007