escaping special characters in vba is not working

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.

3 Answers

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.

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 

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