使用VBA判断单元格各字符是否为中文、英文、数字、符号的复杂代码

Admin | 2009-2-12 12:17:25 | 被阅次数 | 17744

温馨提示!

如果未能解决您的问题，请点击搜索；登陆可复制文章，点击登陆

　　代码如下：

　　Function IsLike(strText As String, pattern As String) As Boolean
IsLike = strText Like pattern
End Function
Function IsChinese(strText As String) As Boolean
Dim i%, h$
h = Hex(Asc(strText))
If Asc(Left(h, 1)) >= 66 And Asc(Left(h, 1)) <= 70 Then
IsChinese = True
End If
End Function

Function StringType(strText As String, Optional outPutType As Integer = 1, Optional sumVar As Boolean = False) As Variant
Dim strtemp As String, blnArray(1 To 5) As String, strPreType As Integer
Dim intNum As Integer, startPos As Integer, intlen As Integer
Dim strArray As Variant, strCompare1 As String, strCompare2 As String, dblSum As Double
If sumVar = True And Not (outPutType <> 2 Or outPutType <> 4) Then sumVar = False
For i = 1 To Len(strText)
    strtemp = Mid(strText, i, 1)
    If i > 1 Then strCompare1 = WorksheetFunction.Asc(Mid(strText, i - 1, 3))
    strCompare2 = WorksheetFunction.Asc(Mid(strText, i, 2))
    If WorksheetFunction.Dbcs(strtemp) = strtemp Then
        strtemp = WorksheetFunction.Asc(strtemp)
        If IsLike(strtemp, "[0-9]") Or IsLike(strCompare1, "[0-9].[0-9]") Or IsLike(strCompare2, "-[0-9]") Then
            If strPreType = 4 Then
                blnArray(4) = Left(blnArray(4), Len(blnArray(4)) - 1) & intNum
            Else
                intNum = 1
                blnArray(4) = blnArray(4) & "- " & i & "/" & intNum
            End If
            strPreType = 4
            intNum = intNum + 1
        ElseIf IsLike(strtemp, "[a-zA-Z]") Then
            If strPreType = 5 Then
                blnArray(5) = Left(blnArray(5), Len(blnArray(5)) - 1) & intNum
            Else
                intNum = 1
                blnArray(5) = blnArray(5) & "- " & i & "/" & intNum
            End If
            strPreType = 5
            intNum = intNum + 1
        ElseIf IsChinese(strtemp) Then
            If strPreType = 1 Then
                blnArray(1) = Left(blnArray(1), Len(blnArray(1)) - 1) & intNum
            Else
                intNum = 1
                blnArray(1) = blnArray(1) & "- " & i & "/" & intNum
            End If
            strPreType = 1
            intNum = intNum + 1
        Else
            strPreType = 0
        End If
    Else
        If IsLike(strtemp, "[0-9]") Or IsLike(strCompare1, "[0-9].[0-9]") Or IsLike(strCompare2, "-[0-9]") Then
            If strPreType = 2 Then
                blnArray(2) = Left(blnArray(2), Len(blnArray(2)) - 1) & intNum
            Else
                intNum = 1
                blnArray(2) = blnArray(2) & "- " & i & "/" & intNum
            End If
            strPreType = 2
            intNum = intNum + 1
        ElseIf IsLike(strtemp, "[a-zA-Z]") Then
            If strPreType = 3 Then
                blnArray(3) = Left(blnArray(3), Len(blnArray(3)) - 1) & intNum
            Else
                intNum = 1
                blnArray(3) = blnArray(3) & "- " & i & "/" & intNum
            End If
            strPreType = 3
            intNum = intNum + 1
        Else
            strPreType = 0
        End If
    End If
Next
strtemp = ""
strArray = Split(blnArray(outPutType), "-")
For i = 1 To UBound(strArray)
    intNum = InStr(1, strArray(i), "/")
    startPos = Mid(strArray(i), 1, intNum - 1)
    intlen = Mid(strArray(i), intNum + 1)
    If sumVar Then
        dblSum = dblSum + WorksheetFunction.Asc(Mid(strText, startPos, intlen))
    Else
        strtemp = strtemp & Mid(strText, startPos, intlen) & " "
    End If
Next
If sumVar Then
    StringType = dblSum
Else
    If Len(strtemp) Then
        StringType = Left(strtemp, Len(strtemp) - 1)
    Else
        StringType = ""
    End If
End If
End Function