1.9 A Vocabulary Tutor


1.9 A Vocabulary Tutor

For the last example of the chapter we are going to have a bit of fun. This program will be an assistant for helping us to learn new vocabulary in a foreign language. The starting point is the table shown as in Figure 1-11 with vocabulary items in two languages. (Translator's note: In this edition they are English and German, as is appropriate for this translation from the German original. In the original, the languages were German and Swedish. Apologies to all you Swedes out there!) Columns C, D, E, and F indicate whether the word in one or the other direction (English German or German English) has already been correctly identified and how many times it has been looked up.

click to expand
Figure 1-11: Vocabulary list with lookup and correct answer information (columns C through F)

When you start the language tutor a dialog appears, as in Figure 1-12. A word is randomly selected from the vocabulary list, where preference is given for words that have not yet been tested and correctly identified. The vocabulary testing goes (also at random) in both directions. If you already know a word, you click on OK, otherwise , on Ask Again Later.

click to expand
Figure 1-12: Form for the vocabulary tutor

With Correct entry and End trainer you exit the dialog. In the first case the input cursor is placed in the column of the vocabulary table from which the last test word was drawn. This makes it possible to make changes in the word list easily.

Constructing the Form

Note  

Almost all of the program code for this example is connected with the form (dialog) shown in Figure 1-12. The greatest hurdle to be jumped consists in constructing this form. If you have never worked with the form editor, you should perhaps take a peek at Chapter 7 in order to understand more fully the relatively brief discussion given here.

Our work begins in the VBA development environment (type Alt+F11 ). There you produce a new form with InsertUserForm. With ViewProperties you open the properties window, whose contents always relate to the object selected in the form. You set the internal name of the form as well as its label with the properties Name and Caption. In our example we will use formQuery as the object name and Language trainer as the title.

Insert into the form, as shown in Figure 1-13, two labels and five command buttons . To do this click on the appropriate control in the toolbox (ViewToolbox) and then with the mouse draw the boundaries of the element in the form. The label field is indicated in the toolbox by a capital letter "A."

click to expand
Figure 1-13: Constructing a Form

For each of the seven controls you must set, as you did previously for the form, the Name and Caption properties. In our example program we have made the following settings:

NAME

CAPTION

PURPOSE

lblWord1

lblWord1

Display the first word

lblWord2

lblWord2

Display the second word

btnNext

Continue

Second word is displayed

btnOK

OK

Word correct, proceed to next word

btnAgain

Ask Again Later

Word incorrect, continue

btnEdit

Correct Entry

Exit form, change word in table

btnEnd

End Trainer

Exit form

Some of the other settings are not absolutely necessary for the proper functioning of the program, but they simplify its use: for the two labels you might set a larger font size with the attribute Font. For each of the buttons you can use the property Accelerator to allow the button to be selected later with Alt+letter. And finally, you can set the property Cancel to True for the end trainer button, so that this button can, as the cancel button, terminate the program and can be invoked with the Esc key as well as by a mouse click.

Program Code for the Form

Note  

Even the program code for this example is somewhat advanced. If you have no programming experience whatsoever, you should perhaps first look at Chapter 4, where elementary notions such as variable and loop, are discussed.

We have now completed our preliminary work. Now we have to provide the form with procedures that will be executed when the form is invoked and the various buttons are pressed. To enable communication between these procedures, certain information must be stored in variables , which will be defined at the beginning of the code module for the form queryForm . (The ampersand ("&") serves to identify Long variables that store integer values.)

 ' Example file Vocabulary.xls Option Explicit Dim firstline&         'first line with words Dim lastline&          'last line with words Dim linenr&            'current line in word table Dim querymode&         ' 0: lang. 1 -> lang. 2,                        ' 1: lang. 2 -> lang. 1 Dim excelWindowstate&  'current window state of Excel Dim startcell As Range 'current cell when trainer is started Const maxTries = 20    'number of tries to find a yet untrained word 

The procedure UserForm_Initialize is executed automatically when the form opens. As long as you are in the development environment, you can simply press the F5 key.

In this procedure the contents of the two label fields are cleared. Furthermore, the variables startcell , firstline , and lastline are initialized . The variable startcell denotes the first table cell of the vocabulary list and will be used in the rest of the program as the starting point for addressing further cells in the list. The variables firstline and lastline provide the first and last line numbers of the vocabulary range.

The calculation of lastline makes use of CurrentRegion , in order to determine the full range of the table (including the title). Rows decomposes this region into rows, while Count determines their number. (These properties will be described fully in the first section of Chapter 5.1.)

 Private Sub UserForm_Initialize()   lblWord1 = "" 'Erase the contents of the two label fields   lblWord2 = ""   Set startcell = Worksheets(1).Range("a3")   firstline = startcell.Row   lastline = startcell.CurrentRegion.Rows.Count   Randomize      'initialize random number generator   ShowNewWord    'display the first word End Sub 

The procedure ShowNewWord has the task of reading a word (one not yet learned, if possible) from the table and displaying it in the first label field. The search algorithm is rather trivial: With the random number function Rnd , which returns a number between 0 and 1, a row ( linenr ) and test direction ( querymode ) are generated. Then, with the method Offset(row, column) either column C or E ”depending on querymode ”of the vocabulary table is examined (see Figure 1-11). If the corresponding cell is empty or if it contains the value 0, then the word is considered not yet learned, and the loop is terminated .

If after maxTries attempts no unlearned word has been found, then a word that has already been learned is tested. For the running of the program this makes no difference ”the word will be read via Offset and displayed in the first label field. The content of the second label field, which contains the word from the previous test, is erased. The following three instructions activate the button Continue, and deactivate the buttons OK and Ask Again Later. Furthermore, the input focus is transferred to the Continue button, so that this button can be operated with the Return key.

 ' randomly choose a word and display it Sub ShowNewWord()   Dim i&   ' attempts to find an unlearned word   For i = 1 To maxTries     linenr = Int(Rnd * (lastline - firstline + 1))     querymode = Int(Rnd * 2)     If Val(startcell.Offset(linenr, 2 + querymode * 2)) = 0 Then       Exit For     End If   Next   lblWord1 = startcell.Offset(linenr, querymode)   lblWord2 = ""   btnNext.Enabled = True   btnOK.Enabled = False   btnAgain.Enabled = False   btnNext.SetFocus End Sub 

The user now sees a form with a single word and attempts to guess the translation. Finally, he or she clicks on the Continue button. In the procedure btnNext_Click the word is displayed in the target language in the second label field. The Continue button is deactivated, and in exchange OK and AGAIN are activated.

 ' show the correct word in the target language Private Sub btnNext_Click()   lblWord2 = startcell.Offset(linenr, 1 - querymode)   btnNext.Enabled = False   btnOK.Enabled = True   btnAgain.Enabled = True   btnOK.SetFocus End Sub 
Note  

The procedure name btnNext_Click has its origin in the name of the object (here btnNext) and the name of the event (Click). To input the code simply execute a double click for the appropriate control in the form. This causes the lines Private Sub name and End Sub to be entered automatically in the program code.

After typing in a response, if the user guessed correctly, he or she clicks OK, and the word will not be asked again. As a result, in btnOK_Click there is stored in column C or E (depending on querymode ) how often the word has been translated correctly. Furthermore, in column D or F is stored how many times the word has been asked. Calling ShowNewWord triggers the display of the next word.

 ' word is identified Private Sub btnOK_Click()   ' Column C/E (correct answers)   startcell.Offset(linenr, 2 + querymode * 2) = _     Val(startcell.Offset(linenr, 2 + querymode * 2) + 1)   ' Column D/F (tries)   startcell.Offset(linenr, 3 + querymode * 2) = _     Val(startcell.Offset(linenr, 3 + querymode * 2) + 1)   ShowNewWord End Sub 

Here btnAgain_Click functions like btnOK_Click . The only difference is that column D/F is changed, but not column C/E.

 ' Word is not identified Private Sub btnAgain_Click()   startcell.Offset(linenr, 3 + querymode * 2) = _     Val(startcell.Offset(linenr, 3 + querymode * 2) + 1)   ShowNewWord End Sub 

Both procedures btnEdit_Click and btnEnd_Click terminate the form. For this the instruction Unload Me is used. In the first case the cell pointer is moved to the last displayed word, so that it can be corrected. In the second case a form is shown to ask whether the modified vocabulary list should be saved.

 ' vocabulary list should be corrected Private Sub btnEdit_Click()   Worksheets(1).Activate   startcell.Offset(linenr).Activate   Unload Me End Sub ' Terminate form, save table Private Sub btnEnd_Click()   Dim result&   Unload Me   result = MsgBox("Should the vocabulary list be saved?", _     vbYesNo)   If result = vbYes Then ActiveWorkbook.Save End Sub 

Additional Code

In order to get the form started correctly, a button (btnStartTrainer) is inserted into the vocabulary table. In the event procedure the form is displayed with Show. This automatically causes UserForm_Initialize to be invoked, and the program proceeds as described above.

 ' Vocabulary.xls, Table 1 Private Sub btnStartTrainer_Click()   formQuery.Show End Sub 

There Is Always Room for Improvement

Of course, there are countless ways in which this program could be improved: a convenient input dialog for new vocabulary, an options form for controlling the test mode (for example, testing only in one direction), a more refined algorithm for choosing the next word to test, extending the table with a column showing pronunciation.




Definitive Guide to Excel VBA
Linux Application Development For The Enterprise (Charles River Media Programming)
ISBN: 1584502533
EAN: 2147483647
Year: 2002
Pages: 134

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net