Conflict between Code Module and Add-In

[JenniferMurphy]

I have a UDF in an add-in module. I decided to make some changes, so I copied it to a code module in a macro-enabled (.xlsm) test workbook. It was all working fine until I closed the test book. When I reopened it, none of the calls to the UDF were getting executed. They all showed a Value error. The workbook calculation setting was Automatic.

If I edited one of the cells (F2) and then closed it (Tab), that cell would execute, but all the rest remained un-executed.

I tried a forced recalculation of the sheet (F9, Shift+F9, Alt+Ctrl+Shift+F9, and the calculate icons in the calculation xection of the Formulas tab). Nothing worked.

I then renamed the UDF in the code module to make it unique (different from the one in the Add-In) and changed all of the calls. That solved the problem. I could close and reopen the workbook and everything still executed.

I thought Excel would use a UDF in a code module before one in an add-in with the same name. No?

Why did this not work? And what do I need to do to get it to work?

 

[Norie]

As far as I know it's not going to treat code in a module in the active workbook differently to that in a module in an add-in, the add-in is basically a workbook after all.

 

[RoryA]

When you entered the formula initially, it actually includes a reference to the source workbook even though you can't see it. When you f2 and entered the formula again, it was like retyping it, so it then referred to the local copy by default. You should also have been able to do a find replace and replace = with = for that range.