继续对类型不匹配 2042 错误进行下一次迭代

Question

我正在寻找VBA中的继续语句。

我找到了一个 VB.NET 的解决方案，但Excel"IDE"无法识别它。

http://msdn.microsoft.com/en-us/library/801hyx6f.aspx

如果发生错误，我想跳过迭代。还有其他方法吗 - 一个更优雅的解决方案 - 但 GOTO(恕我直言是最糟糕的情况(或将整个 Sub 包装在 If 语句中(这也无助于可读性(？

代码片段

'While Loop.... 
    For Each C In w.Range(w.Cells(1, 1), w.Cells(num_rw, num_col))
            If IsError(C.Value) Then:
                MsgBox ("File unsuitable!")
                'Continue While
            End If
            If Var1 <> "" Then
                If C.Value = Var1 Then: _
                  Set Var1_Range = w.Range(w.Cells(C.Row, C.Column), w.Cells(num_rw, C.Column))
            End If
            If Var2 <> "" Then
                If C.Value = Var2 Then: _ 
                  Set Var2_Range = w.Range(w.Cells(C.Row, C.Column), w.Cells(num_rw, C.Column))
            End If
            If Var3 <> "" Then
                If C.Value = Var3 Then:
                  Set Var3_Range = w.Range(w.Cells(C.Row, C.Column), w.Cells(num_rw, C.Column))
            End If
        Next C
        If Var1_Range Is Nothing Then: 'Continue While

'WEND

Answer 1

选项 1

如果要跳过 While 循环的当前迭代，如果C.Value抛出错误，只需将循环后的其余代码放入 if 语句中并添加一个 fileError 变量：

Dim fileError As Boolean
'While Loop....
    fileError = False
    For Each C In w.Range(w.Cells(1, 1), w.Cells(num_rw, num_col))
        If IsError(C.Value) Then:
            MsgBox ("File unsuitable!")
            fileError = True
            Exit For
            'Continue While
        Else
            If Var1 <> "" Then
                If C.Value = Var1 Then: _
                  Set Var1_Range = w.Range(w.Cells(C.Row, C.Column), w.Cells(num_rw, C.Column))
            End If
            If Var2 <> "" Then
                If C.Value = Var2 Then: _ 
                  Set Var2_Range = w.Range(w.Cells(C.Row, C.Column), w.Cells(num_rw, C.Column))
            End If
            If Var3 <> "" Then
                If C.Value = Var3 Then:
                  Set Var3_Range = w.Range(w.Cells(C.Row, C.Column), w.Cells(num_rw, C.Column))
            End If
        End If
    Next C
    If fileError = False Then
        If Var1_Range Is Nothing Then: 'Continue While
        .
        .  'Put the rest of the code in your While loop here
        .
        End If
    End If
'WEND

选项 2

这是实现相同目标的另一种选择。我不认为它比其他选项更干净，但它确实避免了额外的 If 语句和GoTo函数：

'While Loop....
    Do
        For Each C In w.Range(w.Cells(1, 1), w.Cells(num_rw, num_col))
            If IsError(C.Value) Then:
                MsgBox ("File unsuitable!")
                Exit Do
                'Continue While
            Else
                If Var1 <> "" Then
                    If C.Value = Var1 Then: _
                      Set Var1_Range = w.Range(w.Cells(C.Row, C.Column), w.Cells(num_rw, C.Column))
                End If
                If Var2 <> "" Then
                    If C.Value = Var2 Then: _ 
                      Set Var2_Range = w.Range(w.Cells(C.Row, C.Column), w.Cells(num_rw, C.Column))
                End If
                If Var3 <> "" Then
                    If C.Value = Var3 Then:
                      Set Var3_Range = w.Range(w.Cells(C.Row, C.Column), w.Cells(num_rw, C.Column))
                End If
            End If
        Next C
        If Var1_Range Is Nothing Then: 'Continue While
        .
        .
        .
        End If
        Exit Do 'Ensures the loop will never execute more than once
    Loop
'WEND

然后，您可以将Exit Do放在任何要跳到While循环的下一个迭代的任何位置。

Answer 2

您可以使用 Exit For 退出最直接/最新的 For。

For Each C In w.Range(w.Cells(1, 1), w.Cells(num_rw, num_col))
    If IsError(C.Value) Then:
        MsgBox "File unsuitable!"
        Exit For
    End If
    If Var1 <> "" Then
        If C.Value = Var1 Then: _
          Set Var1_Range = w.Range(w.Cells(C.Row, C.Column), w.Cells(num_rw, C.Column))
    End If
    If Var2 <> "" Then
        If C.Value = Var2 Then: _ 
          Set Var2_Range = w.Range(w.Cells(C.Row, C.Column), w.Cells(num_rw, C.Column))
    End If
    If Var3 <> "" Then
        If C.Value = Var3 Then:
          Set Var3_Range = w.Range(w.Cells(C.Row, C.Column), w.Cells(num_rw, C.Column))
    End If
Next C
If Var1_Range Is Nothing Then: 'Continue While