Inconsistent Function Return

[MWE]

I have an application that references a dozen or so procs in my Personal.xls. One of these procs (let's call it Function A) was giving incorrect results. I added a few debug statements to it, but the debug outputs did not appear (actually, I used MsgBox). So I copied A out of Personal.xls and into the target application. The debug statements now appear and the results are correct. I copied it back to Personal.xls, deleted the version in the target application and was back where I started, i.e., incorrect results and no debug statements.

I then renamed A to AA, changed the call in the target application and ran it again. This time everything worked.

So either there is a bogus version of A floating around or A is some special reserved/illegal name. I have searched for A and can not find another copy. The function has been "in use" in multiple applications for several years.

Any ideas?

[BDavidson]

What is A?

[TonyJollans]

I have seen problems with procedure names which can be mistaken for row, column or cell addresses. It's always a good idea to give procedures meaningful names and if doing so makes your problem go away then everybody's happy

[Aaron Blood]

I have seen problems with procedure names which can be mistaken for row, column or cell addresses. It's always a good idea to give procedures meaningful names and if doing so makes your problem go away then everybody's happy

hehehe...
That's too funny.

Um... maybe somefin to do with some of your code not explicitly referring to the activeworkbook? Might be returning info about your Personal.xls file instead of the target file?

[MWE]

thanks for all of you for your replies ...

What is A?

the actual name of the function is xlFirstRow. I used a standard set of prefixes for all functions and subroutines.

I have seen problems with procedure names which can be mistaken for row, column or cell addresses. It's always a good idea to give procedures meaningful names and if doing so makes your problem go away then everybody's happy

I thought that might be the case. However, the function xlFirstRow has worked for "a long time"; and xlFirstRow is a pretty meaningful name (the function finds the first row in a spreadsheet with anything in any cell.

hehehe...
Um... maybe somefin to do with some of your code not explicitly referring to the activeworkbook? Might be returning info about your Personal.xls file instead of the target file?

Given that the function worked fine when it was in one of the target appl's code modules, but not when it was referenced to Personal.xls, I assumed that there was "something" called xlFirstRow somewhere in another library and that library had a higher priority than my Personal.xls. By changing the priority of Personal.xls, I was able to get ?my? version of xlFirstRow to work. It appears that there is a constant called xlFirstRow in the MicroSoft Excel 9.0 Object Library. It is a member of Excel.XlPTSelectionMode.

Looking back at previous uses of xlFirstRow, I found that in all applications (or, at least, all I could ferret out), a copy of xlFirstRow was resident in a local code module. (That was a big surprize as I try to minimize the replication of code that is already in Personal.xls) Since a local code module would have highest priority, the conflict never arose. I can ?fix? the problem by ensuring the my Personal.xls library is always second in priority (Visual Basic for Applications is always #1 priority), but that creates a number of management problems. I can also change the name from xlFirstRow to something not in conflict with another library (with its own set of management problems).

At one time, I was careful enough to actually check variable and procedure names using the Object Browser. A good lesson for us all.