修复带IF_THEN_ELSE与复制粘贴操作的VBA自定义函数错误

需求说明 #

• 当「当前状态」为「Not Yet Start」或「In-Progress」时，「DateComplete」列显示「N/a」
• 当状态为「Complete」时，「DateComplete」列显示格式为dd-mm-yy hh:mm:ss的当前时间
• 后续将状态改为「Shipped」时，「DateComplete」列的日期保持不变，不会被清空

原错误代码分析 #

原代码试图用自定义函数（UDF）实现需求，但存在多处致命问题：

1. If语句嵌套逻辑错误，分支结构混乱，导致条件判断失效
2. 自定义函数不允许执行Copy/PasteSpecial这类修改单元格的操作，Excel会限制UDF的权限，仅允许返回值到当前单元格
3. 参数x定义为字符串类型，却被用于Cells(x)引用单元格，类型不匹配，逻辑矛盾

原错误代码如下：

Function UC (x As String)

If x = "Not Yet Start" or _
x = "In-Progress" Then
    UC = "N/a"
    If x = "Complete" Then
        UC= Format (Now, "dd-mm-yy hh:mm:ss")
    Else
        Cells(x).Copy
        ActiveSheet.Cells(x).PasteSpecial
    End If
End Function

优化方案：使用工作表Change事件 #

由于自定义函数无法满足「状态改为Shipped时保留日期」的核心需求（UDF会随单元格重算自动更新，覆盖原有值），推荐使用工作表Change事件实现，仅在状态单元格被手动修改时触发操作：

实现步骤 #

1. 右键目标工作表标签，选择「查看代码」打开VBA编辑器
2. 粘贴以下代码（根据实际列位置调整statusCol和dateCol的值）：

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 定义状态列和日期完成列的列号，根据实际表格修改
    Dim statusCol As Integer, dateCol As Integer
    statusCol = 2 ' 示例：状态在B列
    dateCol = 3 ' 示例：DateComplete在C列
    
    ' 仅处理状态列的单个单元格修改
    If Target.Column <> statusCol Or Target.Cells.Count > 1 Then Exit Sub
    
    Dim statusVal As String
    statusVal = Trim(Target.Value)
    
    Select Case statusVal
        Case "Not Yet Start", "In-Progress"
            ' 状态为未开始/进行中时，设置为N/a
            Target.Offset(0, dateCol - statusCol).Value = "N/a"
        Case "Complete"
            ' 状态为完成时，写入当前时间并设置格式
            With Target.Offset(0, dateCol - statusCol)
                .Value = Now
                .NumberFormat = "dd-mm-yy hh:mm:ss"
            End With
        Case "Shipped"
            ' 状态改为已发货时，不修改日期列，保留原有值
        Case Else
            ' 其他状态默认不做操作，可根据需求扩展
    End Select
End Sub

方案优势 #

• 仅在状态单元格被手动修改时触发，避免自动重算导致的数据覆盖
• 直接修改单元格值而非使用函数，确保状态改为Shipped时日期保留
• 逻辑清晰，可快速调整列位置或扩展其他状态的处理逻辑

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