CHECK if tbox contain a particular string

User avatar
sal21
PlatinumLounger
Posts: 4448
Joined: 26 Apr 2010, 17:36

CHECK if tbox contain a particular string

Post by 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
Lounger
Posts: 43
Joined: 07 Jun 2023, 15:34

Re: CHECK if tbox contain a particular string

Post by 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

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: CHECK if tbox contain a particular string

Post by 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
Best wishes,
Hans

User avatar
SpeakEasy
5StarLounger
Posts: 601
Joined: 27 Jun 2021, 10:46

Re: CHECK if tbox contain a particular string

Post by 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

User avatar
sal21
PlatinumLounger
Posts: 4448
Joined: 26 Apr 2010, 17:36

Re: CHECK if tbox contain a particular string

Post by sal21 »

SpeakEasy wrote:
12 Jul 2023, 19:57
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

User avatar
SpeakEasy
5StarLounger
Posts: 601
Joined: 27 Jun 2021, 10:46

Re: CHECK if tbox contain a particular string

Post by 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.

User avatar
sal21
PlatinumLounger
Posts: 4448
Joined: 26 Apr 2010, 17:36

Re: CHECK if tbox contain a particular string

Post by sal21 »

SpeakEasy wrote:
13 Jul 2023, 12:02
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
Lounger
Posts: 43
Joined: 07 Jun 2023, 15:34

Re: CHECK if tbox contain a particular string

Post by robertocm »

SpeakEasy wrote:
12 Jul 2023, 19:57
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

User avatar
DocAElstein
5StarLounger
Posts: 651
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Wild things.... they make my brain scream

Post by 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
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(