Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Home > English site > Articles > Fix Links to UDFs > Fix #Name! Errors
Deze pagina in het Nederlands

Fixing Links To UDFs in Addins

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 uses 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 (7/8/2010 12:49:43 PM)

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 (7/9/2010 1:04:05 AM)

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 (7/9/2010 7:30:27 AM)

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 (7/11/2010 10:36:38 PM)

Hi Arijeet,

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

 


Comment by: Arijeet (7/12/2010 7:21:58 AM)

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 (7/12/2010 7:44:56 AM)

Hi Arijeet,

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

 


Comment by: Dutch (3/27/2012 9:13:19 AM)

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.

 


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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].