The Excel Spreadsheet Application as Basis for a

The Excel Spreadsheet Application as Basis for a Computer Assisted Language Learning Activity Authoring System Karl Sklar St. Michaels College School of International Studies Computer Assisted Language Learning GSL 520 Spring 1998 Instructor - Christine Bauer-Ramazani May 3, 1998 1

A spreadsheet consists of an array of addressable cells: each cell is identified by the, column, and row that cross at that cell, (known as its coordinate). Columns are designated by letters, rows by numbers 2

Cell A 1 3

Below are examples of a simple activity designed and run in Excel. This particular activity is designed to encourage the study of adjectival collocates. But, it is easily adapted to many other language based applications. 4

5

Notice that there are two lists on the screen ( they are intentionally obscured). The list on the left is an “array” of adjectives. On the right is a list of common nouns. 6

7

Observe the sliders next to each list. The sliders enable the players to select for display, any adjectival - noun pair combination from their respective lists. 8

9

With a third, smaller slider the player rates the liklihood of occourance of the combination as: high, medium, or low. 10

11

Now, we’ll go through the steps it takes to create this activity in Excel. Initially intimidating, with surprisingly little practice it becomes easy, , , even fun. 12

Begin by creating a text window. Select a cell and adjust its column width and row height until it looks like the illustration. You do this by dragging the appropriate column and row headers. 13

The first adjustment can be rough, it will probably change. 14

The next step is to insert a function into the text window cell. A function is a formula that performs some action. The function we will use here is called the index function. It looks like this: index(cr: cr, cr) 15

index(cr: cr, cr) The function’s “arguments” are in parenthesis. They control its behavior. (cr means column - row, e. g. “b 10” means column b, row 10). 16

index(cr 1: cr 2, cr 3) What does this function do? Actually, it is a sort of pointer. When you install it in a cell the function says, this cell’s value, will become, and will display as mine the contents of whatever other cell I point to. The pointing is specified by the function’s “arguments”, the stuff in the parenthesis. 17

E 11=index(cr 1: cr 2, cr 3) CR 1: CR 2 means all cells between and including cr 1 and cr 2. Eg. a 1: a 100 means: include all cells in column a from 1 thru 100. Cr 3 points to the cell which contains the offset into the array, i. e. , the number of cells “down” from cr 1. The contents of that cell will now appear in e 11. 18

We’ll now begin the actual construction of “the product”. 19

These # put here to clarify “offset” =index(c 7: c 30, c 1) an array is defined by c 7: c 30 (the orange cells) - notice they are all empty. C 1 is the top cell in column C. Notice it contains a 5 (the offset value). Notice also that e 6, the display window is empty. 20

Now, , I’ve typed “test” into the fifth cell of array c 7: c 30. As you might expect, it appears in the text window, e 6 (but, notice it adopts e 6’s formatting). 21

E 6 =Index(c 7: c 30, c 1) a list of words (adjectives) is inserted into c 7: c 30. Warm happens to be in the fifth position. c 1 = 5 so the word “warm” is displayed in e 6 22

We will now install a control object - a slider bar. For this we activate the Visual Basic “programming” feature built into Excel. 23

Clicking on this “tools” brings up the ‘controls’ menu 24

Controls menu Clicking on this activates the slider control option, enabling the placement of slider objects. 25

With the slider installed, the next step is to link it to the index function. To do so we link the slider to cell c 1. 26

Right clicking on the slider brings up this options menu. Click on “properties” to bring up the slider’s properties box. 27

This is the slider’s properties box. Notice the “linked cell” box. Observe that it is set to “c 1”. This done, any change in value output by the slider will be reflected in C 1. Notice that the output range of the slider is 1 - 20. 28

Cell Ci has been linked to the slider. ( notice its value is set at 9). Recall that c 1 selects the cell in the array. 29

As the slider is moved its output becomes the value of cell c 1. This value corresponds to the word next to it. So, as if by magic, , , it appears in e 6, the display window. (remember c 1’s roll in the index function? ) (Now were into the home stretch) 30

The /Tools/options menu allows us to get rid of gridlines and scrollbars 31

Here is how it looks without them. 32

you can now use Excel’s copy and paste feature to duplicate or re create as many of these selectable arrays as needed. We’ll do it once more for the noun set selector (on the right). 33

The word lists can be modified at any time. Changing the list length argument in the function, I. e. (c 7: c 30), lets the list expand up to 65, 000+ cells (c 7: c 65000). 34

The result of our work 35

(The player has under-rated the likelihood of this occourance) 36

Having completed the mechanics, the rest is really just cosmetics. Color, object placement lines, boxes, sound are all optional, the possibilities are endless. 37

so 38

now 39

go crazy!! 40

thanks 41

42