VBA RegExp for Special Characters

Question

When a user enters a "save as" name for the output from my Macros, I want to check for special characters that will cause an error in saving a file - [\/:*?<>|]

I'm using RegExp like so:

Dim regEx As New RegExp
Dim strSpecChar As String: strSpecChar = "*[\/:*?<>|]*" 'Compile Error here
Dim OutputFileName As String: OutputFileName = InputBox("Enter File Name")
With regEx
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = strSpecChar
End With
If regEx.Test(OutputFileName) Then
        'error msg
End If

I'm getting Compile Error: Invalid Character error because of the bar (|) character. I've tried escaping bar with backslash but it doesn't work. Can someone help? I've read a couple posts including this one but it hasn't helped.

SOLUTION: Take a look at blog.tkacprow.pl's comment below and Alex's answer (he helped point out a typo and explains error 5018. Remove * from the strSpecChar variable above.

Regex might be overkill for this. `Instr()` would work just fine. You would end up with just about as many lines of code, but I would take the time to extract a `SafeFileName(filename as string) as string` function. — RubberDuck, Nov 05 '14 at 17:44
Why do you have a * at the beginning of "*[\/:*?"<>|]*"? The * should be preceded by some expression. I think you should remove it escape the "|", escape the backslash and replace " with "". — AnalystCave.com, Nov 05 '14 at 17:49
Why not just use a filedialog box instead of building your own save routine? — EStraka, Nov 05 '14 at 17:51
RegEx might not be the best solution in VBA but not for a reason that you might expect. If you're going to share your workbook with other people they might not have the RegEx reference installed on their machine. It happens in many organizations that many "unimportant" libraries are stripped out of their workstation install images. Sometimes in can affect some users and not all. It's just something that I recently ran into with a client. — Mr. Mascaro, Nov 05 '14 at 18:26

score 3 · Answer 1 · answered Nov 05 '14 at 18:53

There's really no need to use the Regular Expression engine to just test of the existence of one of a group of characters in the string. You can simply use the VBA Like Operator, to accomplish the same task, with no need of referencing an external library:

Const strSpecChar As String = "*[\/:*?<>|]*"
Dim OutputFileName As String: OutputFileName = InputBox("Enter File Name")
If OutputFileName Like strSpecChar Then
    MsgBox "Error Message"
End If

Or, if you want to include the double quote as one of the characters to be excluded:

Const strSpecChar As String = "*[\/:*?<>|""]*"

Note the use of the doubled double quote within the character class, so as to tell VBA to include that rather than mark the end of the string.

@PortlandRunner Thanks. Like is pretty useful for **very simple** patterns, and has the advantage of being faster, and built-in compared with regex. — Ron Rosenfeld, Nov 05 '14 at 20:47

Alex · Accepted Answer · 2014-11-05T18:51:39.227

2

Did not check if your regexp is correct, but you have included the character " in the regexp, which vba treated it as end of string. You could use chr (34) to replace the double quote:

strSpecChar = "*[\/:*?" & Chr(34) & "<>]|*"

edited Nov 05 '14 at 18:51

answered Nov 05 '14 at 17:47

Alex

1,632
1
12
28

thanks for catching that! I'm getting `runtime 5018: Method 'Test' of object 'IRegExp2' failed` now for the regexp engine, so I'll put up another answer if I find one! Thanks! – Nov 05 '14 at 17:56
2

The error is associated with the incorrect regexp used. You could test yours at http://regex101.com/ – Alex Nov 05 '14 at 18:00

VBA RegExp for Special Characters

2 Answers2