vba 识别特殊字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40311253/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Identify Special Characters
提问by Deluq
I need to identify cells that have certain special characters (example: !,.=]\') and mark them with a color.
我需要识别具有某些特殊字符的单元格（例如：!,.=]\'）并用颜色标记它们。
The column can only contain numbers (0-9), letters (a-z), as caps (A-Z) and hyphen (-).
该列只能包含数字 (0-9)、字母 (az)、大写字母 (AZ) 和连字符 (-)。
Example:
例子：
回答by Wiktor Stribi?ew
You can use a regex for this task.
您可以为此任务使用正则表达式。
A useful regex construct here is a negated character class: you use [^...]and insert the ranges you do not want to match in there. So, to match a char other than ASCII letters, digits, and a hyphen, use [^a-zA-Z0-9-].
这里一个有用的正则表达式构造是否定字符类：您使用[^...]并插入您不想在其中匹配的范围。因此，要匹配 ASCII 字母、数字和连字符以外的字符，请使用[^a-zA-Z0-9-].
And use it like
并使用它
Dim strPattern As String: strPattern = "[^a-z0-9-]"
Dim regEx As Object
Set regEx = CreateObject("VBScript.RegExp")
regEx.Global = True
regEx.IgnoreCase = True
regEx.Pattern = strPattern
For Each cell In ActiveSheet.Range("C:C") ' Define your own range here
If strPattern <> "" Then ' If the cell is not empty
If regEx.Test(cell.Value) Then ' Check if there is a match
cell.Interior.ColorIndex = 6 ' If yes, change the background color
End If
End If
Next
回答by Gary's Student
Without regex:
没有正则表达式：
This macro processes column B:
此宏处理B列：
Sub marine()
Dim r As Range, rng As Range, s As String
Dim i As Long, L As Long
Set rng = Intersect(Range("B:B"), ActiveSheet.UsedRange)
For Each r In rng
If r.Value <> "" Then
s = Replace(r.Text, "-", "")
L = Len(s)
For i = 1 To L
If Not Mid(s, i, 1) Like "[0-9a-zA-Z]" Then
r.Interior.Color = vbYellow
End If
Next i
End If
Next r
End Sub
It will accept only numerals, upper and lower case letters, and the dash.
它将只接受数字、大小写字母和破折号。
