logging vba code errors?

Peter h

Active Member
Joined
Dec 8, 2015
Messages
417
I've got some code that I've been having a minor issue with, so I wrote a bit of error handling code to take care of it. With part of my handler code I wanted it to write the error number and description into a cell on a sheet so that I can track when the error occurs and what error. I've googled it and thought I had a simple code that would work, but it's not working the way I thought it would. Can someone tell me how to do it?

Here's the code I tried. The red highlighted line is the one not doing what I'm wanting. Am I using "Err" wrong? Thanks:

Code:
errHandler:
errRow = ThisWorkbook.Sheets("Sheet2").Range("R" & Rows.Count).End(xlUp).Row
With ThisWorkbook.Sheets("Sheet2")
    .Range("R" & errRow + 1) = "Went to error handler"
    .Range("S" & errRow + 1) = Now
    .[COLOR=#ff0000]Range("T" & errRow + 1) = Err.Number & ": " & Err.Description[/COLOR]
End With
Resume tryAgain:
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
That works for me.
In what way is it not working?
 
Upvote 0
You aren't clearing the error before returning to the routine, try. Although I'm not sure if jumping back into the routine without changing stuff to prevent the error from happening on that second run (and third and...) is the way I would go.

Code:
errHandler:
errRow = ThisWorkbook.Sheets("Sheet2").Range("R" & Rows.Count).End(xlUp).Row
With ThisWorkbook.Sheets("Sheet2")
    .Range("R" & errRow + 1) = "Went to error handler"
    .Range("S" & errRow + 1) = Now
    .Range("T" & errRow + 1) = Err.Number & ": " & Err.Description[COLOR="#FF0000"][/COLOR]
End With
Err.Clear
Resume tryAgain:
 
Last edited:
Upvote 0
That works for me.
In what way is it not working?
Maybe I'm not understanding what the outputs for Err.Number and Err.Description should be. This would by much easier to diagnose if I knew how to duplicate the error that's occurring. It's not working (or doesn't seem to be), because the data stored in column "T" is showing "0:00". I thought the Err.Description would be text, not numerical. I've made sure the format of the cell is "General", and the data in the Cell is then "0". Am I missing something? I thought that Err.Description would be the text in the error box. What could be setting the error to 0? I don't use "OnError GoTo 0" anywhere in the code.

You aren't clearing the error before returning to the routine, try. Although I'm not sure if jumping back into the routine without changing stuff to prevent the error from happening on that second run (and third and...) is the way I would go.

Code:
errHandler:
errRow = ThisWorkbook.Sheets("Sheet2").Range("R" & Rows.Count).End(xlUp).Row
With ThisWorkbook.Sheets("Sheet2")
    .Range("R" & errRow + 1) = "Went to error handler"
    .Range("S" & errRow + 1) = Now
    .Range("T" & errRow + 1) = Err.Number & ": " & Err.Description
End With
Err.Clear
Resume tryAgain:

It's a strange error, that is only happening on the first time running the code. I can't seem to replicate it, or figure out why it doesn't happen every time. So, I put my "tryAgain:" at the top of the code, so if I get the error, it just reruns the code, and have not had any issues of it. I thought that doing it that way would possibly cause a loop where it keeps kicking the error every time it goes through the code, but I have not faced that issue.
 
Upvote 0
When I run this
Code:
On Error GoTo errHandler
Range("R1").Value = 0 / 0
Exit Sub
errHandler:
errRow = ThisWorkbook.Sheets("Sheet2").Range("R" & Rows.Count).End(xlUp).Row
With ThisWorkbook.Sheets("Sheet2")
    .Range("R" & errRow + 1) = "Went to error handler"
    .Range("S" & errRow + 1) = Now
    .Range("T" & errRow + 1) = Err.Number & ": " & Err.Description
End With
Resume tryAgain:
I get


Excel 2013 32 bit
RST
2Went to error handler17/01/2018 17:466: Overflow
Sheet2
 
Upvote 0
When I run this
Code:
On Error GoTo errHandler
Range("R1").Value = 0 / 0
Exit Sub
errHandler:
errRow = ThisWorkbook.Sheets("Sheet2").Range("R" & Rows.Count).End(xlUp).Row
With ThisWorkbook.Sheets("Sheet2")
    .Range("R" & errRow + 1) = "Went to error handler"
    .Range("S" & errRow + 1) = Now
    .Range("T" & errRow + 1) = Err.Number & ": " & Err.Description
End With
Resume tryAgain:
I get

Excel 2013 32 bit
R
S
T
Went to error handler
6: Overflow

<tbody>
[TD="align: center"]2
[/TD]

[TD="align: right"]17/01/2018 17:46
[/TD]

</tbody>
Sheet2

Hmmm. I'm not sure what's going on then. Here's my entire code. Do you see anything in the sub that would cause it to write 0:00 in column "T"?

Code:
Private Sub CheckEntered()
Dim IE As InternetExplorerMedium
Dim targetURL As String
Dim lastrow As Long, ws As Worksheet
Dim selectElement As HTMLSelectElement
Dim optionIndex As Integer
Dim lRow As Long
Dim x As String
Dim isa As String
Dim i As Integer
Dim errRow As Long
tryAgain:
lRow = ThisWorkbook.Sheets("Sheet2").Range("P" & Rows.Count).End(xlUp).Row + 1
targetURL = "myurl"
Set IE = New InternetExplorerMedium
    IE.Visible = False ' Set to true to watch what's happening
    IE.Navigate targetURL
'__________________________________________________________
'***                                                    ***
'***   Navigates to the appropriate sample type in Pi   ***
'***                                                    ***
'__________________________________________________________
    Do Until IE.ReadyState = READYSTATE_COMPLETE
        DoEvents
    Loop
i = Me.lb_type.ListIndex
If lb_type.Selected(i) = True Then
         IE.Document.getElementById("ddlSelection").selectedIndex = i + 1
         IE.Document.getElementById("ddlSelection").FireEvent ("onchange")
        
        Do
            DoEvents
        Loop While IE.Document.getElementById("Sample_Arrival_Time") Is Nothing And IE.Document.getElementById("txtMessage") Is Nothing
    
End If
'______________________________________________________________________________________
'***                                                                                ***
'***   Compares last entered sample in worksheet to the last entered sample in Pi   ***
'***                                                                                ***
'______________________________________________________________________________________
Set ws = ThisWorkbook.Sheets(i + 2)
lastrow = ws.Range("C" & Rows.Count).End(xlUp).Row
On Error GoTo errHandler:
If lb_type.Selected(2) = True Then
    isa = IE.Document.getElementById("IsaDate")(1).Text
Else
    x = IE.Document.getElementById("ListBox1")(1).Text
End If
If isa = "" Then
    If Not ws.Cells(lastrow, 3).Text = x Then
        ThisWorkbook.Sheets("Sheet2").Cells(lRow, "P") = Me.lb_type & ":   " & x & " triggered error."
        ThisWorkbook.Sheets("Sheet2").Cells(lRow, "Q") = Me.cb_analyst.Text
            With Me.lb_type
                If .Selected(0) = True Then efs_Web
                If .Selected(1) = True Then efmWeb
                If .Selected(2) = True Then isaWeb
                If .Selected(3) = True Then convWeb
                If .Selected(4) = True Then revWeb
                If .Selected(5) = True Then feedWeb
                If .Selected(6) = True Then otherWeb
            End With
        MsgBox "Please check Pi to make sure the last sample went into Pi."
    End If
Else
    If Not ws.Cells(lastrow, 3).Text = isa Then
        ThisWorkbook.Sheets("Sheet2").Cells(lRow, "P") = Me.lb_type & ":   " & isa & " triggered error."
        ThisWorkbook.Sheets("Sheet2").Cells(lRow, "Q") = Me.cb_analyst.Text
        isaWeb
        MsgBox "Please check Pi to make sure the last sample went into Pi."
    End If
End If
IE.Quit
Set IE = Nothing
Exit Sub
errHandler:
errRow = ThisWorkbook.Sheets("Sheet2").Range("R" & Rows.Count).End(xlUp).Row
With ThisWorkbook.Sheets("Sheet2")
    .Range("R" & errRow + 1) = "Went to error handler"
    .Range("S" & errRow + 1) = Now
    .Range("T" & errRow + 1) = Err.Number & ": " & Err.Description
End With
Resume tryAgain:
End Sub
 
Last edited:
Upvote 0
When you step through, where in that code does the error handler get invoked.

It only does it on the first time a user uses the code. I can step through it right now, and it all works fine. I was trying to log the error because I personally don't use the program much myself, and it happens so infrequently, so I couldn't even tell you what error it's creating. I'm pretty sure its something about something not being assigned in the "IF" statement right after the GoTo line. But it seems like when it does happen and it invokes the handler and goes back up to the "tryAgain:" it goes through the code just fine the second time. Because of this, I'm not over concerned about it, because it seems to be working the way I have it written now, but I'm trying to figure out how to eliminate the error all together.
 
Upvote 0

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top