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
CHECK if tbox contain a particular string
-
- PlatinumLounger
- Posts: 4572
- Joined: 26 Apr 2010, 17:36
-
- Lounger
- Posts: 43
- Joined: 07 Jun 2023, 15:34
Re: CHECK if tbox contain a particular string
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
-
- Administrator
- Posts: 80032
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: CHECK if tbox contain a particular string
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
Hans
-
- 5StarLounger
- Posts: 737
- Joined: 27 Jun 2021, 10:46
Re: CHECK if tbox contain a particular string
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
-
- PlatinumLounger
- Posts: 4572
- Joined: 26 Apr 2010, 17:36
Re: CHECK if tbox contain a particular string
Good!SpeakEasy wrote: ↑12 Jul 2023, 19:57Just 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
Tks
-
- 5StarLounger
- Posts: 737
- Joined: 27 Jun 2021, 10:46
Re: CHECK if tbox contain a particular string
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.
-
- PlatinumLounger
- Posts: 4572
- Joined: 26 Apr 2010, 17:36
-
- Lounger
- Posts: 43
- Joined: 07 Jun 2023, 15:34
Re: CHECK if tbox contain a particular string
These are some tests just for learning, not directly related with the opening question:SpeakEasy wrote: ↑12 Jul 2023, 19:57Just 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
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
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:
-
- 5StarLounger
- Posts: 761
- Joined: 18 Jan 2022, 15:59
- Location: An Englishman, illegally re-routing rivers, in Hof, Beautiful Bavaria. Rule, Britannia!
Wild things.... they make my brain scream
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
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
Regards , Ālan , DocÆlstein
, 

