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

--
david
-----------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...fo&userid=2064
View this thread: http://www.excelforum.com/showthread.php?threadid=46908

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

--
HTH

Bob Phillips

"davidm" wrote in
message ...

Can someone come up with a golden rule governing the use of apostrophes
(" ") 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 b2
contains the string SHEET5!

One would imagine that following (1), the use of Double rather than
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 hard
cases like this and not resort to hit-and-miss, trial-and-error
approaches.


Thanks for any illumination.


--
davidm
------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=469086

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

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

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

--
HTH

Bob Phillips

"arno" wrote in message
...
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

Many thanks Bob.


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=469086

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