excel - 使 IF 函数更高效

Question

我正在使用以下格式根据有限的输入列表从有限的输出列表中获取输出，但我想知道是否有更快/更有效的代码执行方式？有没有办法缩短代码？

代码按原样工作，但我总是执行这样的任务，我只是想知道从 self 开发的角度来看是否有更好的方法。

    If Intersect(Target, Range(Dev_Status & "6:" & Dev_Status & "1000")) = Dev_Raised_IN Then
    
        Target_Column = Dev_Raised
    
        ElseIf Intersect(Target, Range(Dev_Status & "6:" & Dev_Status & "1000")) = Dev_Draft_IN Then
        
            Target_Column = Dev_Draft
            
        ElseIf Intersect(Target, Range(Dev_Status & "6:" & Dev_Status & "1000")) = Dev_Review_IN Then
        
            Target_Column = Dev_Review
            
        ElseIf Intersect(Target, Range(Dev_Status & "6:" & Dev_Status & "1000")) = Dev_Comments_IN Then
        
            Target_Column = Dev_Comments
            
        ElseIf Intersect(Target, Range(Dev_Status & "6:" & Dev_Status & "1000")) = Dev_Approved_IN Then
        
            Target_Column = Dev_Approved
        
    End If

Answer 1

重复的一个很好的解决方案通常是抽象。

Dim src As Range
Set src = Me.Range(Dev_Status & "6:" & Dev_Status & "1000")

当指定参数不相交时，Intersect 函数生成一个 Range 对象引用，它是 Nothing，而一个 Range 表示相交的单元格。假设代码存在于某个 Worksheet_Change 处理程序中(因此 Me 是正在处理的 Worksheet)并且 Target 是验证为仅是单个单元格，那么我们应该对交集求值一次:

Dim intersecting As Range
Set intersecting = Intersect(Target, Me.Range(Dev_Status & "6:" & Dev_Status & "1000"))

代码应该处理 Nothing 的范围:

If intersecting Is Nothing Then Exit Sub

然后它的值就可以安全地进行比较了……是吗？如果单元格包含工作表错误值，则其数据类型将为 Variant/Error，我们对该数据类型执行的任何类型的操作均不涉及 Variant/Error 运算符两边的操作数，将抛出类型不匹配错误。所以我们也应该在这种情况下保释:

If IsError(intersecting.Value) Then Exit Sub

现在我们可以将重复的 If...ElseIf...End If block 变成 Select Case block :

Select Case intersecting.Value

    Case Dev_Raised_IN
        Target_Column = Dev_Raised

    Case Dev_Draft_IN
        Target_Column = Dev_Draft

    Case Dev_Review_IN 
        Target_Column = Dev_Review

    Case Dev_Comments_IN 
        Target_Column = Dev_Comments

    Case Dev_Approved_IN 
        Target_Column = Dev_Approved

    Case Else
        'we don't have a target column:
        Target_Column = -1

End Select

然后可以使用带键的 Collection 或 Dictionary 将整个 block 进一步简化为简单的单行键查找。

当然，这样的集合需要初始化，但是可以使用 Static 局部初始化一次，就像这样(第一次运行进入条件，第二次运行没有):

Static targetColumns As Collection
If targetColumns Is Nothing Then
    Set targetColumns = New Collection
    targetColumns.Add Dev_Raised, Dev_Raised_IN
    targetColumns.Add Dev_Draft, Dev_Draft_IN
    targetColumns.Add Dev_Review, Dev_Review_IN
    targetColumns.Add Dev_Comments, Dev_Comments_IN
    targetColumns.Add Dev_Approved, Dev_Approved_IN
End If

On Error Resume Next '"key not found"
Target_Column = targetColumns(intersecting.Value)
If Err.Number <> 0 Then Target_Column = -1
On Error GoTo 0