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

    UDF & AddIn... doesn't work sometimes? (Ex2003)

    I made some UDFs in VBA. Then I deleted the test sheet data and saved as XLA. That now loads in Excel OK. When I create a new WB, the functions work when I enter them by hand.

    If I now take the original test-sheet, delete the VBA (as I already have that loaded via the XLA) and save. Then close and restart Excel (the XLA loads), then open the test sheet (with VBA removed), the functions DO NOT WORK. If I put a trace in the VBA code in the XLA, they DO FIRE when I edit a formula and do all the right stept including generating the right function value in VBA upon exit. STILL I then get #NAME? in the cell.
    Als when I edit a a function and click "fx" on the fomula bar Excel displays the arguments OK, but no result.

    I'm lost... regretfully I cannot share the functions as they are now, if this remains unclear I can try to make a simplified version that still shows the behavior and share that but I hope this is simple and maybe even logical;....

    More info: also if I enter a formula manually in my test sheet it does not work. If I enter a formula that uses a UDF I added later to the XLA (and which was never in the test sheet, it does work!). There is are no active links in the WB

    EJ

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: UDF & AddIn... doesn't work sometimes? (Ex2003)

    Try deleting all instances of the functions in your worksheets, save and close. Then reopen and try entering the functions from scratch.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: UDF & AddIn... doesn't work sometimes? (Ex2003)

    Are you sure that your add-in is installed (i.e. its check box is ticked in Tools | Add-Ins)?
    Have you made sure that the function names are not the same as the name of any code module, nor the same as the name of the VBA project of any open workbook/add-in?

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

    Re: UDF & AddIn... doesn't work sometimes? (Ex2003)

    Add in is loaded. My function names are unique and not similar to any WBname (don't even have these) or WB's or procedure etc.

    If I replace all function names (e.g. =Funct(1,2)) to Funct1(1,2) and recalc and then rename back, it still doesn't work. When I add a =Funct2(1,2) to my XLA then rename all to that, they all work OK. If I then rename back to =Funct(1,2) they don't work anymore.

    If I enter a formula from scratch it doesn't work. If I delete all sheets (except one) and then all cells with the UDF and then recalc and enter one UDF manual, it doesn't work.

    Almost as if it remembers that I once had the function defined in the sheet's own VBA and although it recognizes the XLA and runs the code it refuses to update the cell

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: UDF & AddIn... doesn't work sometimes? (Ex2003)

    Last time I had a similar problem, I had to do exactly as I described - i.e. delete (not simply remove the = sign) all instances of the function from the workbook, save and close it (possibly restart Excel just to be sure). Then reopen and use the functions as before.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: UDF & AddIn... doesn't work sometimes? (Ex2003)

    What happens if you create a blank new workbook? Do the functions from the add-in work there?

Posting Permissions

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