如何用VBA正则表达式验证Excel单元格指定格式的用户输入

Let's fix this step by step. First, let's clarify your validation requirements to make sure we're aligned:

• Each entry must start with C1-C10 (e.g., C3, C10), followed by a space
• Next, one of three valid options:
  • merge + space + an integer between 1-100
  • complete framed (no numbers allowed after this phrase)
  • width + space + an integer between 1-100
• Multiple valid entries are separated by , (comma + space)
• Empty or whitespace-only input is not allowed

Your current regex doesn't match these rules, and the VBA logic isn't set up for real-time input validation. Let's correct both:

1. Correct Regular Expression #

Here's the regex that fully enforces your requirements:

^(?!\s*$)(?:C(?:10|[1-9]) (?:(?:merge|width) (?:[1-9]\d?|100)|complete framed)(?:, C(?:10|[1-9]) (?:(?:merge|width) (?:[1-9]\d?|100)|complete framed))*)$

Breakdown of key components:

• (?!\s*$): Ensures the input isn't empty or just whitespace
• C(?:10|[1-9]): Matches valid C identifiers (C1-C10, no leading zeros)
• (?:merge|width) (?:[1-9]\d?|100): Validates merge/width followed by 1-100 (covers 1-99 with [1-9]\d?, plus explicit 100)
• complete framed: Matches this exact phrase with no trailing numbers
• (?:, C(...))*: Allows multiple valid entries separated by ,

2. VBA Implementation #

We'll set up two parts: real-time validation (triggers when a user edits a cell) and a batch validation macro (to check all cells at once).

First: Enable Regular Expressions

You have two options:

• Early binding: Go to Tools > References in the VBA editor, check Microsoft VBScript Regular Expressions 5.5
• Late binding (no reference needed): Replace New RegExp with CreateObject("VBScript.RegExp")

Real-Time Validation (Worksheet_Change Event)

Open the code module for your "BY Blocks" worksheet, and paste this code to validate input as users type:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim regEx As Object
    Dim validPattern As String
    Dim cell As Range
    Dim oldValue As Variant
    
    ' Target only G3:G19
    Set regEx = CreateObject("VBScript.RegExp")
    validPattern = "^(?!\s*$)(?:C(?:10|[1-9]) (?:(?:merge|width) (?:[1-9]\d?|100)|complete framed)(?:, C(?:10|[1-9]) (?:(?:merge|width) (?:[1-9]\d?|100)|complete framed))*)$"
    
    With regEx
        .Global = False ' Match the entire string, not just parts
        .IgnoreCase = False
        .Pattern = validPattern
    End With
    
    ' Handle multiple edited cells
    For Each cell In Target
        If Not Intersect(cell, Me.Range("G3:G19")) Is Nothing Then
            Application.EnableEvents = False ' Prevent infinite loop when reverting value
            oldValue = cell.Value
            
            ' Check validity if cell isn't empty
            If cell.Value <> vbNullString Then
                If Not regEx.Test(cell.Value) Then
                    MsgBox "Invalid input in cell " & cell.Address & vbCrLf & _
                           "Valid examples:" & vbCrLf & _
                           "- C5 merge 42" & vbCrLf & _
                           "- C10 complete framed" & vbCrLf & _
                           "- C3 width 100, C7 merge 5", vbExclamation, "Invalid Input"
                    cell.Value = oldValue ' Revert to previous valid value
                End If
            End If
            
            Application.EnableEvents = True
        End If
    Next cell
End Sub

Batch Validation Macro

Use this to check all cells in G3:G19 in one go:

Sub ValidateBYBlocks()
    Dim regEx As Object
    Dim validPattern As String
    Dim cell As Range
    Dim invalidCells As String
    
    Set regEx = CreateObject("VBScript.RegExp")
    validPattern = "^(?!\s*$)(?:C(?:10|[1-9]) (?:(?:merge|width) (?:[1-9]\d?|100)|complete framed)(?:, C(?:10|[1-9]) (?:(?:merge|width) (?:[1-9]\d?|100)|complete framed))*)$"
    
    With regEx
        .Global = False
        .IgnoreCase = False
        .Pattern = validPattern
    End With
    
    ' Scan each target cell
    For Each cell In ThisWorkbook.Worksheets("BY Blocks").Range("G3:G19")
        If cell.Value <> vbNullString Then
            If Not regEx.Test(cell.Value) Then
                invalidCells = invalidCells & cell.Address & vbCrLf
            End If
        End If
    Next cell
    
    ' Show validation results
    If invalidCells <> vbNullString Then
        MsgBox "Invalid input found in cells:" & vbCrLf & invalidCells, vbExclamation, "Validation Results"
    Else
        MsgBox "All cells in G3:G19 are valid!", vbInformation, "Validation Results"
    End If
End Sub

Key Fixes & Notes #

• Real-time checks: The Worksheet_Change event catches edits immediately and warns users, with an option to revert invalid input
• Regex accuracy: Ensures the entire input string matches your rules (not just partial matches)
• Error prevention: Application.EnableEvents = False stops the change event from triggering itself when we revert a cell's value
• User guidance: The error message includes concrete valid examples to help users input correctly

内容的提问来源于stack exchange，提问作者Hris