Excel - тактика для сложной проверки

Вот что я придумал (все Excel 2003)

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

• StartDefs... Enums
• CommonFunctions... функции, используемые все листы
• Sheet _ X _ Functions... функции в частности, для одного листа
• и триггеров событий в самом Sheet_X

Перечисления служат исключительно для того, чтобы избежать жесткого кода; Если я хочу добавить или удалить столбцы, я в основном редактирую перечисления, в то время как в реальном коде я использую символические имена для каждого столбца. Это может звучать немного изощренно, но я научился любить его, когда пользователи пришли в третий раз и попросили меня изменить макеты таблиц.

' module GlobalDefs
Public Enum T_Sheet_X
    NofHRows = 3    ' number of header rows
    NofCols = 36    ' number of columns
    MaxData = 203   ' last row validated
    GroupNo = 1     ' symbolic name of 1st column
    CtyCode = 2     ' ...
    Country = 3
    MRegion = 4
    PRegion = 5
    City = 6
    SiteType = 7
    ' etc
End Enum

Сначала я описываю код, инициируемый событием.

В этом потоке предлагались действия PASTE. На самом деле не поддерживается триггером события в Excel-2003, но, наконец, не большое чудо. Треппинг/Отмена треппинга PASTE происходит при активации/деактивации событий в Sheet_X. При деактивизации также проверяется состояние резервирования. В случае отсутствия защиты я прошу пользователя согласиться на пакетную проверку и повторную защиту. Затем процедуры проверки одной строки и пакетной проверки являются объектами кода в модуле Sheet_X_Functions описаны ниже.

' object in Sheet_X
Private Sub Worksheet_Activate()
' suspend PASTE
    Application.CommandBars("Edit").Controls("Paste").OnAction = "TrappedPaste" ' main menu
    Application.CommandBars("Edit").Controls("Paste Special...").OnAction = "TrappedPaste" ' main menu
    Application.CommandBars("Cell").Controls("Paste").OnAction = "TrappedPaste" ' context menu
    Application.CommandBars("Cell").Controls("Paste Special...").OnAction = "TrappedPaste" ' context menu
    Application.OnKey "^v", "TrappedPaste" ' key shortcut
End Sub

' object in Sheet_X
Private Sub Worksheet_Deactivate()
' checks protection state, performs batch validation if agreed by user, and restores normal PASTE behaviour
' writes a red reminder into cell A4 if sheet is left unvalidated/unprotected
Dim RetVal As Integer
    If Not Me.ProtectContents Then
        RetVal = MsgBox("Protection is currently turned off; sheet may contain inconsistent data" & vbCrLf & vbCrLf & _
                        "Press OK to validate sheet and protect" & vbCrLf & _
                        "Press CANCEL to continue at your own risk without protection and validation", vbExclamation + vbOKCancel, "Validation")
        If RetVal = vbOK Then
            ' silent batch validation
            Application.ScreenUpdating = False
            Sheet_X_BatchValidate Me
            Application.ScreenUpdating = True
            Me.Cells(1, 4) = ""
            Me.Cells(1, 4).Interior.ColorIndex = xlColorIndexNone
            SetProtectionMode Me, True
        Else
            Me.Cells(1, 4) = "unvalidated"
            Me.Cells(1, 4).Interior.ColorIndex = 3 ' red
        End If
    ElseIf Me.Cells(1, 4) = "unvalidated" Then
        ' silent batch validation  ... user manually turned back protection
        SetProtectionMode Me, False
        Application.ScreenUpdating = False
        Sheet_X_BatchValidate Me
        Application.ScreenUpdating = True
        Me.Cells(1, 4) = ""
        Me.Cells(1, 4).Interior.ColorIndex = xlColorIndexNone
        SetProtectionMode Me, True
    End If
    ' important !! restore normal PASTE behaviour
    Application.CommandBars("Edit").Controls("Paste").OnAction = ""
    Application.CommandBars("Edit").Controls("Paste Special...").OnAction = ""
    Application.CommandBars("Cell").Controls("Paste").OnAction = ""
    Application.CommandBars("Cell").Controls("Paste Special...").OnAction = ""
    Application.OnKey "^v"
End Sub

Модуль Sheet_X_Functions в основном содержит часть проверки, относящуюся к этому листу. Обратите внимание на использование Enum здесь - это действительно окупилось для меня - особенно в Sheet_X_ValidateRow рутине - пользователи заставили меня изменить это войлок 100 раз;)

' module Sheet_X_Functions
Sub Sheet_X_BatchValidate(MySheet As Worksheet)
Dim VRow As Range
    For Each VRow In MySheet.Rows
        If VRow.Row > T_Sheet_X.NofHRows And VRow.Row <= T_Sheet_X.MaxData Then
            Sheet_X_ValidateRow VRow, False ' silent validation
        End If
    Next
End Sub

Sub Sheet_X_ValidateRow(MyLine As Range, Verbose As Boolean)
' Verbose: TRUE .... display message boxes; FALSE .... keep quiet (for batch validations)
Dim IsValid As Boolean, Idx As Long, ProfSum As Variant

    IsValid = True
    If ContainsData(MyLine, T_Sheet_X.NofCols) Then
        If MyLine.Cells(1, T_Sheet_X.Country) = "" Or _
           MyLine.Cells(1, T_Sheet_X.City) = "" Or _
           MyLine.Cells(1, T_Sheet_X.SiteType) = "" Then
            If Verbose Then MsgBox "Site information incomplete", vbCritical + vbOKOnly, "Row validation"
            IsValid = False
        ' ElseIf otherstuff
        End If

        ' color code the validation result in 1st column
        If IsValid Then
            MyLine.Cells(1, 1).Interior.ColorIndex = xlColorIndexNone
        Else
            MyLine.Cells(1, 1).Interior.ColorIndex = 3  'red
        End If

    Else
        ' empty lines will resolve to valid, remove all color marks
        MyLine.Cells(1, 1).EntireRow.Interior.ColorIndex = xlColorIndexNone
    End If

End Sub

поддержка Sub 's/Functions в модуле CommonFunctions, которые называются из вышеприведенного кода

' module CommonFunctions
Sub TrappedPaste()
    If ActiveSheet.ProtectContents Then
        ' as long as sheet is protected, we don't paste at all
        MsgBox "Sheet is protected, all Paste/PasteSpecial functions are disabled." & vbCrLf & _
               "At your own risk you may unprotect the sheet." & vbCrLf & _
               "When unprotected, all Paste operations will implicitely be done as PasteSpecial/Values", _
               vbOKOnly, "Paste"
    Else
        ' silently do a PasteSpecial/Values
        On Error Resume Next ' trap error due to empty buffer or other peculiar situations
        Selection.PasteSpecial xlPasteValues
        On Error GoTo 0
    End If
End Sub

' module CommonFunctions
Sub SetProtectionMode(MySheet As Worksheet, ProtectionMode As Boolean)
' care for consistent protection
    If ProtectionMode Then
        MySheet.Protect DrawingObjects:=True, Contents:=True, _
                        AllowSorting:=True, AllowFiltering:=True
    Else
        MySheet.Unprotect
    End If
End Sub

' module CommonFunctions
Function ContainsData(MyLine As Range, NOfCol As Integer) As Boolean
' returns TRUE if any field between 1 and NOfCol is not empty
Dim Idx As Integer

    ContainsData = False
    For Idx = 1 To NOfCol
        If MyLine.Cells(1, Idx) <> "" Then
            ContainsData = True
            Exit For
        End If
    Next Idx
End Function

Одна важная вещь является Selection_Change. Если лист защищен, необходимо проверить строку, которую пользователь только что оставил. Поэтому мы должны отслеживать номер строки, откуда мы пришли, так как параметр TARGET ссылается на выбор NEW.

Если пользователь не имеет защиты, он может заскочить в строки заголовка и начать мешать (при наличии блокировок ячеек, но...), поэтому мы просто не позволим ему/ей поместить туда курсор.

' objects in Sheet_X
Dim Sheet_X_CurLine As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' trap initial move to sheet
    If Sheet_X_CurLine = 0 Then Sheet_X_CurLine = Target.Row

    ' don't let them select any header row    
    If Target.Row <= T_Sheet_X.NofHRows Then
        Me.Cells(T_Sheet_X.NofHRows + 1, Target.Column).Select
        Sheet_X_CurLine = T_Sheet_X.NofHRows + 1
        Exit Sub
    End If

    If Me.ProtectContents And Target.Row <> Sheet_X_CurLine Then
        ' if row is changing while protected
        ' validate old row
        Application.ScreenUpdating = False
        SetProtectionMode Me, False
        Sheet_X_ValidateRow Me.Rows(Sheet_X_CurLine), True ' verbose validation
        SetProtectionMode Me, True
        Application.ScreenUpdating = True
    End If

    ' in any case make the new row current
    Sheet_X_CurLine = Target.Row
End Sub

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

Private Sub Worksheet_Change(ByVal Target As Range)
Dim IsProtected As Boolean

    ' capture current status
    IsProtected = Me.ProtectContents

    If Target.Row > T_FR.NofHRows And IsProtected Then  ' don't trigger anything in header rows or when protection is turned off

        SetProtectionMode Me, False         ' because the trigger will change depending fields
        Application.EnableEvents = False    ' suspend event processing to prevent recursive calls

        Select Case Target.Column
            Case T_Sheet_X.CtyCode
                ' load cities applicable for country code entered
        ' Case T_Sheet_X. ... other stuff
        End Select

        Application.EnableEvents = True    ' continue event processing
        SetProtectionMode Me, True
    End If
End Sub

Вот и все... надеюсь, что эта почта полезна для некоторых из вас парни

удача MikeD