Microsoft Office Tutorials and References
In Depth Information
The Call Stack dialog box shows the list of currently active procedure calls in Break mode. Unless
you write macros that involve a maze of calls to other macros, that themselves call other procedures,
you won’t need the Call Stack dialog box. A word to the wise: Keep your macros simple and limit
their procedure calls to a reasonable level, and you won’t have to worry about relying on a dialog
box to tell you which macro is in error of Break mode.
When you encounter a runtime error and you’ve figured out the cause, it might be that you need to keep
the error-prone code in place because it is such an important component of the larger macro. Actually,
you will come across this situation a lot, so you’ll need to know how to handle errors programmatically
behind the scenes, in a way that the users of your projects will not be bothered by runtime errors.
One of the more common tasks in development projects is to
add a worksheet to the workbook. Your project might involve
building a report onto a new worksheet, or copying various
sections of a master worksheet and pasting those individual
sections to their own new worksheets that you create. Say you
provide an InputBox for users to enter the name of a worksheet
they want to add. What happens if a user already has a
worksheet by that name in the workbook? Two worksheets cannot
have the same name in the same workbook, but the macro still needs to complete its appointed task.
One approach is using an On Error GoTo statement that will trap the error, and point to a certain
section in your macro that should be executed next in order to handle the error. Suppose your macro calls
for a new worksheet to be added, and named by the user as Sheet3. If a worksheet already exists in the
workbook named Sheet3, a 1004 type runtime error message would occur as shown in Figure 17-19.
With the following syntax, you can use an error handler to avoid getting a runtime error message if
an attempt is made to give a new worksheet the same name another worksheet already has. In this
example macro, the user is provided an InputBox to name the new sheet, and informed if the sheet is
added, or if it is not added because duplicate names are not allowed.
Dim mySheetName As String
mySheetName = _
InputBox(“Enter the worksheet name:”, _
“Add and name a new worksheet”)
If mySheetName = “” Then Exit Sub
On Error GoTo ErrorHandler
Worksheets.Add.Name = mySheetName