在整张表上检查excel文件中的特殊字符_和带有Like运算符的字母

Below is the macro code but it is working for a single cell.I want it for entire sheet.

下面是宏代码，但它适用于单个单元格。我希望它适用于整个工作表。

Public Function IsSpecial(s As String) As Long
 Dim L As Long, LL As Long
 Dim sCh As String
 IsSpecial = 0
 For L = 1 To Len(s)
     sCh = Mid(s, L, 1)
     If sCh Like "[0-9a-zA-Z]" Or sCh = "_" Then
     Else
         IsSpecial = 1
         Exit Function
     End If
 Next L
End Function

2 个解决方案

#1

As @ImranMalek said, Regex would do it better, because it is taking too much time to loop on each cell and each letter of your code. However, if you still want to use in your entire sheet, use this:

正如@ImranMalek所说，Regex会做得更好，因为它需要花费太多时间来循环每个单元格和代码的每个字母。但是，如果您仍想在整张工作表中使用，请使用以下命令：

Dim L As Long, LL As Long
Dim sCh As String, s As String
Dim IsSpecial As Boolean

For Each cell In ActiveSheet.UsedRange.Cells
    IsSpecial = False
    If IsEmpty(cell) = False Then
        s = CStr(cell)
        For L = 1 To Len(s)
            sCh = Mid(s, L, 1)
            If sCh Like "[0-9a-zA-Z]" Or sCh = "_" Then
            Else
                IsSpecial = True
                Exit For
            End If
        Next L
        If IsSpecial = True Then
            cell.Interior.ColorIndex = 3
        Else
            cell.Interior.ColorIndex = 4
        End If
    End If
Next

The code will color in green if the name is ok and red if nok.

如果名称正常，代码将显示为绿色，如果为nok，则代码将显示为红色。

And the result is:

结果是：

#2

Effective way of matching patterns is to use regex , consider the general code below

匹配模式的有效方法是使用正则表达式，请考虑下面的一般代码

Check this answer for more information and tweaks.

请查看此答案以获取更多信息和调整。

Sub test()

    Dim regex As Object
    Dim pattern As String

    Set regex = CreateObject("VBScript.RegExp")

    regex.Global = True
    regex.IgnoreCase = True
    regex.pattern = pattern

    'define your regex pattern here
    pattern = "[0-9a-zA-Z]"

    'check each cell in range
    For Each cell In ActiveSheet.Range("A1:A10")
        If pattern <> "" Then
            If regex.test(cell.Value) Then
                'if the pattern matches do some operation
                Debug.Print cell.Address
            End If
        End If
    Next

End Sub

#1