Using Len to Check the Length of a String
To find out how long a string is, use the Len function. The syntax for the Len function is straightforward:
Here, string is any valid string expression. (If string is Null, Len also returns Null.) One use for Len is to make sure a user's entry in an input box or in a text box of a dialog box is of a suitable length. A United States phone number must be 10 digits, for instance. The CheckPassword procedure shown in Listing-2 uses Len to make sure a password the user enters is long enough to be difficult to guess, but not too long.Listing-2: Testing password length with the Len function
1. Sub CheckPassword() 2. Dim strPassword As String 3. BadPassword: 4. strPassword = InputBox _ ("Enter the password to protect this item from changes:" _ , "Enter Password") 5. If Len(strPassword) = 0 Then 6. End 7. ElseIf Len(strPassword) < 6 Then 8. MsgBox "The password you chose is too short." _ & vbCr & vbCr & _ "Choose a password between 6 and 15 characters in length.", _ vbOKOnly + vbCritical, "Unsuitable Password" 9. GoTo BadPassword 10. ElseIf Len(strPassword) > 15 Then 11. MsgBox "The password you chose is too long." _ & vbCr & vbCr & _ "Choose a password between 6 and 15 characters in length.", _ vbOKOnly + vbCritical, "Unsuitable Password" 12. GoTo BadPassword 13. End If 14. End Sub
Listing-2 ensures that a password contains between 6 and 15 characters (inclusive). Here's how the code works:
- Line 2 declares a String variable named strPassword.
- Line 3 contains the label BadPassword, to which the GoTo statements in line 9 and line 12 redirect execution if the password fails either of the checks.
- Line 4 assigns to strPassword the result of an input box that invites the user to enter the password for the item.
- Lines 5 through 13 then use an If statement to check that the password is an appropriate length. First, line 5 checks strPassword for zero length, which would mean that the user either clicked the Cancel button or the close button on the input box or clicked the OK button with no text entered in the input box. If the length of strPassword is zero, the End statement in line 6 terminates the procedure. If the password passes that test, line 7 checks to find out if its length is less than 6 characters; if so, the procedure displays a message box alerting the user to the problem and then redirects execution to the BadPassword label. If the password is 6 or more characters long, line 10 checks to see if it's more than 15 characters long; if it is, the user gets another message box and another trip back to the BadPassword label.
Using StrConv, LCase, and UCase to Change the Case of a String
If you need to change the case of a string, use the StrConv (whose name comes from string conversion), LCase, and UCase functions. Of these, the easiest to use is StrConv, which can convert a string to a variety of different formats varying from straightforward uppercase, lowercase, or propercase (as VBA refers to initial capitals, also known as title case) to the Japanese hiragana and katakana phonetic characters.USING STRCONV
The StrConv function has the following syntax:
Here, the string argument is any string expression, and the conversion argument is a constant or value specifying the type of conversion required. The most useful conversion constants and values are shown in Table below.The most common conversion constants
Constant Value Effect vbUpperCase 1 Converts the given string to uppercase characters vbLowerCase 2 Converts the given string to lowercase characters vbProperCase 3 Converts the given string to propercase (aka title case - the first letter of every word is capitalized) vbUnicode 64 Converts the given string to Unicode using the system's default code page vbFromUnicode 128 Converts the given string from Unicode to the system's default code page
For example, suppose you received from a database program a string called strCustomerName containing a person's name. You could use StrConv to make sure that it was in title case by using a statement such as this:
strProperCustomerName = StrConv(strCustomerName, vbProperCase)
Note that StrConv doesn't care about the case of the string you feed it - it simply returns the case you asked for. For example, feeding StrConv uppercase and asking it to return uppercase doesn't cause any problem.USING LCASE AND UCASE
If you don't feel like using StrConv, you can alternatively use the LCase and UCase functions,
which convert a string to lowercase and uppercase, respectively.
LCase and UCase have the following syntax:
Here, string is any string expression.
For example, the following statement lowercases the string MyString and assigns it to MyLowerString:
MyLowerString = LCase(MyString)
In this tutorial:
- Using Built-in Functions
- What Is a Function?
- Passing Arguments to a Function
- Using Functions to Convert Data from One Type to Another
- Using the Asc Function to Return a Character Code
- Using the Str Function to Convert a Value to a String
- Using the Format Function to Format an Expression
- Using the Chr Function and Constants to Enter Special Characters in a String
- Using Functions to Manipulate Strings
- Using InStr and InStrRev to Find a String within Another String
- Using LTrim, RTrim, and Trim to Trim Spaces from a String
- Using Len to Check the Length of a String
- Using the StrComp Function to Compare Apples to Apples
- Using VBA's Mathematical Functions
- Excel VBA's Date and Time Functions
- Using the DateDiff Function to Return an Interval
- Using File-Management Functions