Known vs unknown words – Excel VBA Code

Orock45 · February 15, 2022, 2:30pm

Hi ! I use NihongoDera - Text Analyzer to analyze texts.

And I always wondered “what do I do with this list ?”

So here is what I do:
First, I go to nihongodera and get a nice list.

白熊 (しろくま、シロクマ)
polar bear (Ursus maritimus)
カフェ (カフェ、カフェー、キャフェ、カッフェー、カッフェ)
cafe; coffeehouse

I copy the list to the clipboard.

In Excel, I’ve created two sheets, one is called “Words” and the other is called “Known”.

First step - I work with the “Words” sheet: If any words is in the “Known” sheet, I simply remove them from the “Words” sheet.

Second step - Then I read the remaining list and if I know some more words I move the row to the known sheet.

I’ve made macros (copy-pasting for the internet) to run it smoothly

In Excel, first step is simply “copy-paste”. With a special macro. (Ctrl-w) I can copy paste directly from NihongoDera.

If you don’t use NihongoDera, I’ve created a shortcut to only remove known words from the list (Ctrl-Shift-e)

Then Second step is just another macro (Ctrl-e) to move the entire row from one sheet (“Words”) to another (“Known”).

So, after some texts… your list of “Known” words will grow and the unknown “words” list will be smaller. That way you won’t have to read that big long list again and can concentrate on a shorter list…

When you have experimented with the file and you are ready to start with your own vocabulary lists, just select all words from the “Known” sheet and remove them!

PS: I don’t know how to share an Excel file on this forum so I’ve put it on GitHub

The file extension is xlsm. It requires Macros to be turned on (since it’s built upon VBA Macros…).

Right now, I’m using Excel 2007.

I really hope that someone will find it useful too.

daikirai · February 15, 2022, 3:08pm

Sorry for being skeptical but pretty much the only thing I was ever told about the security with MS products is to not run VBA macroses from the internet as it’s a literal hazard pile and usually used to deliver malware.

I went ahead and checked yours and it seems legit as of e2f4c3d commit.

Orock45 · February 15, 2022, 3:46pm

I know VBA is well-known for viruses…
Anyway i have posted the VBA code on Github in a text file.
That way, anyone who would like to create a similar function with VBA code can do it, even without using the provided file.

Orock45 · February 25, 2022, 5:35pm

Hi!
I’m focusing on NihongoDera so that you can copy paste directly from NihongoDera and have some “bonuses”.
The words you know will automatically be removed from your “copy paste” action. (ctrl-w for pasting)
And i’ve added the frequency too to help (me) focus on important words.
Ctrl-E will make a word known (sending the word to Known sheet).
I’ve added a “readme” (pdf) with some screenshots.

Excerpt from a direct “copy paste” from Nihongo Dera

Excerpt from the known worksheet