在vba中转义特殊字符是行不通的

I don't know why but I can't escape special characters in patterns.

我不知道为什么,但我无法逃脱模式中的特殊字符。

If StrTest Like "\[*\]" Then ...

It still does not match values like [1234567890]. In other threads I read that "\" is used to escape characters, but it does still not work.

它仍然与[1234567890]等值不匹配。在其他线程中,我读到“\”用于转义字符,但它仍然不起作用。

In between the brackets I want any string to be possible.

在括号之间我希望任何字符串都可以。

3 个解决方案

#1

When using the VBA Like operator, you are supposed to escape special characters by enclosing them in square brackets ([ and ]). What you are using above is syntax for a regular expression, which is not the same thing as a Like pattern. See this page on MSDN for more information about the differences between Like and regular expressions.

使用VBA Like运算符时,您应该通过将它们括在方括号([和])中来转义特殊字符。您上面使用的是正则表达式的语法,它与Like模式不同。有关Like和正则表达式之间差异的更多信息,请参阅MSDN上的此页面。

#2

Try the solution below using RegEx.Replace method.

使用RegEx.Replace方法尝试下面的解决方案。

Note: special thanks for helping with the Pattern from this Answer

注意:特别感谢您帮助解决本答案中的模式

Option Explicit

Sub ClearData()

Dim StrTest As String
Dim Result  As String
Dim Reg1    As Object

StrTest = "[1234567890]"

Set Reg1 = CreateObject("VBScript.RegExp")
With Reg1
    .Global = True
    .IgnoreCase = True
    .Pattern = "[-[\]{}()*+?.,\\/^$|#\s]" ' escape special characters pattern
End With

If Reg1.test(StrTest) Then
    Result = Reg1.Replace(StrTest, Result)
End If

End SUb

#3

Another solution:

Sub no_spch()

For Each cell In range("a1:a10")
spch = "#+-,'.()[]!?`&:*"
For i = 1 To Len(spch)
cell.Value = Replace(Replace(cell.Value, Mid(spch, i, 1), ""), " ", "")
Next
Next

End Sub

#1

#2