Mysterious #NAME error in EXCEL

Graeme · Post by **Graeme** » Sat Feb 05, 2011 11:00 am

So, I have an EXCEL formula, call it vs(a,b,c,....) which works (it's been tested and so on). The sub that has the code for this function is in a vba sheet in the worksheet (so, no xlas, dlls, etc.).

The spreadsheet is saved and sits.

Some time later (days, weeks) the spreadsheet is opened, and the self same formula will not execute, there is just a #NAME? error. (It doesn't even invoke the function and fail for some reason.)

If I merely change the name of the function to vs2(a,b,c,....) and (of course) change the 'return' line from vs = whatever to vs2 = whatever, then the function works.

What the hell is going on and how do I resolve this?

bramj · Post by **bramj** » Sat Feb 05, 2011 11:40 am

assuming you are talking about a function, not a sub. Does making the function volatile help?

sv507 · Post by **sv507** » Sat Feb 05, 2011 8:26 pm

have you checked you haven't got a named range (on a spreadsheet ) called vs?

Graeme · Post by **Graeme** » Sat Feb 05, 2011 9:24 pm

Yes, it's a function, sorry.

No, volatile makes absolutely no difference.

The function has previously worked, There are no name clashes etc. The spreadsheet has not been opened since the last time the function worked.

Please understand, the function is not even invoked. In other words, if we put a break point on Public Function vs(.....) we never get to that break point (to paraphrase somebody that you are all fortunate to not know, "the yellow thingy never appears"...) However, if we edit the function in excel, the 'function arguments' GUI knows all the required inputs of the function..........

AVt · Post by **AVt** » Sat Feb 05, 2011 10:42 pm

can you reproduce it, if saving/working the stuff as older version, for example xl2000?

sv507 · Post by **sv507** » Sun Feb 06, 2011 11:47 am

you wouldn't consider attaching the spreadsheet?

I would certainly doublecheck the name clash - ie insert the name list on the spreadsheet (there are often old references lying around)

my experience of vba functions called from the spreadsheet is that the debugging doesn't work very well - the function always jumps out without evaluating.

to debug you have to either

a) call the function from a VBA sub (ie write a test routine)

b) put in

on error goto errVS

...

exit function

errVS:

debug.print err.description

resume next ' to see the line causing the bug.

[The classic case is debugging a dll call... you have the wrong function arguments to the dll, and the excel vba function doesn't evaluate]

silverside · Post by **silverside** » Sun Feb 06, 2011 12:47 pm

I would second (third?) sv507's suggestion to check for name clashes. Workbook named ranges versus WorkSHEET named ranges can be particularly problematic. Also if you have addins (bloomberg, fincad etc) they may have their own functions clashing, so if you work for AcmeCorp maybe you should define all your functions as e.g. AcmeAverage() instead of just Average()...

Graeme · Post by **Graeme** » Sun Feb 06, 2011 8:00 pm

Thanks, this resolves the issue! Although not exactly in the way described, but still: the workbook name and the function name were the same. No longer and now it seems fine.

I still claim there is weirdness. It used to work. Fact. Although clearly nobody believes me. Cry