CODEWITHSUNDEEP
vbafunctionexcel
Synaptic Engima
Synaptic Engima

Reputation: 157

How can I check if a string only contains letters?

I'm using a function that allows me to review a string of text and evaluate if it is composed of letters. It is housed in a module called "General". The general module only exists to house public functions and variables. Function code is listed below:

Public Function IsAlpha(strValue As String) As Boolean
Dim intPos As Integer

    For intPos = 1 To Len(strValue)
        Select Case Asc(Mid(strValue, intPos, 1))
            Case 65 To 90, 97 To 122
                IsLetter = True
            Case Else
                IsLetter = False
                Exit For
        End Select
    Next
End Function

Next I have two "if" routines that evaluate the first 2 characters of a textbox in my userform. The first routine asks if the 1st character is numeric and the second routine asks if the 2nd character is alpha. Currently, the second "if" routine is ejecting me from the sub-routine when IsAlpha tests True, rather than generating the MsgBox. Is the IsAlpha function not being called correctly?

If routines code listed below:

Private Sub CmdMap_Click()

    With TxtDxCode
        If IsNumeric(Left(Me.TxtDxCode.Text, 1)) Then
            MsgBox "Incorrect DX Code format was entered. ", vbExclamation, "DX Code Entry"
            TxtDxCode.Value = ""
            TxtDxCode.SetFocus
            Exit Sub
        End If

        If IsAlpha(Left(Me.TxtDxCode.Text, 2)) Then
            MsgBox "Incorrect DX Code format was entered. ", vbExclamation, "DX Code Entry"
            TxtDxCode.Value = ""
            TxtDxCode.SetFocus
            Exit Sub
        End If
    End With

Upvotes: 10

Views: 54756

Answers (4)

Excel Hero
Excel Hero

Reputation: 14764

The most direct, concise, and performant way to check if a string contains only alpha characters is with this function:

Function IsAlpha(s) As Boolean
    IsAlpha = Len(s) And Not s Like "*[!a-zA-Z]*"
End Function

And in fact, this is slightly better...

Function IsAlpha(s$) As Boolean
    IsAlpha = Not s Like "*[!a-zA-Z]*"
End Function

Upvotes: 8

barrowc
barrowc

Reputation: 10679

Left(Me.TxtDxCode.Text, 2) returns the first two characters of the string. So, if Me.TxtDxCode.Text was 7ZABC, this expression would return "7Z". This would cause the IsAlpha test to fail.

As you want to examine just the 2nd character, use Mid$ instead:

If IsAlpha(Mid$(Me.TxtDxCode.Text, 2, 1)) Then

This will return "Z" and the IsAlpha test should now succeed

(The string versions Left$, Mid$ etc are slightly faster than the variant versions Left, Mid etc - see here)

Upvotes: 1

SierraOscar
SierraOscar

Reputation: 17637

Why don't you use regular expressions instead? Then there's no loops involved:

Public Function IsAlpha(strValue As String) As Boolean
    IsAlpha = strValue Like WorksheetFunction.Rept("[a-zA-Z]", Len(strValue))
End Function

Also, when you create a User-Defined Function (UDF) you need to ensure that the return value is assigned to the name of the actual function, in this case IsAlpha - not IsLetter otherwise the value will never be passed back.

Upvotes: 15

Saagar Elias Jacky
Saagar Elias Jacky

Reputation: 2688

Try this for IsAlpha

Public Function IsAlpha(strValue As String) As Boolean
Dim intPos As Integer

    For intPos = 1 To Len(strValue)
        Select Case Asc(Mid(strValue, intPos, 1))
            Case 65 To 90, 97 To 122
                IsAlpha = True
            Case Else
                IsAlpha = False
                Exit For
        End Select
    Next
End Function

Upvotes: 5

Related Questions