XLL and vba function name collision leads to permanent #NAME? error

Question

In excel 2003 sp2

• XLL defines xll_function If I call it, it works fine.

• A VBA function of the same name is defined : it takes precedence over the XLL's xll_function.

• the VBA function is removed, a #NAME? appears and stay forever. It does not help to saveas the workbook in another excel version, or in safe mode.

Excel still remembers to look at VBA, even though the VBA function has gone away. Any suggeston on what to do to solve that ?

Answer 1

Try defining a Defined Name same as the function, then deleting it

Answer 2

Excel has a very odd behaviour when it comes to linking a UDF contained in VBA, XLA[M] or XLL.

In general, while editing the workbook the VBA UDF gets priority over the one in XLA[M]/XLL and this is the one linked and saved in the workbook.

However, when you open a workbook, Excel will look first for the UDF in the XLA[M]/XLL and next in the VBA section of the same workbook. Likely this is because the VBA UDF is not yet known to Excel but the one in XLA[M]/XLL already is, and the workbook will then be permanently linked to the latter.

Probably you won't understand which one is called unless you move the workbook to a PC where the XLA[M]/XLL is not available. At that point, the most probably result is a '#NAME' or '#VALUE' in your cell.

My recommendation is to avoid ambiguity by NEVER using the SAME name for UDF in DIFFERENT providers.