Find Term (Function for Translators)

The code below may be hard to read since the formatting is stripped away, but this is probobly my most useful function when it comes to translation.

What it does (in layman's terms):

Create a row and run this function down it. Tell it where the glossary is and it'll search your cells for every term in the glossary. It will give you back a list of all found terms, along with the translations. A perfect reference column to create before begining translation so you never have to look at the glossary while working!

There are 2 parameters: You pass the cell with the text, then the column that contains your glossary (can be a seperate sheet). It will look through the cell for each word in your glossary and at the end, the function returns a result string that will list all terms found, along with the translation in the row to the immediate right of the glossary term.

There are some sub routines on the web that will do a group search and replace in your text, but that is no-good in my book for several reasons. First, it's ugly. You shouldn't be replacing terms in the original text with their translations. Second, it changes the actual text so if something goes wrong, it's hard to spot. Third, sub-routines can't be undone, and that's not good.

This kind of a function makes it so you never have to open the glossary file and look for words - you just pre-process the file by making a REF column and running this function down it. Then paste special (copy, then right click and select paste special, then VALUE), then you are good to start translating. Since the original text isn't changing there is no need to keep the row as a formula after all.

With this in mind, to give it a speed boost, I made the glossary a static variable. This means that unless it is changed, the glossary will remain in memory for the entire time you have the file open. So just pre-process the file, and if you really care about memory, just save and reopen the file again. Static variables can be really useful in situations like this since there isn't a need to keep recreating the glossary array for each cell.

How it works (programmatically):

When you pass it the column (like A:A) that has the glossary terms, it will create a variable array that contains each entry in the list, and the entries in the row to the right. It then goes through all of them and uses Instr to check if the term is inside the cell or not. If it is, it adds it to a new string called result, along with "=" and the translated term, then a line break.

Note: The glossary has to start on the first line of the column (so A1 if A:A) and there can't be any empty rows in the middle of the glossary. This is something I will create a work around for eventually, but I haven't yet. The reason is that in order to avoid empty strings being considered part of the glossary (in which case it would find in every cell of course), the glossary array is created by taking the first cell in the column and an entry is made for every consecutive term until it reaches a blank cell. Then it considers that to be the end of the glossary. The reason I choose to go with just specifying a column instead of a specific range is that when you copy the forumula down the file, A:A will remain A:A while something like A2:A100 will get incremented and that's a pain in the butt.

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

Function FindTerm(ByVal text As String, ByVal term_list As range) As String

Application.ScreenUpdating = False
Dim result As String
Dim i As Long
Static glossary As Variant
glossary = range(term_list.cells(1, 1), term_list.cells(1, 2).End(xlDown))

For i = 1 To UBound(glossary)
If InStr(text, glossary(i, 1)) <> 0 Then
result = (glossary(i, 1) & " = ") & (glossary(i, 2) & vbLf) & result
End If
Next

If Len(result) <> 0 Then
result = Left$(result, (Len(result) - 1))
End If

FindTerm = result
Application.ScreenUpdating = True

End Function

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

RegexExtract V2.0

I tweaked my RegexExtract function so that it supports multiple capture groups. It'll return a string comprised of just the stuff you put in parenthesis. If anyone wants a version where you can specify a seperator (a string of characters that should go in between each match, like a comma), just let me know if the comments.

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

Function RegexExtract(ByVal text As String, _
ByVal extract_what As String) As String

Application.ScreenUpdating = False
Dim allMatches As Object
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
Dim i As Long
Dim result As String

RE.Pattern = extract_what
RE.Global = True
Set allMatches = RE.Execute(text)

For i = 0 To allMatches.Item(0).submatches.count - 1
result = result & allMatches.Item(0).submatches.Item(i)
Next

RegexExtract = result
Application.ScreenUpdating = True

End Function

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

Functions vs Sub Routines

You'll notice that I won't generally post sub routines. Unlike functions, they allow you to alter properties of the worksheet but they also can't be undone and they alter the original text.

In translation and text manipulation, automation is risky work so by principle I write functions that will return the result I want, then I'll copy special by value and overwrite the original.

Functions are also great since you can write them so that they are flexible. IMO, you should never have to alter a function for each project.

Functions also rock since they can be used just like formulas in Excel. ^^


iPhoneからの投稿

Indenting Code

Please note that Ameba destroys the indenting and formatting of code I post so it makes it harder to read. My apologies!

Remember that proper indentation is as important as properly naming your variables!

iPhoneからの投稿

Contains (Function)

This is a function that you can use to search if a cell contains specific text (it returns TRUE or FALSE). Cool thing is that you can pass multiple parameters to the function and it will act like OR. So you can write =Contains(A1, "yes", "no", "dog", "cat") and it will check if the cell contains ANY of the 4 words. If it does, it'll tell you TRUE.

This is helpful when you want to run a filter and work on just cells that contain certain words. Just run this function on all of the cells, then use filter to get everyone that ended up as TRUE.

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

Function Contains(ByVal text As String, _

ParamArray search_text() As Variant) As Boolean

Application.ScreenUpdating = False

Dim result As Boolean

result = False

Dim i As Long

For i = 0 To UBound(search_text())

If InStr(1, text, search_text(i)) <> 0 Then

result = True

End If

Next

Contains = result

Application.ScreenUpdating = True

End Function

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