CheckKanji Excel Function

Here is my kanji grade level checker written in VBA for Excel.

Link:

http://www.aevanko.webs.com/checkkanji.txt

How it works:

Say your cell is A1, then you'd write: =CheckKanji(A1) and by default it will check for any kanji outside of 6th grade. You can also specify the grade level. =CheckKanji(cell, 1) will check (and list up) any kanji outside of Grade 1 level.

I made the Kanji list STATIC meaning that it loads all of it into memory (not really heavy or anything) the first time it's run, and it stays there until Excel is closed. This makes the function run really really fast on all the cells in real-time. Some people prefer never to use the static keywork for variables in functions, but this is one case where it really really helps.

Anyone there?

Does anyone even read this blog? I was really hoping for feedback and even requests but so far this has been a one man affair. :(

iPhoneからの投稿

Working with ranges in excel

In Excel, it's much faster to dump the values of a range of cells into a variable array and loop through each entry than have VBA access excel over and over again. Even faster if you do a quick conversion from variant array to single-dimensional string array!

iPhoneからの投稿

VlookupLeft (Function)

Vlookup is a formula that will change the way you use Excel. It's only downfall is that you can only use it to return values in the rows to the right side of the cell that contains your search value.

Here is a simple function that will allow you to return values to the left of the search value. Just give the function the cell with the value you want to look for, then the column you want to look in (any sheet). As the final 3rd parameter, tell it how many rows forward or back it should go to find the return value. -1 is for the value to the left of the search column and 1 is for the row to the right, etc.

This is useful when the client updates a file and the order of the text is different and you need to copy paste everything you had done previously into the new file. Assuming the client is smart enough to use text IDs that don't change, run this. Give it the ID of the text then the column in the old file where it can look for it. Then if the ID is found, it will return the value found x rows to the right/left of the ID cell. Copy paste done in just a few seconds!

Vlookup is godly and fast, so only use this if you have to get the value in a row to the left of the search column (in those weird cases where the translation is in the column to the left of the original text or the column with the text ID).

------------------------------------------------------------

Function VLookupLeft(ByVal lookup_value, _
ByVal lookup_column As range, _
ByVal return_value_column As Long)

Application.ScreenUpdating = False
With Application
VLookupLeft = .Index(lookup_column.Offset(0, return_value_column), _
.Match(lookup_value, lookup_column.Columns(1), 0), 1)
End With
Application.ScreenUpdating = True

End Function

------------------------------------------------------------

CreateList (Function)

This is a useful function when you have several cells you want to combine to make a single cell. You can optionally specify a divider as well, so say you have:

A1: 15

A2: 20

A3: 25

You can combine them into one string, seperated by a comma by doing =CreateList(A1:A3, ", "). What you'll end up with is "15, 20, 25". The function will ignore all empty columns in the range of cells you give it, as well!

-------------------------------------------------

Function CreateList(ByVal cell_range As range, _
Optional ByVal seperator As String) As String

Application.ScreenUpdating = False
Dim cell As range
Dim newString As String
newString = vbNullString
Dim Counter As Integer
Counter = 0

For Each cell In cell_range
Counter = Counter + 1
newString = newString & cell.Value
If Counter < cell_range.count Then ' If there is more to add
If Len(cell.Value) <> 0 Then ' see if the cell is not empty
newString = newString & seperator
End If
End If
Next

CreateList = newString
Application.ScreenUpdating = True