MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel-based language software

Posted by Bob2000 on January 10, 2002 8:45 AM

Hi! I am currently trying to write a macro to brush up my Spanish. In the first column of an Excel spreadsheet are the Spanish words. In the second column are the English equivalents. More columns may be used in case of additional meanings. How do I get a macro whereby a dialog box would appear when prompted that would randomly ask for the Spanish word for any of the English counterparts in the list (including extended meanings), or the other way round, and where you could write your response and then get a score from 0 to whatever the number of words it asks for(based on the number of words you actually got right on total that could well be over 100!). Additional features may include time limits, past performances, etc. but this is a frill that would be a nice to have only. Thanks for the help!

Posted by Juan Pablo G. on January 10, 2002 9:25 AM

One thing at a time.

For the first part, i would use RAND function, to generate a number between 1 and the number of words in column A (COUNTA(A:A)), that would be

MyRand = Int(Rnd()*Application.Counta(Range("A:A")))+1

Then, use the MATCH function to get that word

MyWord = Application.Match(Range("A:A"),MyRand)

Then, you can display this string 'MyWord' in the userform

Juan Pablo G.

Posted by Bob2000 on January 11, 2002 7:17 AM

Thanks for the tip!