Handling Errors by Choosing to Ignore Them

Some errors can simply be ignored. Let's say that you are going to use the HTML Creator macro from Chapter 14, "Reading from and Writing to the Web." Your code erases any existing index.html file from a folder before writing out the next file.

The Kill (FileName) statement returns an error if FileName does not exist. Is this something you need to worry about? You are trying to delete the file. Who cares if someone already deleted it before running the macro? In this case, you should tell Excel to simply skip over the offending line and resume macro execution with the next line. The code to do this is On Error Resume Next:

Sub WriteHTML()

MyFile = "C:\Index.html" On Error Resume Next Kill (MyFile) On Error Goto 0 Open MyFile for Output as #1 ' etc... End Sub

You need to be careful with On Error Resume Next. It can be used selectively in situations where you know that the error can be ignored. You should immediately return error checking to normal after the line that might cause an error with On Error GoTo 0.

If you attempt to have On Error Resume Next skip an error that cannot be skipped, the macro immediately steps out of the current macro. If you have a situation where MacroA calls MacroB and MacroB encounters a non-skippable error, then the program jumps out of MacroB and continues with the next line in MacroA. This is rarely a good thing.

Page Setup Problems Can Often Be Ignored

Record a macro and perform a page setup. Even if you change only one item in the Page Setup dialog, the macro recorder records two dozen settings for you. These settings notoriously differ from printer to printer. For example, if you record the PageSetup on a system with a color printer, it may record a setting for .BlackAndWhite = True. This setting will fail on another system where the printer doesn't offer the choice. Your printer may offer a .PrintQuality = 600 setting. If the client's printer offers only 300 resolution, then this code will fail. Surround the entire PageSetup with On Error Resume Next to ensure that most settings happen but the trivial ones that fail will not cause a runtime error:

On Error Resume Next With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With

ActiveSheet.PageSetup.PrintArea = "$A$1:$L$27" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = ""

.LeftMargin = Application.InchesToPoints(0.25) .RightMargin = Application.InchesToPoints(0.25) .TopMargin = Application.InchesToPoints(0.75) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 300 .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False .PrintErrors = xlPrintErrorsDisplayed End With On Error GoTo 0

0 0

Post a comment