使用VBA创建Excel日志文件 代码运行结果不符预期求助

需求说明 #

需要实现的日志表包含两列核心字段：

• Tasks（任务列）：用户手动输入任务内容
• By（操作人列）：用户在Tasks列输入内容后，相邻单元格自动填充当前系统登录用户名
  预期效果参考：
  

原有代码问题排查 #

原有代码存在4个核心错误，导致运行结果完全不符合预期：

1. 触发事件选错：所有逻辑写在Workbook_Open事件中，该事件仅在工作簿打开瞬间执行1次，无法实时响应用户后续输入任务的操作，不可能实现“输入即填充”的效果。
2. 单元格引用完全错位：遍历单元格的循环中，全程使用ActiveCell（当前鼠标选中的单元格）做行号计算、偏移写入，和循环正在处理的cell对象没有绑定关系，写入位置完全随机，所以每次运行结果都不一致。
3. 序号列逻辑无效：序号列循环中仅给局部变量cellValue赋值，从来没有把计算结果写回单元格，这段代码运行后不会对表格产生任何修改。
4. 冗余与错误配置：包含大量无意义代码（比如判断单元格为空时再给单元格赋值空字符串），最后重复将计算模式设为手动，没有恢复初始配置，会影响Excel正常的公式计算功能。

正确实现代码 #

要实现实时响应输入的效果，必须使用工作表的Worksheet_Change事件，该事件会在单元格内容被修改时自动触发。

部署步骤 #

1. 打开目标Excel文件，按Alt+F11调出VBA编辑器
2. 在左侧工程面板双击你需要部署日志功能的工作表（例如Sheet1）
3. 将以下代码粘贴到右侧弹出的代码窗口中，保存即可

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 定义监控范围：仅处理B列（Tasks列）2-200行的内容修改
    Dim watchRng As Range
    Set watchRng = Intersect(Target, Me.Range("B2:B200"))
    If watchRng Is Nothing Then Exit Sub
    
    ' 关闭事件触发，避免修改单元格时递归触发本事件导致死循环
    Application.EnableEvents = False
    ' 错误捕获：哪怕代码运行出错，也要保证事件开关被恢复
    On Error GoTo RecoverSetting
    
    Dim cell As Range
    For Each cell In watchRng
        ' 自动填充A列序号（不需要序号可以删掉这段判断）
        If cell.Offset(0, -1).Value = "" And cell.Value <> "" Then
            cell.Offset(0, -1).Value = cell.Row - 1
        End If
        
        ' 填充/清空操作人列
        If Trim(cell.Value) <> "" Then
            ' 任务列有内容，右侧相邻单元格写入当前系统用户名
            cell.Offset(0, 1).Value = Environ("username")
        Else
            ' 任务列内容被清空，同步清空操作人列
            cell.Offset(0, 1).ClearContents
        End If
    Next

RecoverSetting:
    ' 恢复事件触发
    Application.EnableEvents = True
End Sub

适配说明 #

• 如果你的表格列位置和示例不一致，只需要调整Offset的参数即可：Offset(行偏移量, 列偏移量)，以Tasks列单元格为基准，向左一列列偏移量为-1，向右一列列偏移量为1
• 如果不需要自动生成序号，直接删除代码中对应序号填充的判断块即可
• 不要在代码中随意修改ScreenUpdating、Calculation这类Excel全局配置，除非你明确知道修改的影响，否则容易导致其他功能异常。

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