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