+ Reply to Thread
Results 1 to 3 of 3

solving #Value error on User defined formula reading from different workbooks

  1. #1
    Forum Contributor
    Join Date
    09-30-2009
    Location
    Barcelona
    MS-Off Ver
    Excel 2010
    Posts
    274

    Arrow solving #Value error on User defined formula reading from different workbooks

    Hi, this is the situation:
    I have a macro that creates a number of rows and in some columns there are formulas that call userdefined formulas and external files to get results. When I execute the macro to create the rows there is no problem and even work with solver.

    if at some point one of the external files is closed and a formula is edited (with no actual change) the formula returns "#Value!"
    to sove that I can reopen the file and by going to edit-->replace "=" by "=" all gets recalculated and correct results are returned. (normal recalculate --F9-- does not work, but I can live with that)

    up until here is quite understandable.

    HOWEVER:

    I recorded a macro to do it exactly programatically and it does not work
    any clues on the reason / possible workaround??


    Please Login or Register  to view this content.

    and this is the user defined function:

    Please Login or Register  to view this content.
    and other formulas contained

    Please Login or Register  to view this content.
    Last edited by bagullo; 05-25-2012 at 09:38 AM. Reason: clarity

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: solving #Value error on User defined formula reading from different workbooks

    you need to make your udfs volatile or pass them all the ranges they require as arguments so that they recalculate automatically. then you shouldn't need to bother replacing = with =
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Forum Contributor
    Join Date
    09-30-2009
    Location
    Barcelona
    MS-Off Ver
    Excel 2010
    Posts
    274

    Re: solving #Value error on User defined formula reading from different workbooks

    hey JosephP! Thank you

    I added application.volatile inside the main udf and problem was solved. Closing a file immediately creates an error, but reopening it automatically soves it! Sweet!!

    thanks!

+ 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