I have an .xlam file with several useful UDFs I've programmed in VBA. I load the .xlam as an add-in so I can use said functions in any of my projects as necessary. This has worked flawlessly since I set it up years ago, until just recently. Now when I use any of those functions, I just get "#NAME!" errors, despite the fact that Excel will auto-complete the UDF names for me when I type "=" and one of the function names into a blank cell.
I've double-checked that Excel was up to date (currently running Office 365). Tried it on Office 2021; no difference. Tried removing and re-adding the .xlam. Rebooted Windows. Tried a brand-new spreadsheet, saving as .xlsx, .xlsm, and .xlsb. Double-checked that macro security settings are correct.
So I figured out that what changed is that, although I hadn't modified any of the VBA code, I had just discovered the ability to rename modules and decided to ditch the unhelpfully vague "Module1," "Module2," etc. that they are inserted with. But it turns out that if you rename the module to the exact same name as the function it contains, Excel gets confused about what's going on, and throws the "#NAME?" error. The simple solution is to append "_module" to the end of each module name, or any other naming convention that keeps modules from having the same name as the functions they contain.