CODEWITHSUNDEEP
vbaexcel
user249611
user249611

Reputation: 65

excel vba formula construction double quotes versus single

I found answer under the title "Using variables in R1C1 formula construction" that makes my formula work. Can someone explain, the logic as to how a formula is constructed and stored in a cell.

Original:

=IF(ISNUMBER(SEARCH("IAV",RC[-2])),MID(RC[-2],FIND("IAV",RC[-2])+6,11),"")

Working:

sFRM = "=IF(ISNUMBER(SEARCH('IAV',RC[-2])),MID(RC[-2],FIND('IAV',RC[-2])+6,11),'')"
.Range("o2:o" & lr).FormulaR1C1 = Replace(sFRM, Chr(39), Chr(34))

thanks, inadvance

Upvotes: 2

Views: 2145

Answers (1)

OpiesDad
OpiesDad

Reputation: 3435

I think you are trying to figure out why there are single quotes in the VBA version, but double quotes in the excel sheet.

In VBA, to create a variable of type string, you put it inside quotation marks. So the line:

myvar = "help"

causes the variable myvar to be a string with the value of help [without quotes]

But what if you wish to have quotation marks in your string? So you want myvar to actually be equal to

John said "Help"

If you tried to set a variable equal to this, VBA would treat the quotation mark as a symbol exiting the string and would cause an error, such as occurs in the following line:

myvar = "John said "Help""

This would not work, because VBA would see the second quotation mark and think that the string is complete (and equal to [John said ]) but then it doesn't know what the next character, 'H' is telling it to do.

There are a few ways to correct this, but your code does so by using single quotes and then replacing them with double quotes. The same line using that method is:

myvar = "John said 'Help'"
myvar = replace(myvar,chr(39), chr(34))

Here, the string originally has the desired sentence, but with single quotation marks around the word Help.

Chr(39) represents a single quotation mark and Chr(34) represents a double quotes. So the replace function changes all of the single quotation marks with double quotes.

Instead, you could have done this:

myvar = "John said " & chr(34) & "Help" & chr(34)

and it would have created the same string.

Your code sets the variable sFRM to the desired value with single quotes instead of double quotes and then replaces the single quotes with double quotes. The double quotes at the begining and end of the line of code are being used to denote that the letters inside them are to be put into a string.

The single quotation marks are being used as placeholders to be replaced. There are other ways to do this, but the method you have provided will work.

Upvotes: 4

Related Questions