Jul 07 2021 01:15 AM - edited Jul 07 2021 04:44 PM
Hi all,
I have an Excel add-in which I load to be able to use a custom function in it. I save everything and then when I open it later, the formula has the path to the file and the function and its parameters, but the result is 0. I then have to go back to the add-in manager and point to the XLAM file again. Then I edit the cell, remove the path and enter, which activates the function again. I've also noticed that after pointing to the XLAM in the Add-in manager, and then double clicking the actual XLAM file also causes the formula to refresh.
I obviously can't do this every time as I also have other files referencing this and I even have the files from this folder importing to a Power Query, so I can't edit/update the formulas and add-in every time. The XLAM is protected, so I can't actually import the modules either. I've also already made the folder to the add-in a trusted location.
I'm running Office 365 (64bit) on Windows 10.
Why is it doing this and how do I fix it?
Thanks in advance for your help.
Jul 07 2021 07:42 AM
@MierMoto There is probably a problem in your Excel entry in the registry.
Open the registry editor and navigate to this location:
Computer\HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Excel\Options
In that key, most of Excel's settings are listed. Find the ones that begin with the word "OPEN" (without quotes):
look closely, these MUST be in order: OPEN, OPEN1, OPEN2, OPEN3, .....
If there is a gap, make sure you close that gap by renaming them. Make sure you close Excel first.
After renaming them, you should be able to add the add-in as usual.
Jul 07 2021 04:42 PM
@Jan Karel Pieterse thanks for the quick reply.
Unfortunately it doesn't seem to the solution, unless I'm checking the wrong folders. I have three OPTION entries and they're in the correct order (see below). I also followed the path you indicated:
I'm not sure that I'm just interpreting your last comment incorrectly, but I don't have an issue opening the add-in, it's just that when I use the custom function from the add-in in a cell, save and close the file and then later open it, it seems to have lost the connection to that function and reverts back to a 0 result and instead of only showing the function name, it also has the path to where the function is stored, but it's not using it. I then have to going and re-point the add-in to the XLAM file and only then does it pick up the function.
By the way, I also updated my post to say that I'm running Office 365, 64bit on Windows 10.
Jul 08 2021 01:36 AM
Jul 08 2021 01:49 AM
Jul 08 2021 02:48 AM
Jul 08 2021 02:57 AM
@Jan Karel Pieterse No problem! I really appreciate the help.
Jul 08 2021 04:32 AM
Jul 08 2021 05:07 AM
Jul 08 2021 07:09 AM
Solution@MierMoto That is not how it is supposed to work (but you already know that).
A gap in the OPEN sequence in the registry would have explained this behavior, but apparently something else is amiss here.
All I can think of now is to suggest you to do an online repair of your Office. But I'm afraid that does not warrant a solution.
If you're brave (you'll loose your customizations and installed add-ins will be removed), you could:
Now open Excel and install your add-in. Double-check if on reopening Excel the add-in loads. Then open your workbook which uses the UDFs.
Jul 12 2021 05:38 PM
@Jan Karel Pieterse that seems to have done the trick! Unfortunate that it had to go to that level, but it worked nonetheless.
Thank you so much for sticking in there and helping me through to a solution! I really appreciate that.
Jul 13 2021 02:30 AM
@MierMoto Which one helped please? The registry or the online repair?
Jul 14 2021 07:20 AM
@MierMoto As a side note, I wrote this article today: https://jkp-ads.com/Articles/excel-addin-remove-problem.asp
Jul 14 2021 05:16 PM
Jul 08 2021 07:09 AM
Solution@MierMoto That is not how it is supposed to work (but you already know that).
A gap in the OPEN sequence in the registry would have explained this behavior, but apparently something else is amiss here.
All I can think of now is to suggest you to do an online repair of your Office. But I'm afraid that does not warrant a solution.
If you're brave (you'll loose your customizations and installed add-ins will be removed), you could:
Now open Excel and install your add-in. Double-check if on reopening Excel the add-in loads. Then open your workbook which uses the UDFs.