none
How to analyse a string of characters in VBA RRS feed

  • Question

  • Hi,

    I need to know if a list of character contains only letters or if it contains a numeric part, or only numeric.

    How can I do that ?

    Thanks

    Saturday, June 14, 2014 2:34 PM

Answers

  • WLID1966,

    At first I thought it could be done with a formula in a custom field, and it probably could, but the formula would be unwieldy. Therefore I developed this little macro to do the job.

    Sub chkStrforNumLet()
    Dim t As Task
    Dim str As String
    Dim Num As Boolean, Ltr As Boolean

    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            str = t.Name
            Num = False: Ltr = False
            For i = 1 To Len(t.Name)
                If Asc(str) > 47 And Asc(str) < 58 Then Num = True
                If Asc(str) > 64 And Asc(str) < 91 Or _
                     Asc(str) > 96 And Asc(str) < 123 Then Ltr = True
                If Num = True And Ltr = True Then Exit For
                str = Mid(str, 2)
            Next i
            If Num = True And Ltr = True Then t.Text1 = "Mixed"
            If Num = True And Ltr = False Then t.Text1 = "Numbers"
            If Num = False And Ltr = True Then t.Text1 = "Letters"
        End If
    Next t
    End Sub

    If this answers your question, please mark it as the answer.

    John

    • Marked as answer by WLID1966 Saturday, June 14, 2014 3:54 PM
    Saturday, June 14, 2014 3:27 PM

All replies

  • WLID1966,

    At first I thought it could be done with a formula in a custom field, and it probably could, but the formula would be unwieldy. Therefore I developed this little macro to do the job.

    Sub chkStrforNumLet()
    Dim t As Task
    Dim str As String
    Dim Num As Boolean, Ltr As Boolean

    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            str = t.Name
            Num = False: Ltr = False
            For i = 1 To Len(t.Name)
                If Asc(str) > 47 And Asc(str) < 58 Then Num = True
                If Asc(str) > 64 And Asc(str) < 91 Or _
                     Asc(str) > 96 And Asc(str) < 123 Then Ltr = True
                If Num = True And Ltr = True Then Exit For
                str = Mid(str, 2)
            Next i
            If Num = True And Ltr = True Then t.Text1 = "Mixed"
            If Num = True And Ltr = False Then t.Text1 = "Numbers"
            If Num = False And Ltr = True Then t.Text1 = "Letters"
        End If
    Next t
    End Sub

    If this answers your question, please mark it as the answer.

    John

    • Marked as answer by WLID1966 Saturday, June 14, 2014 3:54 PM
    Saturday, June 14, 2014 3:27 PM
  • Great!

    Thanks a lot John!

    Saturday, June 14, 2014 3:55 PM
  • WLID1966,

    You're welcome and thanks for the feedback.

    John

    Saturday, June 14, 2014 4:47 PM