Microsoft Access Database Development With VBA

Strings

 

Strings and Code

 

A String Variable

A message box is a special dialog box used to display a piece of information to the user. As opposed to a regular form, the user cannot type anything on 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:

MsgBox([Message] [Buttons] [Title] [HelpFile] [Context])

The MsgBox function takes five arguments and only the first one is required: the Message.

The Message of a Message Box

The Message 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 "That's All Folks". Here is an example:

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

This would produce:

Message Box

You can also create it from other pieces of strings. The Message argument can be made of up to 1024 characters. To display the Message 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:

Private Sub cmdMessageBox_Click()
    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

The Buttons on a Message Box

The Buttons argument specifies what button(s) should display on the message box. There are different kinds of buttons available and Visual Basic recognizes them by a numeric value assigned to each. The Buttons argument is a value of the VbMsgBoxStyle enumeration. It can be one of the following constants:

VbMsgBoxStyle Member Constant Value Button(s) Displayed
vbOKOnly 0 OK
vbOKCancel 1 OK Cancel
vbAbortRetryIgnore 2 Abort Retry Message Box Button: Ignore
vbYesNoCancel 3 Yes Message Box Button: No Cancel
vbYesNo 4 Yes Message Box Button: No
vbRetryCancel 5 Retry Cancel

Here is an example that displays the Yes and the No buttons on the message box:

Private Sub cmdMessageBox_Click()
    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?", _
           VbMsgBoxStyle.vbYesNo
End Sub

This would produce:

Message Box

You can use the name of the member of the VbMsgBoxStyle enumeration directly, that is, without qualifying it. Here is an example:

Private Sub cmdMessageBox_Click()
    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

Or you can use the constant value of the member of the enumeration if you know it. Here is an example:

Private Sub cmdMessageBox_Click()
    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?", 4
End Sub

These three formats would produce the same result.

The Icons 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 a member of the VbMsgBoxStyle. The available members for the icons are:

VbMsgBoxStyle Member Integer Value Description
vbCritical 16
vbQuestion 32 Question
vbExclamation 48 Exclamation
vbInformation  64 Information

To use one of these icons, you have two options. You can combine its VbMsgBoxStyle button with the VbMsgBoxStyle icon member. To perform this combination, you use the Or operator. Here is an example:

Private Sub cmdMessageBox_Click()
    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?", _
           VbMsgBoxStyle.vbYesNo Or VbMsgBoxStyle.vbQuestion
End Sub

This would produce:

Message Box

Once again, you can use the name of the member of the VbMsgBoxStyle enumeration without qualifying it. Here is an example:

Private Sub cmdMessageBox_Click()
    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

The second alternative it to use the integral constants instead of the members of the  enumeration. Here is an example:

Private Sub cmdMessageBox_Click()
    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?", _
           4 Or 32
End Sub

Alternatively, you can add (using the arithmetic addition) the integral value of the button to the integral value of the icon. For example, the integral value of the Yes/No button is 4 and the integral value of the question icon is 32. If you add both, you get 4 + 32 = 36. Therefore, if you use 36 for the second argument, you would get the question icon, the Yes, and the No button. This would be done as follows:

Private Sub cmdMessageBox_Click()
    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?", 36
End Sub

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 one more member of the a member of the VbMsgBoxStyle enumeration. The available members are:

VbMsgBoxStyle Member Integral Constant 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
vbDefaultButton4  768 The fourth button

Once again, to specify a default value, use the Or operator to combine a VbMsgBoxStyle Member with any other combination. Here is an example:

Private Sub cmdMessageBox_Click()
    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?", _
           VbMsgBoxStyle.vbYesNoCancel Or VbMsgBoxStyle.vbQuestion _
           Or VbMsgBoxStyle.vbDefaultButton2
End Sub

This would produce:

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

Once again, you can use the name of the member of the VbMsgBoxStyle enumeration directly without qualifying it. Here is an example:

Private Sub cmdMessageBox_Click()
    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?", _
           vbYesNoCancel Or vbQuestion Or vbDefaultButton2
End Sub

Also, remember that you can use the constant integer of a member. Here is an example:

Private Sub cmdMessageBox_Click()
    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?", _
           3 Or 32 Or 256
End Sub

You can also arithmetically add the constant values of the desired members.

The Title or Caption 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 is equipped with the "Microsoft Office Access" string as its default value. 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:

Private Sub cmdMessageBox_Click()
    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?", _
           vbYesNoCancel Or vbQuestion Or vbDefaultButton2, _
           "Crofton Circle of Friends - Membership Application"
End Sub

This would produce:

Message Box

Notice that the caption is now customized instead of the routine "Microsoft Office Access". The caption can also be a string created from an expression or emanating from a variable or value.

Help for a Message Box

If your application is using a help file, you can specify this and let the message box use it. The HelpFile argument is a string that specifies the name of the help file, and the Context argument provides the number that corresponds to the appropriate help topic for the message box.

The Return Value of a Message Box

The MsgBox() function can be used to return a value. This value corresponds to the button the user clicks on the message box. Depending on the buttons the message box is displaying, after the user has clicked, the MsgBox() function can return one of the following values:

If the user click The function returns Numeric Value
OK vbOK 1
Cancel vbCancel 2
Abort vbAbort 3
Retry vbRetry 4
Ignore vbIgnore 5
Yes vbYes 6
No vbNo 7
 

Practical LearningPractical Learning: Creating Message Boxes

  1. Start Microsoft Access
  2. From the resources that accompany these lessons, open the Exercise2 database
  3. Double-click the Messages form to open it
     

  4. Switch the form to Design View
  5. Right-click the Message 1 button and click Build Event...
  6. On the Choose Builder dialog box, click Code Builder
  7. Click OK
  8. In the Code Editor, implement it as follows:
    Private Sub cmdMessage1_Click()
        MsgBox "This is Visual Basic as simple as it can get"
    End Sub
  9. To test the form, return to Microsoft Access and switch the form to Form View
  10. On the form, click the Message 1 button
  11. Notice that a message box displays. Also notice the caption on the title bar displays Microsoft Access
  12. Click OK to close the message box
  13. Click the Message 2 button and notice that nothing happens
  14. Return to the Code Editor
  15. Instead of the title bar displaying Microsoft Access as the caption, you can set your own caption. This is done through the 3rd argument of the MsgBox function. To see an example, on the Object combo box, select cmdMessage2 and implement its Click event as follows:
    Private Sub cmdMessage2_Click()
        MsgBox "Before formatting a floppy disk, " & _
               "make sure you know its content", , _
               "Disk Formatting Instructions"
    End Sub
  16. Return to the form
  17. Click the Message 2 button
  18. Return to the Code Editor
  19. When creating a message box using the MsgBox function, you can decide which button you want to use, using one of the constants we have listed earlier.
    To see an example, on the Object combo box, select cmdMessage3 and implement its Click event as follows:
    Private Sub cmdMessage3_Click()
        MsgBox "This will be your only warning", _
               vbOKOnly + vbExclamation, _
               "Attention! Attention!! Attention!!!"
    End Sub
  20. Test the form and the Message 3 button. Return to the Code Editor
  21. If you want to display a message on different lines, you can use the vbCrLf constant.
    As an example, on the Object combo box, select cmdMessage4 and implement its Click event as follows:
    Private Sub cmdMessage4_Click()
        MsgBox "You are about to embark on a long journey." & _
               vbCrLf & "If your courage is still fresh, " & _
               "now is the time to let us know!", _
               vbOKCancel + vbQuestion, _
               "Accept or Cancel the Mission"
    End Sub
  22. Test the form and experiment with the Message 4 button. Then return to the Code Editor
  23. You can also display a message on various lines using the Chr() function. To see an example, on the Object combo box, select cmdMessage5 and implement its Click event as follows:
    Private Sub cmdMessage5_Click()
        MsgBox "This message usually appears when trying " & _
               "to format a floppy disk while the floppy drive " & _
               "is empty. " & Chr(13) & Chr(10) & _
               "When or if this happens, make sure you have a " & _
               " floppy disk in the floppy drive.", _
               vbAbortRetryIgnore + vbCritical, _
               "Floppy Disk Formatting"
    End Sub
  24. Test the form and the Message 5 button. Then return to the Code Editor
  25. The usefulness of the MsgBox function is demonstrated in your ability to perform an action based on the button the user has clicked on the message box. Indeed, the implementations we have used so far were on the MsgBox method. If you want to get the button that the user has clicked, you have to use the function itself. The true capture of the clicked button is revealed by your finding out the clicked button. This is done using conditional statements that we have not learned so far. Therefore, we will just learn how to implement the function and how to assign a response button to it; throughout this tutorial, and whenever necessary, we will eventually see what to do when a certain button has been clicked. To see an example, on the Object combo box, select cmdMessage6 and implement its Click event as follows:
    Private Sub cmdMessage6_Click()
        Dim intResponse As Integer
        
        intResponse = MsgBox("Your printing configuration " & _
                             "is not fully set." & vbCrLf & _
                             "If you are ready to print, click" & vbCrLf & _
                             "(1) Yes: To print the document anyway" & vbCrLf & _
                             "(2) No: To configure printing" & vbCrLf & _
                             "(3) Cancel: To dismiss printing", _
                             vbYesNoCancel + vbInformation, _
                             "Critical Information")
    End Sub
  26. Test the form and the Message 6 button. Then return to the Code Editor
  27. When a message box displays, one of the buttons, if more than one is displaying, has a thicker border than the other(s); such a button is called the default button. By default, this is the 1st or most left button on the message box. If you want to control which button would be the default, use one of the default constant buttons listed above. To see an example, on the Object combo box, select cmdMessage7 and implement its Click event as follows:
    Private Sub cmdMessage7_Click()
        Dim intAnswer As Integer
        
        intAnswer = MsgBox("Do you want to continue this " & _
                           "operation?", _
                           vbYesNoCancel + vbQuestion + vbDefaultButton2, _
                           "National Inquiry")
    End Sub
  28. Test the form and the Message 7 button. Then return to the Code Editor
  29. Although the user cannot type on a message box, not only can you decide what it displays, but you can also use string variables that would be available only when the form is running. As an example, on the Object combo box, select cmdMessage8 and implement its Click event as follows:
    Private Sub cmdMessage8_Click()
        Dim strEmplName As String
        Dim intInquiry As Integer
        
        strEmplName = CStr(txtEmployeeName)
        intInquiry = MsgBox("Hi, " & strEmplName & Chr(13) & _
                            "I think we met already." & vbCrLf & _
                            "I don't know when. I don't know where." & vbCrLf & _
                            "I don't know why. But I bet we met somewhere.", _
                            vbYesNo + vbInformation, _
                            "Meeting Acquaintances")
    End Sub
  30. To test the form, return to Microsoft Access
  31. On the Employee Name text box, type Joseph Douglas
  32. Click the Message 8 button and see the result
  33. Click one of the buttons to close the message box
  34. Close the running form, click its close button Close

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:

An Input Box

Creating an Input Box

The function used to accomplish this is called InputBox and its basic syntax is:

Public Function InputBox( _
   ByVal Prompt As String, _
   Optional ByVal Title As String = "", _
   Optional ByVal DefaultResponse As String = "", _
   Optional ByVal Xpos As Integer = -1, _
   Optional ByVal YPos As Integer = -1 _
) As String

The Message of an Input Box

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:

Private Sub cmdInputBox_Click()
    InputBox "Enter your date of birth:"
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. For example, instead of the question above, you can implement the InputBox() function as follows:

Public Function Main() As Integer
    InputBox("Please enter your date of birth as mm/dd/yyyy")
    Return 0
End Function

This would produce:

Prompt

Another 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:

Private Sub cmdInputBox_Click()
    InputBox "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:

Private Sub cmdInputBox_Click()
    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:

Private Sub cmdInputBox_Click()
    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:

Private Sub cmdInputBox_Click()
    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:

Private Sub cmdInputBox_Click()
    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

Strings and Code

 

A String Variable

In Lesson 3, we had an introduction to strings. We saw that a string could be declared using the String data type. After declaring a String variable, it is initialized as empty. You can also initialize it with any string of your choice. Here is an example:

Private Sub cmdString_Click()
    Dim strValue As String
    
    strValue = "République d'Afrique du Sud"
    MsgBox strValue
End Sub

This would produce:

String

The Length of a String

In many operations, you will want to know the number of characters a string consists of. To get the size of a string, you can call the Len() function. Its syntax is:

Len(strValue As String) As Integer

The Len() function takes one argument, which is the string you are considering. The function returns the number of characters of the string. Here is an example:

Private Sub cmdString_Click()
    Dim strValue As String
    Dim iLen As Integer
    
    strValue = "République d'Afrique du Sud"
    iLen = Len(strValue)
    MsgBox "The length of """ & strValue & """ Is " & _
            CStr(iLen) & " characters."
End Sub

This would produce:

String

The String() Function

One way you can initialize a string is to fill it up with a certain character of your choice. To do this, you can call the String() function. Its syntax is:

String(number, character) As String

The second argument to this function is a character you want to repeat in the string. The first argument to this function is the number of occurrences you want the second argument to be repeated in the string. The String() function returns a String value. Here is an example:

Private Sub cmdString_Click()
    Dim strValue As String
    
    strValue = String(18, "#")
    MsgBox strValue
End Sub

This would produce:

String

The String Object

 

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:

LTrim(string) As String

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

RTrim(string) As String

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

Trim(string) As String

On the other hand, if you want to include or add empty spaces in a string, you can call the Space() function. Its syntax is:

Space(number) As String

Because all these functions return a string, they can be written as LTrim$, RTrim$, Trim$, and Space$.

Strings Comparisons

To compare two strings, you can call the StrCmp() function. Its syntax is:

StrComp(string1, string2, compare)

The first and the second arguments to this function are strings and both are required. After the function has performed the comparison, it returns

  • -1 if string1 is less than string2
  •   0 if string1 and string2 are equal
  •   1 if string1 is greater than string2

The third argument allows you to specify the comparison in binary or text format. This argument can have one of the following values:

Constant Value Description
vbBinaryCompare 0 Perform a binary comparison
vbTextCompare 1 Perform a textual comparison

Here is an example:

Private Sub cmdCreate_Click()
    Dim strValue1 As String
    Dim strValue2 As String
    Dim iComparisonValue
    
    strValue1 = "République d'Afrique du Sud"
    strValue2 = "Republic of South Africa"
    iComparisonValue = StrComp(strValue1, strValue2, vbTextCompare)
    
    MsgBox "Comparing """ & strValue1 & """ with """ & _
           strValue2 & """ produces " & CStr(iComparisonValue)
End Sub

This would produce:

String

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:

StrReverse(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:

Private Sub cmdCreate_Click()
    Dim strValue As String
    Dim strRev As String
    
    strValue = "République d'Afrique du Sud"
    strRev = StrReverse(strValue)
    
    MsgBox strValue
    MsgBox strRev
End Sub

This would produce:

String

String

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

 
 
 

The Character Cases of a String

 

Introduction

Each alphabetical character in the English language has two representations: lowercase and uppercase. Characters in lowercase are: a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, and z. Their equivalent characters in uppercase are represented as A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, and Z. Characters used for counting are called numeric characters; each one of them is called a digit. They are 0, 1, 2, 3, 4, 5, 6, 7, 8, and 9. There are other characters used to represent things in computer applications, mathematics, and others. Some of these characters, also called symbols are ~ , ! @ # $ % ^ & * ( ) _ + { } ` | = [ ] \ : " ; ' < > ? , . / These characters are used for various reasons and under different circumstances. For example, some of them are used as operators in Mathematics or in computer programming. Regardless of whether a character is easily identifiable or not, all these symbols are character types.

Converting a String to Uppercase

To convert a lowercase character to uppercase, you can use the UCase() function. Its syntax is:

UCase(strValue As String) As String

This function simply takes one argument, the string that needs to be converted. Each letter of the string that is already in uppercase would remain in uppercase. Each letter of the string that is in lowercase would be converted to uppercase. Each non-letter character of the argument would not be converted. This function returns a string value. Here is an example:

Private Sub cmdString_Click()
    Dim strValue As String
    
    strValue = "République d'Afrique du Sud"
    MsgBox strValue
    strValue = UCase(strValue)
    MsgBox strValue
End Sub

This would produce:

String

String1

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

Converting a String to Lowercase

To convert a string to lowercase, you can call the LCase() function. Its syntax is:

LCase(strValue As String) As String

This takes one argument as the string to convert. Any letter in lowercase in the argument would remain in lowercase. Any letter in uppercase would be converted to lowercase. The non-letter characters of the argument would be kept “as-is”.

Since the LCase() function produces a string, you can also write it as LCase$.

Conversion to Appropriate Case

The LCase$() and the UCase$() functions are used to process the whole string. An alternative to these two functions consists of calling the StrConv() function. Its syntax is:

StrConv(string, conversion As vbStrConv, LocalID As Long) As String

This function takes two required arguments. The first argument is the string that will be considered for conversion. The second argument allows you to specify how the conversion would be performed. This argument can have one of the following values:

Constant Value Description
vbUpperCase 1 Converts the lowercase letters of the string to uppercase
vbLowerCase 2 Converts the uppercase letters of the string to lowercase
vbProperCase 3 Converts the first letter of every word of the string to uppercase
vbWide 4 Converts narrow (single-byte) characters in string to wide (double-byte) characters
vbNarrow 8 Converts wide (double-byte) characters in string to narrow (single-byte) characters
vbKatakana 16 For Japan, converts Hiragana characters in string to Katakana characters
vbHiragana 32 For Japan, converts Katakana characters in string to Hiragana characters. 
vbUnicode 64 Converts the string to Unicode using the default code page of the system.
vbFromUnicode 128 Converts the string from Unicode to the default code page of the system

Here is an example:

Private Sub cmdCreate_Click()
    Dim strValue As String
    Dim strConversion As String
    
    strValue = "republic of south africa"
    strConversion = StrConv(strValue, vbProperCase)
    
    MsgBox strValue
    MsgBox strConversion
End Sub

This would produce:

String

String

Sub-Strings

 

Introduction

A sub-string is a string that is created or retrieved from an existing string. Once again, the Microsoft Visual Basic library provides different techniques of creating it. These include getting characters from the left, from the right, or from any part inside the string.

Creating a Sub-String

The Left() function can be used to get a number of characters from the left side of a string. The syntax of this function is:

Left(string, length) As String

This function takes two required arguments. The first argument is the string on which the operation will be performed. The second argument, a constant integer, is the number of characters to retrieve from the first argument. Here is an example:

Private Sub cmdCreate_Click()
    Dim strValue As String
    Dim strLeft As String
    
    strValue = "République d'Afrique du Sud"
    strLeft = Left(strValue, 10)
    
    MsgBox strValue
    MsgBox strLeft
End Sub

This would produce:

String

String

Because the Left() function produces a string, you can also write it as Left$ with the $ sign indicating that it returns a string. As its name suggests, the Left() function creates a new string using characters on the left side of the considered string. If you want your sub-string to contain characters from the right side of a string, you can call the Right() function. Its syntax is:

Right(string, length) As String

This function follows the same rules as the Left() function except that it works from the right side. Here is an example:

Private Sub cmdCreate_Click()
    Dim strValue As String
    Dim strRight As String
    
    strValue = "République d'Afrique du Sud"
    strRight = Right(strValue, 14)
    
    MsgBox strValue
    MsgBox strRight
End Sub

This would produce:

String

String

As mentioned for the Left() function, you can write the Right() function as Right$ to indicate that it returns a string.

While the Left$() and the Right$() functions work on both sides of a string, you may want to use characters starting at any position of your choice to create a sub-string. This operation is supported by the Mid() function. Its syntax is:

Mid(string, start[, length) As String

This function takes two required and one optional arguments. The first argument, which is  required, is the string on which the operation will be carried. The second argument, also required, is the position from where to start the sub-string inside the string argument. Here is an example:

Private Sub cmdCreate_Click()
    Dim strValue As String
    Dim strMid As String
    
    strValue = "République d'Afrique du Sud"
    strMid = Mid(strValue, 14)
    
    MsgBox strValue
    MsgBox strMid
End Sub

This would produce:

String

String

As you can see, if you omit the third argument, the returning sub-string would start at the start position from the string argument up to the end of the string. If you prefer, you can create a sub-string that stops before the end. To do this, you can pass the number of characters as the third argument. Here is an example:

Private Sub cmdCreate_Click()
    Dim strValue As String
    Dim strMid As String
    
    strValue = "République d'Afrique du Sud"
    strMid = Mid(strValue, 14, 7)
    
    MsgBox strValue
    MsgBox strMid
End Sub

This would produce:

String

String

Finding a Character or a Sub-String in a String

If you have a string and want to find a character or a sub-string in it, you can call the InStr() function. Its syntax is:

InStr([start, ]string1, string2[, compare])

The first argument specifies the position from the string where to start looking for. This first argument is not required. The second argument is required and specifies the string to examine. The third argument specifies the character or the string to look for in the second argument. The fourth argument, which is optional, specifies whether the criterion would be binary or text-based.

Here is an example:

Private Sub cmdCreate_Click()
    Dim strValue As String
    Dim iPos As Integer
    
    strValue = "Republic of South Africa"
    iPos = InStr(1, strValue, "South")
    
    MsgBox "In """ & strValue & """, " & " South can be found at " & CStr(iPos)
End Sub

This would produce:

String

The InStr() function works from the left side of the considered string. If you want to find an occurrence of one or more characters from the right side side of a string, you can use the InStrRev() function instead.

Replacing Occurrences in a String

After finding a character or a sub-string in an existing string, one of the operations you can perform would consist of replacing that character or that sub-string with another character or a sub-string. To do this, you can call the Replace() function. Its syntax is:

Replace(expression, find, replace[, start[, count[, compare]]])

The first argument to this function, expression, is required. It holds the string that will be considered.

The second argument, find, also required, is the character or the sub-string to look for in the expression.

The third argument also is required. It also is passed as a string. If the find string is found in expression, it would be replaced with the replace string.

When you call the Replace() function with the three required arguments, it would proceed from the most left character of the expression string. Instead of start with the first character, you can specify another starting position of your choice within expression. The fourth argument, which is optional, allows you to pass this factor as a constant integer.

The fifth argument also is optional. If you omit it, every time find would be found in expression, it would be replaced with replace, as many times as possible. If you want, you can limit the number of times this replacement should occur even if find is found more than once. To specify this factor, pass the fifth argument as a constant integer.

The compare argument, also optional, allows you specify whether the comparison would be carried in text or binary format. This argument can be passed as Text or Binary.

 
 
   
 

Previous Copyright © 2002-2013 FunctionX, Inc. Next