SOLVED

Excel Add-in disconnecting

Copper Contributor

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.

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):

 

2021-07-07_16-40-15.png

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.

@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: 

MierMoto_1-1625701049567.png

 

MierMoto_0-1625700986219.png

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
Thanks again for the reply.

I tired the security setting before, but I actually didn't have that option to unblock the file. The area at the bottom of my properties dialog is empty.

The function I'm using is a UDF, but it works just fine when I enter it the first time. It'll be:
=BFCVolSimple("EP",R16088,S16088,5)

...but then when I open it later, the cell value will be 0 and the formula will be:
='C:\Users\User\AppData\Roaming\Microsoft\AddIns\BFC Volume Functions EP.xlam'!BFCVolSimple("EP",R16088,S16088,5)

I also didn't change the file path. It's using the default "C:\Users\User\AppData\Roaming\Microsoft\AddIns\BFC Volume Functions EP.xlam" path.
Fair enough. Sorry if I'm repeating my question, but:
1. Is that add-in checked in your list of add-ins
2. Is the add-in actually loaded (check in the VBA Editor in the project explorer, see whether it is there)
You say the add-in is protected; can you contact its developer by any chance?

@Jan Karel Pieterse  No problem! I really appreciate the help.

 

  1. It's definitely checked. In fact, when I reopen the file and I try to refresh the link to the UDF, I get prompted that the file already exists and whether I want to overwrite it. I keep it switched on. Simply opening the add-in dialog and checking whether it's switched on, doesn't help. I need to actually browse for it again (although it hasn't moved) and select it again. Only then does the formula update and the values revert to what they need to be.
  2. It's loaded when I use it at first. Then when I come back the file later, it's not in the VBA Editor anymore. When I then repoint it to the UDF, it pops back in the VBA Editor.
How exactly do you "try to refresh the link to the UDF" precisely?
From the above I think what is happening is that Excel does not load the add-in when Excel starts, even though it is checked, correct?
I suspect that's what happens, yes.

I refresh the link by opening the add-in manager again (where I can see it's in the list and ticked) and then I browse for the file (it immediately opens in the correct location, where I can see the file), I double click it and then it warms me that it's already there, to which I ignore it and overwrite it. Then the dialog closes and the cells immediately updates and shows the correct values.
best response confirmed by MierMoto (Copper Contributor)
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:

  1. Close all instances of Excel (check in Taks manager, details tab, kill any trailing Excel.exe there)
  2. open regedit and export Excel's entry in the registry:
    Computer\HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Excel
  3. 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.

@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.

@MierMoto Which one helped please? The registry or the online repair?

@Jan Karel Pieterse it was the registry edit.

@MierMoto As a side note, I wrote this article today: https://jkp-ads.com/Articles/excel-addin-remove-problem.asp 

@Jan Karel Pieterse excellent, thanks for the extra tip. Fortunately I didn't get that annoying issue! Good to know though. These add-in issues seem to be quite persistent.
1 best response

Accepted Solutions
best response confirmed by MierMoto (Copper Contributor)
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:

  1. Close all instances of Excel (check in Taks manager, details tab, kill any trailing Excel.exe there)
  2. open regedit and export Excel's entry in the registry:
    Computer\HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Excel
  3. 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.

View solution in original post