如何在VBA中跟踪400错误？

Question

我找不到错误在哪里。拉取动态文件名的类似代码在另一个选项卡中工作。

Sub MonthlyBCRCPL()

    Dim filePath As String
    Dim CardsRCPLWb As Workbook
    Set CardsRCPLWb = ActiveWorkbook
    filePath = CardsRCPLWb.Sheets("BCRCPL").Range("A1").Value

    'Optimize Code
    Call OptimizeCode_Begin

    Const FlashFolder As String = "\\apacdfs\SG\GCGR\GROUPS\ASEAN\Dashboard\Cards\Flash\"
    Flashname = Format(CardsRCPLWb.Sheets("ASEAN - CARDS, RCPL").Range("C2").Value, "YYYYMMDD")
    Flashname = "ASEAN SD Regional Dashboard - " & Flashname & ".xlsx"
    Flashpath = FlashFolder & Flashname
    Dim FlashWb As Workbook
    Set FlashWb = Workbooks.Open(Flashpath)
    If FlashWb Is Nothing Then MsgBox "SD Flash File does not exist": Exit Sub

Answer 1

考虑在子例程中使用handling the error，并让它发出一条消息。然后，正确地继续/跳过/退出剩余的代码，甚至在异常期间从内存中释放对象。这是VBA (以及一般编程)中的最佳实践。

我怀疑找不到路径，可能是网络UNC，文件命名无效，例如使用了特殊字符，或者尝试打开时工作簿不存在：

Sub MonthlyBCRCPL()
   On Error Goto ErrHandle:
   ...code...

ExitSubBlock:    
   Set CardsRCPLWb = Nothing
   Set FlashWb = Nothing
   Exit Sub

ErrHandle:
   Msgbox Err.Number & " - " & Err.Description, vbCritical, "RUNTIME ERROR"
   Goto ExitSubBlock
   ' Resume Next       
End Sub