Listing Safely Deleting Worksheets Using the Delete Sheet Function

' deletes the worksheet given in the ws parameter ' If bQuiet then do not display Excel alerts

Function De1eteSheet(ws As Worksheet, bQuiet As Boolean) As Boolean Dim bDeleted As Boolean

On Error GoTo ErrHandler bDeleted = False

If CountVisibleSheets(ws.Parent) > 1 Then ' ok to delete - display alerts? If bQuiet Then Application.DisplayAlerts = False

' finally! delete the darn thing bDeleted = ws.Parent.Worksheets(ws.Name).De1ete

Else

' forget it - need at least ' one visible sheet in a ' workbook. bDeleted is ' already false End If

ExitPoint:

' make sure display alerts is always on Application.DisplayAlerts = True DeleteSheet = bDeleted Exit Function ErrHandler:

bDeleted = False Resume ExitPoint End Function

' returns a count of all of the visible sheets ' in the workbook wb

Function CountVisib1eSheets(wb As Workbook) As Integer Dim nSheetIndex As Integer Dim nCount As Integer nCount = 0

For nSheetlndex = 1 To wb.Sheets.Count

If wb.Sheets(nSheetIndex).Visib1e = xlSheetVisible Then nCount = nCount + 1 End If

Next

CountVisibleSheets = nCount End Function

The DeleteSheet function looks longer than it really is due to all the comments I put in it. So what gives? Why go through all this trouble to delete a worksheet? Two reasons. One, because it really isn't that much trouble. You can reuse this procedure in nearly every project. Two, developers hate runtime errors.

DeleteSheet requires that two parameters be passed to it: first, a worksheet variable named ws that represents the worksheet to be deleted; second, a Boolean variable named bQuiet. If bQuiet is true, then the function won't allow Excel to display the alert that would normally appear when you delete a worksheet.

Inside the DeleteSheet function, you only need one variable—bDeleted, a Boolean variable that keeps track of whether the worksheet was deleted. At the end of the function, the value of this variable is returned as the result of the DeleteSheet function call. Technically, you could even do away with this variable and just assign the result right to the DeleteSheet function. Personally, however, I think it's easier to read when you use a separate variable to keep track of the result as you make your way through the function.

Because you're trying to write a bulletproof procedure, you need to enable error handling in case an unplanned run-time error occurs. This procedure demonstrates an error-handling technique you haven't looked at yet. If an error occurs, then program execution is transferred to the first line following the ErrHandler label.

For the purposes of this procedure, all you need to do is report that the worksheet wasn't deleted. You could simply allow the function to end at this point. However, you should also always make sure to turn DisplayAlerts back on (set it to true). Because this is something you always need to do, even when an error doesn't occur, I've added another label named ExitPoint above the ErrHandler label. Notice the statement Resume ExitPoint in the ErrHandler code. This statement instructs program execution to resume execution at the first line following the ExitPoint label. In the ExitPoint block, you turn DisplayAlerts back on and then return the result of the function. The code between the Exit-Point label and the Exit Function statement is always executed, error or no error.

Okay. Back to the meat of the function. One of the most important things this function does is ensure that the worksheet to be deleted is not the last visible worksheet in the workbook. You must always have at least one visible sheet, worksheet or otherwise, in a workbook. Consequently you need to employ the CountVisibleSheets function. This function just loops through every sheet in the workbook, checking the Visible property and incrementing a count if the sheet is visible. Note that you can't just look at worksheets because that may not produce the intended result. If you have one visible chart sheet and one visible worksheet (two visible sheets total), you can still safely delete the worksheet. If you only considered worksheets in the CountVisibleSheets function, then it would return one visible sheet in this example and the DeleteSheets function would think that it couldn't safely delete the worksheet.

After you've determined it is safe to delete the worksheet you must consider whether you should be quiet about deleting the sheet (suppress the Alert message) or not. If you should be quiet, then you'll just turn alert messages off using the DisplayAlerts property of the Application object.

When you finally get to the statement that actually deletes the worksheet, you may be surprised that this statement is so long. Why not just use ws.Delete? The answer is that you have a distinct advantage when you use the Delete method associated with the Worksheets object rather than the Worksheet object. The advantage is that by using the Worksheets object, you can capture a result code that indicates whether the sheet was actually deleted.

Though the VBA documentation doesn't say so, the Delete method of the Worksheets object actually returns a Boolean result that indicates whether the worksheet was deleted. This is helpful because if you allow the alert to be displayed, the user may choose not to delete the worksheet. The DeleteSheet function captures the value returned by the call to the Delete method.

This doesn't work if you use the Worksheet object because once the worksheet is deleted, the ws variable is toast. Deleting any object also deletes any programmatic information associated with any object variables that referred to the object.

Anyway, because you need to get to the Worksheets object, you first have to get a reference to the workbook using the Parent property.

At this point, execution falls on through the ExitPoint label where it turns DisplayAlerts back on and reports the result of the function to the calling procedure. Clear as mud, right? Hopefully, it's much clearer than that.

Once again, this is another example of the type of defensive programming that you need to implement to avoid run-time errors. All of the extra code beyond the call to the Delete method is defensive. If you don't write specific procedures to handle common tasks such as deleting worksheets, you're either duplicating code in multiple procedures, or you're writing risky code that always assumes the worksheet being deleted isn't the last visible sheet. Unless you lock the workbook and worksheets, you have nothing to prevent users from hiding worksheets, so this isn't a safe assumption.

That said, writing safe procedures doesn't take much effort. Once you write a safe procedure, you can benefit from using it over and over, so you save time in the long run. Also, you can build useful features into these safe procedures, such as the ability to display or not display alerts to the user.

Moving and Copying Worksheets

Moving and copying worksheets is performed easily using the Move and Copy methods. The syntax for each method is identical.

YourWorksheetObject.Move [Before], [After]

YourWorksheetObject.Copy [Before], [After]

The parameters for the Move and Copy methods are described in the following list.

Before This optional parameter specifies which worksheet the worksheet(s) should be placed before. Before should be a worksheet object. You can't specify the After parameter if you specify

Before. If neither Before nor After is specified, the worksheet(s) will be placed in a new workbook.

After This optional parameter specifies which worksheet the worksheets(s) should be placed after. After should be a worksheet object. You can't specify Before if you specify After.

The following snippet shows how you might use these methods. Pretty simple stuff, eh?

Sub SimpleWorksheetMovement()

' copy the third worksheet to a new book ThisWorkbook.Worksheets(3).Copy

' copy the third worksheet before the 2nd worksheet ThisWorkbook.Worksheets(3).Copy ThisWorkbook.Worksheets(2) ' move the second worksheet to end of workbook ThisWorkbook.Worksheets(2).Move _

after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

End Sub

So, what useful things can you do with these methods? It might be nice to be able to alphabetize those long books—you know, the ones with countless worksheet tabs? If you implement a simple sort routine that uses the Move method, this isn't a very hard task. Check out Listing 7.9.

0 0

Post a comment