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.
[sourcecode language="csharp"] 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 [/sourcecode]
We can expand this function to find the start and end of the character as well with teh following snippet.
[sourcecode language="csharp"] 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 [/sourcecode]
Below is the output for intLocStart and intLocEnd after the function has completed processing.
Reference