MS-Excel / Functions and Formula

---

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:

Len(string)

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:

StrConv(string, conversion)

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:

LCase(string)
UCase(string)

Here, string is any string expression.
For example, the following statement lowercases the string MyString and assigns it to MyLowerString:

MyLowerString = LCase(MyString)