If you want to work with VBA, eventually, you’ll struggle with embedded strings. It might be a simple task such as passing a string to a function or something more complex like creating a SQL statement on the fly. When the time comes, you can avoid the struggle by remembering these five rules.
Rule #1: Use a pair of quotation marks to delimit a string
VBA insists that you delimit a literal string. Usually, you’ll do so by wrapping the literal strings in a pair of quotation marks. For instance, if you’re searching for all the records that match Harkins, you must wrap Harkins in quotation marks as follows:
“Harkins”
When you enter a single quotation mark, you must enter a second – VBA wants to see a pair of them.
If you omit the quotation marks, VBA will interpret Harkins as a variable and then return an error when it doesn’t find it (assuming of course that you haven’t actually declared a variable named Harkins.) Use the pound symbol (#) to delimit dates. Numeric values don’t require a delimiter.
Rule #2: Use two quotation marks to denote one when embedding one string in another
What in the world does that mean? Sometimes an example works better than words. Let’s take the following search string component where Harkins is a literal string:
[LastName] = Harkins
You might try the following, but it won’t work:
“[LastName] = “Harkins””
VBA will interpret Harkins as a variable, which will return an error. The correct way to delimit Harkins within the larger string follows:
“[LastName] = “”Harkins”””
It might not make sense, but that’s how it works. Insert two quotation marks both before and after the string you’re embedding. Then, don’t forget the closing quotation mark for the entire component—the one to match the opening quotation mark before [LastName].
Rule #3: Use a pair of apostrophes to delimit one string within string
Using quotation marks to embed one string in another gets confusing (Rule #2). Fortunately, VBA also recognizes the apostrophe character (‘) as a string delimiter. Instead of requiring six quotation marks, you could delimit the above filtering component as follows:
“[LastName] = ‘Harkins'”
That’s one apostrophe and one quotation mark at the end, not three apostrophes. This form is easier to remember, easier to type, and easier to interpret when you’re reviewing the code later.
Rule #4: Don’t use apostrophes to delimit a string that contains an apostrophe
An apostrophe-delimited string can’t contain an apostrophe. For example, the following string would return an error:
“[LastName] = ‘O’Harkins'”
This rule limits the possibilities and there’s no easy way around it.
Rule #5: Use the ampersand character to concatenate string variables
It’s all but impossible to embed a string variable using just delimiters. For instance, neither of the following forms where strLastName is a variable will work as expected:
“[LastName] = strLastName”
“[LastName] = “”strLastName”””
The first form returns an error. Unfortunately, you might not catch the error with the second form, as it evaluates as follows:
[LastName] = “strLastName”
VBA will use strLastName as a literal string and continue. If you were searching the LastName field, the query would return no records (as it’s unlikely to find a last name entry of strLastName).
When embedding a string variable, use the ampersand character (&) to concatenate a string variable and its delimiters as follows:
“[LastName] = ‘” & strLastName & “‘”
That’s a combination of everything you’ve learned. First, that’s an apostrophe and a quotation mark following the = sign ( ‘ ” ). At the end, there’s a quotation mark, an apostrophe, and a second quotation mark ( ” ‘ ” ). If strLastName equals Harkins, this statement evaluates as follows:
[LastName] = ‘Harkins’
You can replace literal string delimiters with the Chr(34) and Chr(39) functions to reduce confusion and mistakes. Chr(34) returns the quotation mark and Chr(39) returns the apostrophe.
