vba - VBA错误句柄-恢复和打印错误

Question

我正在尝试建立以下结构。
我有一个循环，有时循环步骤之一可能返回错误，但我想跳过它并继续循环直到结束。

但是，如果任何循环执行有错误，我想知道它在像“Missing load:(1，20，36)”这样的单元格中打印，该数字是循环中我的变量之一接收到的唯一值。

因此，我认为每次循环执行之一返回错误时，我都需要构建此变量值的列表，并在循环过程结束时使用此列表返回此错误消息。

更新:

对于下面的内容，我想知道在SQL查询中所有“sProdId”最终值的列表，该值由于任何错误而无法执行。通常，它尝试在数字SQL字段中插入#Value。

    Sub SavetoSQL()

        Dim conn As New ADODB.Connection
        Dim iRowNo As Integer

        Dim Ddate
        Ddate = Range("refdate")

        Dim RngRefdate As Date
        RngRefdate = DateSerial(Year(Ddate), Month(Ddate), Day(Ddate))



 With Sheets("Hist Prods temp")

     'Open a connection to SQL Server
            conn.Open "Provider=SQLOLEDB;Data Source=XXXXX;Initial Catalog=XXXXXX;User Id=XXXX;Password=XXXXXXX;"

            'Skip the header row
            iRowNo = 2

            'Loop until empty cell in sRefDate
            Do Until .Cells(iRowNo, 1) = ""
                sRefDate = .Cells(iRowNo, 1)
                sProdId = .Cells(iRowNo, 2)
                sPrice = .Cells(iRowNo, 3)
                sValue = .Cells(iRowNo, 4)
                sDV01 = .Cells(iRowNo, 5)
                sDelta1 = .Cells(iRowNo, 6)
                sDeltaPct = .Cells(iRowNo, 7)
                sGamma = .Cells(iRowNo, 8)
                sVega = .Cells(iRowNo, 9)
                sTheta = .Cells(iRowNo, 10)
                sDelta2 = .Cells(iRowNo, 11)
                sIVol = .Cells(iRowNo, 12)

'Generate and execute sql statement to import the excel rows to SQL Server table

conn.Execute "INSERT INTO [dbo].[Prices] ([Date],[Id_Product],[Price],[Value],[DV01],[Delta1$],[Delta%],[Gamma$],[Vega$],[Theta$],[Delta2$],[Ivol],[Last_Update]) values ('" & sRefDate & "', '" & sProdId & "'," & sPrice & "," & sValue & "," & sDV01 & "," & sDelta1 & "," & sDeltaPct & "," & sGamma & "," & sVega & "," & sTheta & "," & sDelta2 & "," & sIVol & ",GETDATE())"


 iRowNo = iRowNo + 1

 Loop

 conn.Close
 Set conn = Nothing

 End With

 End Sub

Answer 1

好吧，您对VBA中的错误处理有些困惑，请访问Chip的网站适当的Error Handling in VBA。

您的代码应类似于

Sub MyMacro()
On Error GoTo Errhandler
    Dim errLog As String
    Do Until
        ' My loop code
        'Save variable X value in a list of error values.
    Loop
ExitErrHandler:
    If Len(errLog) > 0 Then
        Range("M2") = "Missing loads: (" & Left(errLog, Len(errLog) - 2) & ")"
    End If 
    Exit Sub

Errhandler:
    'Make a Note of the Error Number and substitute it with 1234
    If Err.Number = 1234 Then
        ' If an error occurs, display a message in a cell with all X values on the list.
        errLog = errLog & yourUniqueValue & ", "
        Resume Next
    Else
        MsgBox "Another Error occurred." & vbCrLf & vbCrLf & Err.Number & " - " & Err.Description
        Resume ExitErrHandler
    End If
End Sub