Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Function not recognized (Excel2003)

    I have a little mystery...

    Had a sheet that required a sheet userfunction. So I used VBA and wrote a small module with one function called "HM" which takes three arguments.

    Tested that in the sheet, by entering in a cell e.g.: =HM(A1,B1,C1)

    And that worked like a breeze. Next, I decided to make that function available also for new sheets. I searched and looked at PERSONAL.xls and at the possibility to create an add-in.

    Decided for the add-in. So, created a new sheet, copied the VBA HM-code and saved as XLA.

    Next I loaded the XLA, created a new sheet and tried a cell with: =HM(A1,B1,C1) and (of course) that worked.

    So, now I took my original sheet, removed that HM VBA-code from there and saved it (there is no other VBA code left, there are NO names defined -I use JKP's name-manager).

    Now I opened my original sheet again (this time the version without the built-in HM-function) and type there too: =HM(A1,B1,C1)

    Guess what... I get a #NAME? error in the cell. When I open a new sheet in the same session and type it there, it works fine....

    Tried a lot but this puzzles me <img src=/S/igiveup.gif border=0 alt=igiveup width=31 height=23>

    Any suggestions?

  2. #2
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function not recognized (Excel2003)

    If you open Excel (without opening any workbooks), then open your original sheet, does it work?
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Function not recognized (Excel2003)

    Apparently Excel keeps track of function names used in the workbook.
    Try the following:
    - Open the problem workbook (the one from which the original version of the function was deleted).
    - Delete all formulas that use the HM function.
    - Save and close the workbook.
    - Quit Excel.
    - Restart Excel and open the workbook.
    - Enter a formula using the HM function.
    The formula should now work correctly.

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function not recognized (Excel2003)

    Thanks,

    Tried that, doesn't help

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function not recognized (Excel2003)

    Yes, tried that too (I had exactly the same thoughts, that why I als checked for names and links) doesn't help

    Actually, I created a new sheet, tested the function (it worked of course), then I copied all relevant cells in the original sheet into that new sheet and tested the same cell again: #NAME? ...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Function not recognized (Excel2003)

    Once you get #NAME, you won't get rid of it in that session. You *must* quit and restart Excel.

    I tested the method I described in my previous reply twice - it worked both times. I suspect that there is still a reference to the function somewhere. If not, you'll have to reconstruct the workbook from scratch, I guess.

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function not recognized (Excel2003)

    You cracked it !!

    I created new WB, copied old one completely in there. Did NOTHING, did "save as", gave new name. Quit Excel, restarted, now it works...

    Pfff....

    Thanks for your great help!

Posting Permissions

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