CODEWITHSUNDEEP
excelvba
whytheq
whytheq

Reputation: 35605

Test for existence of Worksheet without using On Error Resume Next

I use On Error Resume Next too much in my VBA. It's a lazy habit.

The following will autofit certain columns if the sheet has not already been deleted from the workbook - if it has been deleted then an error is raised and the compiler moves to the next line of code.

What other approach can I use to achieve the same result?

On Error Resume Next
    bkExampleWorkbook.Sheets("Foo").Columns("E:G").AutoFit
    bkExampleWorkbook.Sheets("Bar").Columns("K:M").AutoFit
On Error GoTo 0

Upvotes: 0

Views: 636

Answers (1)

Tom 'Blue' Piddock
Tom 'Blue' Piddock

Reputation: 2210

Assuming you are working with the same sheet names and only want to resize them if they exist you can start with a function to make it easy to see if they exist and resize them if they do:

The basics

Function AutoFitSheetRange(objWorkBook As Workbook, _
                           strSheetName As String, _
                           strSheetRange As String) As Boolean

    Dim sheet As Worksheet, boolSheetFound As Boolean
    For Each sheet In objWorkBook.Worksheets
        If sheet.Name Like strSheetName Then
            boolSheetFound = True
            Exit For
        End If
    Next
    If boolSheetFound Then
        objWorkBook.Sheets(strSheetName).Range(strSheetRange).AutoFit
        AutoFitSheetRange= True
    Else
        AutoFitSheetRange= False
    End If
End Function

Using it

You can then loop over your sheets in whatever way suits you to resize a specific range (and shorthand it as columns):

AutoFitSheetRange bkExampleWorkbook, "Foo", "E:G"
AutoFitSheetRange bkExampleWorkbook, "Bar", "K:M"

Not forgetting to add error handling

You don't want to get rid of error handling but you want the function to make sure you still handle errors but more elegantly than On Error Resume Next which can cause undesirable results:

'Error Handled version
Function AutoFitSheetRange(objWorkBook As Workbook, _
                           strSheetName As String, _
                           strSheetRange As String) As Boolean
On Error Goto AutoFitSheetRangeError

    Dim sheet As Worksheet, boolSheetFound As Boolean
    For Each sheet In objWorkBook.Worksheets
        If sheet.Name Like strSheetName Then
            boolSheetFound = True
            Exit For
        End If
    Next

    If boolSheetFound Then 'Resize the range!
        objWorkBook.Sheets(strSheetName).Range(strSheetRange).AutoFit
        AutoFitSheetRange = True
    Else
        AutoFitSheetRange = False
    End If

Exit Function ' No error hit so exit

AutoFitSheetRangeError:
    AutoFitSheetRange = False
    Debug.Print Err.Message 'Print out the debug error
End Function

Flexible error responses!

This gives you the flexibility to then see if the columns were resized regardless of errors occuring and so making future decisions easier:

If AutoFitSheetRange(bkExampleWorkbook, "Foo", "E:G") Then
    MsgBox "I couldn't resize Foo! Doing nothing."
End If
If AutoFitSheetRange(bkExampleWorkbook, "Bar", "K:M") Then
    'Do something here
End If

I haven't had the chance to test this myself but let me know how it goes.

Edit:

In light of a comment by @brettdj, I thought it best to separate the function for checking sheet existance to make it a little more concise. If all you want to do is check a sheet exists then this function is more than enough:

'Error Handled version
Function SheetExists(objWorkBook As Workbook, strSheetName As String) As Boolean
On Error Goto SheetExistsError

    Dim sheet As Worksheet 
    For Each sheet In objWorkBook.Worksheets
        If sheet.Name Like strSheetName Then
            SheetExists = True
            Exit Function
        End If
    Next

SheetExistsError:
    SheetExists = False
    Debug.Print "Couldn't find sheet " & Err.Description 'Print out the debug error
End Function

Upvotes: 4

Related Questions