+ Reply to Thread
Results 1 to 7 of 7

UDF gives #VALUE! error when workbook opened, but F2/Enter clears it

  1. #1
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,995

    UDF gives #VALUE! error when workbook opened, but F2/Enter clears it

    This is in 2007.

    I have a UDF in a workbook that works fine when I first enter it in a formula. However, when I close then later re-open the workbook, every formula gives a #VALUE! error. The formula is only a call to the UDF. When I click F2 then hit Enter without changing anything to force reevaluation, the formula gives the correct result. Pressing F9 to force recalc has no effect.

    I have Medium security level on macros, so I am prompted to allow them to run when the file opens. I click the Options... button then "Enable this content". The UDF will not be allowed to run on file open, and I am wondering if it is not automatically forced to run when I then allow macros to run.

    (The UDF looks at the indent level of the adjacent column to calculate an outline number for a WBS.)

    Is there a way to have the function run when I open the file, or at least a way to force it to run after I have enabled macros, without visiting each cell?
    Attached Files Attached Files
    Last edited by 6StringJazzer; 04-14-2010 at 11:22 AM. Reason: correct typo in title
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: UDF gives #VALUE! error when workbook opened, but F2/Enter clears it

    It doesn't calculate because you don't pass the arguments to the function, so Excel sees no dependency. Suggest you rewrite the function and invocations to do so.

    I have a set of functions I use to do this, but use a separate column to set the WBS level, which both controls the WBS numbering and does (can) set the indent level. I'll post them if you like.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,995

    Re: UDF gives #VALUE! error when workbook opened, but F2/Enter clears it

    Quote Originally Posted by shg View Post
    It doesn't calculate because you don't pass the arguments to the function, so Excel sees no dependency. Suggest you rewrite the function and invocations to do so.
    I did, and I see the same behavior; attached.

    I have a set of functions I use to do this, but use a separate column to set the WBS level, which both controls the WBS numbering and does (can) set the indent level. I'll post them if you like.
    If it's no trouble I would appreciate it. Might be able to understand what my own problem is.
    Attached Files Attached Files

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: UDF gives #VALUE! error when workbook opened, but F2/Enter clears it

    Here's what I use. I pass the entire range above the WBS formula because I typically have many rows associated with each WBS.
    Attached Files Attached Files

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,995

    Re: UDF gives #VALUE! error when workbook opened, but F2/Enter clears it

    This is valuable and I may switch over to your code, but I still don't understand why mine opens with the #VALUE! error and yours does not. I modified my code (attached in post higher up) to take parameters instead of making assumptions about where things are, and it still does the same thing.

    So in practical terms my problem is solved but my education is still incomplete

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,995

    Question Re: UDF gives #VALUE! error when workbook opened, but F2/Enter clears it

    Quote Originally Posted by 6StringJazzer View Post
    ...I still don't understand why mine opens with the #VALUE! error and yours does not.
    Well, now, here's a strange thing. I built that file on my machine with 2007, and it opens with the error. I downloaded it from the link above to my machine with 2003, and it notified me of the conversion, asked for permission to run macros, and then the formula worked fine. So the plot thickens.

  7. #7
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: UDF gives #VALUE! error when workbook opened, but F2/Enter clears it

    I get similar issues. UDF's work fine when called from the Workbook.Open event if the workbook is opened in 2003, but the UDF evaluates to #Value! if the file is opened in 2010. This is casuing my Workbook.Open event to error out.

    Edit***

    Putting...
    Please Login or Register  to view this content.
    ... at the beginning of my Workbook.Open event seems to have fixed the issue.
    Last edited by Whizbang; 05-03-2011 at 04:48 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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