If Error vs. On Error

[AndyTampa]

This macro is supposed to refresh a worksheet that already exists. I'm trying to error check before deleting the existing data and replacing it with new data.

I'm creating a new temporary sheet and scraping a table from a webpage. If the scrape fails, I want to delete the new sheet and display a message box., and not continue. I tried an IF Error, but it always errored. I can't find how to do "On Error, do this and this and this."

If there is no error, I want to delete the existing worksheet and rename the new worksheet. I'm familiar with adding/deleting/renaming worksheets, but it's the error handling I'm having difficulty with.

The other thing is whether there is an error or not, once it's done with the related actions, I need to end the macro. Putting EndIf in the Then or Else part of an If loop caused an issue when I tried it.

 

[6StringJazzer]

For a specific solution please paste all of your code into a post. I highly recommend using code tags (highlight the code then click the VBA button) so your code is more readable.

Generically, you can check for an error like this:

Private Sub Sample()

   DoSomething
   If Err.Number > 0 Then
      ' An error occurred!
      ' Perform error handling
      Err.Clear
   End

   ' Continue on

End Sub

or you can trap an error:

Private Sub Sample()

   On Error GoTo Whoops
   Do Something
   ' Do all the normal stuff

   Exit Sub

Whoops:

   MsgBox "An error occurred!"

End Sub

 

[AndyTampa]

For a specific solution please paste all of your code into a post. I highly recommend using code tags (highlight the code then click the VBA button) so your code is more readable.

Generically, you can check for an error like this:

Private Sub Sample()

   DoSomething
   If Err.Number > 0 Then
      ' An error occurred!
      ' Perform error handling
      Err.Clear
   End

   ' Continue on

End Sub

or you can trap an error:

Private Sub Sample()

   On Error GoTo Whoops
   Do Something
   ' Do all the normal stuff

   Exit Sub

Whoops:

   MsgBox "An error occurred!"

End Sub

For a specific solution please paste all of your code into a post. I highly recommend using code tags (highlight the code then click the VBA button) so your code is more readable.

Generically, you can check for an error like this:

Private Sub Sample()

   DoSomething
   If Err.Number > 0 Then
      ' An error occurred!
      ' Perform error handling
      Err.Clear
   End

   ' Continue on

End Sub

or you can trap an error:

Private Sub Sample()

   On Error GoTo Whoops
   Do Something
   ' Do all the normal stuff

   Exit Sub

Whoops:

   MsgBox "An error occurred!"

End Sub

I actually don't have any code for this yet because I can't figure out how to do this. You've actually posted my problem. For the example with the GoTo, I've read using this type of direction is ill-advised. The example with the Err.Number looks promising, but I have had issues with If/Then/Else and embedding an End IF in the If/Then.

For example what I want is IF there is an error, THEN delete the new sheet I added, display an error message, and end the macro. If there is no error, scrape the webpage, reformat the data, delete the old information sheet, rename the new information sheet and and the macro.

Another problem is where to put the error-checking. On error can be added before the request to scrape the data and gives instructions if it errors. Do I put the If Err.Number after the attempt to scrape?

The next problem is testing if it works. So far, the scrape works every time so I can't test if there is an error. It would only fail if they change the URL or the format of the table on the page. I don't have access to the page to test the error. How can I do that?

 

[6StringJazzer]

For the example with the GoTo, I've read using this type of direction is ill-advised.

There is nothing ill-advised about creating an error handler. It is a standard structure in VBA and other languages. (Other uses of GoTo are indeed ill-advised, but don't confuse a GoTo statement with the use of GoTo in an On Error statement.)

what I want is IF there is an error, THEN delete the new sheet I added, display an error message, and end the macro. If there is no error, scrape the webpage, reformat the data, delete the old information sheet, rename the new information sheet and and the macro.

There are two ways to do this. I prefer to avoid Exit Sub statements but many programmers use them. Here are both:

Private Sub Sample()

   DoSomething
   If Err.Number > 0 Then
      ' An error occurred!
      ' Perform error handling
      Exit Sub
   End

   ' Continue on with everything else

End Sub

Private Sub Sample()

   DoSomething
   If Err.Number > 0 Then
      ' An error occurred!
      ' Perform error handling
      Err.Clear
   Else

   ' Continue on with everything else

   End If

End Sub

Another problem is where to put the error-checking.

An On Error statement should be placed as close as possible before the line of code that is expected to have the potential to raise an error. An If Err.Number > 0 statement should be placed as closed as possible after it.

The next problem is testing if it works. So far, the scrape works every time so I can't test if there is an error. It would only fail if they change the URL or the format of the table on the page. I don't have access to the page to test the error. How can I do that?

I can't advise you on that without seeing all your code. You would probably have to create your own test page and change the URL in your code to use that.

 

[AndyTampa]

Thank you 6StringJazzer, I was able to use this after the scrape:

    If Err.Number > 0 Then
        MsgBox ("I WAS UNABLE TO UPDATE THE ADJUSTMENT CODES." & vbCrLf & vbCrLf & "The Adjustment Codes NERD document may have changed." & vbCrLf & vbCrLf & "This requires correction of the macro." & vbCrLf & vbCrLf & "If valid codes are marked as invalid, delete the row and" & vbCrLf & "add them manually after the final OSR is created.")
        Application.DisplayAlerts = False
        Sheets("tempAdjCodes").Delete
        Application.DisplayAlerts = True
        Err.Clear
    Else

I was able to test it using this before the scrape:

    On Error Resume Next
    Err.Raise (11)

Now that I know it works I can remove the code that forces the error. However, I need to add another test to see if the result was correct after formatting. If not, I'll be using the same instructions as if the scrape failed, including the msgbox. I need to reformat the code so I only have those instructions once.

 

[AndyTampa]

SUCCESS!

This is what I've come up with to create a temporary sheet then error check and, if all is good, replace the original.

Sub RefreshAdjCodes()

    Sheets.Add.Name = "tempAdjCodes"
    Sheets("tempAdjCodes").Activate
    
'    On Error Resume Next
'    Err.Raise (11)
    
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;internal webpage" _
        , Destination:=Range("tempAdjCodes!$A$1"))
        .Name = _
        "webpage name"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

'------------------------------------------------------
'| IF THE SCRAPE FAILS, DELETE THE NEW ADJCODES SHEET |
'------------------------------------------------------
    If Err.Number > 0 Then
        Fatal
    Else
        Columns("A:A").Delete
        Columns("D:H").Delete
        Rows("1:1").Delete
        Range("A2").Cut Destination:=Range("A1")
        Rows("2:2").Delete
        Range("A1").Select
        If Range("A1").Value = "CHARGE" And Range("B1").Value = "CREDIT" And Range("C1").Value = "DESCRIPTION" Then
            Application.DisplayAlerts = False
            Sheets("AdjCodes").Delete
            Application.DisplayAlerts = True
            Sheets("tempAdjCodes").Name = "AdjCodes"
        Else
            Fatal
        End If
    End If
    Sheets("AdjSheet").Activate
    
End Sub
-------------------------------------------------------------
Sub Fatal()

        MsgBox ("I WAS UNABLE TO UPDATE THE ADJUSTMENT CODES." & vbCrLf & vbCrLf & "The Adjustment Codes document may have changed." & vbCrLf & vbCrLf & "This requires correction of the macro." & vbCrLf & vbCrLf & "If valid codes are marked as invalid, delete the row and" & vbCrLf & "add them manually after the final OSR is created.")
        Application.DisplayAlerts = False
        Sheets("tempAdjCodes").Delete
        Application.DisplayAlerts = True
        Err.Clear

End Sub

I wonder how many of those options in the scrape are unnecessary.

 

[6StringJazzer]

I wonder how many of those options in the scrape are unnecessary.

I believe all of the starting with FieldNames are defaults and could be omitted. If you don't know, where did the code come from?

 

[AndyTampa]

I believe all of the starting with FieldNames are defaults and could be omitted. If you don't know, where did the code come from?

I started recording a macro in Excel and chose Data > From Web, then went to my URL and selected the proper table on the page. Excel did the code.

 

[6StringJazzer]

Yes, that is typical code from the macro recorder. It will set every property, even if you didn't actually set that property when recording.

 

[AndyTampa]

Since I don't understand the properties, would it be best to just leave them alone? I'd like to shorten the code just to clean it up, but I don't want to mess it up.