none
Excel Add-In Function Has #Name? Error RRS feed

  • Question

  • I wrote a user-defined Excel function using VBA and saved it as an xlam Add-In. That worked fine when I first did it. I was able to use my function in a number of spreadsheets as expected. Some time has now lapsed (meaning untold things may have happened on my Windows 8.1 system). When I try to open one of these spreadsheets with or without doing a link Update, the function references now produce a #Name? error.

    I have tried to recreate the UDF using VBA after looking up again how to do that (some well-hidden tricks required), but that didn't help and in fact exposed some additional problems that I also don't understand (we'll ignore those for now). 

    The xlam file is in the c:\Users\Me\AppData\Roaming\Microsoft\AddIns folder as I gather it should be.

    Any ideas on how to get passed this problem?

    Thx.  Steve

    Tuesday, December 1, 2015 10:32 PM

Answers

  • Steve,
    Re: more add-in code stuff

    In general...
    An Excel add-in is a workbook that is not visible.
    The name of the workbook is visible in the VBE (visual basic editor) Project window and the workbook can be modified using the VBE.
    So, it is not a special creation - its just another workbook, but one that is opened everytime Excel starts (if the workbook is checkmarked in the Add-ins dialog box).

    Questions & answers (maybe)...
    Q:  "I don't understand your point about "A function that is called from an Excel worksheet must be
           in a standard module, not in a sheet, userform or ThisWorkbook module"
    A:  There is more than one kind of module in a workbook.  Each sheet has its own module,
          the workbook has its own module ("ThisWorkbook") and a UserForm has its own module.
          These modules are of the nature "Private" and code placed in them is not accessible from a
          worksheet cell function. 
          The module you "insert" is not private and is the one you should place your code.
          (commonly referred to as a standard or general module)

    Q.  "I also don't understand "The multiple name issue might be solved (helped) by using a new name
           for the Add-in"
    A:   Add-in & workbook are two different names for the same thing.  Creating a new workbook/add-in with
          a different name  might "help" Excel become less confused. 
          I would start fresh with a new wb name and a new module name and
          then delete the original workbook before loading the new add-in.

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Marked as answer by Cincy Steve Friday, December 4, 2015 1:45 PM
    • Edited by James Cone Tuesday, October 25, 2016 2:44 AM update link
    Thursday, December 3, 2015 12:54 AM
  • RE:  add-in code

    Some difficulties did appear right away...
      In VBA the apostrophe ' is used for code comments, not the //.
      The decCola variable is not declared... Dim decCola as Range
      "Year" is the name of a function in Excel VBA;  better to use another variable name... vYear would work.

    It is good practice to use "Option Explicit" (no quotes) as the first line in each module.
    That will flag undeclared variables for you.

    A function that is called from an Excel worksheet must be in a standard module, not in a sheet, userform or ThisWorkbook module.
    Also, the module must not use an "Option Private Module" declaration and the function  must be a "Public" function.

    The multiple name issue might be solved (helped) by using a new name for the Add-in. [Edited: function changed to Add-in]

    Possibly contributing to the situation...
      The use of "On Error Resume Next"  should be limitied to sections of the code where it is specifically needed.
      Such as:
     '---
      On Error Resume Next
      Set Sludge = Some Object
      On Error GoTo 0
      If Not Sludge is Nothing Then
     '---
      Note:  If no error handling is present in a sub/function routine then VBA climbs the call tree looking for an error handler to exercise.

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Marked as answer by Cincy Steve Friday, December 4, 2015 1:46 PM
    • Edited by James Cone Tuesday, October 25, 2016 2:42 AM update link
    Wednesday, December 2, 2015 4:34 PM

All replies

  • Re:  #name error with add-in

    Have you loaded (opened) the add-in workbook from the "Add-ins" Dialog box?

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Tuesday, October 25, 2016 2:40 AM
    Wednesday, December 2, 2015 6:41 AM
  • Jim -

    Thanks for the quick response.  I have loaded the add-in workbook (from the Add-Ins dialog box under the Developer tab), meaning the add-in is checked. 

    On the off chance it's the add-in code itself that's causing the problem (one of the additional problems I mentioned when trying to regenerate the add-in was an inexplicable compiler syntax error that has since gone away), here's the code.

    Thanks again.  Steve

    P.S.  I just found what looks like a big clue.  The add-in function appears in the Insert Function dialog box, but with the name AddInWorkbook.xlam!ApplyCOLAs.ApplyCOLAs (i.e. the function name appears twice).  When I alter one of the formula references to contain the name twice, I get the compiler syntax error.

    Public Function ApplyCOLAs(eligYear As Variant, primaryYear As Variant, currentYear As Variant, aime As Variant, colas As Range) As Variant
    Dim adjustedAime As Variant
    Dim year As Variant
    Dim decemberCOLA As Variant
    adjustedAime = aime
    For Each decCOLA In colas
    // Get the year from the current row in the colas range and the column before the range
    year = decCOLA.Offset(0, -1).Value
    // Get the COLA from the previous row
    decemberCOLA = decCOLA.Offset(-1, 0).Value
    // Apply the COLA starting in the eligYear + 1 and continuing to the max of the primary and current years
    // (i.e. at least until the primary year and eventually beyond)
    If year <= WorksheetFunction.Max(primaryYear, currentYear) Then
        If year > eligYear Then
            adjustedAime = WorksheetFunction.Floor(adjustedAime * (1 + decemberCOLA / 100), 0.1)
        End If
    End If
    Next
    ApplyCOLAs = adjustedAime
    End Function




    • Edited by Cincy Steve Wednesday, December 2, 2015 1:59 PM
    Wednesday, December 2, 2015 1:35 PM
  • RE:  add-in code

    Some difficulties did appear right away...
      In VBA the apostrophe ' is used for code comments, not the //.
      The decCola variable is not declared... Dim decCola as Range
      "Year" is the name of a function in Excel VBA;  better to use another variable name... vYear would work.

    It is good practice to use "Option Explicit" (no quotes) as the first line in each module.
    That will flag undeclared variables for you.

    A function that is called from an Excel worksheet must be in a standard module, not in a sheet, userform or ThisWorkbook module.
    Also, the module must not use an "Option Private Module" declaration and the function  must be a "Public" function.

    The multiple name issue might be solved (helped) by using a new name for the Add-in. [Edited: function changed to Add-in]

    Possibly contributing to the situation...
      The use of "On Error Resume Next"  should be limitied to sections of the code where it is specifically needed.
      Such as:
     '---
      On Error Resume Next
      Set Sludge = Some Object
      On Error GoTo 0
      If Not Sludge is Nothing Then
     '---
      Note:  If no error handling is present in a sub/function routine then VBA climbs the call tree looking for an error handler to exercise.

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Marked as answer by Cincy Steve Friday, December 4, 2015 1:46 PM
    • Edited by James Cone Tuesday, October 25, 2016 2:42 AM update link
    Wednesday, December 2, 2015 4:34 PM
  • Jim -

    Wow.  Thanks again for the help.

    I am mystified by how the add-in could have worked initially given the problems you point out.  But as I said, I can't really be sure what changes I made or were made on my system since I first did this.  Anyway, I added the Option Explicit, declared decCOLA, changed the name of the "year" variable, and changed the // to ' as you suggested (I'm really a C# programmer, which explains the //). 

    I don't understand your point about "A function that is called from an Excel worksheet must be in a standard module, not in a sheet, userform or ThisWorkbook module".   Can you explain that further.  My understanding (admittedly limited) has been that I needed to open a new workbook using any name I want, create the VBA module containing my UDF, save the workbook as an Add-In (i.e. an .xlam which is saved in the Add-In directory under my User/etc) and then make sure the add-in is loaded/opened thereafter by Excel as you suggested.  This latter step need only be done once since Excel seems to treat the selected add-ins as a persistent setting. 

    Finally, I also don't understand "The multiple name issue might be solved (helped) by using a new name for the Add-in. [Edited: function changed to Add-in]".  Are you referring to the name of the workbook that's saved as an add-in/xlam, the VBA module (which seems to default to "Module 1"), the public function, or something else entirely.  In my current case, when insert function shows "ApplyCOLAs.ApplyCOLAs", what do each of those names refer to.

    Obviously, my understanding of this is limited.  I thought I was OK when I got it to work previously, but apparently not.

    Thanks again.  Steve

    Wednesday, December 2, 2015 11:45 PM
  • Steve,
    Re: more add-in code stuff

    In general...
    An Excel add-in is a workbook that is not visible.
    The name of the workbook is visible in the VBE (visual basic editor) Project window and the workbook can be modified using the VBE.
    So, it is not a special creation - its just another workbook, but one that is opened everytime Excel starts (if the workbook is checkmarked in the Add-ins dialog box).

    Questions & answers (maybe)...
    Q:  "I don't understand your point about "A function that is called from an Excel worksheet must be
           in a standard module, not in a sheet, userform or ThisWorkbook module"
    A:  There is more than one kind of module in a workbook.  Each sheet has its own module,
          the workbook has its own module ("ThisWorkbook") and a UserForm has its own module.
          These modules are of the nature "Private" and code placed in them is not accessible from a
          worksheet cell function. 
          The module you "insert" is not private and is the one you should place your code.
          (commonly referred to as a standard or general module)

    Q.  "I also don't understand "The multiple name issue might be solved (helped) by using a new name
           for the Add-in"
    A:   Add-in & workbook are two different names for the same thing.  Creating a new workbook/add-in with
          a different name  might "help" Excel become less confused. 
          I would start fresh with a new wb name and a new module name and
          then delete the original workbook before loading the new add-in.

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Marked as answer by Cincy Steve Friday, December 4, 2015 1:45 PM
    • Edited by James Cone Tuesday, October 25, 2016 2:44 AM update link
    Thursday, December 3, 2015 12:54 AM
  • Jim -

    All of that was very helpful.  It now makes sense and works as I would expect based on your explanations.  I hope others will find it helpful too.

    Steve

    Friday, December 4, 2015 1:53 PM