CHECK if tbox contain a particular string

[sal21]

with a button1 click i need to check if tbox1 contain this format of string

A123
A675
B221
R789
Z233
ECC...

in effect i need to check if the first char in tbox1 is a letter and the rest of string have only tree number

NOTE:
max lenght of string is 4

[robertocm]

Code: Select all

Private Sub CommandButton1_Click()
Dim s As String, a() As String, b As Boolean
s = Me.TextBox1.Value
If InStr(s, vbCrLf) Then
    b = True
    a = Split(s, vbCrLf)
    For i = 0 To UBound(a) - 1
        s = a(i)
        If Not Len(s) = 4 Or IsNumeric(Left(s, 1)) Or Not IsNumeric(Right(s, 3)) Then b = False
        'Debug.Print s & vbTab & b
    Next i
Else
    If Len(s) = 4 And Not IsNumeric(Left(s, 1)) And IsNumeric(Right(s, 3)) Then b = True
End If
MsgBox b
End Sub

[HansV]

Or

Code: Select all

Private Sub Button1_Click()
    If Me,TBox1.Text Like "[A-Z][0-9][0-9][0-9]" Then
        MsgBox "OK"
    Else
        MsgBox "Not OK"
    End If
End Sub

[SpeakEasy]

Just for fun:

Code: Select all

Public Function CheckText(strSource As String) As Boolean
    With CreateObject("vbscript.regexp")
        .Pattern = "^[A-Z]\d{3}$"
        CheckText = .Test(strSource)
    End With
End Function

[sal21]

Just for fun:

Code: Select all

Public Function CheckText(strSource As String) As Boolean
    With CreateObject("vbscript.regexp")
        .Pattern = "^[A-Z]\d{3}$"
        CheckText = .Test(strSource)
    End With
End Function

Good!
Tks

[SpeakEasy]

Just to be clear, my suggestion is pretty much the exact same thing as HansV's, only using 'proper' regular expressions rather than the VBA LIKE operator's somewhat more limited implementation.

[sal21]

Just to be clear, my suggestion is pretty much the exact same thing as HansV's, only using 'proper' regular expressions rather than the VBA LIKE operator's somewhat more limited implementation.

OK, tks.

[robertocm]

Just for fun:

Code: Select all

Public Function CheckText(strSource As String) As Boolean
    With CreateObject("vbscript.regexp")
        .Pattern = "^[A-Z]\d{3}$"
        CheckText = .Test(strSource)
    End With
End Function

These are some tests just for learning, not directly related with the opening question:

Code: Select all

Sub test()
Dim ArStr() As String, s As String, s_p As String, i As Integer
s = "He is ""Best Friend"" of my family. This is a ""Second example"" and something else"
s_p = """.+?"""

ArStr = RegExpFind(s, s_p)
For i = 0 To UBound(ArStr)
    Debug.Print ArStr(i)
Next i
End Sub

Public Function RegExpFind(strSource As String, PatternStr As String) As String()
Dim ArStr() As String, TheMatches As Object, i As Integer

With CreateObject("vbscript.regexp")
    .Pattern = PatternStr
    .Global = True
    .IgnoreCase = Not MatchCase
    .MultiLine = MultiLine

    If .test(strSource) Then
        'Run RegExp to get the matches, which are returned as a zero-based collection
        Set TheMatches = .Execute(strSource)
        ReDim ArStr(0 To TheMatches.Count - 1)
        For i = 0 To UBound(ArStr)
            ArStr(i) = CStr(TheMatches(i))
        Next
        RegExpFind = ArStr
    Else
        'https://stackoverflow.com/questions/35221148/return-an-empty-string-array-in-a-vba-function
        'https://stackoverflow.com/questions/43005454/vba-create-empty-array
        ReDim RegExpFind(0) As String
    End If
End With
End Function

Sub Another_Test()
Dim s As String, a() As String
s = "A123" & vbCrLf & "A675"
a = Split(s, vbCrLf)
'Debug.Print UBound(a)
ReDim a(0) As String
'https://stackoverflow.com/questions/36495054/how-to-know-a-string-array-is-empty-in-vba
'https://stackoverflow.com/questions/26290781/check-if-array-is-empty-vba-excel
If (Not a) = -1 Then
    Debug.Print "Array has been initialized"
End If
End Sub

References:
Patrick Matthews (2011-01-08), Using Regular Expressions in Visual Basic for Applications and Visual Basic 6
https://stackoverflow.com/questions/225 ... -and-loops?
https://stackoverflow.com/questions/206 ... ith-quotes
https://stackoverflow.com/questions/356 ... -of-a-cell
https://stackoverflow.com/questions/546 ... s-with-vba
https://www.vbforums.com/showthread.php ... ation-mark

My workbook with code errors related with RegExp:

C++_workbench.xlsm

[DocAElstein]

Hi

Here is a few Eileen’s lounge posts that have some good links and explanations on these "wild reg expressions things“
http://www.eileenslounge.com/viewtopic. ... 44#p175744
http://www.eileenslounge.com/viewtopic. ... 22#p202122
http://www.eileenslounge.com/viewtopic. ... 22#p202322
http://www.eileenslounge.com/viewtopic. ... 73#p276673
http://www.eileenslounge.com/viewtopic. ... 48#p299548
http://www.eileenslounge.com/viewtopic. ... 43#p299643

Good references for these sort of string manipulations are often associated with Microsoft Word. That makes sense I suppose, as MS Excel is more to do with boxes and numbers than words, and MS Word is often more to do with words than anything else.
( I have also noticed that older documentation on visual basic to do with strings and string manipulations is often useful when looking for help with sting manipulations in VBA )



Alan