Web Development

Video: Five simple rules for embedding strings in VBA code

In this TR Dojo episdoe, Bill Detwiler shares five things you should know about embedding strings in VBA code.

If you work with VBA, odds are you'll eventually 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. But when the time comes, you can avoid the struggle by remembering the five simple rules I share in this week's TR Dojo episode.

Check out the following links for more VBA resources:

For those who prefer text to video, click the View Transcript link below the video player windows or check out Susan Harkins' article, "5 rules for embedding strings in VBA code," on which this video is based.

You can also sign up to receive the latest TR Dojo lessons through one or more of the following methods:

About

Bill Detwiler is Managing Editor of TechRepublic and Tech Pro Research and the host of Cracking Open, CNET and TechRepublic's popular online show. Prior to joining TechRepublic in 2000, Bill was an IT manager, database administrator, and desktop supp...

7 comments
larry.thornburg
larry.thornburg

This was all I was thinking through the entire video. It makes the code much easier to traverse later (Instead of getting caught up finding beginning and ending string terminating double quotes). Sometimes it can get mind numbing when concatenating a string using the shell run method.

aikimark
aikimark

When faced with data that may contain apostrophe characters, Chr(39), you can use this trick strLastname = "O'Detwiler" [lastname] = "'" & Replace(strLastname, "'", "''") & "'" This doubles-up the apostrophe characters, just like you did when you doubled-up the quote characters. It all goes to hell if you have text values (in one of your data source columns/fields) that contain both apostrophe and quote characters. The only way out of that mess is to find a quick encoding function and compare the encoded form of your search argument against the encoded form of your column/field values. Consider Base64 or two byte hex digit encoding. To make this faster, you could store both the plain text and the encoded form of your data in two separate columns.

oldbaritone
oldbaritone

This used to be taught in first-semester freshman programming classes. It's so fundamental that you can't code without understanding it. It boggles my mind that this requires explanation in a tech forum like TR Dojo.

netmanco
netmanco

This helps when reading code, I now understand why there is a need for double "". Thanks from this amateur\noob

scott
scott

I read this piece on stings in VBA. It was a good idea and I appreciate you doing it. But does anybody proof these things. I was not in a position to view it as a video so I read the transcript. Just reread it and you will find more than six occasions where you did not insert a space to separate words. And, no offense, but the least you could have done is provide examples of the code as you described it. I read this because I did not know how to do it. Without examples simple minds like mine find it hard to understand what you are talking about. It seemed a little amateurish for a organisation like TechRepiblic.