Forum Discussion
Excel Add-in disconnecting
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.
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:- Close all instances of Excel (check in Taks manager, details tab, kill any trailing Excel.exe there)
- open regedit and export Excel's entry in the registry:
Computer\HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Excel - Delete that key
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.
14 Replies
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.
JKPieterse 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.
- If the add-in is checked in the list of add-ins and the add-in loads when Excel is opened, this normally works without a hitch.
Here are two articles I wrote about add-ins which may or may not apply?
https://jkp-ads.com/Articles/Excel-Add-ins-fail-to-load.asp
https://jkp-ads.com/Articles/FixLinks2UDF.asp
