Visual Basic for Applications/使用 Like 运算符进行验证

概述

此 VBA 示例可以在任何常用的 Microsoft Office 应用程序中运行。本页上的示例使用 Like 运算符来比较字符串。第一个示例演示了如何检查字符串是否符合英国国民保险号码 (NINO) 的正确格式，NINO 是一个类似于美国社会安全号码 (SSN) 的号码。格式规则清晰，因此这是一个很好的示例。与本集中其他检查输入时非法字符的示例不同，此方法仅在用户完成输入后执行。

VBA 代码

代码需要一个名为 Userform1 的用户窗体、两个文本框，TextBox1 和 TextBox2，以及一个名为 CommandButton1 的命令按钮。将 UserForm1 属性 ShowModal 设置为 false 以便于学习。将下面的代码复制到三个相应的模块中，并将工作簿保存为 xlsm 文件后缀。
打开工作簿时，将显示用户窗体。在 TextBox1 中输入数字格式，完成后按 Tab 键移至下一个文本框。如果数字格式正确，则插入点会移动，否则会停留在错误的文本中等待更正。将 BeforeUpdate() 的 Cancel 参数设置为 true 可以阻止移动。
请注意，除非自上次插入点进入框以来文本发生了更改，否则 Before_Update() 事件根本不会运行。因此，强调一下，离开框后，如果用户再次在框中点击而没有进行更改，则在移至下一个框时事件不会运行。如果这带来了问题，请考虑使用 Exit 事件进行测试。
另请参阅 输入框，了解其他许多与验证相关的过程。

代码更改

到目前为止还没有更改。

用于 ThisWorkbook 模块

Private Sub Workbook_Open()
    ' Runs when workbook opens
    
    Load UserForm1
    UserForm1.Show

End Sub

用于 UserForm1 模块

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

用于标准模块

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

另请参阅

外部链接