Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Is Letters Function - Check Whether a String Consist of Only Letters

             Today's post is about how to create a custom function to determine whether a string is only consists of letters or not. When we develop systems, sometimes we need to check this kind of properties of strings. It won't be the major part of the program, but it can be essential. 

After understanding this code you can even develop your own functions to determine the existence or non-existence of any character or characters.

So let's create our custom function. We are going to develop this function with the use of important table call ASCII table. Every character has an ASCII code. ASCII code is the numerical representation of the character. This ASCII table is available in lot of websites. You can easily find a table from internet.

If you carefully observe a ASCII table, you will find that ASCII code of A - Z is 65 - 90. And ASCII code of a - z is 97 - 122. 

That means

Asc(A)=65
Asc(B)=66
'''''''''
'''''''''
Asc(Z)=90

and 
Asc(a)=97
Asc(b)=98
'''''''''
'''''''''
Asc(z)=122

So below is our developed custom function base on this property of the letters.

Function IsLetters(Str As String) As Boolean

Dim i As Integer
For i = 1 To Len(Str)
    Select Case Asc(Mid(Str, i, 1))
        Case 65 To 90, 97 To 122
            IsLetters = True
        Case Else
            IsLetters = False
            Exit For
    End Select
Next i

End Function

Here below is a detailed explanation about this custom function.

Function IsLetters(Str As String) As Boolean

Name of our function is IsLetters

Str As String

This means that parameters of our function should be string data type.

As Boolean

This part explains about the data type of return value. So the return value should be either true or false.

We use variable i to loop through each and every character of the string

For i = 1 To Len(Str)
    ........
    ........
Next i

When it loop through the each character Asc(Mid(Str, i, 1)) gives the ASCII value of each character.




And we have used Case statement to determine whether ASCII code of each character is belongs to ASCII code of A-Z or a-z. If ASCII code of every character in that string belongs to 65-90 and 97-122 then it will return true. If any character has value out side that range, IsLetters variable become false and will exit the for loop. Hence the return value of the function will be false.

Contact Form

Name

Email *

Message *

Popular Posts