vba - 尝试在Err.Clear中使用On Error GoTo

Question

我在几个Excel实例上使用循环。我正在使用多台PC和实例浏览数据，因此每个实例将获取下一个可用文件。如果多个实例正在打开同一文件(csv格式)，则VBA会出错。我希望错误处理标签仅转到循环中的下一个文件。但是，我只能使此错误处理起作用一次。第二次无法处理该错误。如果代码的另一部分导致错误处理失败，我将完整的代码保留在下面。

Sub RunRoutine()



CloseOtherWorkbook
Application.StatusBar = False
manualcalc
Calculate
ListAllFile
Calculate
Sheets("RUN").Select
Set wBRun = ActiveWorkbook
Workbooks.Open Filename:=Range("FO_CalcName_Range").Value, ReadOnly:=True
Set wBCalc = ActiveWorkbook
wBRun.Activate

For Each C In ActiveSheet.Range("FILE_RANGE_RUN").Cells
    Err.Clear
    On Error GoTo Error_handler:
    wBRun.Activate
    Sheets("RUN").Select
    C.Select
    ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate

    If ActiveCell.Value = False Then
        Application.ScreenUpdating = True
        Application.StatusBar = False
        Application.StatusBar = "Run Routine" & " - " & C
        Application.ScreenUpdating = False
        Range("Date_Range").Value = C
        ActiveSheet.Calculate
        FO_RawName = Range("FO_RawName_Range").Value
        Workbooks.Open FO_RawName, ReadOnly:=True 'this is where the code fails
        Set wBRaw = ActiveWorkbook
        wBRaw.Activate
        Columns("A:dn").Select
        Selection.Copy

        wBCalc.Activate
        Sheets("CALC").Select
        Columns("A:dn").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        ResizeRows
        wBRaw.Activate
        Application.CutCopyMode = False
        ActiveWorkbook.Close False
        wBRun.Activate
        RunallSheets
    Else
        'do nothing
    End If
Error_handler:

Next
Application.ScreenUpdating = True
wBCalc.Activate
ActiveWorkbook.Close False
Application.StatusBar = False
Application.ScreenUpdating = True
wBRun.Activate
manualcalc
ThisWorkbook.Save
Application.OnTime Now + TimeValue("00:10:00"), "RunRoutine"

结束子

Answer 1

即使也有错误处理，也有可能避免使用GOTO

最佳做法是有意识地处理错误，即在您期望它们时将其捕获并适当对待

这意味着您必须在调试时让代码保持任何错误的状态

例如，为了捕获可能的工作簿打开异常，您可能需要:

具有打开工作簿并返回的特定功能:

True(如果成功)，以及打开的工作簿

的对象引用

False(如果不是)

例如

Function OpenWorkbook(wbName As String, wb As Workbook) As Boolean
    On Error Resume Next
    Set wb = Workbooks.Open(wbName, ReadOnly:=True)
    OpenWorkbook = Not wb Is Nothing
End Function

如下使用它

    ... your code
    ActiveSheet.Calculate

    If OpenWorkbook(Range("FO_RawName_Range").Value, wBRaw) Then
        Columns("A:dn").Select '<--| this will select columns "A:DN" in wBRaw active sheet
        Selection.Copy

        wBCalc.Activate
        Sheets("CALC").Select
        Columns("A:dn").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        ResizeRows
        wBRaw.Activate
        Application.CutCopyMode = False
        ActiveWorkbook.Close False
        wBRun.Activate
        RunallSheets
    End If

最后，您可能还想避免使用 Activate/ Active.../ Select/ Selection，并使用完全限定的范围引用来提高代码性能(在工作簿/工作表之间进行切换非常耗时)，而又不要放松对范围的控制