Excel 2003 VBA string literal help location

  • Thread starter Thread starter Walter Briscoe
  • Start date Start date
W

Walter Briscoe

I run VBA in Microsoft Visual Basic 6.5.1053 from Microsoft Office Excel
2003 (11.8332.8333) SP3 from Windows Vista Business Service Pack 2.

1) What help topic specifies the rules for forming a VBA string literal?
AFAIK, these are something like: A string literal is a sequence of
characters, starting and ending with a quote, in which a quote is
represented by two quotes.
e.g. "Hello, World" which has the value Hello, World and
"""Hello, World""" which has the value "Hello, World".

I think that a string literal can't specify more than one line.
e.g. This is an error:
"Hello,
World".
Backslash is not an escape character.
e.g. The value of """Hello, \r\nWorld""" is "Hello, \r\nWorld".
If You want that vbCrLf is your friend.
e.g. """Hello, " & vbCrLf & "World""".

When I ask for help on string literal, I get a myriad of hits, none of
which seem relevant to my interest.

2) How is help data organised, physically? If I could grep (a UNIX text
search program) the text, I might get a pointer to the answer to 1)
above, and subsequent awkward help questions. I may be looking in the
wrong place for the answer to 1).

Microsoft has a VBA specification document.
<http://msdn.microsoft.com/en-us/library/dd361851(v=prot.10).aspx>
points to
<http://download.microsoft.com/download/0/a/6/0a6f7755-9af5-448b-907d-
13985accf53e/%5BMS-VBAL%5D.pdf>
in which "3.3.4 String Tokens" contains the relevant information. (In
my time, I have read syntax descriptions and believe I understand the
slightly cryptic presentation.) Sadly, I interpret it to mean that
"Hello,
World" is a valid String Token. I can't insert such a token in code. ;)

Please note that my interest is in reading how to create valid string
literal in a help topic, rather than merely knowing how to construct a
string literal.
 
hi Walter,

MsgBox "how to do a Carriage return" & vbCrLf & "ok it's fine"

MsgBox "how to do a double Carriage return" & vbCrLf & vbCrLf & "ok it's fine"
 
another example,

http://ooo.developpez.com/faq/?page=Chaine#Question208


this function allows you to apply a newline in a string, all 30 characters, without breaking words.

______________________________________________________________________________________________________________________________________________
Sub Test()
Dim txt As String, nbc As Integer

txt = "Le club Developpez.com est le principal site d'entraide des développeurs francophones avec jusqu'à 70 000 " & _
"visites par jour. Le club est fréquenté par 800 000 développeurs (développeurs, chefs de projets, DBA,...) tous les mois. Le club" & _
" vous apporte gratuitement une multitude d'avantages : newsletter, magazine, actualités, cours, tutoriels, articles, FAQ's, tests, " & _
"comparatifs, débats, sondages, outils, sources, composants et exemples de codes, les BLOGs des développeurs, et enfin la TV des " & _
"développeurs. Le club est animé bénévolement par tous les membres du club et en particulier par l'équipe de rédaction du club. " & _
"L'inscription est facile, rapide et gratuite : Inscrivez-vous."

nbc = 30

MsgBox ScinderChaine(txt, nbc)
End Sub


Function ScinderChaine(DonneesDeBase As String, NbCar As Integer)
Dim Cible As String
Dim Resultat As String, Chaine As String
Dim i As Integer, X As Integer

Cible = DonneesDeBase

For i = 1 To Len(Cible)
X = InStr(NbCar, Cible, " ")

If X = 0 Then
Chaine = Cible
Resultat = Resultat & Chaine
Exit For
End If

Chaine = Mid(Cible, 1, X)
Resultat = Resultat & Chaine & Chr(10)
Cible = Mid(Cible, Len(Chaine) + 1)
Next

ScinderChaine = Resultat
End Function
 
also note that Chr(10) equals vbCrLf

you can check it with this function,


Function ScinderChaine(DonneesDeBase As String, NbCar As Integer)
Dim Cible As String
Dim Resultat As String, Chaine As String
Dim i As Integer, X As Integer

Cible = DonneesDeBase

For i = 1 To Len(Cible)
X = InStr(NbCar, Cible, " ")

If X = 0 Then
Chaine = Cible
Resultat = Resultat & Chaine
Exit For
End If

Chaine = Mid(Cible, 1, X)
Resultat = Resultat & Chaine & vbCrLf & vbCrLf '***************Attention: double carriage return
Cible = Mid(Cible, Len(Chaine) + 1)
Next

ScinderChaine = Resultat
End Function
 
Walter Briscoe said:
If You want that vbCrLf is your friend.
e.g. """Hello, " & vbCrLf & "World""".
Click to expand...

IMHO, it is better to use vbNewline. That represents CRLF on non-Mac
platforms, and just CR on Mac platforms.

You can find this and other constants by putting "visual basic contants" in
the VBA help search field, then clicking on the link by that name, then
clicking on "miscellaneous contants".
 
hi joeu,

Excel XP (vba), we can use vbNewLine, vbCrLf and Chr(10)
CRLF have no effect and produces no error,
then my question is, can you use vbCrLf and Chr(10) on Mac ?

thank you for your reply
 
In message said:
IMHO, it is better to use vbNewline. That represents CRLF on non-Mac
platforms, and just CR on Mac platforms.
Click to expand...

That is a good point.
I should follow it as I tie my code to Windows without any benefit.
You can find this and other constants by putting "visual basic
contants" in the VBA help search field, then clicking on the link by
that name, then clicking on "miscellaneous contants".
Click to expand...

Merci bien à Isabelle (Je n'ai pas de clavier français, mais il y a
toujours charmap. C'est charmap en français? )
[Thanks to Isabelle (I don't have a French keyboard, but there is always
charmap. Is charmap called charmap in French?)]

Thanks to joeu2004, too.

I want to find "Excel 2003 VBA string literal help location" as shown in
my subject. It is a particular question, which I extended to the
general, and I continue to wait for answers to my real questions.

My hypothesis is there is no Excel 2003 VBA string literal help. ;)
 
Walter Briscoe submitted this idea :
In message said:
IMHO, it is better to use vbNewline. That represents CRLF on non-Mac
platforms, and just CR on Mac platforms.
Click to expand...

That is a good point.
I should follow it as I tie my code to Windows without any benefit.
You can find this and other constants by putting "visual basic
contants" in the VBA help search field, then clicking on the link by
that name, then clicking on "miscellaneous contants".
Click to expand...

Merci bien à Isabelle (Je n'ai pas de clavier français, mais il y a
toujours charmap. C'est charmap en français? )
[Thanks to Isabelle (I don't have a French keyboard, but there is always
charmap. Is charmap called charmap in French?)]

Thanks to joeu2004, too.

I want to find "Excel 2003 VBA string literal help location" as shown in
my subject. It is a particular question, which I extended to the
general, and I continue to wait for answers to my real questions.

My hypothesis is there is no Excel 2003 VBA string literal help. ;)
Click to expand...

You'd probably get more success googling Classic VB forums. But
generally, strings can be as long as you want, with as many line feeds
as you need. Though, it makes more sense to store long strings in a
worksheet and just retrieve them for use with VBA. Otherwise, here's
some example of storing a long string in a constant...

Const sMY_STRING As String = "Here is an example of a long string" _
& " that can be wrapped on many lines." _
& vbLF & vbLF _
& "You could continue this indefinitely" _
& " for as many lines as you like!"
 
In message said:
Walter Briscoe submitted this idea :
[snip]
My hypothesis is there is no Excel 2003 VBA string literal help. ;)
Click to expand...

You'd probably get more success googling Classic VB forums. But
Click to expand...
[snip]

Thanks, Garry. I think I already understand VBA string literals.
I want a VBA help topic which documents the rules for string literals.
Without a specification document, I view my knowledge as mere hearsay.
If I identified where VBA help is located and converted it to text,
I could grep for all occurrences of "string literal".
 
also note that Chr(10) equals vbCrLf

No, Chr(10) does not equal vbCrLf, rather, it equals the predefined string
constant vbLf. The Cr in vbCrLf stands for Carriage Return while the Lf
stands for Line Feed... vbCrLf is a predefined two-character string constant
whose first character as an ASCII code of 13 and whose second character has
an ASCII code of 10. Excel uses vbLf (character with ASCII code of 10) for
its newline character. If you use vbCrLf, the Carriage Return character
remains, but being a non-printing character, you can't easily see it. Go
into the VB editor and execute this line of code in the Immediate Window...

Range("A1").Value = "One" & vbCrLf & "Two"

Then select the cell and put the cursor in the Formula Bar... click it so
that it is to the right of the word One on the first line and make sure it
is not next to the "e" when you click it... you will see what looks like a
"space" after the "e"... that is the Carriage Return character. Excel and VB
pretty much use the Line Feed (vbLf) character for its newline character.
So, you might ask, what is the vbCrLf character sequence for? It is the
character sequence that Windows (and DOS before it) uses for its newline
character sequence. If you were to read in an entire text file stored on the
hard disk into a String variable, you would find the Carriage Return/Line
Feed character sequence everywhere there is a new line in the text file. It
might be useful to know that Mac computers (at least the older operating
system) use a Carriage Return for its text file newlines (UNIX/Linux uses
the Line Feed for its newline which, since the newer Mac OS is Linux based,
as I understand it, is why I am unsure what its newer OS uses for newlines).
There is a universal predefined VB constant that automatically adjusts
itself to be the proper newline character (sequence) for Macs and PCs...
vbNewLine... it is equivalent to vbCrLf on a PC and vbCr on a Mac (at least
for the older OSs... and possibly vbLf for the new OSs, but not being a Mac
person, I'm not sure of this latter possibility... it may be vbCr for all
Macs... I have no way of checking).

Rick Rothstein (MVP - Excel)
 
Back
Top