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

另請參閱

外部連結