VBA 如何在单元格中的用户输入上使用正则表达式 [英] VBA How to use regular expression on a user input in a cell

问题描述

我正在尝试使用正则表达式在单元格中指定用户输入.这个想法是只使用这些字符串:

I am trying to specify the user input in a cell with a regular expression. The idea is to use only these strings:

C1, C2, C3, C4, C5, C6, C7, C8, C9, C10

后跟空格，然后合并或完成边框或宽度，最后是空格，然后是从 1 到 100 的整数.

followed by blank space then merge or complete framed or width and finally followed by blank space and then an integer from 1 to 100.

例如C4合并1，C5宽2，C7完整装裱.完整框后不能有整数，每3个组合必须用逗号分隔.

For example C4 merge 1, C5 width 2, C7 complete framed. There must be no integer after complete framed and every 3 combinations must be separated with comma.

直到现在我才发现如何只使用特定的词，这是表达:^(?!\s*$)(?:merge|complete framed|width )+$.这是我正在为之工作的工作表.目前我只需要在 G 列中执行此操作:

Until now I found only how to use only specific words and this is the expression: ^(?!\s*$)(?:merge|complete framed|width )+$. Here is my sheet I am working for. Currently I need to do this in column G only:

 C6,merge,1,  C10,merge,1, C8,merge,1, C9,merge,1, C1,complete framed,1, C6,merge,1, C2,complete framed,1, C5,complete framed,1, C8,merge,1, C1,complete framed,1, C5,merge,1, C5,merge,1, C8,merge,1, C9,merge,1, C7,merge,1, C2,complete framed,1, C6,merge,1, C1,complete framed,1
C5,merge,1, C8,merge,1, C7,merge,1, C9,merge,1, C6,merge,2, C1,complete framed,2, C7,merge,1, C6,merge,1, C8,merge,1, C6,width,1, C8,width,1
C6,merge,1, C4,merge,1 .     

现在的区别是在完整框架"之后不能有整数

The difference now is after "complete framed" there must be no integer

 Sub by_blocks_regex()


 Dim strPattern As String: strPattern = "\b(C(?:10|[1-9])),(merge|complete framed|width),(\d+)"
 Dim strReplace As String: strReplace = ""
 Dim regEx As New RegExp
 Dim strInput As String
 Dim strOutput As String
 Dim Myrange As Range
 Dim currMatch As match

 Dim cell As Range
 Set Myrange = ThisWorkbook.Worksheets("BY Blocks").Range("G3:G19")
 Dim currCell As Range

 For Each currCell In Myrange
     If strPattern <> vbNullString Then
         strInput = currCell.Value
     End If
         With regEx
         .Global = True
         .MultiLine = True
         .IgnoreCase = False
         .Pattern = strPattern

     If regEx.Test(strInput) Then
     strOutput = regEx.Replace(strInput, strPattern)
                    'MsgBox ("A cell match with the pattern:   ") &  currCell
                'Else
                   'MsgBox ("No cell match with the pattern:   ") & strPattern
                End If
   End With
   Next currCell
   End Sub

我测试了正则表达式，它找到了单元格中的每个字符串，但我不知道如何在单元格输入中使用 strPattern 并分隔每个子字符串，以便只允许有效值，例如 C5 width 1, C4 merge 2. 也许必须使用正则表达式 IsMatch 方法.当用户在单元格中键入一些数据以查找每个允许的匹配项并验证输入数据时.任何人都知道应该如何做到这一点?

I tested the regular expression and it finds every string in the cells but I can`t figure out how to use the strPattern in a cell input and separate each substring so to allow only valid values like C5 width 1, C4 merge 2. Maybe regex IsMatch method must be used. When the user types some data in the cell to find every allowed match and validate the input data. Anyone with an idea how this should be done?

推荐答案

此代码将在您更新单元格时检查每组三个组合.将代码粘贴到工作表而不是模块中.它假定您的文本遵循以下规则:

This code will check each set of three combinations when you update a cell. Paste the code in the worksheet rather than a module. It assumes your text follows the rule of:

每3个组合必须用逗号分隔

every 3 combinations must be separated with comma

例如，字符串C6 merge 1, C4 merge 1 将检查C6 merge 1 和C4 merge 1.它忽略逗号和前导/尾随空格.

For example, the string C6 merge 1, C4 merge 1 will have C6 merge 1 and C4 merge 1 checked. It ignores the comma and leading/trailing spaces.

注意:我没有检查 RegExp 模式，因为这不是我的强项，但它会在您在 G 列中输入数据时进行检查.也许可以发布一个单独的问题以获得正确的 Reg Exp 语法?

Note: I haven't checked the RegExp pattern as that's not my forte, but it checks as you enter the data in column G. Maybe post a separate question to get the correct Reg Exp syntax?

您可能想要创建具有工作表作用域的 RegEx 变量，该变量在工作表被激活时被初始化并在您离开工作表时被销毁(省去每次更新单元格时都必须执行的操作).

You may want to create the RegEx variable with a worksheet scope that gets initialised when the sheet is activated and destroyed when you leave the sheet (save having to do it each time you update a cell).

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim strPattern As String
    Dim regEx As RegExp
    Dim vValues As Variant
    Dim vValue As Variant

    If Not Intersect(Target, Range("G:G")) Is Nothing Then
        strPattern = "\b(C(?:10|[1-9])),(merge|complete framed|width),(\d+)"
        Set regEx = New RegExp

        vValues = Split(Target, ",")

        With regEx
            For Each vValue In vValues
                '.Global = True 'Not needed as possible matches are passed one at a time using Split.
                '.MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern

                If .Test(Trim(vValue)) Then
                    MsgBox "Match found in " & Target.Value & " : " & Trim(vValue)
                Else
                    MsgBox "No match"
                End If
            Next vValue
        End With
    End If  

    Set regEx = Nothing

End Sub

这篇关于VBA 如何在单元格中的用户输入上使用正则表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！