[RESOLVED] is there a method like "isAlphabetic"

[bobk544]

Hello,

I see where VBA has a method or property called "isNumeric" but i can't seem to find a similar version for the characters a to z, ie something like "isAlphbetic" or "isCharacter" ect?

Thanks for any pointers!
BobK

[Mitch_s_s]

wouldn't this work for you?

Not isNumeric

[bobk544]

yes that would work for the most part, but i think i'd also be pickup control characters and so on maybe?

actually, i was hoping there would be something that would zero in on the alphabet, but i'm going to try (not IsNumeric) and see what happens!

thanks for your response on this!
bobk

[opus]

Isnumeric is working on a Variant, I guess you need to check each character, if that's correct remember that the ASCII codes for alphabets are all within a defined range, use that to check if each character is alphabetical.

[Siddharth Rout]

Hi

I am not sure if there is any such function or not but YES! you can create one...

Paste this in as module.

Function isalpha(str As String) As Boolean
 
Dim Flag As Boolean
Flag = True
 
For i = 1 To Len(Trim(str))
 
    If Asc(Mid(Trim(str), i, 1)) > 64 And Asc(Mid(Trim(str), i, 1)) < 90 Or _
    Asc(Mid(Trim(str), i, 1)) > 96 And Asc(Mid(Trim(str), i, 1)) < 123 Then
 
    Else
        Flag = False
    End If
Next i
 
If Flag = True Then
    isalpha = True
Else
    isalpha = False
End If
 
End Function

In an excel sheet use the function as =isalpha(A1). Where A1 is the cell where you enter the word you want to test.

If the word you entered contains all letters then the above function will return true. If even 1 letter is numeric then the above function will return false...

Hope this helps...

[bobk544]

Thanks all, loooks good appreciate the excellent! help!

BobK

[Mitch_s_s]

koolsid knows too much lol

[Siddharth Rout]

Thanks all, loooks good appreciate the excellent! help!

Glad to be of help

koolsid knows too much lol

Thanks for the compliment but wish I knew 'that' much

[Mitch_s_s]

how did you get you know as much as you do?
i'm just startin out really, playing around etc
bought john walkenbachs new book ,been told its quite good!

[Mitch_s_s]

lol that'll do me

[VBAhack]

bought john walkenbachs new book ,been told its quite good!

I have his Excel 2000 edition and it is falling apart from constant use! As a 2nd book, I've found VBA Developer's Handbook by Getz to be a great follow-on....

[opus]

The function could be shortened like this:

      Function isalpha(str As String) As Boolean
      isalpha = True
      For i = 1 To Len(Trim(str))
          If Asc(Mid(Trim(str), i, 1)) > 64 And Asc(Mid(Trim(str), i, 1)) < 90 Or _
          Asc(Mid(Trim(str), i, 1)) > 96 And Asc(Mid(Trim(str), i, 1)) < 123 Then
          Else
              isalpha = False
              Exit Function
          End If
      Next i
      End Function

And note that in other languages there will be more alphabetical characters (like "ä" [if this one shows on your computer]).

[VBAhack]

The function could be shortened like this:

Or this:

Function isalpha(str As String) As Boolean
    Dim i As Integer, k As Integer
    isalpha = True
    For i = 1 To Len(Trim(str))
        k = Asc(Mid(Trim(str), i, 1))
        If k > 64 And k < 90 Or k > 96 And k < 123 Then
        Else
            isalpha = False
            Exit Function
        End If
    Next i
End Function

[bobk544]

Thanks again all, super help!

BobK

[si_the_geek]

When you are comparing the same value multiple times in an If statement (or multiple If statements), a Select Case is more efficient..

Function isalpha(str As String) As Boolean
Dim i As Integer
  isalpha = True
  For i = 1 To Len(Trim(str))
    Select Case Mid$(Trim(str), i, 1)
    Case "A" to "Z", "a" to "z"
    Case Else
      isalpha = False
      Exit For
    End Select
  Next i
End Function

..using one less function (Asc) makes it more efficient too, and a bit more readable.

[VBAhack]

Nice one Si!

[Mitch_s_s]

I have his Excel 2000 edition and it is falling apart from constant use! As a 2nd book, I've found VBA Developer's Handbood by Getz to be a great follow-on....

thanks for that, i will look into once i've got stuck into this one!!

Cheers!