WHAT's THE RULE?: Use of apostrophes(" ")

[davidm]

Can someone come up with a golden rule governing the use of apostrophe
(" ") in writing out text in VBA? For example:

1. Msgbox "Enter value as ""Number"" of Apples" ---->produces
<Enter value as "Number" of apples>
I don't have much problem with the convention here but see:


2. Range("a1") = "=INDIRECT("""& Range("b2") & """)" ---> produces

=INDIRECT("Sheet5!") in Cell A1 of the activesheet (assuming Cell b
contains the string SHEET5!

One would imagine that following (1), the use of Double rather tha
Treble null strings as in *Range("a1") = "=INDIRECT(""& Range("b2")
"")"* should work. What gives?

I need a handle to guide me in the correct usage of apostrophes in har
cases like this and not resort to hit-and-miss, trial-and-erro
approaches.


Thanks for any illumination

 

[Bob Phillips]

When you are using strings, you need one to tell VBA it is text, then two
for every embedded apostrophe, so that VBA does not think you are closing
the text.

Think about it, and strip out the words and add spaces

Msgbox "Enter value as ""Number"" of Apples" becomes
Msgbox " "" ....."" "

Range("a1") = "=INDIRECT("""& Range("b2") & """)" becomes
Range("a1") = "=INDIRECT("" " & Range("b2") & " "" "

it is pretty consistent

 

[arno]

it is pretty consistent

strange, I always use try and error

write 1 into B1 and hello into B2. The result should be

1"hello"

the formula is
=B1&""""&B2&""""

does this fit to your rule?

arno

 

[Bob Phillips]

Yes.one to indicate text, 2 to add a quote, one to end text.

=B1&" "" "&B2&" "" "

 

[davidm]

Many thanks Bob.

 

[arno]

Yes.one to indicate text, 2 to add a quote, one to end text.
cool. thank you.
arno