Improving my Error Handling With Arrays

**freakytiki34** · 07-23-2012, 01:32 PM

Alright. I've got a general function that takes a pattern as a string and an (optional) name of a workbook. It then looks through all of the sheets in that workbook, and creates an array of the sheets that match that pattern. Basically, it's a sheet searching function. What I'm proud of is its flexibility. What I'm not proud of is its error handling abilities. If something goes wrong, it goes straight to code/compiler errors. If it can't find any sheets, invalid strings, workbook isn't there, etc.

So. I need help improving it's ability to respond to bad situations in a more productive way than spitting errors at me.

Please Login or Register  to view this content.

What do you think? How can I make this code better?

**nilem** · 07-23-2012, 02:21 PM

maybe so

Please Login or Register  to view this content.

see also here

**JosephP** · 07-23-2012, 02:27 PM

I'd change it to take a workbook object as the second argument and I'd convert the sheet names and pattern to lower case before using Like so that the match is case insensitive.

**shg** · 07-23-2012, 02:51 PM

I prefer handling objects to strings, and avoid dynamic redimensioning of arrays:

Please Login or Register  to view this content.

EDIT:

and avoid dynamic redimensioning of arrays

And notice that nilem does as well

**freakytiki34** · 07-23-2012, 05:11 PM

Thanks to all of you. I'll be improving the code shortly.
A question for nilem, or anyone else who knows what this does.

"On Error Resume Next: Err.Clear"

I see this On Error Resume Next thing everywhere, and I can't really understand the explanations in the reference library. Both what it does, and why it's so common.

**JosephP** · 07-23-2012, 05:14 PM

on error resume next suppresses any runtime errors that may occur and simply carries on processing the next line of code. it is often overused but has its place as long as it is limited in scope.