Listing Ensuring the Existence of a Worksheet Name before Using It

' Determines if a given worksheet name exists in a workbook Function WorksheetExists(wb As Workbook, sName As String) As Boolean Dim s As String

On Error GoTo bWorksheetExistsErr s = wb.Worksheets(sName).Name

WorksheetExists = True Exit Function bWorksheetExistsErr:

WorksheetExists = False End Function

This function catches the run-time error that would occur in your procedure if you didn't first check the validity of the worksheet name using this function. If it sees that an error occurs, it returns false signifying that the worksheet doesn't exist; otherwise it returns true. The following fragment illustrates how to use this function:

Dim ws As Worksheet

If Not WorksheetExists(ThisWorkbook, "Sheet1") Then MsgBox "Can't find worksheet named Sheet1", vbOKOnly Exit Sub End If

Set ws = ThisWorkbook.Worksheets("Sheet1")

Alternatively, you can do something like this. Dim ws As Worksheet

If WorksheetExists(ThisWorkbook, "Sheet1") Then Set ws = ThisWorkbook.Worksheets("Sheet1")

Else

Set ws = Nothing End If

If Not ws Is Nothing Then

' do stuff to/with the worksheet End If

What if you need to check for a code name? This is nearly as easy. Listing 7.3 provides an example.

0 0

Post a comment