[Lesson 8]<<[Table of Contents]>>[Lesson 10]
Excel 2010 VBA can handle strings just as well as the stand-alone Visual Basic program. All the string handling functions in Visual Basic such as Len, Right, Left, Mid, Trim, Ltrim, Rtrim, Ucase, Lcase, Instr, Val, Str, Chr, and Asc can be used in Excel 2010 VBA macro programming.
9.1 The InStr function
InStr is a function that looks for and returns the position of a substring in a phrase.
Example 9.1
Private Sub cmdInstr_Click() Dim phrase As String phrase = Cells(1, 1).Value Cells(4, 1) = InStr(phrase, “ual”) End Sub
The function InStr(phrase,”ual”) will find the substring “ual” from the phrase “Visual Basic” entered in cells(1,1) and then return its position, in this case, it is 4 from the left.
9.2 The Left function
Left is a function that extracts the characters from a phrase, starting from the left.
Left(phrase,4) means 4 characters are extracted from the phrase, starting from the leftmost position.
Example 9.2
Private Sub cmdLeft_Click() Dim phrase As String phrase = Cells(1, 1).Value Cells(2, 1) = Left(phrase, 4) End Sub
This code returns the substring “Visu” from the phrase “Visual Basic” entered in cells(1,1)
9.3 The Right function
Right is a function that extracts the characters from a phrase, starting from the Right.
Right(phrase,5) means 5 characters are extracted from the phrase, starting from the rightmost position.
Example 9.3
Private Sub cmdRight_Click() Dim phrase As String phrase = Cells(1, 1).Value Cells(3, 1) = Right(phrase, 5)
This code returns the substring “Basic” from the phrase “Visual Basic” entered in cells(1,1)
9.4 The Mid function
Mid is a function that extracts a substring from a phrase, starting from the position specified by the second parameter in the bracket.
Mid(phrase,8,3) means a substring of three characters are extracted from the phrase, starting from the 8th position from the left, including empty space.
Example 9.4
Private Sub cmdMid_Click() Dim phrase As String phrase = Cells(1, 1).Value Cells(5, 1) = Mid(phrase, 8, 3) End Sub
This code returns the substring “Bas” from the phrase “Visual Basic” entered in cells(1,1)
9.5 The Len function
Len is a function that returns the length of a phrase(including empty space in between)
Example 9.5
Private Sub cmdLen_Click() Dim phrase As String phrase = Cells(1, 1).Value Cells(6, 1) = Len(phrase) End Sub
The code returns 12 for the phrase “Visual Basic” entered in cells(1,1)
9.6 The Ucase and the Lcase functions
The Ucase function converts all the characters of a string to capital letters. On the other hand, the Lcase function converts all the characters of a string to small letters. For example,
Ucase(“excel vba”) =EXCEL VBA Lcase(“Excel VBA”) =excel vba
9.7 The Str and Val functions
The Str is the function that converts a number to a string while the Val function converts a string to a number. The two functions are important when we need to perform mathematical operations.
9.8 The Chr and the Asc functions
The Chr function returns the string that corresponds to an ASCII code while the Asc function converts an ASCII character or symbol to the corresponding ASCII code. ASCII stands for “American Standard Code for Information Interchange”. Altogether there are 255 ASCII codes and as many ASCII characters. Some of the characters may not be displayed as they may represent some actions such as the pressing of a key or produce a beep sound. The format of the Chr function is
Chr(charcode)
and the format of the Asc function is
Asc(Character)
The following are some examples:
Chr(65)=A, Chr(122)=z, Chr(37)=% , Asc(“B”)=66, Asc(“&”)=38
