Help with VBA Error Handling

[jo15765]

I have never done any error handling, I would always just let the code crash, and pick up the pieces after the fact. Well this time I am going to try to add error handling, and what my thoughts are is to have the code say on error jump to the next and then show a log at the end of what failed. For example..

Workbooks.Open Filename:="C:\JJones\Daily\Daily.xls"
With Sheets(Array("Today", "Yesterday")).Select

And of course some of the array's are more complex and that is just a snippet. I was thinking something along the lines of if Today doesn't exist or a problem opening that, then proceed to Yesterday and then show me a log at the end of what the error(s) are.

Is that a feasible mindset or am I way out in left field? Also, how difficult would that be to code?

 

[GTO]

Greetings jo,

From the code at #10, I think we'd want this as a first step for the first file:

Option Explicit
    
Sub CodeSnippet_01()
Dim wb      As Workbook
    
    Set wb = Workbooks.Open(Filename:="C:\Richard\Daily\Friday\Friday_Reports.xls")
    With wb
        .SaveAs Filename:="C:\Master_Reports\Friday\" & _
                            Left(wb.Name, InStrRev(wb.Name, ".") - 1) & _
                            "_" & _
                            VBA.Format(Date, "mmddyyyy") & ".xls"
    
        Run "PrintToPDF_MultiSheetToOne_Early"
        .Close True
        '.Close& ".xls"
    End With
End Sub

Notice how after setting a reference to the opening workbook, we then use 'wb' for the With, instead of using the active workbook.

Now I don't know if 'PrintToPDF_MultiSheetToOne_Early' changes anything in the wb, but I have not seen this before:

 .Close& ".xls"

I tried it and the wb does close, but if any changes have been made, Excel asks if you want to Save changes. Could you tell me what the ampersand and ".xls" are doing?

 

[GTO]

I forgot to mention, in the SaveAs, we can get rid of the '.xls' from the original filename, using Left and InStrRev, so we can get a new filename like:
'zTempDel_10202011.xls'
instead of:
'zTempDel.xls_10202011.xls'

 

[RoryA]

Two things missing from VBA: Try-Catch type error checking where you can divert if the code fails and the ability to handle the error and return to the part of the code that caused the issue (or even tell you which line it was)

Not strictly true - Resume and Resume Next allow that. Also, you can add line numbers and use Erl to report the offending line number.

 

[jo15765]

The PrintToPDF method does not change anything with the wb settings. I am going to play with the code you posted and see exactlywhat it is does, this afternoon. I am excited to see how it will help and benifit me...

 

[GTO]

The PrintToPDF method does not change anything with the wb settings. I am going to play with the code you posted and see exactlywhat it is does, this afternoon. I am excited to see how it will help and benifit me...

My bad, I certainly could have asked as to "RefreshOnOpen" at the same time. Does this change anything?

As the procedure (Sub or Function) 'PrintToPDF_MultiSheetToOne_Early' does not change anything in Friday_Reports.xls, change to .Close False right after it.

Off to bed for this lad, but I'll check back. Once we have the initial code finalized, we can include error checking or similar to see if a file exists and such.

Mark

PS Please do commment on .Close& ".xls"

 

[GTO]

@ rorya:

Hi Rory

Not strictly true - Resume and Resume Next allow that. Also, you can add line numbers and use Erl to report the offending line number.

I'm not sure if I've ever heard if Erl. Sounds familiar, maybe from an article or thread, but I'm pretty sure I've never tried it. For a simple test, I tried:

Option Explicit
    
Sub ReallySillyErrorHandle()
1:  Dim lLineCount As Long
2:
3:      On Error GoTo 14
4:      lLineCount = ActiveSheet.Cells(Rows.Count + 1, 1).Row
5:      On Error GoTo 0
6:
7:      If lLineCount = 0 Then
8:          lLineCount = ActiveSheet.Cells(Rows.Count, 1).Row
9:      End If
10:
11:     MsgBox lLineCount
12:
13: Exit Sub
14:     MsgBox "Error on line: " & Erl & vbCrLf & _
                "Error No: " & Err.Number & vbCrLf & _
                "Description: " & Err.Description
15:
16:     Resume Next
End Sub

Neat(!) and thanks for mentioning.

I do not see any type of option to "add" line numbering. Am I missing something, or is this up to the coder to either be typing in when writing the code, or, copy the code off somewhere and fix it (maybe in a text file and i/o for example)?

In closing, I just have been wanting to ask: weren't you a fairly young looking lad on a boat last year?

Mark

 

[Nalani]

I don't think VBA has the option of inserting line numbers.
But MZ-Tools does.(sort of)

I took your code and got rid of your numbers. I then clicked on Add Line Numbers from MZ-Tools and came up with the following:

Sub ReallySillyErrorHandle()
        Dim lLineCount As Long
 
10          On Error GoTo 90
20          lLineCount = ActiveSheet.Cells(Rows.Count + 1, 1).Row
30          On Error GoTo 0
 
40          If lLineCount = 0 Then
50              lLineCount = ActiveSheet.Cells(Rows.Count, 1).Row
60          End If
 
70         MsgBox lLineCount
 
80     Exit Sub
90         MsgBox "Error on line: " & Erl & vbCrLf & _
                      "Error No: " & Err.Number & vbCrLf & _
                      "Description: " & Err.Description
 
100        Resume Next
End Sub

Of course you could do a Find and Replace of the Zero's, with a little fixing afterwards.

 

[GTO]

I don't think VBA has the option of inserting line numbers.
But MZ-Tools does.(sort of)...

Thank you very much Nalani

Mark

 

[jo15765]

RefreshOnOpen --- running this macro refreshes queries that are on the spreadsheet so that I don't have to manually do it
PrintToPDF --- this macro automatically converts the worksheet to a .pdf and saves the .pdf

The .close& ".xls" is:

WIth ActiveWorkbook -- I declared active workbook here
...code
...code
.Close& ".xls" ---Saying to close the "ActiveWorkbook".Close and close the .xls

Let me know if I need to provide any other code snippets that may help. And thanks again for all the assistance.

 

[RoryA]

In closing, I just have been wanting to ask: weren't you a fairly young looking lad on a boat last year?

Mark

Hi Mark,

Yes, I was but a combination of babies and spreadsheet glare has aged me terribly!