VBA Blogs: Common Traps 1

Welcome to this week’s VBA blog!  In line with previous months, we’re going to keep this month’s blogs on a theme, and this month’s theme is all about common errors and issues that people run into.

Suppose I’m entering in a simple formula that checks a value for a particular name, and provides an answer.  It might look something like this:

If I’m using VBA to populate this, I might think that I can just use the following line of code:

However, this quickly gives me an error:

Hm, what’s happened here?  Using quotation marks has meant that my VBA code is processing the second set of quotation marks just before Amy as the end of my text string, and is expecting the code to stop at that point (or do something to add onto the text string, such as concatenate additional items).

What can we do to avoid this problem?  Well, if we want to tell VBA that we want to use those quotes as part of the text string, we need to type it in twice:

This tells VBA that the quotation mark isn’t the end of the string – it’s merely a character that we want to use.  Then, the formula works perfectly:

Hopefully you won’t get caught out using hard-coded parameters in the future now!  See you next week for more common traps in VBA!