Using " as a character in a statement

Question

Hi there I am trying to use " as a character in my code, but VBA always auto corrects it as excess characters (like if I try """). I tried to use the Char(34) character code but it still doesn't work. I want to loop through cells and search for values within the cell such as "ActiveCell.Value" but the code doesn't recognize " as a character. Here is the code:

Sub zero()

Do Until IsEmpty(ActiveCell.Value)

Shell ("C:\Program Files (x86)\Google\Chrome\Application\Chrome.exe -url https://www.google.co.uk/search?q=" & Chr(34) & ActiveCell.Value & Chr(34))

ActiveCell.Offset(1).Select

Loop

End Sub

You need to double up the quotes to escape them in VBA, so `doubleQuotes = """"` is assigning one double-quote character, which is indeed `Chr(34)`. "but it doesn't work" isn't a useful problem description we can work with. How is it failing? — Mathieu Guindon, Apr 24 '18 at 15:36
Could you tell us what `Debug.Print "C:\Program Files (x86)\Google\Chrome\Application\Chrome.exe -url https://www.google.co.uk/search?q=" & Chr(34) & ActiveCell.Value & Chr(34) ` returns? — TomJohn, Apr 24 '18 at 15:37
This code works 100% fine for me. Are you getting an error, incorrect results? What's the problem exactly? — JNevill, Apr 24 '18 at 15:50

score 4 · Accepted Answer · answered Apr 24 '18 at 15:43

4

You need to use four rabbits ears within a VBA string for a single quotation character.

For this reason I define

Public Const vbQuote As String = """"

and use in this way

"https://www.google.co.uk/search?q=" & vbQuote & ActiveCell.Value & vbQuote

answered Apr 24 '18 at 15:43

Bathsheba

231,907
34
361
483

Thank you this method worked, I was actually asked by a colleague who doesn't have a SO account to post this that's why I couldn't define clearly what the problem was. – Rhyfelwr Apr 25 '18 at 08:36

Using " as a character in a statement

1 Answers1