VBA Excel: Strings

	Strings

Introduction to Strings

A String

A string is one or a combination of characters. To declare a variable for it, you can use either String or the Variant data types. To initialize the variable, put its value in double-quotes and assign it to the variable. Here are examples:

Here is an example:

Sub Exercise()
    ActiveCell = "AAA"
End Sub

When this code runs, the value AAA would be entered into any cell that is currently selected.

Sub Exercise()
    Dim FirstName As Variant
    Dim LastName As String

    FirstName = "William"
    LastName = "Sansen"
End Sub

Producing a Beeping Sound

If you want, you can make the computer produce a beeping a sound in response to something, anything. To support this, the Visual Basic language provides a function called Beep. Its syntax is:

Public Sub Beep()

Here is an example of calling it:

Sub Exercise()
    Beep
End Sub

If this function is called when a program is running, the computer emits a brief sound.

String Concatenation

A string concatenation consists of adding one string to another. to support this operation, you can use either the + or the & operator. Here are examples:

Sub Exercise()
    Dim FirstName As Variant
    Dim LastName As String
    Dim FullName As String

    FirstName = "William"
    LastName = "Sansen"
    FullName = LastName + ", " & FirstName

    ActiveCell = "Full Name: " & FullName
End Sub

This would produce:

String

Introduction to Characters

Getting the ASCII Character of a Number

The characters used in the US English and the most common characters of Latin-based languages are created in a list or map of character codes. Each character is represented with a small number between 0 and 255. This means that each character must fit in a byte.

To help you find the equivalent ASCII character of such a number, the Visual Basic language provides a function named Chr. Its syntax is:

Public Function Chr(ByVal CharCode As Integer) As String

When calling this function, pass a small number as argument. Here is an example:

Sub Exercise()
    Dim Character As String
    Dim Number As Integer

    Number = 114
    Character = Chr(Number)

    ActiveCell = "The ASCII character of " & Number & " is " & Character
End Sub

This would produce:

Character

Besides finding the ASCII equivalent of a number, the Chr() function can be used to apply some behavior in a program. For example, a combination of Chr(13) and Chr(10) would break a line in an expression, which is equivalent to the vbCrLf operator.

Getting the Wide ASCII Character of a Number

If you pass a number lower than 0 or higher than 255 to the Chr() function, you would receive an error. The reason you may pass a number higher than 255 is that you may want to get a character beyond those of US English, such as ā. To support such numbers, the Visual Basic language provides another version of the function. Its syntax is:

Public Function ChrW(ByVal CharCode As Integer) As String

The W here represents Wide Character. This makes it possible to store the character in the memory equivalent to the Short integer data type, which can hold numbers from -32768 to 32767. Normally, you should consider that the character should fit in a Char data type,, which should be a positive number between 0 and 65535.

Here is an example:

Sub Exercise()
    Dim Character As String
    Dim Number As Long

    Number = 358
    Character = ChrW(Number)

    ActiveCell = "The ASCII character of " & Number & " is " & Character
End Sub

This would produce:

Wide Character

The Length of a String

The length of a string is the number of characters it contains. To assist you with finding the length of a string, the Visual Basic language provides a function named Len. Its syntax is:

Public Function Len(ByVal Expression As String) As Integer

This function expects a string as argument. If the function succeeds in counting the number of characters, which it usually does, it returns the an integer. Here is an example:

Sub Exercise()
    Dim Item As String
    Dim Length As Integer

    Item = "Television"
    Length = Len(Item)

    ActiveCell = "The number of characters in """ & Item & """ is " & Length
End Sub

This would produce:

Length

The Microsoft Excel library provides the LEN() function that produces the same result.

Characters, Strings, and Procedures

Passing a Character or a String to a Procedure

Like a normal value, a character or a string can be passed to a procedure. When creating the procedure, enter the argument and its name in the parentheses of the procedure. Then, in the body of the procedure, use the argument as you see fit. When calling the procedure, you can pass a value for the argument in double-quotes. In the same way, you can apply any of the features we studied for procedures, including passing as many arguments as you want or passing a mixture of characters, strings, and other types of arguments. You can also create a procedure that receives an optional argument.

Returning a Character or a String From a Function

To create a function that returns a character or a string, create the procedure using the Function keyword and, on the right side of the parentheses, include the String data type preceded by the As keyword or use the $ character. Here is an example we saw in Lesson 5:

Function GetFullName$()
    Dim FirstName$, LastName$

    FirstName = "Raymond"
    LastName = "Kouma"

    GetFullName$ = LastName & ", " & FirstName
    
End Function

When calling the function, you can use it as a normal function or you can retrieve the value it returns and use it as you see fit. Here is an example:

Function GetFullName$()
    Dim FirstName$, LastName$

    FirstName = "Raymond"
    LastName = "Kouma"

    GetFullName$ = LastName & ", " & FirstName
    
End Function
    
Sub Exercise()
    Range("B2") = GetFullName$
End Sub

Character and String Conversions

Introduction

To convert an expression to a string, you can call the VBA's CStr() function. Its syntax is:

Public Function CStr(ByVal Expression As Variant) As String

The argument can be almost any expression that can be converted it to a string, which in most cases it can. If it is successful, the function returns a string. Here is an example:

Sub Exercise()
    Dim DateHired As Date

    DateHired = #1/4/2005#
    ActiveCell = CStr(DateHired)
End Sub

The CStr() function is used to convert any type of value to a string. If the value to be converted is a number, you can use the Str() function. Its syntax is:

Public Function Str(ByVal Number As Variant) As String

This function expects a number as argument. Here is an example:

Sub Exercise()
    Dim Number As Double

    Number = 1450.5 / 2
    ActiveCell = Str(Number)
End Sub

Numeric Hexadecimal Conversion

In Lesson 3, we saw that the Visual Basic language supports hexadecimal number and we saw how to initialize an integer variable with a hexadecimal number. Now, on the other hand, if you have a decimal number but need it in hexadecimal format, you can convert it. To support this operation, you can call the Hex() function. Its syntax is:

Function Hex(ByVal Number As { Byte | Integer | Long | Variant} ) As String

This function is used to convert either an integer-based or a decimal number to its hexadecimal equivalent. It returns the result as a string. Here is an example:

Sub Exercise()
    Dim Number As Integer

    Number = 28645
    ActiveCell = Hex(Number)
End Sub

The Microsoft Excel library provides more functions to perform this type of operation.

Numeric Octal Conversion

If you have a decimal number you want to convert to its octal format, you can call the Oct() function. Its syntax is:

Function Oct(ByVal Number As { Byte | Integer | Long | Variant} ) As String

This function takes an integer-based or a decimal number and converts its octal equivalent. It returns the result as a string. Here is an example:

Sub Exercise()
    Dim Number As Double

    Number = 28645
    ActiveCell = Oct(Number)
End Sub

The Microsoft Excel library provides more functions to perform this type of operation.

Case Conversion

If you are presented with a string or an expression whose cases must be the same, you can convert all of its characters in either uppercase or lowercase.

To convert a character, a string or an expression to uppercase, you can call the VBA's UCase() or the Microsoft Excel's UPPER() functions. These functions take one argument as the string or expression to be considered. The syntaxes are:

Function UCase(ByVal Value As String) As String
Function UPPER(ByVal Value As String) As String

Each function receives a character or string as argument. If a character is already in uppercase, it would be returned the same. If the character is not a readable character, no conversion would happen and the function would return it. If the character is in lowercase, it would be converted to uppercase and the function would then return the uppercase equivalent.

Here is an example:

Sub Exercise()
    Dim ProgrammingEnvironment As String

    ProgrammingEnvironment = "Visual Basic for Application for Microsoft Excel"
    ActiveCell = UCase(ProgrammingEnvironment)
End Sub

To convert a character or a string to lowercase, you can call the VBA's LCase() or the Microsoft Excel's UPPER() functions. Their syntaxes are:

Function LCase(ByVal Value As String) As String
Function LOWER(ByVal Value As String) As String

The function takes a character or a string as argument. If a character is not a readable symbol, it would be kept "as is". If the character is in lowercase, it would not be converted. If the character is in uppercase, it would be converted to lowercase.

The Sub-Strings of a String

Introduction

A sub-string is a character or a group of characters or symbols that are part of an existing string. The Visual Basic language provides functions to create, manipulate, or manage sub-strings. The primary rule to keep in mind is that a sub-string is part of, and depends on, a string. In other words, you cannot have a sub-string if you do not have a string in the first place.

The Left Sub-String of a String

If you have an existing string but want to create a new string using a number of characters from the left side characters of the string, you can use the Microsoft Excel's LEFT() or the VBA's Left() functions. Their syntaxes are:

Function LEFT(ByVal str As String, ByVal Length As Integer) As String
Function Left(ByVal str As String, ByVal Length As Integer) As String

Each function takes two arguments and both are required. The first argument is the existing string. The second argument is the number of characters counted from the left side of the string. Here is an example:

Sub Exercise()
    Dim Process As String

    Process = "learning"
    ActiveCell = "To " & Left(Process, 5) & " is to gain understanding"
End Sub

This would produce:

Left

The Right Sub-String of a String

To create a new string using one or more characters from the right side of an existing string, call the Microsoft Excel RIGHT() or the VBA's Right() functions. Its syntax is:

Function RIGHT(ByVal str As String, ByVal Length As Integer) As String
Function Right(ByVal str As String, ByVal Length As Integer) As String

Both arguments are required. The first argument is the original string. The second argument is the number of characters counted from the right side of the string.

The Mid Sub-String of a String

You may want to create a string using some characters either from the left, from the right, or from somewhere inside an existing string. To assist you with this, the Visual Basic language provides a function named Mid and the Microsoft Excel library is equipped with a function named MID. Here is an example of calling the Mid() function:

Sub Exercise()
    Dim ProgrammingEnvironment As String

    ProgrammingEnvironment = "VBA for Microsoft Excel"
    ActiveCell = "The " & Mid(ProgrammingEnvironment, 10, 13) & " language"
End Sub

Finding a Sub-String

One of the most regular operations you will perform on a string consists of finding out whether it contains a certain character or a certain contiguous group of characters. To help you with this operation, the Visual Basic language provides the InStr() function and the Microsoft Excel library equipped with the FIND() function. Their syntaxes are:

InStr([start, ]string1, string2[, compare])
FIND([Find_Text, Within_Text, Start_Num)

In the first version of the function, the String1 argument is the string on which the operation will be performed. The String2 argument is the character or the sub-string to look for. If String2 is found in String1 (as part of String1), the function return the position of the first character. Here is an example:

The first version of the function asks the interpreter to check String1 from the left looking for String2. If String1 contains more than one instance of String2, the function returns (only) the position of the first instance. Any other subsequent instance would be ignored. If you want to skip the first instance or want the interpreter to start checking from a position other than the left character, use the second version. In this case, the Start argument allows you to specify the starting position from where to start looking for String2 in String1.

The InStr() function is used to start checking a string from the left side. If you want to start checking from the right side, call the InStrRev() function. Its syntax is:

InstrRev(stringcheck, stringmatch[, start[, compare]])

Replacing a Character or a Sub-String in a String

After finding a character or a sub-string inside of a string, you can take action on it. One of the operations you can perform consists of replacing that character or that sub-string with another character or a sub-string. To do this, the Visual Basic language provides the Replace() function and Microsoft Excel provides the REPLACE() function. Its syntax is:

Replace(expression, find, replace[, start[, count[, compare]]])
REPLACE(Old_Text, Find_Text, Start_Num, Num_Characters, New_Text)

The first argument is the string on which the operation will be performed. The second argument is the character or string to look for in the Expression. If that character or string is found, the third argument is the character or string to replace it with.

Other Operations on Strings

Reversing a String

Once a string has been initialized, one of the operations you can perform on it consists of reversing it. To do this, you can call the StrReverse() function. Its syntax is:

Function StrReverse(ByVal Expression As String) As String

This function takes as argument the string that needs to be reversed. After performing its operation, the function returns a new string made of characters in reverse order. Here is an example:

Sub Exercise()
    Dim StrValue As String
    Dim StrRev As String

    StrValue = "République d'Afrique du Sud"
    StrRev = StrReverse(StrValue)

    ActiveCell = StrValue & vbCrLf & StrRev
End Sub

Because the StrReverse() function returns a string, you can write it as StrReverse$.

Strings and Empty Spaces

The simplest string is probably one that you declared and initialized. In some other cases, you may work with a string that you must first examine. For example, for some reason, a string may contain an empty space to its left or to its right. If you simply start performing a certain operation on it, the operation may fail. One of the first actions you can take on a string would consist of deleting the empty space(s), if any on its sides.

To remove all empty spaces from the left side of a string, you can call the LTrim() function. Its syntax is:

Function LTrim(ByVal str As String) As String

To remove all empty spaces from the right side of a string, you can call the RTrim() function. Its syntax is:

Function RTrim(ByVal str As String) As String

To remove the empty spaces from both sides of a string, you can call the Trim() function. Its syntax is:

Function Trim(ByVal str As String) As String

Creating an Empty Spaced String

If you want to create a string made of one or more empty spaces, you can call the Space() function. Its syntax is:

Function Space(ByVal Number As Integer) As String

This function is the programmatic equivalent to pressing the Space bar when typing a string to insert an empty space between two characters.

The Message Box

Introduction

A message box is a special dialog box used to display a piece of information to the user. The user cannot type anything in the message box. There are usually two kinds of message boxes you will create: one that simply displays information and one that expects the user to make a decision.

A message box is created using the MsgBox function. Its syntax is:

Function MsgBox(Prompt[, Buttons] [, Title] [, Helpfile, Context]) As String

The MsgBox() function takes five arguments and only the first one is required.

The Message of a Message Box

The Prompt argument is the string that the user will see displaying on the message box. As a string, you can display it in double quotes, like this "Your credentials have been checked.". Here is an example:

Sub Exercise()
    MsgBox ("Your credentials have been checked.")
End Sub

This would produce:

Message Box

You can also create the message from other pieces of strings. The Prompt argument can be made of up to 1024 characters. To display the Prompt on multiple lines, you can use either the constant vbCrLf or the combination Chr(10) & Chr(13) between any two strings. Here is an example:

Sub Exercise()
    MsgBox ("Your logon credentials have been checked." & _
            vbCrLf & "To complete your application, please " & _
            "fill out the following survey")
End Sub

This would produce:

Message Box

If you call the MsgBox() function with only the first argument, it is referred to as a method (a method is a member function of a class; the class in this case is the Application on which you are working). If you want to use the other arguments, you must treat MsgBox as a function. That is, you must assign it to a variable or to an object.

The Buttons of a Message Box

The Buttons argument specifies what button(s) should display on the message box. There are different kinds of buttons available and the VBA language. Each button uses a constant integer as follows:

Constant	Numeric Value	Display
vbOKOnly	0
vbOKCancel	1
vbAbortRetryIgnore	2
vbYesNoCancel	3
vbYesNo	4
vbRetryCancel	5

When calling the MsgBox() function and specifying the button, you can use one of the above constant numeric values. Here is an example that displays the Yes and the No buttons on the message box:

Sub Exercise()
    ActiveCell = MsgBox("Your logon credentials have been checked " & _
           "and your application has been approved: " & _
           "Congratulations!" & vbCrLf & _
           "Before leaving, would you like " & _
           "to take our survey survey now?", vbYesNo)
End Sub

This would produce:

The Buttons of a Message Box

The Icon on a Message Box

Besides the buttons, to enhance your message box, you can display an icon in the left section of the message box. To display an icon, you can use or add a member of the MsgBoxStyle enumeration. The members that are meant to display an icon are:

Icon Constant	Numeric Value	Description
vbCritical	16
vbQuestion	32
vbExclamation	48
vbInformation	64

To use one of these icons, you must combine the value of the button to the desired value of the icon. To perform this combination, you use the OR operator. Here is an example:

Sub Exercise()
    Dim iAnswer As Integer
    
    iAnswer = MsgBox("Your logon credentials have been checked " & _
                    "and your application has been approved: Congratulations!" & _
                     vbCrLf & "Before leaving, would you like " & _
                     "to take our survey survey now?", vbYesNo Or vbQuestion)
End Sub

This would produce:

The Icon of a Message Box

When calling the MsgBox() function, if you want to show one or more buttons and to show an icon, you can use either two members of the MsgBoxStyle enumeration using the OR operator, or you can add one of the constant values of the buttons to another contant values for an icon. For example, 3 + 48 = 51 would result in displaying the buttons Yes, Ne, and Cancel, and the exclamation icon.

The Default Button of a Message Box

If you create a message box with more than one button, the most left button usually has a thick border, indicating that it is the default. If the user presses Enter after viewing the button, the effect would be the same as if he had clicked the default button. If you want, you can designate another button as the default. To do this, you can use or add another member of the MsgBoxStyle enumeration. The members used to specify the default button are:

Default Button Constant	Numeric Value	If the message box contains more than one button, the default would be
vbDefaultButton1	0	The first button
vbDefaultButton2	256	The second button
vbDefaultButton3	512	The third button

Once again, to specify a default value, use the OR operator to combine a Default Button Constant with any other combination. Here is an example:

Sub Exercise
    ActiveCell = MsgBox("Your logon credentials have been checked " & _
           "and your application has been approved: Congratulations!" & _
           vbCrLf & "Before leaving, would you like " & _
           "to take our survey survey now?", _
           vbYesNo Or _
    	       vbQuestion Or vbDefaultButton2)
End Sub

This would produce:

The Default Button of a Message Box

These additional buttons can be used to further control what the user can do:

Constant	Value	Effect
vbApplicationModal	0	The user must dismiss the message box before proceeding with the current database
vbSystemModal	4096	The user must dismiss this message before using any other open application of the computer

The Title of a Message Box

The Title argument is the caption that would display on the title bar of the message box. It is a string whose word or words you can enclose between parentheses or that you can get from a created string. The Title argument is optional. As you have seen so far, if you omit, the message box would display the name of the application on the title bar. Otherwise, if you want a custom title, you can provide it as the third argument to the MsgBox() function. The caption can be a simple string. Here is an example:

Sub Exercise()
    ActiveCell = MsgBox("Your logon credentials have been checked " & _
           "and your application has been approved: Congratulations!" & _
           vbCrLf & "Before leaving, would you like " & _
           "to take our survey survey now?", _
           vbYesNo Or vbQuestion, _
           "Crofton Circle of Friends - Membership Application")
End Sub

This would produce:

The Title of a Message Box

Notice that the caption is now customized instead of the name of the application. The caption can also be a string created from an expression or emanating from a variable or value.

The Returned Value of a Message Box

The MsgBox() function can be used to return a value. This value corresponds to the button the user clicked on the message box. Depending on the buttons the message box is displaying, after the user has clicked, the MsgBox() function can return a value. The value can be a member of the MsgBoxResult enumeration or a constant numeric value recognized by the Visual Basic language. The value returned can be one of the following values:

If the user click	The function returns	Numeric Value
	vbOK	1
	vbCancel	2
	vbAbort	3
	vbRetry	4
	vbIgnore	5
	vbYes	6
	vbNo	7

The Input Box

Introduction

The Visual Basic language provides a function that allows you to request information from the user who can type it in a text field of a dialog box. The function used to accomplish this is called InputBox and its basic syntax is:

InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

Presenting the Message

The most basic piece of information you can provide to the InputBox() function is referred to as the prompt. It should be a string that the user will read and know what you are expecting. Here is an example:

Sub Exercise()
    InputBox("Enter your date of birth as mm/dd/yyyy")
End Sub

This would produce

The Prompt of an Input Box

Upon reading the message on the input box, the user is asked to enter a piece of information. The type of information the user is supposed to provide depends on you, the programmer. Therefore, there are two important things you should always do. First you should let the user know what type of information is requested. Is it a number (what type of number)? Is it a string (such as the name of a country or a customer's name)? Is it the location of a file (such as C:\Program Files\Homework)? Are you expecting a Yes/No True/False type of answer (if so, how should the user provide it)? Is it a date (if it is a date, what format is the user supposed to enter)? These questions mean that you should state a clear request to the user and specify what kind of value you are expecting. A solution, also explicit enough, consists of providing an example to the user.

The Title of an Input Box

The second argument to the InputBox() function allows you to optionally specify the title of the input box. This is the string that would appear on the title bar. Since this is an optional argument, if you don't pass it, the input box would display the name of the application. Otherwise, to display your own title bar, pass the Title argument.

The title is passed as a string. Here is an example:

Sub Exercise()
    ActiveCell = InputBox("Please enter your date of birth as mm/dd/yyyy", _
                          "Student Registration")
End Sub

This would produce:

The Title of an Input Box

Notice that the caption is now customized instead of the name of the application. The caption can also be a string created from an expression or emanating from a variable or value.

The Default Value of an Input Box

Sometimes, even if you provide an explicit request, the user might not provide a new value but click OK. The problem is that you would still need to get the value of the text box and you might want to involve it in an expression. You can solve this problem and that of providing an example to the user by filling the text box with a default value. To support this, the InputBox() function provides the third argument.

To present an example or default value to the user, pass a third argument to the InputBox() function. If you want to use this argument to provide an example the user can follow, provide it with the right format. Here is an example:

Sub Exercise()
    ActiveCell = InputBox("Enter Student Name:", _
             "Student Registration", "John Doe")
End Sub

Here is an example of running the program:

The Default Value of an Input Box

Notice that, when the input box displays with a default value, the value is in the text box and the value is selected. Therefore, if the value is fine, the user can accept it and click OK. Another way you can use the default value is to provide a value the user can accept; that is, the most common or most likely value the user would enter. Here is an example:

Sub Exercise()
    ActiveCell = InputBox("Enter Birth State:", _
             "Student Registration", "VA")
End Sub

Here is an example of running the program:

The Default Value of an Input Box

Once again, notice that the user can just accept the value and click OK or press Enter.

The Location of the Input Box

By default, when the input box comes up, it displays in the middle of the screen. If you want, you can specify where the input box should be positioned when it comes up. To assist you with this, the InputBox() function is equipped with a fourth and a fifth arguments. The fourth argument specifies the x coordinate of the input box; that is, the distance from its left border to the left border of the monitor. The fifth argument specifies the distance from the top border of the input box to the top border of the monitor.

The Return Value of an Input Box

When the input box displays, after typing a value, the user would click one of the buttons: OK or Cancel. If the user clicks OK, you should retrieve the value the user would have typed. It is also your responsibility to find out whether the user typed a valid value. Because the InputBox() function can return any type of value, it has no mechanism of validating the user's entry. To retrieve the value of the input box dialog when the user clicks OK, you can get the returned value of the InputBox() function.

After being used, the InputBox() function returns a string. Here is an example of getting it:

Sub Exercise()
    Dim StudentName As String

    StudentName = InputBox("Enter Student Name:", _
                           "Student Registration")
    MsgBox ("Student Name: " & StudentName)
End Sub

You can also get any type of value from an input box. That is, when the InputBox() function exits, thanks to the flexibility of the Visual Basic language, the compiler can directly cast the returned value for you. Here is an example:

Sub Exercise()
    Dim DateOfBirth As Date

    DateOfBirth = InputBox("Please enter your date of birth as mm/dd/yyyy", _
                           "Student Registration")
    MsgBox("Date of Birth: " & DateOfBirth)
End Sub