excel - Test for existence of Worksheet without using On Error Resume Next -

i use on error resume next in vba. it's lazy habit.

the following autofit columns if sheet has not been deleted workbook - if has been deleted error raised , compiler moves next line of code.

what other approach can use achieve same result?

on error resume next     bkexampleworkbook.sheets("foo").columns("e:g").autofit     bkexampleworkbook.sheets("bar").columns("k:m").autofit on error goto 0 

assuming working same sheet names , want resize them if exist can start function make easy see if exist , resize them if do:

the basics

function autofitsheetrange(objworkbook workbook, _                            strsheetname string, _                            strsheetrange string) boolean      dim sheet worksheet, boolsheetfound boolean     each sheet in objworkbook.worksheets         if sheet.name strsheetname             boolsheetfound = true             exit         end if     next     if boolsheetfound         objworkbook.sheets(strsheetname).range(strsheetrange).autofit         autofitsheetrange= true     else         autofitsheetrange= false     end if end function 

using it

you can loop on sheets in whatever way suits resize specific range (and shorthand columns):

autofitsheetrange bkexampleworkbook, "foo", "e:g" autofitsheetrange bkexampleworkbook, "bar", "k:m" 

not forgetting add error handling

you don't want rid of error handling want function make sure still handle errors more elegantly on error resume next can cause undesirable results:

'error handled version function autofitsheetrange(objworkbook workbook, _                            strsheetname string, _                            strsheetrange string) boolean on error goto autofitsheetrangeerror      dim sheet worksheet, boolsheetfound boolean     each sheet in objworkbook.worksheets         if sheet.name strsheetname             boolsheetfound = true             exit         end if     next      if boolsheetfound 'resize range!         objworkbook.sheets(strsheetname).range(strsheetrange).autofit         autofitsheetrange = true     else         autofitsheetrange = false     end if  exit function ' no error hit exit  autofitsheetrangeerror:     autofitsheetrange = false     debug.print err.message 'print out debug error end function 

flexible error responses!

this gives flexibility see if columns resized regardless of errors occuring , making future decisions easier:

if autofitsheetrange(bkexampleworkbook, "foo", "e:g")     msgbox "i couldn't resize foo! doing nothing." end if if autofitsheetrange(bkexampleworkbook, "bar", "k:m")     'do here end if 

i haven't had chance test myself let me know how goes.

edit:

in light of comment @brettdj, thought best separate function checking sheet existance make little more concise. if want check sheet exists function more enough:

'error handled version function sheetexists(objworkbook workbook, strsheetname string) boolean on error goto sheetexistserror      dim sheet worksheet      each sheet in objworkbook.worksheets         if sheet.name strsheetname             sheetexists = true             exit function         end if     next  sheetexistserror:     sheetexists = false     debug.print "couldn't find sheet " & err.description 'print out debug error end function