Creating Strings

Direct Creation of Strings

Quotation marks can be used directly to create strings.

[Excel VBA] In Excel VBA, a string is created by enclosing the character sequence in double quotes and assigning it to a variable. The sample file path is Samples\ch06\Excel VBA\CreatingStrings.xlsm.

Sub Test()
    Dim strA As String
    strA = "Hello"
    Debug.Print strA
End Sub

Running the procedure outputs the following in the Immediate Window:

Hello

[Python] In Python, a string is created by enclosing the character sequence in single or double quotes and assigning it to a variable.

Example using single quotes:

>>> a = 'Hello'

Example using double quotes:

>>> a = "Hello"

If the string contains a newline, the following methods can be used.

[Excel VBA] In Excel VBA, insert the carriage return–line feed character vbCrLf at the newline position and join the parts with the & operator. Sample file path: Samples\ch06\Excel VBA\CreatingStrings.xlsm.

Sub Test2()
    Dim strA As String
    strA = "Hello" & vbCrLf & "VBA"
    Debug.Print strA
End Sub

Running the procedure outputs:

Hello
VBA

[Python] In Python, enclose the string in triple quotes. Example:

>>> a = '''Hello
Python'''
>>> a
'Hello\nPython'

In the result, \n represents the newline character. Triple quotes are three consecutive single quotes.

If the string contains single or double quotes, the following methods apply.

[Excel VBA] In Excel VBA, single quotes can be entered directly into the string; double quotes are represented by two consecutive double quotes. Sample file path: Samples\ch06\Excel VBA\CreatingStrings.xlsm.

Sub Test3()
    Dim strA As String
    Dim strB As String
    strA = "I'm VBA."
    strB = "I love ""VBA""."
    Debug.Print strA
    Debug.Print strB
End Sub

Output in the Immediate Window:

I'm VBA.
I love "VBA".

[Python] In Python, use different quotation marks to enclose the entire string. Example:

>>> a = "I'm Python."
>>> a
"I'm Python."
>>> b = 'I love "Python".'
>>> b
'I love "Python".'

A fixed-length string is a string whose length is predetermined. If the given string’s length exceeds the fixed length, the excess part is truncated; if shorter, it is padded with spaces at the end.

[Excel VBA] In Excel VBA, the syntax for creating a fixed-length string is:

String * size

Example: Declare a string strB of length 8 bytes. If the assigned length is less than 8, pad with spaces; if greater, truncate the excess. Sample file path: Samples\ch06\Excel VBA\CreatingStrings.xlsm.

Sub Test4()
    Dim strB As String * 8
    strB = "Hello VBA!"
    Debug.Print strB
End Sub

Output:

Hello VB

[Python] Python has no fixed-length string data type. To obtain a fixed-length string, write a function: if the given string is longer than the fixed length, slice to get the leftmost substring; if shorter, pad with spaces at the end.

[Excel VBA] The String function can repeat a single-character string. Sample file path: Samples\ch06\Excel VBA\CreatingStrings.xlsm.

Sub Test5()
    Dim strC As String
    strC = String(10, "ABC")
    Debug.Print strC
End Sub

Output:

AAAAAAAAAA

The Space function generates a specified number of spaces. Sample file path: Samples\ch06\Excel VBA\CreatingStrings.xlsm.

Sub Test6()
    Dim strD As String
    strD = "Hello" & Space(5) & "VBA!"
    Debug.Print strD
End Sub

Output (five spaces inserted between the two strings):

Hello     VBA!

Creating Strings via Type Conversion

Type conversion functions can convert variables of other types into strings, indirectly creating string variables.

[Excel VBA] In Excel VBA, use the CStr function to convert other types to string. Example: create a short integer variable, convert it with CStr, and output in the Immediate Window. Sample file path: Samples\ch06\Excel VBA\CreatingStrings.xlsm.

Sub Test7()
    Dim intA As Integer
    Dim strB As String
    intA = 123
    strB = CStr(intA)
    Debug.Print strB
End Sub

Output:

the string "123"

[Python] In Python, use the str function. Example:

>>> a = 123
>>> b = str(a)
>>> b
'123'

Length of a String

The length of a string is the number of characters it contains. Use the appropriate function to get the length.

[Excel VBA] Use the Len function. Sample file path: Samples\ch06\Excel VBA\CreatingStrings.xlsm.

Sub Test8()
    Dim strL As String
    strL = "Hello python & VBA!"
    Debug.Print Len(strL)
End Sub

Output:

19

[Python] Use the len function:

>>> len('hello python')   # length
12

Escape Characters

[Python] In Python, certain characters represent special operations, e.g., \n for newline, \r for carriage return. These are called escape characters. Common escape characters are listed in Table 6-1.

Table 6-1 Common Escape Characters in Python

When a string contains quotes, different quotation marks can avoid conflicts; escape characters offer another solution:

>>> a = 'Single quote is \'.'
>>> a
"Single quote is '."
>>> b = "Double quote is \"."
>>> b
'Double quote is ".'

To keep the escape character literal, prefix the string with r:

>>> a = 'Hello \nPython.'
>>> a
'Hello \nPython.'
>>> b = r'Hello \nPython.'
>>> b
'Hello \\nPython.'

a uses \n as escape, b uses r to disable escaping, so \\n in b represents a literal backslash + n.

[Excel VBA] Excel VBA has no concept of escape characters; special operations use Chr or predefined constants. Special characters are listed in Table 6-2.

Table 6-2 Special Characters in Excel VBA

Example: insert a tab between two strings:

Sub Test4()
    Dim strA As String
    strA = "Hello" & vbTab & "VBA"
    Debug.Print strA
End Sub

Output:

Hello   VBA