Bill Detwiler:
I'm Bill Detwiler, and during this episode of TR Dojo, I'llshow five things you should know about embedding strings in VBA code.
If you work with VBA, odds are you'll eventually strugglewith embedded strings. It might be a simple task such as passing a string to afunction or something more complex like creating a SQL statement on the fly.
But when the time comes, you can avoid the struggle byremembering five simple rules from Susan Harkins, TechRepublic's MicrosoftOffice blogger.
The first rule is to use a pair of quotation marks todelimit a string.
While VBA doesn't require a delimiter for numeric values, itdoes require them for dates and literal strings.
For dates, you'll wrap the value with pound signs.
For literal strings, you'll wrap the value with quotationmarks.
For example if you’re searching for all the records thatmatch Detwiler, you must wrap Detwiler in quotation marks.
If you omit the quotation marks, VBA will interpret Detwileras a variable and then return an error when it doesn’t find it (assuming ofcourse that you haven’t actually declared a variable named Detwiler.)
Her second rule is to use two quotation marks to denote asingle one when embedding one string in another.
Not sure what I mean by this? Well, let's look at anexample.
Say we have the search string component LastName equalsDetwiler, where Detwiler is a literal string.
You might assume you can delimit Detwiler and the entiresearch string component with two pairs of quotation marks, but it won’t work:
VBA will interpret Detwiler as a variable, which will causean error.
The correct way to delimit Detwiler within the larger stringis to use two quotation marks on each side--in addition to the pair ofquotation marks around the entire component.
As you can imagine, using quotation marks to embed onestring in another can get confusing. Fortunately, VBA also recognizes theapostrophe character (’) as a string delimiter.
And that's Susan's third rule, use a pair of apostrophes todelimit one string within a string.
Instead of requiring six quotation marks, you could delimitthe search string component I just mentioned with a pair of apostrophes aroundDetwiler and a pair of quotation marks around the entire component.
This form is easier to remember, easier to type, and easierto interpret when you’re reviewing the code later.
Unfortunately, this apostrophe-delimiting method won't workwith every type of string.
And, this brings us to the fourth rule--Don't useapostrophes to delimit a string that contains an apostrophe.
For example, a string, like the one shown here, with a lastname of O'Detwiler returns and error.
Notice the apostrophe within our apostrophe-delimitedstring?
This rule limits the type of strings you can delimit withapostrophes, and there’s no easy way around it.
It’s all but impossible to embed a string variable usingjust delimiters. For instance, neither of these examples, where strLastName isa variable will work as expected:
The first form returns an error.
And unfortunately, you might not catch the error with thesecond one.
VBA will just evaluate it like this--using strLastName as aliteral string. If you were using this example to search the LastName field,the query would just return no records--as it’s unlikely there's a last nameentry with the value s t r L a s t N a m e.
This is when the fifth rule comes into play.
When embedding a string variable, use the ampersandcharacter to concatenate a string variable and its delimiters
Like this:
In this example, we're using all five rules.
First, there's a quotation mark at the beginning.
Second, there's an apostrophe and a quotation mark followingthe equal sign.
There are two ampersands wrapping the string variable.
And at the end, there’s a quotation mark, an apostrophe, anda second quotation mark.
If strLastName equals Detwiler, this statement wouldevaluate like this:
Well, I hope these rules will help the next time you'reembedding strings in VBA code.
And, here's one last tip. If you're worried about confusingyour apostrophes and quotation marks, try replacing them with theircorresponding character functions. Chr(34) for a quotation mark and Chr(39) foran apostrophe.
As always, for more teachings on YOUR path to becoming an ITNinja, visit trdojo.techrepublic.com, sign-up for our newsletter, or follow meon Twitter.
Thanks for visiting the TR Dojo.
