Fixing Links To UDFs in Addins

Pages in this article

Methods to Fix #Name! Errors

Use fixed location

Of course the simplest way to avoid the problem is by fixing the location of your addin. Tell all your users where the addin should be installed (or even better: create a setup tool that doesn't allow it to be installed elsewhere). Your #Name! errors will not resurface.

Don't use an addin

Well, not exactly so, you could still have an addin. But instead of keeping your UDF code inside the addin, you create a facility that copies the UDF routine into each workbook that uses it.

This is a neat solution, but it requires that your user has the security option "Trust Access to Visual Basic Project" set. John Walkenbach's Power Utility Pack used to use this trick.

Redirect the UDFs to the new location

This is the technique I'll describe extensively in this article. The addin checks each workbook the user opens whether this new workbook contains a link to the addin. If so, it ensures the link points to the proper location.

Comments

All comments about this page:

Comment by: Arijeet (8-7-2010 12:49:43) deeplink to this comment

Hello Jan,

Many thanks for your response for the registry editing using Setup-Factory.
I am facing exactly the same problem as described in this link.

Scenario:
I have an User-Defined Function called Age() in Module1 of a file Test.xlsm. If I refer to this function through this file Test.xlsm, I can use it.

Then I convert this Test.xlsm to an add-in and save it as Test.xlam. I open a new excel workbook and through Excel options, I install this add-in.
Then I try using the function Age(), (which is present in the add-in) in my new workbook. But I get an error like #NAME.

So, I refered to your solution here. But still I get the errors. Request your help on this please.

Comment by: Jan Karel Pieterse (9-7-2010 01:04:05) deeplink to this comment

Hi Arijeet,

Have you installed the add-in after saving it? Save-as add-in does not trigger Excel to install that add-in.

Comment by: Arijeet (9-7-2010 07:30:27) deeplink to this comment

Hello Jan,

Yes, I installed the add-in through Excel Options. I am using Excel 2007.

I have observed another interesting point. This behaviour does not happen on Windows7. It happens only on Windows XP.

After installing the add-in, while trying to search for the function through Formulas->Insert function->User-defined Functions, in Windows7 I can see only "Age" whereas in Windows XP I find it listed as "Test.xlam!Module1.Age()". It seems Windows7 is able to identify the user-defined functions, whereas Windows XP does not.

Request your expert comments please.

Thanks a lot,
Best Regards,

Arijeet.

Comment by: Jan Karel Pieterse (11-7-2010 22:36:38) deeplink to this comment

Hi Arijeet,

Odd indeed. I cannot reproduce that behaviour on my Win XP machine though.

Comment by: Arijeet (12-7-2010 07:21:58) deeplink to this comment

Hello Jan,

After your response yesterday, I tried the same series of steps on a Windows7 machine. And there it was working fine. In this case, when I went to Formulas ->Insert function -> User-defined function, I could see the listed function as "Age". And on using it on a new sheet, it was just fine.

In case of Windows XP, the same function is always listed as Test.xlam!Module1.Age. But in this case, the Automatic update of links is disabled, and this does not allow the use of User-defined functions in other modules. So, I had to enable this option
[Excel Options -> Trust Center -> Trust Center Settings... -> Add-ins -> Disable all Application add-ins (Uncheck this option)]; and now my code works fine on Windows XP machine also.

Thanks for all your help.

Best Regards,

Arijeet.

Comment by: Jan Karel Pieterse (12-7-2010 07:44:56) deeplink to this comment

Hi Arijeet,

Thanks for letting us know! So this is in fact an Excel security setting, unrelated to the operating system.

Comment by: Dutch (27-3-2012 09:13:19) deeplink to this comment

I have seen the error appear when a UDF was stored in a workbook that had the same name as an UDF in an Add-In (this was more or less unintentional but both UDF were functionally identical).

Excel initially appeared to link the UDF to the one in the workbook, but after opening it again it had linked it to the one in the Add-In (with full path).

So when I opened the workbook on another PC where the Add-In was not available, Excel had shown #NAME.

I resolved this by making sure the UDF in the workbook had a unique name.

Comment by: Steven Velletri (12-10-2021 14:56:00) deeplink to this comment

Does this work with the latest version of excel. I am running office 365 and I can't seem to get it working. It complains about a compatibility issue.

Cheers
Steven

Comment by: Jan Karel Pieterse (12-10-2021 17:40:00) deeplink to this comment

Hi Steven,

Does it highlight anything that it finds is wrong?

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.