Subroutine (Delete Name Ranges) Errors "sometimes"

D

Dennis

Using XL 2003 & 97

60 % of the time the subroutine below; works fine.

Other times it errors on Name (wanting a definition)

I have not "Dim" ed "Name" previous to this loop thinking that "Name" is a
system thing. This may be my error.

Also, I am not sure how to error trap the routine - if no names exist.

I used "On Error Resume Next" and it still errored


Sub DeleteAllNames() 'ALL sheets
For Each Name In Names
Name.Delete
Next Name
End Sub


Any thoughts?

TIA Dennis
 
D

Dennis

Determined that the following macro must be run from a "Module"
not from "This Workbook." Otherwise the code errors on "Name"

Can anyone explain why?

BTW the macro below s/b credited to Don Guillett
 
D

Dave Peterson

Try this code under a worksheet module:

Sub testme()
MsgBox Name
End Sub

Name is a bad variable name. Lots of things can have names--including the
worksheet that owns the code. And this unqualified property (and undeclared
variable), will refer to the sheet name that owns the code.

If you declared the variable:
dim Name as Name
It would be exceedingly difficult to follow what you want.

Sub DeleteAllNames() 'ALL sheets
Dim myName as Name
For Each MyName In Names
MyName.Delete
Next myName
End Sub

But this may not do what you want if you put the code behind the worksheet.

The same unqualified Names (for each myname in names) doesn't belong to the
workbook. It belongs to the worksheet. If you have any names that are local to
the sheet (not just workbook names on that sheet!), those will be the ones
deleted.

I'd keep the code in a General module, too--to make life simpler.

But if you fully qualify stuff (and choose nice variable names), it could work:

Sub DeleteAllNames2() 'ALL sheets
Dim myName As Name
For Each myName In ThisWorkbook.Names
myName.Delete
Next myName
End Sub
 
D

Dennis

You took quite a bit of time and your thoughts helped me with VBA background
beyond the specific solution.

Thank you!
 
Top