Encountering Errors on Purpose

Because programmers hate errors, this concept might seem counter-intuitive, but errors are not always bad. Sometimes it is faster to simply encounter an error.

Let's say you wanted to find out if the active workbook contains a worksheet named Data. To find this out without causing an error, you could code this: DataFound = False

For each ws in ActiveWorkbook.Worksheets If ws.Name = "Data" then DataFound = True Exit For End if Next ws

If not DataFound then Sheets.Add.Name = "Data"

This takes eight lines of code. If your workbook had 128 worksheets, the program would loop through 128 times before deciding that the data worksheet is missing.

The alternative is to simply try to reference the data worksheet. If you have error checking set to resume next, the code runs, and the Err object is assigned a number other than 0:

On Error Resume Next X = Worksheets("Data").Name

If not Err.Number = 0 then Sheets.Add.Name = "Data" On Error GoTo 0

This code runs much faster. Errors usually make me cringe, but in this case, and many other cases, they are perfectly acceptable.

0 0

Post a comment