CODEWITHSUNDEEP
regexexcelvba
Flo Gaede
Flo Gaede

Reputation: 11

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.

Upvotes: 1

Views: 8668

Answers (3)

user2284877
user2284877

Reputation: 138

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

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33672

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

Upvotes: 1

Knowledge Cube
Knowledge Cube

Reputation: 1010

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.

Upvotes: 4

Related Questions