+ Reply to Thread
Results 1 to 9 of 9

Excel 2007 : #Name problem with udf

  1. #1
    Registered User
    Join Date
    01-31-2011
    Location
    San Jose, CA
    MS-Off Ver
    Excel 2007
    Posts
    6

    #Name problem with udf

    Hi there,

    I have a custom formula that I have loaded into a module and was working until I closed excel. When I reopened it all the cells with the formula have #Name. I found another post suggesting to enable macros, but mine all were. Any other suggestions?

    I'm using excel 2007

    Thanks.

  2. #2
    Forum Moderator DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Suffolk, UK
    MS-Off Ver
    2002, 2007 & 2010
    Posts
    21,520

    Re: #Name problem with udf

    Presumably you saved the file as .xlsm (or .xls)

    Re: security: http://office.microsoft.com/en-us/ex...010096919.aspx

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    13,817

    Re: #Name problem with udf

    Hi Number1 and welcome to the forum.

    Look http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

    Put Application.Volatile in the first line after the UDF name.

    hope that works.
    One test is worth a thousand opinions.
    Click the * below to say thanks.

  4. #4
    Registered User
    Join Date
    01-31-2011
    Location
    San Jose, CA
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: #Name problem with udf

    @DonkeyOte
    The file is saved as .xlsm
    I have enabled all macros and enabled Trust access to the VBA project object model

    @MarvinP
    I tried the Application.Volatile with no luck.
    After reading the link you gave me though I am kind of hesitant to use it though. It sounds like it will auto calculate which I do not want. I am using a lot of formulas in the file which look at a ton of data. I had to switch everything to manual calculation so I could make changes without my computer freezing every time. And before you ask, I did recalculate multiple times and even edited the formula in one cell to get it to recalculate

  5. #5
    Forum Moderator DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Suffolk, UK
    MS-Off Ver
    2002, 2007 & 2010
    Posts
    21,520

    Re: #Name problem with udf

    I'd suggest posting a sample file with the UDF in place and cell call so we can test

    #NAME? errors generally stem from the UDF being unavailable either because of macros being disabled or the Function being stored in the wrong place in VBE
    (the fact the function calls are working correctly prior to close & re-open implies that they are stored correctly and that there are no other unknown reference issues)

    re: Volatile - agreed - avoid unless strictly nec. [or to reduce risk of error] - see http://www.decisionmodels.com/calcsecretsj.htm for more UDF info.
    Last edited by DonkeyOte; 01-31-2011 at 03:03 PM. Reason: modified last sentence

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    13,817

    Re: #Name problem with udf

    Is there a chance you have a named range in your formula that isn't being recognized when you use the formula a second time. Also, are you using any "reserved" words as names in your formula like: Date or Mod or Today or Now....
    Also, for the #Name stuff, I remember using variables that are not like Column letters. So AAB could now be a column while in 2003 they only allowed 2 letter column letters.

    Also in reading http://answers.yahoo.com/question/in...3220010AAqHGs9 the arguments passed to the UDF must match the object.

    Let us know what it was when you find it.

  7. #7
    Registered User
    Join Date
    01-31-2011
    Location
    San Jose, CA
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: #Name problem with udf

    Here is an example file
    Attached Files Attached Files

  8. #8
    Forum Moderator DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Suffolk, UK
    MS-Off Ver
    2002, 2007 & 2010
    Posts
    21,520

    Re: #Name problem with udf

    The issue is your module name is the same as that of your function - prefix module names with mod or equivalent such that they are distinct, eg: modCountu

  9. #9
    Registered User
    Join Date
    01-31-2011
    Location
    San Jose, CA
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: #Name problem with udf

    That did it! Thank you, I would never had known to look for that. Seems like that is a check that should be built in to not let happen.

    Thanks again, now I can get back to work...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1