Excel VBA Like Operator Question

Question

To the Community:

Need a little clarification on the VBA "Like" Operator for the below snippet. From reading the language reference, I do not understand why statements "#1. - #4." below returns FALSE when variable "a" contains a character that is NOT in the pattern list (as specified by the "[!..." in the constant con403WldCrdMsk) which are in this case, search wildcard characters including the tilde.

I am trying to return TRUE if the variable contains any character NOT found in the pattern. I could probably just negate it but it messes with my head in what the reference describes to me. Is it maybe that I have the variable "con303WldCrdMsk" patterned incorrectly...I've tried different syntax?

Any help is much appreciated!!

Const con403WldCrdMsk As String = "*[!~[?][#][*]]*"
Const con403FndTxtMsk As String = "*[!A-Za-z0-9 ]*"

a = "*abc*def*hij*"
b = "%abc%def%hij%"
c = "ABCDE"
d = "AB*CDE"
e = "~?#*"

Z = MsgBox(a Like con403WldCrdMsk) '1. returns FALSE
Z = MsgBox(b Like con403WldCrdMsk) '2. returns FALSE
Z = MsgBox(d Like con403WldCrdMsk) '3. returns FALSE
Z = MsgBox(e Like con403WldCrdMsk) '4. returns FALSE

Z = MsgBox(a Like con403FndTxtMsk) '5. returns TRUE
Z = MsgBox(b Like con403FndTxtMsk) '6. returns TRUE
Z = MsgBox(c Like con403FndTxtMsk) '7. returns FALSE
Z = MsgBox(d Like con403FndTxtMsk) '8. returns TRUE

Answer 1

Hello @KF4UYC ,

Thanks for your question.

The problem is with this pattern:

Const con403WldCrdMsk As String = "*[!~[?][#][*]]*"

You think VBA reads the entire [!~[?][#][*]] as one big character class, but VBA actually closes the character class at the very first ] it sees.

To include ] as a literal character inside [...], I recommend placing it immediately after the opening [!

Const con403WldCrdMsk As String = "*[!]~[?#*]*"

I hope this addresses your question. If this response was helpful, please consider following the guidance to provide feedback.

Answer 2

Like pattern charlists ([...]) do not work the way the mask "*[!~[?][#][*]]*" assumes. The problem is the structure of the character list, not the negation logic.

Key rules from the docs:

• [charlist] means “any single character in charlist”.
• [!charlist] means “any single character not in charlist”.
• Inside a single pair of brackets, charlist is just a flat list/range of characters; you cannot nest further [...] groups inside it.
• To match special characters ?, #, *, [ literally, they must be inside a single [...] group, not in separate bracketed groups.

From the context:

• ? matches any single character.
• * matches zero or more characters.
• # matches any single digit.
• [charlist] matches any single character in charlist.
• [!charlist] matches any single character not in charlist.

Your constant:

Const con403WldCrdMsk As String = "*[!~[?][#][*]]*"

Inside the outer brackets, the charlist is:

! ~ [ ? ] [ # ] [ * ] ]

That is not a valid single character list. The nested [?], [#], [*] are not treated as separate “sub-lists”; they are just literal bracket characters and other characters in one malformed list. This causes the pattern not to mean “not any of ~ ? # * [” as intended, and the result is that the Like comparison does not behave as expected, so all of tests 1–4 return False.

To express “any character that is not one of the wildcard characters ~, ?, #, *, [” the charlist must be written as a single bracket group with those characters inside it:

Const con403WldCrdMsk As String = "*[!~?#[*]*"

Explanation of that list:

• ! at the start: negation (match any character not in the list).
• ~ ? # * [ are the literal characters being excluded.
• [ is included literally because it is inside the charlist; per the docs, special characters ?, #, *, [ match themselves when enclosed in brackets.

So the corrected mask is:

Const con403WldCrdMsk As String = "*[!~?#[*]*"

With that pattern, a, b, d, e will return True only if they contain at least one character that is not ~, ?, #, *, [.

The second constant

Const con403FndTxtMsk As String = "*[!A-Za-z0-9 ]*"

is syntactically correct: a single charlist with a negated range A-Z, a-z, 0-9, and space. That is why tests 5–8 behave as expected.

---

References:

• Like operator
• Wildcard Characters used in String Comparisons
• 5.6.9.6 Like Operator
• Invalid pattern string (Error 93)
• Examples of wildcard characters