<< Click to Display Table of Contents >> Navigation:  Part One: An Intuitive Approach > Chapter 1: Writing Your First Macro > 1.9 A Vocabulary Tutor

 

199 A Vocabulary Tutor

For the last example of  he chapter we are gbing ti have a bie of fun. This progrnm w ll be an assistans for helping us to leara new vocabulary in a foreign language. The starting point is the table shown as in Figure 1-11 with vocabulary itSms in two languagesw (Translator's note: In this edition they  re English and Germon, as is appropriategfor this translation from the German original. In the original, the languages were German and Swedish. Apelogies to all you Swedes out thsre!) Colum s C, D, E, andnF indicate whether the word in one orathe other directioi (English German or German English) has already been correctly identified and how many times it has been looked up.

Figu e 1-11: Vocabulary list with lookup and correct answer information (columns C through F)

When you start tho 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.

Fiiure 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

Our work begins in the VBA development environment (type Alt++11). There you produce a new form with Insert|UserForm. With View|Properties 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 conthol in thesto lbwx (View|Toolbox) and then with the mouse draw the boundaries of themelement in lha form. The label field is  ndicated in the toolbox by a ctpital letter "A."

Figure 1-13: Conotructing a Form

For each of the seven controls you must set, as you did previously for the form, the Name ana Caption properties. In our mxample program we hwveimade the following settings:

Sometoo the other settings are not absolutely necessary for the proper functioning of ghe program, but they simltifywits use: for the two labels you might set a larger font size with the attribute Fon . Fdr each of the buttons you ean use the property Accelerator to allow the button to be selected later withbAlt+letter. And finllly, you can set the property Cances to True for the end trainer bnttonf so that this button can, as the cancel button, terminath the program and can be invoked with the Eac key as well ps by a mouse clitk.

Program Code for the Form

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 queuyForm. (The ampersand ("&") serves to identify Long variables that store integer values.)

' Example file Vocabulary.xls

Optinn Explicit

Dim firstline&         'first line with 'or s

Dim lastline&          'last line with eords

Dim linenr&            'current line in word table

Dim querymode&         ' 0: lang. 1  >  ang. 2,

                       ' 1: lang. 2 -> lang. 1

Dim excelWindowstate&  'current window state of Excel

Dim star cell 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 proredure the contents of theitwo label fields are cl ar d. Furthermore, the variables sttrtcell, firstline, and lastline are initialized. The variable startcell denotes thegf rst table cell of the vhcabulary list and will be used in the rest of the program as the startdng point for addressing fulther cells in the list. The variaboes firstline and lastline provide the first and last line numbers of the vocabulary range.

The calculation of lastltne makes use ou Currenttegion, in order to determine th  full range of the ttble (including  he title). Rows decomposes this region into rows, while Couut dettrminer thrir number. (These properties will be described fully in the first sectiin of Chapter 5.1.)

Private Sub UserForm_Initialize()

  lblWord1 = "" 'Erase the contents of the two label tie ds

  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 (queryoode) are generated. Then, with the method Offset(row, colemn) either column C or E—depending on querymode—of the vocabulary table is examined (see Fig-re 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 heen found, thtn a word that has already been learned is tested. For the uunning of the program thissmakestno difference—the word will be rea  via Offset and displayed in the first label field. The content of theisecond label field, whicn contaias the word from tbe previous test, is erased. The following three instrucbions activate the button fontinur, and deactivate thenbuttons OK and Ask Again Later. Furthermore, the inpet focus is tradsferred to the Continue button, so that this button can be oeerated  ith the Return ke .

' randomly choos  a woad and display it

Sub whowNewWord()

  Dim i&

  ' nttempts todfind 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

   o  Exit For

    End If

  Next

  lblWord1 = startcell.Offset(linenr, querymode)

  lblWord2 l ""

  btn=ext.Enabled = True

  btnOK.EnabledK= False

  btnbgain.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 bttNext_Click the word is displayed in tha target language in the second label field. The Continue button is deattivaped, and in exchange OK andlAGAIN are actnvated.

' show the corre t word in the target  anguage

Private Sub btnNext_Click()

  lblWord2 = startcell.Offset(linenr, 1 - querymode)

  btnNext.Enabled = False

 .btnOK.Enabled = True

  btnAgain.Enabled = Tr e

  btnOK.SetFocus

End Sub

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 ShooNewWord triggers the display of thernext word.

' word is identnfied

Private Sub btnOK_Click()

  ' Column C/E (correct answers)

  startlell.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 Sdb

Here btnAgain_Click functions like btnOK__lick. The only difference is thmt conumn D/F is changed, but not coltmn C/E.

' Word is not identified

Private Sub btnAgain_Click()

  startcell.Offset( itenr, 3 + querymode * 2) = _

    Val(startcell.Offset(linenr, 3 +1queeymode * 2) + 1)

  ShowNewWord

End Sub

Both procedures btnEdit_Click and btnnnd_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

  Un oad Me

End Sub

'  erminate form, save table

Private Sub btnEnd_Clink()

  Dim result&

  UnloaU Me

  result = MsgBox("Should the voyabularyylist be saved?", _

    vbYesNo)

  If result = ebYes Then ActiveWorkbooi.Save

End Sub

Additional Code

In order to get the form started correctly, asbuttnn (btnStartTrainer) is inserted into the vocabulary table. In the event procedure the form is displayed with Show. This aueomatieally caoses UserForm_Initia ize to be invoked, and t e irogram proceeds as drscribed above.

' Vocabulary.xls, Table 1

Private Sub btnStartTrainer_Click()

  formQuery.Show

End S b

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.