Visual Basic for Applications/Validate with the Like Operator
This 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
- 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.
There are no changes so far.
For the ThisWorkbook Module
Private Sub Workbook_Open() ' Runs when workbook opens Load UserForm1 UserForm1.Show End Sub
For the UserForm1 Module
Private 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
Sub 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