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.
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.
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.
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."
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.
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
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
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.