All you aspiring VBA programmer, you cannot forgo this awesome and fully tested VBA function to test if all character in a string is alphabet or alphanumeric.

A simple snippet that loops 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) aka alphabet. If all the characters in the string (strValue) are alphabet, the function returns TRUE. If the function detected anyone of the character to be a none alphabet, it exits the for loops and returns FALSE.


Function IsAllLetterAlphabets(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

We can expand this function to find the start and end of the character as well with teh following snippet.

Public Sub SplitChar(strText As String)
Dim intPos As Integer
Dim blnFirstFound As Boolean
Dim intLocStart As Integer
Dim intLocEnd As Integer
Dim strTagFirst As String, strTagLast As String, strTagMid As String

blnFirstFound = False
For intPos = 1 To Len(strText)
Select Case Asc(Mid(strText, intPos, 1))
Case 65 To 90, 97 To 122
If Not blnFirstFound Then
intLocStart = intPos
blnFirstFound = True
End If
Case Else
If blnFirstFound Then
intLocEnd = intPos
Exit For
End If
End Select
Next

strTagFirst = Mid(strText, 1, intLocStart - 1)
strTagLast = Mid(strText, intLocEnd, Len(strText) - (intLocEnd - 1))
strTagMid = Mid(strText, intLocStart, intLocEnd - intLocStart)

End Sub

Below is the output for intLocStart and intLocEnd after the function has completed processing.

Xybernetics Excel VBA - Is Character String

Reference