VBA isString vs isNumeric

Question

I am validating data that is in some textboxes in a VB userform in Excel.

The data can either be a number that is 6-digits long, a string that is at least 3 characters long, or a combination of string and digits.

To do this I wrote:

If Len(Trim(Me.TextBox1)) = 6 And IsNumeric(Trim(Me.TextBox1)) Then
  (do operation)
Elseif Len(Trim(Me.TextBox1)) > 2 and IsString(Trim(Me.TextBox1)) Then
  (do another operation)
Else
  (do third operation)
End if

I can make it work with isNumeric, but it seems VBA does not support isString.

Is there a clever work around to solve this issue?

Answer 1

You can call the worksheet function

Application.WorksheetFunction.IsText

I would probably feel more comfortable walking the string though and testing with AscW and if expecting A-Za-z then look for values in the ranges 65-90 and 95-122.

You can definitely improve on the following. You can refer to the ascii codes to determine the acceptable values that constitute text for you. Note also that there are a ton of functions out there on the web for performing exactly this task.

 Public Sub test()
    Dim s As String, i As Long
    s = "pr81"
    For i = 1 To Len(s)                          ' 65-90 and 95-122.
        Select Case True
        Case (AscW(Mid$(s, i, 1)) >= 65 And AscW(Mid$(s, i, 1)) <= 90) Or _
             (AscW(Mid$(s, i, 1)) >= 95 And AscW(Mid$(s, i, 1)) <= 122)
        Case Else
            MsgBox "Values are not all in range A-Za-z"
            Exit For
        End Select
    Next i
End Sub

---

Thanks to @DirkReichel here is a much simplified test:

Option Explicit
Public Sub test()
    Dim s As String, i As Long
    s = "pr81"
    For i = 1 To Len(s)
        If LCase(Mid$(s, i, 1)) = UCase(Mid$(s, i, 1)) Then
            MsgBox "Not all letters"
            Exit For
        End If
    Next i
End Sub