Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Help find SIMPLE ERROR in coding for random search in list

0

Dear Forum, 

Please find enclosed file - 

"Indonesia Vocab - Macro list question - Working- 10th July 2021 -(edit)"

This file contains a list of Indonesia words for learning the language.

If the active cursor is placed in cell E13 then press Left arrow then new Indonesian word / phase will appear with the corresponding English meaning in the cell E20. 

The VBA macro for this worksheet was already setup by VARIATUS,  a very kind & helpful person from this forum.

BUT I have discovered a problem  - after a few months of using this worksheet I have found that as I enter NEW words to the bottom of column D, IE beyond Row 4375, but no (words / phases) are select.  Random row numbers are still appearing below 4375BUT only the contents of row (D,4375) appear IE 

Kepada anggota group yang merayakan Natal saya mengucapkan Selamat hari Natal

 ... this is obliviously some small typo in the coding, BUT I cannot see it... ( I don't have any knowledge  of VBA programming. Only a very small amount of understanding) 

BTW - all the other functions of this enclosed macro are correct - like assigning Blocked to known words/phases when cell E13 is double clicked.. No change required to any of the other coding. 

Please could someone review & revise to coding to correct the error of search NEW words/phases in column D.

Really appreciate your help.TIA

excel
MS Office Version: 2010
posted by: Leander Rebanks (rep: 4) on Sun Jul 11, 2021 at 11:09 pm
Answer
Discuss

Answers

0
Selected Answer

Hello Leander,

The reason for your experience is that you are leaving column D blank. The code is based on there being numbers in that column.

There are several ways in which we could deal with the problem. One is to add numbers to colmn D automatically. Another is to delete column D entirely. I favour the latter because, as you found out, the numbers in column D can easily be replaced with Excel's own row numbers. But If I remove the column your instructions won't be correct anymore.

So I think the best way forward is for you to decide how you want to deal with column D.

  1. Add numbers there as you add phrases in columns E:F
  2. Have the program add missing numbers automatically (which may mess up "Blocked" numbers in some circumstances)
  3. Remove colmn D.

It's the curse of that index. This is what's happening:-
The code produces a number between 24 and the last used row in column D. Correct after the above corrections. It then retrieves the number in column D of the row it determined and writes that number to D13. Correct. Then your formula in E13 uses that number to retrieve the word using the formula =VLOOKUP(D13,D24:F4398,2). Wrong!

For one thing, the lookup range ends in row 4398 - far smaller than the actual determined by the code. For another, your formula returns an approximate value if it doesn't find an exact one. This is why it always returns the last value when the real value is not within range. To disable that feature the function  should stipulate the match as "not approximate", resulting in  ...

=VLOOKUP(D13,D24:F4398,2,False)

This corrected formula will fail to find a match (#N/A error) because the number you want isn't available in the defined range. Change the range definition and you are back in business.

EDIT 20 Jul 2021

Our project will benefit from your imput from now on. Therefore I attach the workbook on which I have been working these past 9 days.

After opening the workbook select "Teacher" as user and enter your birthday as 31 December 2020 (using the way your computer is  set up to understand dates). This will give you access to the hidden worksheets which include, apart from the vocabulary itself, the tabs "Users", where you can see and set passwords (birthdays), "Help", where the system is explained, and "Methodology" where I wrote down some of the ideas I tried to implement. The user interface is designed to be intuitively usable but it will help you to read all I wrote.

It's a complicated project and I spent the last many hours on trouble-shooting. I discovered some flaws, which I fixed, but mostly just apparent flaws caused by lack of data. So, your part of the deal will be to convert your existing data into a system that my program is designed to work with. Then you may find real bugs which I shall be glad to fix once you describe their effect to me.

Good luck.

posted by: Variatus (rep: 4889) on Mon Jul 12, 2021 at 10:27 am
Discuss

Discussion

Dear VARIATUS, Many thanks for your reply. With reference to your suggestions – 1, 2 or 3 above : – I certainly favour option 1 - Add numbers there (column D) as you/(I) add phrases in columns E:F However, I have just tried this and it doesn’t stop the problem. Please try for yourself with full list of index numbers in column D With active cursor in E13 – press right arrow until you see index number in D13 > 4375 You will then notice that the word/phrase does not correspond to the index number selected – actually any index > 4375 returns last the original (last) phrase “kepada…etc” Please try for yourself to see.. I really appreciate your help & suggestions. Looking forward to hearing back from you… Lea…

PS - I do need the reference index in colun D because sometimes the phases need to be edited & therefore easier to find. Also I need indexing to be sure that new words are also being selected.
Leander Rebanks (rep: 4) Jul 12, '21 at 9:42 pm
Leander,
The entire system is error prone because of its design. The number in D13 shouldn't be allowed to control everything. That control belongs to the number generated by the code. The code should write the word to E13 and the number to D13 for information only. The formula in E13 is superfluous, in fact. Nothing but trouble - as you see :-)
I have started work on an improved version of your idea (I am so much enthused by it!). My new design will not have column D or its information. But please do let me know how you use those numbers to keep track of your phrases. Tell me why you can't use Excel's row numbers for the same purpose. I will offer you an alternative way once I understand how you make column D work for you.
Variatus (rep: 4889) Jul 13, '21 at 12:02 am
Dear VARIATUS, Many thanks for your latest reply regarding Lookup formula. I have edited it & now everything works perfectly - Really appreciated!
For the formula =VLOOKUP(D13,D24:F4683,2,FALSE)
 the F4683 is now the last row in column D.
Lastly, is there a way of substituting the reference F4683 = for the LAST cell in column D? Otherwise I will need to add new reference everytime I add new word/phases to column E.
Leander Rebanks (rep: 4) Jul 13, '21 at 5:48 am
Hi Variatus, Totally agree with your last comments regarding not requiring D13 and not using my formula in E13. I think you already know how I am using the current vocab list – via searching new random words by “right arrow” then double click to block known words/phases. The ONLY reason I have D13 reference is to know the cell location of random phase selected – I could certainly use excel cell reference instead of my own indexing. Therefore I am totally agree with you to remove column D & my indexing formula. BUT still maintaining the desired function of the worksheet. I really appreciate your attention to this & admire your enthusiasm for this application. Looking forward to hearing back from you. TIA.
Leander Rebanks (rep: 4) Jul 13, '21 at 9:47 pm
Hello Leander, Today will be the third day I'm working on your project full time :-) It's tuyrning out bigger than I thought. But it's also turning into something different than expected. Don't peg your hopes too high. You may not like it. I think I can finish tomorrow - not today.
As for your lookup formula, you need a "dynamically expanding named range". These are two concepts, "dynamically expanding" and "named range". The former is a formula and the latter transfers the formula from the cell to a named range so that the VLOOKUP formula that contains the range reference can just use the range's name instead of the clutter of an unwieldy function to return the last row. I suggest you google for these things first and come back with a new question if you need help.
Regards!
Variatus (rep: 4889) Jul 14, '21 at 8:11 pm
Hello Variatus,
Many thanks for the amazing coding & effort you have put into the last worksheet.
I have fuly reviewed the documentation on HELP and methodology (seems you must have done alot of research of teaching lanuages to write this).
I entered Teacher user & bithday.
Also I added my full vocabuary in vocab coloum, but I am receiving "Runtime error - 1004" 
I have attached - 224 TXL 210717 Teach Vocabulary - 21th July 2021
at the top of thread with full vocab - please review & advise how to make work without runtime error.
Looking forward to hearing back from you.
Kind regards,
Lea...
Leander Rebanks (rep: 4) Jul 21, '21 at 9:59 pm
Hello Leander,
I have no idea what causes the crash. I think - maybe - I remember having that problem before and fixing it by not using the CodeName for the worksheet in question (with the thought that perhaps the CodeName isn't yet available that soon after opening the workbook. Fact is, I don't know. But it's funny because your workbook crashes. My own workbook doesn't: same code, same Excel version, same computer - and there's nothing wrong with the code. Perhaps the reason is that your version of Excel isn't 365 and made some invisible changes to the file.
Luckily, we may have an easy solution. The offending line (Dash.Activate) isn't really necessary at that point. Please just remove it. Instead, please include the following line at the top of the procedure ResetDashBoard: 
Sub ResetDashBoard()
    ' 224
    
    If Not Dash Is ActiveSheet Then Dash.Activate
    Application.EnableEvents = False

Actually, this will run only one nano-second later than before but I hope that will prove to be enough. If not, I shall have to invent something more complicated. :-)
Regards
Variatus (rep: 4889) Jul 22, '21 at 9:26 pm
And here is the point of the exercise: Under normal circumstances the Dashboard would be the ActiveSheet. But if it is not, then one of the other sheets must be active at that moment, and all the other sheets are being hidden in the next step. When the code tries to hide the ActiveSheet a crash may occur. This line of code intends to make sure that the Dashboard is active, meaning the code will not try to hide it. There will not be any adverse effect if it's missing until one day when you forgot all about this.
Variatus (rep: 4889) Jul 22, '21 at 9:27 pm
Hello Variatus,
Many thanks for your latest comments & code suggestion.
Today I opened the program & just signed in as pupil1 and the program seemed to be working correctly. (As the other tabs vocab, help & Method were hidden.)
This gave me a chance to "play" with your newly designed program - it is Fun & Enjoyable to use!.
I like the red dialog of encouragement when correct.
However I am a little confused because the loop of words / phases only seems to pick about 5 or 10 different items then it random loops again. Remember I have already added a vacb list of approx 5000 words / phases.
I was hoping the program it would do a random select on ALL words / rows in the vocab database - approx 5000 words / phases / rows - its obviously a setting that I haven't figured out yet. Please can you advise how I can edit coding to ramdom select from whole vocab list.
(FYI - I didn't need to change any coding, so I have left everything as you originally sent it. Also FYI - I m not using Excel 365)
I would prefer the setup the a random word / phase is taken from the whole database - not just 20 new words a day.
I really appreciate & admire the work you have put it this clever little program...
Kind regards, Lea...
Leander Rebanks (rep: 4) Jul 22, '21 at 10:28 pm
Hello Leander,
This is a little complicated. I didn't find out until I thought about the subject deeply. And then I explained about methodology where you can find my thoughts in detail.
In essence, the purpose of blocking certain items is to avoid asking items the student knows and it negates the purpose of selecting at random. "Any one but ..." isn't the same as "any one" and once it's not random one has to decide which to show, and this is answered by the purpose of blocking: "show one that is new to the student".
At the same time, with 5000 items, the student may ask 25,000 times and never hit every one. This fact - a feature of "random" - makes it impossible to teach a curriculum. This led me to design "levels" which I imagined as about 5 words plus 25+ phrases. I argued that the student can't understand the phrases until he lerarned the words. This realization ended my interest in selecting at true random from 5000+ words: If the words are not somewhat controlled the student gets too many unknowns. If this is intended and part of the system, then giving preference to teaching words will definitely improve efficacy.
In consequence, I abandoned that part of your idea and that is the way the system is. If you want to maintain random picking of all phrases we must take your original design as a base and improve its functionality. The system I designed doesn't lend itself to that method.
Variatus (rep: 4889) Jul 23, '21 at 7:53 pm
In my workbook all items are put into a chain. Then a random selection is made from the first 50. If the student knows that item it becomes blocked, meaning it's excluded from the first 50 and another item takes its place.
The initial chain sorts items by Level. All items of level 1 are added to the chain before items of level 2, level 2 before level 3 etc Therefore the first 50 include more items of lower levels but there is no rule that it can contain only one level. It's open end.
The initial chain includes only items of one class: either Bahasa - English or English - Bahasa.
The initial chain also only includes only one type (words, phrases or both ["Idioms"]). Therefore you can have all your vocabulary included in the chain by selecting "Idioms".
Finally, the random selection is skewed in two ways. One, for the first 5 levels the number 50 is reduced: 25, 30, 35, 45 and 50. The chain has the same length but the number of items from which a random is drawn is smaller. Two, once a student knows 66% of one level he advances to the next. This has no effect on the chain or the selection size but it will give preference to the remaining 33% of the items of the previous, lower level. They will be shown more frequently.
Variatus (rep: 4889) Jul 23, '21 at 8:26 pm
Today's comment (part 3)
The system always works with all items in the vocabulary, meaning they are in the "chain", but there are a number of ways in which you can limit the effective choice to a much smaller number.
(1) The "Selection size" (50 by default) can't be bigger than the number of items in the "chain". E.g. if you have only 25 items in the "Understood" class, the "chain" will only have 25 items (all levels), and the selection size will be 25 minus number of items "known" (=blocked) while that class is selected.
(2) The length of the chain is further reduced by the selection of "Type" (Words, Phrases or Idioms [=both]). If, of the 25 words of "Understood" class, only 5 are categorized as "Words" then the chain will only ever have 5 items while type "Words" is selected.
(3) If you start at level 1 and you never mark any item as "Known" then the selection size will forever only comprise of the same 25 items.
Bottom line, Leander, I think your complaint is based on vocabulary classification, not the system's limits. Apply filters to the Vocabulary to see how many and which items are in each Class, Type and Level.
Please consider the fact that there is absolutely no difference between one item selected randomly from 5000 or 25. Key is that the item must not be known to the user. It doesn't matter how it was selected. But if it was selected more smartly the efficacy of teaching will be higher.
Regards
Variatus (rep: 4889) Jul 23, '21 at 9:15 pm
Hello Variatus,
Thanks for the detailed explaination & all the work you have put into the project.
I will continue to play around with your program,
Really appreciated.
Best regards,
Lea...
Leander Rebanks (rep: 4) Jul 24, '21 at 5:52 am
Leander,
I'ver been thinking that the way forward I have planned for may not be as easy for you to find as I anticipated. So, here is some more explanation.
Initially, your Vocabulary has 3 columns of blanks: Class, Type and Level.
(1) Where the Class is blank the item is counted as "Understood"
(2) Where the Type is blank the item is counted as "Phrase"
(3) Where theLevel is blank the item is counted with the highest level.
This means that the "chain" of Speak/Words is empty and that of Understood/Phrase has all 4000 entries. The level has no bearing on the chain. or the selection size.
To start converting your Vocabulary for use with this system you should first eliminate the incompatible. That is your entries with reversed languages. They are incompatible because my system switches focus whereas yours switches columns. To adapt, mark those items with "Speak" and switch the texts so that Bahasa is in the Bahasa column. Don't change the classifications of Type and Level at this time.
(continued)
Variatus (rep: 4889) Jul 25, '21 at 8:15 pm
(cont. 2)
In the second step you may go through all your items and classify them as "Words" or "Phrases". That will leave you with 4 chains as follows ...
(1) Lots of "Understood" (incl. blanks) but just a few "Speak"
(2) Lots of "Phrases" (incl blanks) but relatively few "Words".
Now you should start on setting levels. This is much work and you should be careful to do it right. Therefore invest time on planning.
At the core of the method is the "word". Remember, you want to make phrases using only known words. Therefore you have to select the words you want to teach carefully and methodically. I would recommend making a list of the first 150-odd words and divide the list by priority. You will find that words like I and you have a higher priority than he and they, school before university, garden before field, car before airplane.
I suggest 6 words for each level. So, having sorted your 150 words by priority, divde them into groups of 6 and assign levels to the groups from 1 and up. That would give you 30 levels.
Enter these 150 words at the top of the Vocabulary. Assign all of them to Class "Understood". I would classify them by word type (nouns, verbs, adjectives etc) but that isn't required. I think it will help you to be able to filter by word type and know your statistics.
Add English translations and eliminate duplicates (using Excel's Find function).
(continued)
Variatus (rep: 4889) Jul 25, '21 at 9:05 pm
(cont. 3)
Now create the "Speak" equivalents for your 150 words. It's likely that there will be more than 150 equivalents because each Bahasa word has several near meanings in English. E.g. "bagus" may mean good or satisfactory but the exact translation of "satisfactory" will not be "bagus". There is no need to stick with only 150 English words. The "Speak" translations do not exactly mirror the "Understood" translations. This way you can teach nuances.
Enter the "Speak" translations in the same sequence as the original words, perhaps just below each other for easy referencing. Remember, the sequence of entering will determine their place in the chain.
In the next step create phrases. Use the Find function to find existing ones. Review them. Eliminate duplicates. Mark them as "Understood" and Type "P0", and assign phrases to the same level as the words they teach. Remember that the phrase "This is good" can only be taught in the level in which the last of its 3 words was introduced. At low levels this is a problem. But that soon gives way to an abundance of possibilities.
Filter the Vocabulary by Level and Class to pull up lists of what you have. Add what's missing.
Then create the "Speak" equivalents of the entries you created.
(continued)
Variatus (rep: 4889) Jul 25, '21 at 9:15 pm
(cont. 4)
You want at least 25 phrases in each level, always re-using the words previously taught. Remember, the words on their own will not come up again but the student meets them again and again in the phrases. So, he learns to use them.
Obviously, the way to work is to do one level at a time. As you search for existing phrases using the words you intend to teach you will find inspiration.
If you have 30 levels of 6 words and 25 phrases each, you will have 930 entries at the end of the above plan. That leaves you with thousands of phrases, and some words, that were not used yet. You can filter them by blanks in the Class, Type and Level columns. Remember that these entries will still be in the chains but they will only come up after the first 30 levels. I suggest you classify them as "Understood" and even set their Type but don't assign a level.
Instead, you can pick 6 words from them and create a Level 31 etc in the same manner for creating levels I described above.
(continued)
Variatus (rep: 4889) Jul 25, '21 at 9:46 pm
(cont. 5)
One more thing to remember: The chains from which questions are picked always include items from the next higher level, meaning words the student didn't learn. The smaller the number of items per level, the larger the number of higher level items in the chain. For "Words" it would be 44 out of 50 and for "Phrases" only half the chain can be of the current level at maximum. A student advances to the next level when he knows 66%. That means that the chain also includes items from lower levels.
This uneven advance is partly abated by the system but the student must also help by spending a small percentage of time on learning words rather than phrases.
The system will reset all words marked as "Known" from the previous level (and up) and all phrases marked as "Known" from the new level (and up). This happens when a user's level is upgraded because he learned more than 66% of the previous.
As I am writing this it occurs to me that this should cause a circular reaction where the reset causes the learning level to drop below 66%. I think the problem should self-adjust but we have to see how it develops in practise.
(continued)
Variatus (rep: 4889) Jul 25, '21 at 10:23 pm
(cont. 6)
By numbers: If there are 31 items in a level (6 words + 25 phrases) then 67% would be reached at 21. Presume that all 6 words were learned then 10 phrases are still not learned. Now the 6 words are reset bringing the total of "known" items down to 15, which is half of the 31. The cycle will be broken when the user has learned 21 of the phrases.
In other words, it can't be broken at all (meaning the leval can't ever advance) if there aren't more than 21 phrases in a level. It's easier to break when there are more than 25 phrases. The calculation changes if the number of words is larger or smaller than 6.
Please keep an eye on this. Maybe the code needs to be tweaked. The problem could be defeated if the 66% level would be applied to the phrases only. I prefer not to invest more time in the system until you have developed a method of creating levels (words + phrases). Maybe my theory doesn't hold up in practice.
Such an improvement would also give us the chance to move to a new question and close this thread before Don complains :-)
Regards
Variatus (rep: 4889) Jul 25, '21 at 10:23 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login