Visual Basic/Built In String Functions

Comparison

Two strings are equal by value if they have the same content:

If "Hello" = "Hello" Then MsgBox "A"

The statement Option Compare Text can be placed at the top of a module to make the comparison case-insensitive, impacting =, <, >, <=, >=, <>:

Option Compare Text
Sub Test()
  If "Hello" = "hello" Then MsgBox "A"
  If "aaa" < "BBB" Then MsgBox "B"
End Sub

To test whether two strings are equal by reference, that is, whether they start at the same address, you can use StrPtr function.

To test whether a string is greater than or less than another using lexicographical order, you can use <, >, <=, >=, <>. To decide whether a string is less than another, the two are compared character by character and as soon as a character from one string is found to have a lower ASCII code than the corresponding (same position) character in the other string, the first is declared to be less than the second. The converse test also works.

Another way of testing whether strings are equal or greater than one another is the StrComp function. Unlike =, <, >, etc., the StrComp function has an optional argument that controls whether the comparison is case-sensitive.

Example:

strHello = "hello": strHello2 = "Hello"
If StrComp(strHello, strHello2, vbTextCompare) = 0 Then
  Debug.Print "The strings are the same."
End If

Links:

StrComp Function, Access 2007, office.microsoft.com
StrComp Function, Office 2013, msdn.microsoft.com

Concatenation

The operator intended to perform string contatenation is &. The operator + can sometimes be used to the same effect, but not always:

a = "123"
b = "456"
c = a & b 'Yields 123456
d = a + b 'Yields 123456: applied to two strings
Debug.Print c, d

a = 123   'A number, not a string
b = "456" 'A string
c = a & b 'Yields 123456
d = a + b 'Yields 579: applied to a number and to a string
Debug.Print c, d

Containment

To find out if one string is a substring of another, use the InStr function as follows:

  If InStr("Hello","He") > 0 Then
    MsgBox "The second string is contained in the first string."
  End If
  If InStr("He","Hello") > 0 Then
    MsgBox "This never gets printed; wrong argument order."
  End If

InStr function returns the position of the substring if it is found or zero otherwise.

The two-argument use of InStr function is case-sensitive. A case-insensitive containment test can be done as follows:

  If InStr(UCase("Hello"), UCase("he")) > 0 Then
    MsgBox "The second string is contained in the first string when we " & _
           "disregard the case."
  End If

Replacing

To replace a string with another string inside a third string, use the built-in function Replace as follows:

  Result = Replace("Teh Beatles", "Teh", "The") 'results into "The Beatles"

Indexing and Substrings

Strings can be used almost as if they were lists of characters. The nth character in a string can be returned by subscripting:

Mid$(String1, n, 1)

The values of n start from 1 rather than from 0.

It is also possible to return a substring of a string. The same function is used but instead of specifying 1, you specify the length of the substring:

Offset = 2: Length = 3
Debug.Print Mid$("abcdef", Offset , Length) 'Prints bcd

If you ask for more characters than are available, you get just what there is, no error is raised:

Debug.Print Mid$("abcdef", 2, 33) 'Prints bcdef

You can also use Mid$ on the left-hand side of an assignment:

String1 = "aaaaaa"
Debug.Print String1 'Prints aaaaaa
Mid$(String1, 2, 3) = "bbb"
Debug.Print String1 'Prints abbbaa
Mid$(String1, 3, 1) = "cccccc"
Debug.Print String1 'Prints abcbaa
Mid$(String1, 2, 3) = "d"
Debug.Print String1 'Prints adcbaa

When Mid$ is on the left, it doesn't change the total length of the string: it just replaces the specified number of characters. If the right-hand side specifies fewer characters than are asked for, only that number of characters is replaced; if it specifies more, only the number asked for is used.

Links:

MID, MIDB, Excel 2003, office.microsoft.com

String constants

String constants can be declared like any other constant:

 Const s As String = "abcdef"

String Functions

Strings are not objects so they do not have methods but there is a number of functions that manipulate strings. Note that none of the functions modify the original string, except for Mid$ when it is on the left hand side of an assignment statement:

Asc: Returns the integer code of the first character of the string. The inverse function would be Chr.
Len: Returns the length of the string.
InStr: Returns the character index of the first occurrence of the substring in a string or zero if the substring is not found.
InstrB: Like InStr except that it returns the byte position. It has to be remembered, that Visual Basic 6 strings are Unicode strings.
InstrRev: Like InStr except that it returns the character position of the last occurrence of the substring.
Left$: Returns the specified number of characters from the beginning of the string. If there are fewer characters in the string Left$ returns the whole string, no error is raised,
Mid$: Returns a number of characters starting at the given position, on the left hand side it replaces those characters,
Right$: Returns the specified number of characters from the end of the string, if there are not that many characters, then Right$ returns the whole string.
IsNumeric: Returns true if the string looks like a number.
LTrim$, RTrim$, Trim$: Returns a copy of the string with leading, trailing or leading and trailing spaces removed respectively. Note that only ASCII spaces (character code 32) are removed, other whitespace characters such as tabs are treated as non-spaces.
LCase$, UCase: Converts the whole string to lower case or upper case respectively.
Val: Returns a number corresponding to the number found at the start of the string. Note that Val is not locale aware, which means that it always expects decimal points regardless of the regional settings of your computer; if you are reading from a comma delimited file this is probably the function you want to use.
Str: Returns a string corresponding to the given number. Like Val this is not locale aware. This is the function you should use if you are creating a text file containing numbers to be read on someone else's computer.
CStr: Converts the expression to a string. This procedure is locale aware and the correct function to use if converting numbers and differently typed values to strings for user-display. Usually it is unnecessary because Visual Basic automatically converts when necessary and uses Regional Settings to do so.
Format$: Converts a number to a string using a specific format. The format is provided as a string of characters, that shows how many digits should be given before and after the decimal point. Like CStr, Format$ is locale aware so the decimal separator will be whatever is specified in the user's Regional Settings. Format$ also provides for conversion of dates to various built-in and custom string formats.
CBool, CByte, CCur, CInt, CLng, CSng, CDbl, CDec: Locale aware conversions to Boolean, Byte, Currency, Integer, Long, Single, Double, Decimal.
Split: Chops a string into pieces and returns a Variant Array. If no delimiter is specified then spaces will be used. Delimiters may be any string of any length. Two adjacent delimiters delimit an empty string.
Hex$: Returns a string of Hex characters representing a number.
Oct$: Returns a string of Octal characters representing a number.
Replace$: Returns a string with occurrences of a specified substring replaced with a new string. Note that the substring and the new string do not have to be the same size.
StrComp: Returns -1 if the first string is less than the second, 0 if they are identical, +1 if the first is greater than the second. Takes an optional argument that determines the comparison algorithm: vbBinary for exact comparisons using the character codes, vbTextCompare for case insensitive comparisons.

Quotes in strings

Because the double quote (") is used to delimit strings, you can't use it directly to specify a quote within a string. For example

 ' Does not work - syntax error
 Debug.Print "Fred says "Hi" to you"

One way is to use that Chr() function to specify the character code (34)

 ' Works fine
 Debug.Print "Fred says " & Chr$(34) & "Hi" & Chr$(34) & " to you"

Another is to double the double-quotes. You might find this more or less readable than the above way.

 ' Works fine too
 Debug.Print "Fred says ""Hi"" to you"

Startswith and Endswith

Visual Basic does not have functions "startsWith" (or "BeginsWith") and "endsWith" found in some other programming languages. But it has "Like" comparison operator used for simple pattern matching that does the job when used with "*" to stand for "any string of characters":

If "Hello World" Like "Hello*" Then MsgBox "It starts."
If "Hello World" Like "*World" Then MsgBox "It ends."
If LCase("Hello World") Like "hello*" Then MsgBox "It starts, case insensitive."
If LCase("Hello World") Like "*world" Then MsgBox "It ends, case insensitive."
Ending = "World"
If "Hello World" Like "*" & Ending Then MsgBox "It ends."

Furthermore, you can write these functions yourself using "Left" function:

Function StartsWith(Str1 As String, Str2 As String, Optional CaseIns As Boolean) As Boolean
  StartsWith = False
  If CaseIns Then 'Case insensitive
    If Left(LCase(Str1), Len(Str2)) = LCase(Str2) Then
      StartsWith = True
    End If
  Else
    If Left(Str1, Len(Str2)) = Str2 Then
      StartsWith = True
    End If
  End If
End Function

Function EndsWith(Str1 As String, Str2 As String, Optional CaseIns As Boolean) As Boolean
  EndsWith = False
  If CaseIns Then 'Case insensitive
    If Right(LCase(Str1), Len(Str2)) = LCase(Str2) Then
      EndsWith = True
    End If
  Else
    If Right(Str1, Len(Str2)) = Str2 Then
      EndsWith = True
    End If
  End If
End Function

For a one-off comparison to a constant string, a function call may be an overkill:

If Left(String1, 9) = "Beginning" Then
  'Starts with, case-sensitive
  ...
Else
  ...
End If

If Right(String1, 3) = "end" Then
  'Ends with, case-sensitive
  ...
Else
  ...
End If

Pattern Matching

You can do simple pattern matching with Like keyword; for complex pattern matching, see Regular Expressions. The special characters in the patterns of Like include ? for a single char, * for any number of chars, # for a single decimal digit, [...] for a single char in the list, and [!...] for a single char not in the list.

Examples:

If "Hello World" Like "Hello*" Then MsgBox "A"
If "Hello World" Like "*World" Then MsgBox "B"
If "Itam" Like "It?m" Then MsgBox "G"
If "AB345" Like "[A-Z][A-Z]###" Then MsgBox "C"
If "Ab345" Like "[a-zA-Z][a-zA-Z]###" Then MsgBox "D"
If "Item" Like "[!;][!;][!;][!;]" Then MsgBox "E"
If Not "It;m" Like "[!;][!;][!;][!;]" Then MsgBox "F"

Links:

Like Operator, Visual Basic for Applications, msdn.microsoft.com
Like Operator, Visual Studio 2005, msdn.microsoft.com

Previous: Strings

Contents

Next: Regular Expressions