escaping special characters in vba is not working

Question

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.

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

If any of these answers helped you please consider [accepting them](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work). Accepting answers rewards those who help you. It also helps others with the same problem to find a working answer. — David Rushton, May 10 '17 at 12:56

score 3 · Answer 1 · answered May 10 '17 at 12:22

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.

score 1 · Answer 2 · edited May 23 '17 at 12:10

Try the solution below using RegEx.Replace method.

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

score 0 · Answer 3 · answered May 10 '17 at 12:59

0

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

answered May 10 '17 at 12:59

user2284877

138
1
9

escaping special characters in vba is not working

3 Answers3