I've been working on some spreadsheets with buttons/macros to apply
advanced filters. With a lot of help from here on some of it, they've
been working well.
Something very strange happened when I opened the workbook up this
morning. All the buttons started giving me the "Runtime error 1004" box
(by now, one of my personal favorites). I figured out that somehow,
XL2000 was confusing a named range in the spreadsheet and the name of a
macro used to create the named range, both called "FilterRange". I
didn't know this could be an issue. Does XL see both range names and
macro names as somehow the same? Is it a good idea in general to avoid
using the same name for a range and a macro?
When I changed the macro name to MFilterRange, the problem went away.
I should mention that the last thing I changed yesterday on this
workbook was to make all the filter criteria refer to an external cell
location for the criteria value so I could change that value in one
place in the worksheet and have everything that used that value change
too. I think somehow that doing this started the confusion. Or at
least, I can't think of what else I did that changed it.
Any suggestions?

In general you want to avoid repeating names as much as is possible in all
areas of what you are doing. It makes debugging and understaing a whole pile
more difficult. Once you have two different things with the same name then
you have to be more explicit in how you reference the items. In your case the
function or procedure would have to be referenced with where it came from in
order that it not be confused with the range name. Assuming the procedure was
in a module called Module1 you would have to call it by typing
Module1.MyProcedure so that the compiler would know exactly what you were
refering to.

That aside when you are reading your code and you refer to FilterRange it is
not always going to be obvious whether you are refering to the procedure or
the named range. Debugging becomes a nightmare when this happens.

HTH

"davegb" wrote:

I've been working on some spreadsheets with buttons/macros to apply
advanced filters. With a lot of help from here on some of it, they've
been working well.
Something very strange happened when I opened the workbook up this
morning. All the buttons started giving me the "Runtime error 1004" box
(by now, one of my personal favorites). I figured out that somehow,
XL2000 was confusing a named range in the spreadsheet and the name of a
macro used to create the named range, both called "FilterRange". I
didn't know this could be an issue. Does XL see both range names and
macro names as somehow the same? Is it a good idea in general to avoid
using the same name for a range and a macro?
When I changed the macro name to MFilterRange, the problem went away.
I should mention that the last thing I changed yesterday on this
workbook was to make all the filter criteria refer to an external cell
location for the criteria value so I could change that value in one
place in the worksheet and have everything that used that value change
too. I think somehow that doing this started the confusion. Or at
least, I can't think of what else I did that changed it.
Any suggestions?

I guess this is called learning by experience. I actually thought about
whether or not I should name the range and the macro the same, and
decided to do so would be a plus from a programming POV because then it
would be easy to follow that the range "FilterRange" was created by the
macro FilterRange. I thought that the range would always be in quotes,
the macro name would never be, so there wouldn't be a problem. I was
astonished when the macro wouldn't run, and to test the range name
validity, I did a Edit GoTo and it went to the macro! So I hear what
you're saying.
What I still don't understand is why the problem didn't surface sooner.
I created the range and the macro over a week ago. Since then, I've
been adding additional sheets to the workbook. An Auto_Open macro
creates a named range "FilterRange" in each sheet when the workbook is
opened. When I go to an individual sheet, the different filters for
that particular sheet use "FilterRange". Some filters are common to all
sheets, others unique to a particular sheet. But they've been working
very consistently.
Tuesday I changed them all so that the filters and conditional
formatting all use common cells on each sheet for the input, so that if
the regulations change, all I have to do is edit one cell, and
everything else changes automatically. That's the last thing I did
before I left, and I tested to make sure they worked. But I didn't have
time to close the worksheet, reopen it, and test. That happened the
next morning, and the macros failed.
Can you, Jim, or anyone suggest why they failed at that point, rather
than way back when I first created them with the same name?