Excel VBA IF 然后 IF 语句

Question

我正在使用"If Then If msgbox vbYesno"语句，我不确定如何正确摆脱它（我知道Goto不干净）。

有人可以告诉我我的错误是什么吗？我没有发现有人使用类似的东西。

Sub IF_THEN_IF()
If Sheet1.Range("A1").Value > 500 Then
    If MsgBox("A1 > 500, Is this correct", vbYesNo, "Amount of Lines") = vbYes Then
        Range("H11").FormulaR1C1 = "My Formula"
    Else
        GoTo Jump
    End If
Else
Jump:    
    Range("H11").FormulaR1C1 = "I have Jumped"
End If
End Sub

Answer 1

如果您不打算"跳跃"，您可以离开您的程序，以其他方式跳跃：

Sub IF_THEN_IF()
    If Sheet1.Range("A1").Value > 500 Then
        If MsgBox("A1 > 500, Is this correct", vbYesNo, "Amount of Lines") = vbYes Then
            Range("H11").FormulaR1C1 = "My Formula"
            Exit Sub
        End If
   End If
   'Jump
    Range("H11").FormulaR1C1 = "I have Jumped"
End Sub

另一种选择是使用布尔变量：

Sub IF_THEN_IF()
    Dim Jump as Boolean
    Jump = Sheet1.Range("A1").Value <= 500
    If Not Jump Then Jump = MsgBox("A1 > 500, Is this correct", vbYesNo, "Amount of Lines") <> vbYes
    If Jump Then
        Range("H11").FormulaR1C1 = "I have Jumped"
    Else
        Range("H11").FormulaR1C1 = "My Formula"
    End If
End Sub

Answer 2

这个答案是对你对Vityaya的回答的评论的回应。

将代码从 Jump 转换为子例程，并根据需要调用它。

Sub IF_THEN_IF()
With Sheet1 
    If .Range("A1").Value > 500 Then
        Dim res as Variant
        res = MsgBox("A1 > 500, Is this correct", vbYesNo, "Amount of Lines")
        If res = vbYes Then
            .Range("H11").FormulaR1C1 = "My Formula"
        Else
            Jump
        End If
    Else   
        Jump
    End If
End Sub
Sub Jump()
   'code here
End Sub

Answer 3

您可以使用

AND运算符

Sub IF_THEN_IF()    
If Sheet1.Range("A1").Value > 500 And _
   MsgBox("A1 > 500, Is this correct", vbYesNo, "Amount of Lines") = vbYes Then
    Range("H11").FormulaR1C1 = "My Formula"
Else
    Range("H11").FormulaR1C1 = "I have Jumped"
End Sub

Answer 4

一个可能的选项是这样的：

Sub IfThenElse()
    With Sheet1
        If .Range("A1").value > 500 Then
            If MsgBox("A1 > 500, Is this correct", vbYesNo, "Amount of Lines") = vbYes Then
                .Range("H11") = "My Formula"
            Else
                .Range("H11") = "I have Jumped"
            End If
        Else
            .Range("H11") = "I have Jumped"
        End If
    End With
End Sub

您有两个嵌入式 if。就这样。在 Excel VBA IF 然后 IF 语句@Scott Holtzmann 的答案中，您可以看到如何通过遵循 DRY 方法避免两次写"I have jumped"。