String-Based Functions

Sub-Strings of a String

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.

The Left Sub-String

The Left() function can be used to get a number of characters from the left side of a string. and create a new 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 = "Welcome to Visual Basic Programming"
    strLeft = Left(strValue, 7)
    
    MsgBox strValue
    MsgBox strLeft
End Sub

This would produce:

Because the Left() function produces a string, you can also write it as Left$ with the $ sign indicating that it returns a string.

The Right Sub-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 = "Welcome to Visual Basic Programming"
    strRight = Right(strValue, 24)
    
    MsgBox strValue
    MsgBox strRight
End Sub

This would produce:

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

The Middle Sub-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 = "Welcome to Visual Basic Programming"
    strMid = Mid(strValue, 12)
    
    MsgBox strValue
    MsgBox strMid
End Sub

This would produce:

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 = "Welcome to Visual Basic Programming"
    strMid = Mid(strValue, 12, 12)
    
    MsgBox strValue
    MsgBox strMid
End Sub

This would produce:

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 = "Welcome to Visual Basic Programming"
    iPos = InStr(1, strValue, "Basic")
    
    MsgBox "In """ & strValue & """, " & " Basic can be found at " & CStr(iPos)
End Sub

This would produce:

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:

Public Function Replace( _
   ByVal Expression As String, _
   ByVal Find As String, _
   ByVal Replacement As String, _
   Optional ByVal Start As Integer = 1, _
   Optional ByVal Count As Integer = -1, _
   Optional ByVal Compare As CompareMethod = CompareMethod.Binary
) As String

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.

Practical Learning: Replacing Occurrences in a String

1. Start Microsoft Access
2. In the list of files, click Business Starter from the previous lesson
3. In the Navigation Pane, right-click Replacement and click Design View
4. On the form, right-click Replace and click Build Event
5. In the Choose Builder dialog box, double-click Code Builder and implement the event as follows:

   Private Sub cmdReplace_Click()
       Dim strPhoneNumber
       
       strPhoneNumber = Replace(txtPhoneNumber, " ", "")
       ' If there is an opening parenthesis, remove it
       strPhoneNumber = Replace(strPhoneNumber, "(", "")
       ' If there is a closing parenthesis, replace it with -
       strPhoneNumber = Replace(strPhoneNumber, ")", "-")
   
       txtReplacement = strPhoneNumber
   End Sub

6. Return to Microsoft Access and switch the form to Form View
7. Click Phone # and type a valid US/Canada phone number such as (301) 927-3594
8. Click the Replace button:

   

9. Save and close the form

Character and String Conversions

Numeric Hexadecimal Conversion

To let you convert a decimal number to its hexadecimal format, the Visual Basic language provides the Hex() function. Its syntax is:

Public Function Hex(
   ByVal Number As { Byte | Integer | Long |
                     Single | Double | Currency | Variant } ) As String

Practical Learning: Converting to Hexadecimal

1. On the Ribbon, click File and click Open
2. In the list of files, click Business Mathematics from previous lessons
3. In the Navigation Pane, right-click Hexadecimal and click Design View
4. On the form, right-click Convert to Hexadecimal and click Build Event
5. In the Choose Builder dialog box, double-click Code Builder and implement the event as follows:

   Private Sub cmdConvertToHexadecimal_Click()
       Dim number
       
       number = txtNumber
       txtHexadecimal = Hex(number)
   End Sub

6. Return to Microsoft Access and switch the form to Form View
7. Click Number and type a number such as 820384
8. Click Convert to Hexadecimal

   

9. Replace the number with a decimal one such as 6829.283 and click the button:

   

10. Replace the number with currency value such as $29.95 and click the button:

    

11. Save and close the form

Numeric Octal Conversion

To let you convert a decimal number to its octal format, the Visual Basic language provides the Oct() function. Its syntax is:

Public Function Oct(
   ByVal Number As { Byte | Integer | Long | 
   Single | Double | Currency | Variane } ) As String

Practical Learning: Converting to Hexadecimal

1. In the Navigation Pane, right-click Octal and click Design View
2. On the form, right-click Convert and click Build Event
3. In the Choose Builder dialog box, double-click Code Builder and implement the event as follows:

   Private Sub cmdConvert_Click()
       Dim number
       
       number = txtNumber
       txtHexadecimal = Hex(number)
       
       txtOctal = Oct(number)
   End Sub

4. Return to Microsoft Access and switch the form to Form View
5. Click Number and type a number such as 820384
6. Click Convert to Hexadecimal

   

7. Replace the number with a decimal one such as 6829.283 and click the button:

   

8. Replace the number with currency value such as $29.95 and click the button:

   

9. Save and close the form

Introduction to Numeric Formatting

Formatting a Number in a String

Number formatting consists of specifying how a number should display on a form or a report. To support it, the Visual Basic language provides a function named Format. This function can be used for different types of values The most basic technique consists of passing it an expression that holds the value to display. The syntax of this function is:

Public Function Format(ByVal expression As Object,
		       Optional ByVal style As String = "") As String

The simplest way to use this function is to pass it a number or a string as argument. The function would then produce that number.

Practical Learning: Introducing Number Formatting

1. In the Navigation Pane, right-click General Formatting and click Design View
2. On the form, right-click the Display button and click Build Event
3. In the Choose Builder dialog box, double Code Builder and implement the event as follows:

   Function CalculateNetPay(ByVal HourlyRate As Double, _
                            ByVal TotalTime As Double) As Double
       CalculateNetPay = HourlyRate * TotalTime
   End Function
   
   Private Sub cmdDisplay_Click()
       Dim HourlySalary
       Dim TimeWorked
       Dim Pay
       
       HourlySalary = 24.2
       TimeWorked = 42.5
       Pay = CalculateNetPay(HourlySalary, TimeWorked)
       
       txtHourlySalary = Format(HourlySalary)
       txtTimeWorked = Format(TimeWorked)
       txtNetPay = Format(Pay)
   End Sub

4. Return to Microsoft Access and switch the form to Form View
5. On the form, click the Display button

   

6. Return to Microsoft Visual Basic

A Number in a General Format

Besides the Format() function, the Visual Basic language provides some additional functions. To control how the number should display, you can pass the second argument of the Format() function. This argument would be passed as a string.

A number is in general format if it doesn't use any particular formtting. To use this format, pass the second argument to the Format() function as "general number". This is case-insensitive.

Practical Learning: Using General Formatting

1. Change the code of the button as follows:

   Private Sub cmdDisplay_Click()
       Dim HourlySalary
       Dim TimeWorked
       Dim Pay
       
       HourlySalary = 17.5
       TimeWorked = 38
       Pay = CalculateNetPay(HourlySalary, TimeWorked)
       
       txtHourlySalary = Format(HourlySalary, "GENERAL NUMBER")
       txtTimeWorked = Format(TimeWorked, "general number")
       txtNetPay = Format(Pay, "General Number")
   End Sub

2. Return to Microsoft Access and click the Display button

   

3. Return to Microsoft Visual Basic

A Number in a Fixed Format

To display a number in a fixed format, pass the second argument as "fixed" in case-insensitive. A number is in a fixed format if:

• It uses the character considered as the decimal separator, which, in US English, is the period
• On the left side of the decimal separator, there will be at least one digit. For example, if you provide a number such as .5, the function would use 0 on the left of the period
• The number will display with 2 digits on the right side of the decimal separator. If the argument is provided as a natural number, the function would add .00 to the right side of the number
• If the value is provided with one digit on the right side the decimal separator, the function would add 0 as the last digit
• If the value is passed with more than one digit, the function would reduce it to two digits using the same mechanism as the Floor() or the Ceiling() function

Practical Learning: Using Fixed Formatting

1. Change the code of the button as follows:

   Private Sub cmdDisplay_Click()
       Dim HourlySalary
       Dim TimeWorked
       Dim Pay
       
       HourlySalary = 17.5
       TimeWorked = 38
       Pay = CalculateNetPay(HourlySalary, TimeWorked)
       
       txtHourlySalary = Format(HourlySalary, "FIXED")
       txtTimeWorked = Format(TimeWorked, "fixed")
       txtNetPay = Format(Pay, "Fixed")
   End Sub

2. Return to Microsoft Access and click the Display button

   

3. Return to Microsoft Visual Basic

A Number in a Standard Format

The standard format is a combination of the general and the fixed formats: The number must use the decimal separator and the thousands separator, including the mechanisms to reconcile the digits on both sides of the decimal separator. To display a number in the standard format, you have various options. You can pass the second argument of the Format() function as "standard" in case-insensitive.

Practical Learning: Using Standard Formatting

1. Change the code of the button as follows:

   Private Sub cmdDisplay_Click()
       Dim HourlySalary
       Dim TimeWorked
       Dim Pay
       
       HourlySalary = 28.5
       TimeWorked = 178.5
       Pay = CalculateNetPay(HourlySalary, TimeWorked)
       
       txtHourlySalary = Format(HourlySalary, "STANDARD")
       txtTimeWorked = Format(TimeWorked, "standard")
       txtNetPay = Format(Pay, "Standard")
   End Sub

2. Return to Microsoft Access and click the Display button

   

3. Save and close the form

Formatting a Number

To further support the ability to format a number, the Visual Basic language provides a function named FormatNumber. Its syntax is:

Public Function FormatNumber(
   ByVal Expression As Object,
   Optional ByVal NumDigitsAfterDecimal As Integer = -1,
   Optional ByVal IncludeLeadingDigit As TriState = TriState.UseDefault,
   Optional ByVal UseParensForNegativeNumbers As TriState = TriState.UseDefault,
   Optional ByVal GroupDigits As TriState = TriState.UseDefault) As String

Practical Learning: Formatting a Number

1. In the Navigation Pane, right-click Salary Evaluation and click Design View
2. On the form, right-click the Evaluate button and click Build Event
3. In the Choose Builder dialog box, double-click Code Builder
4. Implement the event as follows:

   Private Sub cmdEvaluate_Click()
       Dim hourlySalary
       Dim weeklySalary
       Dim monthlySalary
       Dim yearlySalary
   
       hourlySalary = CDbl(txtHourlySalary)
        
       weeklySalary = hourlySalary * 40
       monthlySalary = weeklySalary * 4
       yearlySalary = monthlySalary * 12
   
       txtWeeklySalary = FormatNumber(weeklySalary)
       txtMonthlySalary = FormatNumber(monthlySalary)
       txtYearlySalary = FormatNumber(yearlySalary)
   End Sub

5. Return to Microsoft Access and switch the form to Form View
6. In the Hourly Salary text box, type a number such as 27.50
7. Click the Evaluate button

   

8. Save and close the form

Formatting a Monetary Value

Introduction

To display a number as a monetary value, you can pass the second argument of the Format() function as "currency" in case-insensitive.

Practical Learning: Formatting a Monetary Number

1. In the Navigation Pane, right-click Loan Payment and click Design View
2. On the form, right-click the Evaluate button and click Build Event
3. In the Choose Builder dialog box, double-click Code Builder
4. Implement the event as follows:

   Private Sub cmdCalculate_Click()
       Dim periods
       Dim loanAmount
       Dim interestRate
       Dim regularPayment
       Dim principalPayment
       
       loanAmount = CDbl(txtLoanAmount)
       interestRate = CDbl(txtInterestRate) / 100#
       periods = txtPeriods
   
       regularPayment = Pmt(interestRate / 12#, periods, -loanAmount, 0#, 0)
       principalPayment = PPmt(interestRate / 12#, 1, periods, -loanAmount, 0#, 0)
   
       txtLoanAmount = Format(loanAmount, "currency")
       txtLoanPayment = Format(regularPayment, "CURRENCY")
       txtPrincipalPayment = Format(principalPayment, "Currency")
   End Sub

5. Return to Microsoft Access and switch the form to Form View
6. Click Loan Amount and type a number such as 32.500
7. Click Interest Rate and type a decimal number such as 12.35
8. Click Number of Periods and type a number of months such as 60

   

9. Click the Calculate button

   

10. Return to Microsoft Visual Basic

Formatting a Currency Value

As an alternative to the Format() function, to let you control how a monetary value should be displayed, the Visual Basic language provides a function named FormatCurrency. Its syntax is:

Public Function FormatCurrency(Expression As Object,
			       NumDigitsAfterDecimal As Integer,
			       IncludeLeadingDigit As TriState,
			       UseParensForNegativeNumbers As TriState,
			       GroupDigits As TriState) As String

Practical Learning: Formatting a Currency Value

1. In the Navigation Pane, right-click Straight-Line Method1 accessed in Lesson 4 and click Design View (or use the Straight-Line Method2 form)
2. On the form, right-click the Calculate button and click Build Event... If you are using the Straight-Line Method1 form, in the Choose Builder dialog box, click Code Builder and click OK
3. Change the event as follows:

   Private Sub cmdCalculate_Click()
       Dim cost
       Dim salvageValue
       Dim estimatedLife
       Dim depreciation
   
       cost = CDbl(txtCost)
       salvageValue = CDbl(txtSalvageValue)
       estimatedLife = CDbl(txtEstimatedLife)
   
       depreciation = SLN(cost, salvageValue, estimatedLife)
   
       txtCost = FormatCurrency(cost)
       txtSalvageValue = FormatCurrency(salvage)
       txtEstimatedLife = FormatNumber(life, 0)
       txtDepreciation = FormatCurrency(depreciation)
   End Sub

4. Return to Microsoft Access and switch the form to Form View
5. Click Asset Original Value and type a number such as 36800
6. Click Salvage Value and type a number such as 8500
7. Click Estimated Life and type a natural number such 6

   

8. Click the Calculate button:

   

9. Save and close the form

Other Techniques of Formatting a Number

Formatting a Number as a Percentage

A percentage of a number represents its rate on a scale, usually of 100 (or more). The number is expressed using digits accompanied by the % sign.

To programmatically use a percentage number, you can use the Format() function. Besides the Format() function, to support percent values, the Visual Basic language provides a function named FormatPercent. Its syntax is:

Function FormatPercent(
   ByVal Expression As Variant,
   Optional ByVal NumDigitsAfterDecimal As Integer = -1,
   Optional ByVal IncludeLeadingDigit As Integer = -2,
   Optional ByVal UseParensForNegativeNumbers As Integer = -2,
   Optional ByVal GroupDigits As Integer = -2
) As String

Only the first argument is required and it is the number that needs to be formatted. When calling this function, pay attention to the number you provide as argument. If the number represents a percentage value as a fraction of 0 to 1, make sure you provide it as such. An example would be 0.25. In this case, the Visual Basic interpreter would multiply the value by 100 to give the result. Here is an example:

Private Sub cmdFunction_Click()
    Dim DiscountRate As Double

    DiscountRate = 0.25
    MsgBox FormatPercent(DiscountRate)
End Sub

This would produce:

If you pass the value in the hundreds, the interpreter would still multiply it by 100. Although it is not impossible to get a percentage value in the hundreds or thousands, you should make sure that's the type of value you mean to get.

Formatting a Number With Wild Cards

To let you further control how a numhber should display, the second argument of the Format() function can include some special characters. To represent the integral part of a number, use the # symbol. You can enter as many # signs as you want. To specify the number of digits to display on the right side of the decimal separator, type a period on the right side of # followed by the number of 0s representing each decimal place.

If you call the Format() function with the Standard option, it would consider only the number of digits on the right side of the decimal separator. If you want to display more digits than the number actually has, call the FormatNumber() function and pass a second argument with the desired number. Here is an example:

Private Sub cmdFunction_Click()
    Dim Number As Double

    Number = 20502.48
    MsgBox FormatNumber(Number, 4)
End Sub

This would produce:

In the same way, if you want the number to display with less numbers on the right side of the decimal separator, specify that number.

You can call the Format() function to format the number with many more options. To represent the integral part of a number, you use the # sign. To specify the number of digits to display on the right side of the decimal separator, type a period on the right side of # followed by the number of 0s representing each decimal place. Here is an example:

Private Sub cmdFunction_Click()
    Dim Number As Double

    Number = 20502.48
    MsgBox Format(Number, "#.00000")
End Sub

This would produce:

The five 0s on the right side of the period indicate that you want to display 5 digits on the right side of the period. You can enter as many # signs as you want; it would not change anything. Here is an example:

Private Sub cmdFunction_Click()
    Dim Number As Double

    Number = 20502.48
    MsgBox Format(Number, "##########.00000")
End Sub

This would produce the same result as above. To specify that you want to display the decimal separator, include its character between the # signs. Here is an example:

Private Sub cmdFunction_Click()
    Dim Number As Double

    Number = 20502.48
    MsgBox Format(Number, "###,#######.00000")
End Sub

This would produce:

You can include any other character or symbol you want in the string to be part of the result, but you should include such a character only at the beginning or the end of the string, otherwise the interpreter might give you an unexpected result.

Practical Learning: Formatting a Number With Wild Cards

1. On the Ribbon, click File and click Open
2. In the list¸of files, click Geometry1 from the previous lessons
3. In the Navigation Pane, right-click Hexagon and click Design View
4. In the Controls section of the Ribbon, click Image and click the form
5. From the resources that accompany these lessons, select and open Hexagon

   

6. On the form, right-click the Calculate button and click Build Event
7. In the Choose Builder dialog box, click Code Builder and click OK
8. Implement the event as follows:

   Public Function CalculatePerimeter(ByVal value As Double) As Double
       CalculatePerimeter = value * 6#
   End Function
   
   Public Function CalculateArea(ByVal value As Double) As Double
       CalculateArea = value * value * 3# * Sqr(3#) / 2#
   End Function
   
   Private Sub cmdCalculate_Click()
       Dim side As Double
       Dim perimeter, area
   
       side = CDbl(txtSide)
   
       perimeter = CalculatePerimeter(side)
       area = CalculateArea(side)
           
       txtPerimeter = Format(perimeter, "#.0000")
       txtArea = Format(area, "#.000000")
   End Sub

9. Return to Microsoft Access and switch the form to Form View
10. Click Side and type a positive number such as 158.49
11. Click the Calculte button

    

12. Save and close the form
13. Close Microsoft Access