Is that a letter?

In IsLetter Function for VBA, Outlook MVP David Lee posts a function for checking if a given string is a letter.

I like it because it actually checks if the entire string, regardless of its length, is comprised of letters. It doesn't hardcode the string length — the loop makes the length of the string superficial. If there is only one character, it simply loops once.

It uses the Asc function to determine the character code for each character in the string, taking advantage of the fact that the letters A-Z and a-z are consecutive. You can see this if you use the character set workbook I use.

One thing it does that I don't like is it explicitly sets the return value to
False

, and it repeatedly sets the return value to
True

. A better workflow would be to assume that the input string contains only letters (IsLetter =
True

), then set IsLetter to
False

(and immediately exit) if we encounter a non-letter (is that a word?). This way, we only set IsLetter to
True

or
False

once, instead of wasting processor time setting it to
True

N times.

Function IsLetter(strValue As String) As Boolean
  Dim intPos As Integer
  ' assume True
  IsLetter = True

  For intPos = 1 To Len(strValue)
    Select Case Asc(Mid(strValue, intPos, 1))
    Case 65 To 90, 97 To 122

    Case Else
      ' not a letter, return False
      IsLetter = False
      Exit For
    End Select
  Next intPos
End Function

In a comment on that article I posted two additional ways to check if a given string contains only letters. They still loop through each character of the input string, but they use the Like Operator instead of comparing each character to character codes.

RegExp to the Rescue

We can also use Regular Expressions to check if a string contains only letters. This would mean only one pass through a string, instead of looping!

We need two functions from the Regular Expressions page: GetRegEx and TestRegex. Once you copy those functions to a standard module, the following function may be used to check if a given string consists only of letters a-z (case insensitive):

Function IsAlpha(stringToCheck As String) As Boolean
  Dim regex As Object ' VBScript.RegExp
  Set regex = GetRegEx
  If Not regex Is Nothing Then
    IsAlpha = TestRegex(regex, "^[a-zA-Z]+$", stringToCheck)
  End If
End Function

"^[a-zA-Z]+$" roughly translates to from the start of the string, match any number/combination of upper or lower case letters until the end of the string.

RegEx vs. Loop

How does the RegEx perform against a loop?

I used the following extremely un-scientific procedure to test the relative speeds of the IsLetter (loop) and IsAlpha (RegEx) functions.

Sub TestRegexLoop()

  Dim i As Long
  Dim startTimeRegex As Single
  Dim endTimeRegex As Single
  Dim startTimeLoop As Single
  Dim endTimeLoop As Single
  Dim msg As String
  Dim is_a_letter As Boolean

  Const numberOfLoops As Long = 1000
  Const stringToCheck As String = _
    "Thequickbrownfoxjumpsoverthelazydog"

  ' use Regex
  startTimeRegex = Timer
  For i = 1 To numberOfLoops
    is_a_letter = IsAlpha(stringToCheck)
  Next i
  endTimeRegex = Timer

  ' use Loop
  startTimeLoop = Timer
  For i = 1 To numberOfLoops
    is_a_letter = IsLetter(stringToCheck)
  Next i
  endTimeLoop = Timer

  msg = "Number of iterations: " & numberOfLoops & vbCrLf
  msg = msg & "Using Regex: " & _
    Format(endTimeRegex - startTimeRegex, "#.###") & " seconds" & vbCrLf
  msg = msg & "Using Loop: " & _
    Format(endTimeLoop - startTimeLoop, "#.###") & " seconds" & vbCrLf
  MsgBox msg
End Sub

Results

Let's see how both functions performed. At just 1,000 iterations, RegEx is already much slower than a loop.

At 10,000 iterations, this is a Mike Tyson fight. The function that uses a loop is barely breaking a sweat, while RegEx is clocking in at 14 seconds. I quit.

RegEx vs. Loop Rematch

We're not being fair. After all, IsAlpha had to instantiate the RegExp object every time it is called. That has to account for some of the slowdown. The original IsAlpha is a function trying to do too much. So we alter the IsAlpha function to take in an existing RegExp object…

Function IsAlpha(regex As Object, stringToCheck As String) As Boolean
  IsAlpha = TestRegex(regex, "^[a-zA-Z]+$", stringToCheck)
End Function

…and we create the RegExp object in our test procedure and pass it to IsAlpha:

Sub TestRegexLoop2()

  Dim i As Long
  Dim startTimeRegex As Single
  Dim endTimeRegex As Single
  Dim startTimeLoop As Single
  Dim endTimeLoop As Single
  Dim msg As String
  Dim is_a_letter As Boolean
  Dim regex As Object  ' RegExp

  Const numberOfLoops As Long = 1000
  Const stringToCheck As String = "Thequickbrownfoxjumpsoverthelazydog"

  ' use Regex
  startTimeRegex = Timer

  Set regex = GetRegEx
  If Not regex Is Nothing Then
    For i = 1 To numberOfLoops
      is_a_letter = IsAlpha(regex, stringToCheck)
    Next i
  End If
  endTimeRegex = Timer

  ' use Loop
  startTimeLoop = Timer
  For i = 1 To numberOfLoops
    is_a_letter = IsLetter(stringToCheck)
  Next i
  endTimeLoop = Timer

  msg = "Number of iterations: " & numberOfLoops & vbCrLf
  msg = msg & "Using Regex: " & _
    Format(endTimeRegex - startTimeRegex, "#.###") & " seconds" & vbCrLf
  msg = msg & "Using Loop: " & _
    Format(endTimeLoop - startTimeLoop, "#.###") & " seconds" & vbCrLf
  MsgBox msg
End Sub

I have a feeling this is going to be more of a fair fight.

Results

That's more like it! Even passing the RegExp object between two functions has barely slowed it down.

At 10,000 iterations the RegEx function is only slightly slower. In my humble non-scientific opinion, it is statistically insignificant.

Even at 100,000 iterations they are both taking nearly 1 second to complete.

Conclusion

Even though RegExp can evaluate the entire string in one go, the loop was still faster. Refactoring the IsAlpha function to create the RegExp object only once, however, and passing it to another function, closed the gap. I like RegEx but don't have a strong preference, so when I use it I will keep in mind that the RegExp object should be created in a parent procedure and passed to a child function for it to use RegExp parsing methods.