A few days ago I responded to a tweet from Sam Plett (@samplett) who was lamenting the difficulty of determining if a character is a letter or number in VBA. Here’s his exact tweet: Why is it so difficult in #vba to check if a character is a letter or a number – argh! I tweeted back saying that it isn’t that hard. VBA’s built-in IsNumeric function tells us if a character is a number. If the character isn’t a number, then it’s a character. I was wrong. When I read Sam’s tweet I fixated on the word character and failed to see letter. IsNumeric tells us if a character is a number. But, if the character isn’t a number, then we don’t know if it’s a letter, a symbol, punctuation, etc. Apologies to Sam for not paying closer attention to what he said and responding to quickly.
Sam is right. While VBA has native functions that test to see if an item is numeric (IsNumeric), is an object (IsObject), or is a date (IsDate), it does not have one that tests to see if an item is composed entirely of letters. That’s simple enough to correct.
The code below implements an IsLetter function. The code for this is simple. Loop through all the characters in a string checking the ASCII value of each one to see if it falls into the range 65 – 90 (uppercase A-Z) or 97 – 122 (lowercase a-z). If all the characters in the string fall into those ranges, then IsLetter returns True. If any character falls outside of those ranges, then IsLetter stops processing the remaining characters (no need to check them once we know the string is not composed of all letters) and returns False.
Function IsLetter(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
This was a perfect learning tool. this lead me to the function I was needing to check a single character within a string.
Function IsLetter(varChr, notChr) As Boolean
Select Case Asc(varChr)
Case 65 To 90, 97 To 122
Case Else
notChr = True
End Select
End Function
Thank you very much.
You’re welcome, Steven. Glad you found it useful.
WHAT A SHITTY ASS SITE. JK its the wjec coursework thats making me feel a little rough. Thanks for the code
You’re welcome, Omar.
I would add before the loop:
‘Checks to make sure input is not empty
If strValue = Empty Then
‘If it is, return false
IsLetter = False
Exit Function
End If
Otherwise an error will occur if an empty input is given.
Hi, Kye.
Thanks for the suggestion. I tested the code as is with both Null and Empty values and don’t get an error. Both return False. Are you getting an error?
Yeah, I was getting an error as soon as went into the for loop. Since it was from position 1, to 0 (Being the length of the string)
Nevermind, everything is all good now, I don’t know why it was giving me an error to start with…
Do you remember what the error was?
Error 13, that is all I can remember, it doesn’t matter anymore, it’s all fine
Understand. I was just curious.
Pingback: Is that a letter? - JP Software Technologies
All very cool. Thanks a lot for sharing. I dont have to say which solutions i choose right?
You’re welcome, Peter. No, you don’t.
I would do this slightly differently. Since we know that an input string with at least one non-letter character will make the function False, assume that it’s true unless proven otherwise.
Also, the Like function will serve the same purpose as checking the character code value.
Function IsLetter(strValue As String) As Boolean
Dim intPos As Long
‘ assume true
IsLetter = True
For intPos = 1 To Len(strValue)
IsLetter = (Mid$(strValue, intPos, 1) Like “[a-zA-Z]“)
If Not IsLetter Then Exit For ‘ Exit Function
Next intPos
End Function
And here’s another version that might offer a few less processing cycles:
Function IsLetter(strValue As String) As Boolean
Dim intPos As Long
‘ assume true
IsLetter = True
Do Until IsLetter = False Or intPos = Len(strValue)
intPos = intPos + 1
IsLetter = (Mid$(strValue, intPos, 1) Like “[a-zA-Z]“)
Loop
End Function