excel - 如何检查字符串(或单元格)中是否包含 kajji 字符？

Question

我在一家日本公司工作。因此，发送给我的大多数 Excel 表格大多是日文的。所以我创建了一个 excel 宏，提示用户选择他们想要翻译的单元格范围。宏循环遍历获取单元格值的单元格范围，将其复制到 Google 翻译文本框中，等待翻译，复制翻译，并将单元格值设置为翻译。

现在所有这些都在起作用，我可以翻译我扔给它的任何范围。我遇到的问题是翻译文件所需的时间。我已经尽我所能加快与谷歌的连接。我看到的下一个减慢宏的事实是，我无法找到一种简单的方法来确定单元格是否包含任何日语(平假名、片假名、汉字)。所以我正在寻找一个基本上可以做到这一点的函数:

Function isJapanese(cell as Range)
     If cell.Value is Japanese Then
            isJapanese = True
     Else
            isJapanese = False
     End If
End Function

我已经在检查字符串是否包含拉丁字母(这使它跳过了一些单元格)、某些符号以及我能想到的任何其他字符或字符串，这些字符或字符串只有英文单元格才有。

下面是我到目前为止的代码(我正在使用用户表单来获取翻译函数的变量)

Function Translate_Range(rng As String, in_exp As String, out_exp As String) As Boolean
Dim japCheck As Boolean, japCount As Integer, cellAddress As String, transText As String, langDesired As String, wkb As String, sht As String, searchRange As Range, doneCheck As Boolean
doneCheck = False
wkb = ActiveWorkbook.Name
sht = ActiveSheet.Name
Workbooks(wkb).Worksheets(sht).Activate
japCount = 0
japCheck = True
Set searchRange = Range(rng)
For Each cell In searchRange
    If cell.Value <> "" And InStr(cell.Text, "mm") = 0 And InStr(cell.Text, "±") = 0 Then
        japCheck = IsAlpha(cell.Text)
        If japCheck = True Then
            GoTo NextIteration
        Else
            transText = translate_string(cell.Address, in_exp, out_exp)
            ActiveSheet.Range(cell.Address).Value = transText
        End If
    End If
NextIteration:
    Next
    doneCheck = True
    Translate_Range = doneCheck
End Function

Private Function translate_string(cell As String, input_exp As String,  output_exp As String)
Dim str As String
str = ActiveSheet.Range(cell).Value
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = False
IE.navigate "http://translate.google.com/#" & input_exp & "/" & output_exp & "/" & str
    Do Until IE.readyState = 4
        DoEvents
    Loop
Do Until result_data <> ""
    CLEAN_DATA = Split(Application.WorksheetFunction.Substitute(IE.Document.getElementById("result_box").innerHTML, "</SPAN>", ""), "<")
    For i = LBound(CLEAN_DATA) To UBound(CLEAN_DATA)
        result_data = result_data & Right(CLEAN_DATA(i), Len(CLEAN_DATA(i)) - InStr(CLEAN_DATA(i), ">"))
    Next
Loop
IE.Quit
translate_string = result_data
End Function

Private Function IsAlpha(strValue As String) As Boolean
    IsAlpha = strValue Like WorksheetFunction.Rept("[a-zA-Z]", Len(strValue))
End Function

我觉得汉字字符必须有一个 Unicode 协议(protocol)。当涉及到这样的事情时，我只是非常缺乏经验(我是一名机械工程师，他制作 excel 宏来让我的生活更轻松，所以我对这样的技术编程主题的知识缺乏抱歉)

Answer 1

这个子，从我链接到的答案中大量借用，可以确定一个字符串是否有任何拉丁字符:

Sub t()
Dim k       As Long
Dim myString As String
myString = Range("Z1").Value  'Edit this as needed
Dim latinChars As Long

latinChars = 0
For k = 1 To Len(myString)
    If IsLatin(Mid(myString, k, 1)) Then
        ' Has latin characters
        latinChars = latinChars + 1
    End If
Next k

If latinChars = 0 Then
    ' Doesn't have latin characters
    Debug.Print "Doesn't have latin characters"
Else
    ' Has latin characters
    Debug.Print "Has latin characters"
End If

End Sub

Function IsLatin(Str As String) As Boolean
IsLatin = True
For i = 1 To Len(Str)
IsLatin = IsLatin And Abs(AscW(Mid(Str, i, 1)) - 64) < 64
Next i
End Function