Web Development

5 rules for embedding strings in VBA code

Don't struggle to pass strings. Instead, apply these five simple rules when working with embedded strings in VBA.

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.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

12 comments
numcrun
numcrun

Rule 4 is not correct. Just make the apostrophe a double apostrophe and it works.

TexasJetter
TexasJetter

I know it's a bit off topic, but I've used a function as you described in the past, but be very careful when passing parameters to a SQL statement. Generally concatenating your SQL statement is a big no no, opening you up to SQL Injection. It is much better to use a parameterized statement, like: Dim myconn As SqlConnection = New SqlConnection(sConnectionString) myconn.Open() Dim sSQL As String = "Select * from Customer where ID=@id" Dim sCommand As SqlCommand = New SqlCommand(sSQL) sCommand.Parameters("id").Value = textID.Text Dim dr As SqlDataReader = sCommand.ExecuteReader() One of the great benefits of using parameterized statements is that it will escape all characters, not just the quotes. Edit: Sorry, this was ment to be a reply to allen@ Use functions to assist post...

jody.burton
jody.burton

I could have used this article last week as I was modifying an Excel macro for some added functionality. This would have saved me some aggravation. @allen, neat idea. I could see making those functions part of a module that gets imported to any project that I add code to.

DBlayney
DBlayney

Good practical advice, it's so easy to get this wrong. Tip #3 is good but it only achieves the correct result if the application that the string is being passed to also accepts apostrophes; strictly speaking the apostrophes should be used outside and the quotes inside. Tip #5 needs a little care too. Normally VBA can handle operators written immediately after a variable name and will space everything nicely for you. In this case, if the space between strLastName and the ampersand is missed thus, strLastName&, VBA will interpret the ampersand as a type-declaration character (Long) and probably an error will be generated. Instead of using the Chr function it can be easier to read if you just define a couple of constants: Public Const Apostrophe = "'", Quote = '"'

Tony Hopkinson
Tony Hopkinson

Fortunately you can use two apostrophes.... Personally I consider that unlucky, not to mention daft.

allen
allen

To make it less daunting trying to keep up with how many quotes to add, I usually create a simple function to wrap quotes around a string which I name wt for wrap text. Then when creating strings with embedded quoted text I just use the function like this: strVar = "Adding quoted text to a string is as easy as " & wt("this is quoted") & "" and you get: Adding quoted text to a string is as easy as "this is quoted". I use this with embedding variables also like this: sqlSQL = "Insert into tblCodes (CodeName,CodeValue) Values(" & wt(passedName) & "," & wt(passedCode) & ")" to get: Insert into tblCodes (CodeName,CodeValue) Values("MyName","MyCode") I handle dates in sql statements similarly with a wrap dates function; wd(Date()) where wd is a function that wraps '#'s around the date, it returns #3/7/2011#

Justin James
Justin James

... the system kept the "smart quotes" in the article, so doing a copy/paste into the editor may keep them as well. If you copy/paste from the article and it gives you grief, erase and re-type the quotation marks. This bites me on the rear end every now and then in my articles too. :( Good article! J.Ja

TexasJetter
TexasJetter

Sorry, I meant to post a reply, but it ended up at the top level. Still trying to get adjusted to the new site layout - at least that's my excuse ;)

Tony Hopkinson
Tony Hopkinson

especially when you are double escaping ie passing quotes to another application hich is expecting an escaped set as well.

Slayer_
Slayer_

I'll keep that in mind the next time I am doing string manipulation.

Mark W. Kaelin
Mark W. Kaelin

I'll remove the smart quotes. I hate smart quotes.

ssharkins
ssharkins

Thanks for removing the quotes Mark and thanks Justin for mentioning them so we could take care of that. I just checked Word and smart quotes are turned off, so I don't know how I managed to insert them in the article -- I'll keep an eye out for them!

Editor's Picks