Visual Basic for Applications/Validate with the Like Operator
Summary
editThis VBA example can be run in any of the commonly used Microsoft Office applications. The examples on this page make use of the Like operator to compare strings. The first example shows how to check that a string conforms to the correct format of the United Kingdom's National Insurance Number (NINO), a number used in much the same way as the US Social Security Number (SSN). The rules for the format are clear so it makes a good example. Unlike examples elsewhere in this set that check for illegal characters while they are being entered, this method is carried out only when the user has completed the entry.
The VBA Code
edit- The code needs a user form called Userform1, two text boxes, TextBox1 and TextBox2, and a command button called CommandButton1. Set the UserForm1 property ShowModal to false for convenient study. Copy the code below into the three respective modules and save the workbook with an xlsm file suffix.
- When the workbook is opened, the user form will be displayed. Type a number format into TextBox1 and when complete, press the tab key to move to the next textbox. If the number format is correct then the insertion point will move, but if not it will stay in the faulty text ready for correction. Setting the Cancel argument of BeforeUpdate() to true prevents the move.
- Note that the Before_Update() event will not run at all unless a change has been made the text since the last time that the insertion point entered the box. So, to labor the point, after leaving the box, if the user clicks in it again without changes, the event does not run when moving on. If this poses a problem then consider the use of the Exit event for testing.
- See also Input Boxes for a number of other validation-related procedures.
Code Changes
editThere are no changes so far.
For the ThisWorkbook Module
editPrivate Sub Workbook_Open()
' Runs when workbook opens
Load UserForm1
UserForm1.Show
End Sub
For the UserForm1 Module
editPrivate Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
'Runs on exit from the textbox provided that changes to text were made.
'Setting Cancel to True keeps the insertion point in the text
'instead of tabbing on.
If IsNINO(TextBox1.Value) Then
'all ok
Else
Cancel = True
End If
End Sub
For the Standard Module
editSub testIsNINO()
'run this to test the IsNINO procedure
Dim sIn As String
'set nino here to test
sIn = "QQ123456A"
MsgBox IsNINO(sIn)
End Sub
Function IsNINO(sIn As String) As Boolean
' Checks format of UK National Insurance Number (NINO)
' Converts to upper case for comparison
'NOTES: Ref:National Insurance Numbers (NINOs): Format and Security:
' https://www.gov.uk/hmrc-internal-manuals/national-insurance-manual/nim39110
'A NINO is made up of two letters, six numbers and a final letter, which is always A, B, C, or D.
'D, F, I, Q, U, and V are not used as first or second letter of prefix.
'Letter O is not used as the second letter of prefix.
'Prefix combinations BG, GB, KN, NK, NT, TN and ZZ are not to be used.
'Suffix characters can be only A,B, C,or D. (Elsewhere in examples space has been included here.)
Dim bTemp As Boolean
Const s1 As String = "[ABCEGHJKLMNOPRSTWXYZ]" 'alphabet less D, F, I, Q, U, and V; pattern for the first letter
Const s2 As String = "[ABCEGHJKLMNPRSTWXYZ]" 'alphabet less D, F, I, O , Q, U, and V; pattern for the second letter
Const s3 As String = "######" 'includes only six integers; pattern for the six integers
Const s4 As String = "[ABCD]" 'includes only A, B, C, or D; pattern for the end letter
' Four parts of number to check are each in square brackets
' Right hand side of like operation concatenates
' all four pattern strings as one. Notice that the alpha patterns here make
' use of long format notation where every character permitted has been included.
' Instead, the alpha patterns could have been expressed as ranges; eg; "[ABCD]" is same as "[A-D]"
bTemp = UCase(sIn) Like s1 & s2 & s3 & s4
If bTemp Then
' Check for illegal pairs
Select Case Left$(UCase(sIn), 2)
Case "BG", "GB", "KN", "NK", "NT", "TN", "ZZ"
IsNINO = False
MsgBox "Illegal prefix pair detected."
Exit Function
Case Else
IsNINO = True
Exit Function
End Select
Else
MsgBox "Illegal characters detected."
IsNINO = False
Exit Function
End If
End Function