基于日期锁定Excel行的工作追踪宏优化咨询

你的宏思路贴合需求，但存在逻辑漏洞和可优化的细节，以下是具体调整建议和修改后的代码：

核心问题与优化方向 #

• 未声明变量：allowRange、aboveLastRow未在代码开头声明，建议开启Option Explicit强制变量声明，避免隐式变量引发的意外错误。
• 仅判断最后一行填满才执行逻辑：原宏中如果最后一行A-M列未填满13个单元格，整个宏直接跳过，不符合“当日可编辑多条未完成记录”的需求——员工当天编辑多条记录时，未填满的行也需要保护过往日期的历史行。
• 仅检查最后一行日期：原宏只判断最后一行N列的日期，若中间存在过往日期的行，这些行不会被锁定，应该遍历所有行，逐行验证日期是否需要锁定。
• 硬编码固定值：密码、工作表名、固定行号（1000）都直接写在代码里，改成常量更便于后续维护。
• 缺乏错误处理：如果N列单元格不是有效日期格式，代码会报错，需要增加日期有效性判断。
• 重复代码冗余：If和Else分支里的保护工作表、添加编辑范围代码重复，可提取为公共逻辑简化代码。

修改后的宏代码 #

Option Explicit

Sub ProtectPastDateRows()
    ' 定义常量，便于后续修改维护
    Const WS_NAME As String = "Production Table"
    Const PROTECT_PWD As String = "111111"
    Const DATE_COL As Integer = 14 ' N列对应的序号是14
    Const FIRST_DATA_ROW As Integer = 2 ' 数据起始行（跳过表头）
    
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim currentDate As Date
    Dim i As Long
    Dim allowRange As AllowEditRange
    Dim editableStartRow As Long
    
    ' 初始化工作表和当前日期
    Set ws = ThisWorkbook.Sheets(WS_NAME)
    currentDate = Date
    
    ' 先解除工作表保护
    ws.Unprotect Password:=PROTECT_PWD
    
    ' 删除所有已存在的允许编辑范围
    For Each allowRange In ws.Protection.AllowEditRanges
        allowRange.Delete
    Next allowRange
    
    ' 获取A列最后一行数据的行号
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 从最后一行往上遍历，找到第一个可编辑的起始行（所有过往日期行都锁定）
    editableStartRow = FIRST_DATA_ROW
    For i = lastRow To FIRST_DATA_ROW Step -1
        ' 先验证单元格是有效日期，再判断是否早于当前日期
        If IsDate(ws.Cells(i, DATE_COL).Value) Then
            If ws.Cells(i, DATE_COL).Value < currentDate Then
                editableStartRow = i + 1
                Exit For
            End If
        End If
    Next i
    
    ' 处理所有行都是过往日期的情况，允许编辑最后一行的下一行
    If editableStartRow > lastRow Then editableStartRow = lastRow + 1
    
    ' 添加允许编辑的范围：A-I列和K-M列，从可编辑起始行到工作表最后一行
    If editableStartRow <= ws.Rows.Count Then
        ws.Protection.AllowEditRanges.Add Title:="EditRange_AI", Range:=ws.Range("A" & editableStartRow & ":I" & ws.Rows.Count)
        ws.Protection.AllowEditRanges.Add Title:="EditRange_KM", Range:=ws.Range("K" & editableStartRow & ":M" & ws.Rows.Count)
    End If
    
    ' 删除过往日期行的数据验证
    If editableStartRow > FIRST_DATA_ROW Then
        ws.Range("A" & FIRST_DATA_ROW & ":M" & editableStartRow - 1).Validation.Delete
    End If
    
    ' 重新保护工作表，UserInterfaceOnly参数让宏可直接修改工作表，无需反复解除保护
    ws.Protect Password:=PROTECT_PWD, UserInterfaceOnly:=True

End Sub

额外说明 #

1. UserInterfaceOnly:=True：添加该参数后，只有用户界面操作会被限制，宏可以直接修改保护状态下的工作表，无需每次执行都先解除保护，提升效率。
2. 日期判断逻辑：从最后一行往上遍历，确保所有早于当前日期的行都被锁定，第一个日期不早于今天的行开始允许编辑，符合“当日可编辑多条记录”的需求。
3. 常量定义：所有固定配置都放在代码开头的常量区，后续修改工作表名、密码或日期列时，直接修改常量即可，不用逐行查找代码。
4. 错误防护：用IsDate验证N列单元格是否为有效日期，避免非日期值导致的运行错误。

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