Проверка содержимого ячейки Excel с использованием кода VBA

Question

Я ищу решение, чтобы проверить и выделить мою ячейку в случае ложного.

Я попробовал самое многообещающее решение: Regex. Но все равно не могу найти нужный мне шаблон.

Моя последняя попытка была такой: "[AZ-0-9_.]". Это работает, только если ячейка содержит только символ и ничего больше, если символ является частью строки, он не работает.

Проблема в том, что он не перехватывает ячейки с нечетным символом в строке текста: Пример C4UNIT| или B$GROUP.

Ячейка спецификации может содержать только заглавные буквы и два допустимых символа - тире и подчеркивание _

Это мой полный код:

Function ValidateCellContent()

    Sheets("MTO DATA").Select

    Dim RangeToCheck As Range
    Dim CellinRangeToCheck As Range
    Dim CollNumberFirst As Integer
    Dim CollNumberLast As Integer
    Dim RowNumberFirst As Integer
    Dim RowNumberLast As Integer

    '--Start on Column "1" and Row "3"
    CollNumberFirst = 1
    RowNumberFirst = 3

    '--Find last Column used on row "2" (Write OMI Headings)
    CollNumberLast = Cells(2, Columns.count).End(xlToLeft).Column
    RowNumberLast = Cells(Rows.count, 1).End(xlUp).Row

    '--Set value of the used range of cell addresses like: "A3:K85"
    Set RangeToCheck = Range(Chr(64 + CollNumberFirst) & RowNumberFirst & ":" & Chr(64 + CollNumberLast) & RowNumberLast)
    Debug.Print "Cells used in active Range = " & (Chr(64 + CollNumberFirst) & RowNumberFirst & ":" & Chr(64 + CollNumberLast) & RowNumberLast)

    For Each CellinRangeToCheck In RangeToCheck
        Debug.Print "CellinRangeToCheck value = " & CellinRangeToCheck
        If Len(CellinRangeToCheck.Text) > 0 Then

            '--Non Printables (Space,Line Feed,Carriage Return)
            If InStr(CellinRangeToCheck, " ") _
                                        Or InStr(CellinRangeToCheck, Chr(10)) > 0 _
                                        Or InStr(CellinRangeToCheck, Chr(13)) > 0 Then
                CellinRangeToCheck.Font.Color = vbRed
                CellinRangeToCheck.Font.Bold = True

                '--Allowed Characters
            ElseIf Not CellinRangeToCheck.Text Like "*[A-Z-0-9_.]*" Then
                CellinRangeToCheck.Font.Color = vbRed
                CellinRangeToCheck.Font.Bold = True

            Else
                CellinRangeToCheck.Font.Color = vbBlack
                CellinRangeToCheck.Font.Bold = False

            End If
        End If

    Next CellinRangeToCheck

End Function

Answer 1

Я изначально предполагал, что вы хотите использовать RegEx для решения вашей проблемы. Согласно вашему комментарию вы вместо этого, кажется, используете Like оператор.

---

Likeоператор

В то время как Like принимает диапазоны символов, которые могут напоминать регулярные выражения, есть много различий и мало сходств между ними:

• Like использования ! отрицать диапазон символов вместо ^ используется в RegEx.
• Like не позволяет / знает квантификаторы после закрывающей скобки ] и, таким образом, всегда соответствует одному символу на пару скобок [], Чтобы сопоставить несколько символов, вам нужно добавить несколько копий скобок диапазона символов.
• Like не понимает продвинутых понятий, таких как захват групп или взгляд в будущее / взгляд назад
• наверное больше различий...

Недоступность квантификаторов листьев Like в действительно плохом месте для вашей проблемы. У вас всегда должен быть один диапазон символов для сравнения для каждого символа в тексте вашей ячейки. Таким образом, единственный способ использовать Like Оператор будет выглядеть следующим образом:

Private Function IsTextValid(ByVal stringToValidate As String) As Boolean

    Dim CharValidationPattern As String
    CharValidationPattern = "[A-Z0-9._-]"

    Dim StringValidationPattern As String
    StringValidationPattern = RepeatString(CharValidationPattern, Len(stringToValidate))

    IsTextValid = stringToValidate Like StringValidationPattern

End Function

Private Function RepeatString(ByVal stringToRepeat As String, ByVal repetitions As Long) As String

    Dim Result As String
    Dim i As Long
    For i = 1 To repetitions
        Result = Result & stringToRepeat
    Next i
    RepeatString = Result

End Function

Затем вы можете передать текст, который вы хотите проверить IsTextValid как это:

If IsTextValid("A.ASDZ-054_93") Then Debug.Print "Hurray, it's valid!" 

Согласно вашему комментарию, небольшой Worksheet_Change событие, чтобы поместить в модуль рабочего листа вашего соответствующего рабочего листа. (Вам также нужно будет разместить две вышеупомянутые функции там. В качестве альтернативы вы можете сделать их общедоступными и поместить их в стандартный модуль.):

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim ValidationRange As Range
    Set ValidationRange = Me.Range("A2:D5")
    Dim TargetCell As Range

    For Each TargetCell In Target.Cells

        ' Only work on cells falling into the ValidationRange
        If Not Intersect(TargetCell, ValidationRange) Is Nothing Then

            If IsTextValid(TargetCell.Text) Then
                TargetCell.Font.Color = vbBlack
                TargetCell.Font.Bold = False
            Else
                TargetCell.Font.Color = vbRed
                TargetCell.Font.Bold = True
            End If

        End If

    Next TargetCell

End Sub

---

Регулярные выражения

Если вы хотите продолжить путь по RegEx, попробуйте это выражение:

[^A-Z0-9_-]+

Он будет генерировать совпадение всякий раз, когда переданная строка содержит один или несколько символов, которые вам не нужны. Все ячейки, содержащие только допустимые символы, не должны возвращать совпадения.

Объяснение:
A-Z будет соответствовать всем заглавным буквам,
0-9 будет соответствовать всем числам,
_- будет соответствовать символам подчеркивания и тире.
Предыдущий ^ будет отменять весь набор символов, что означает, что RegEx соответствует только символам, не входящим в набор.
Следующие + сообщает механизму RegEx о соответствии одному или нескольким символам из вышеупомянутого набора. Вы хотите сопоставить свои данные, только если там есть хотя бы один недопустимый символ. И если их больше одного, они все равно должны совпадать.

После этого адаптировать систему к изменяющимся требованиям (различные символы считаются законными) так же просто, как переключать несколько символов в [скобках].

Смотрите живой пример онлайн.

Answer 2

Попробуй это:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
'we want only validate when cell content changed, if whole range is involved (i.e. more than 1 cell) then exit sub
If Target.Cells.Count > 1 Then Exit Sub
'if there is error in a cell, also color it red
If IsError(Target) Then
    Target.Interior.ColorIndex = 3
    Exit Sub
End If
'validate cell with our function, if cell content is valid, it'll return True
'if it i    s not valid, then color cell red
If Not ValidateText(Target.Value) Then
    Target.Interior.ColorIndex = 3
End If
End Sub

Function ValidateText(ByVal txt As String) As Boolean
Dim i As Long, char As String
'loop through all characters in string
For i = 1 To Len(txt)
    char = Mid(txt, i, 1)
    If Not ((Asc(char) >= 65 And Asc(char) <= 90) Or char = "-" Or char = "_") Then
        'once we come upon invalid character, we can finish the function with False result
        ValidateText = False
        Exit Function
    End If
Next

ValidateText = True
End Function