+ Reply to Thread
Results 1 to 15 of 15

UDF not calculating on workbook open in xl2010 only...xl2003 perfect

  1. #1
    Forum Contributor
    Join Date
    05-24-2006
    Location
    Los Angeles
    MS-Off Ver
    2019
    Posts
    326

    UDF not calculating on workbook open in xl2010 only...xl2003 perfect

    Hi,

    I have a UDF that works perfectly in xl2003, but fails to auto recalc on workbook open in xl2010. I have to hit F9 and then it will work perfectly thereafter.

    I've tried inserting "Application.Calculate" and "Application.Calculation = xlCalculationAutomatic" into Workbook_Open in ThisWorkbook to force a recalc on open, but it still fails.

    Thoughts?

    Lawrence
    Last edited by skysurfer; 01-10-2013 at 04:47 PM.

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

    Re: UDF not calculating on workbook open in xl2010 only...xl2003 perfect

    Is the UDF passed all of its arguments?

    Post the code.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: UDF not calculating on workbook open in xl2010 only...xl2003 perfect

    The Calculation setting is stored in the workbook object itself, but you already know that I assume. Basically a 2007/2010 workbook is a zip file. The information inside is either XML or Binary.

    For the fun:

    - create a copy of the workbook
    - change the extension to .zip
    - open using WinZip or Windows Explorer
    - open/view workbook.xml in the xl folder

    It should contain a line indicating the calculation setting for the workbook

    example:

    Please Login or Register  to view this content.
    in your case you want to have this value set to:

    Please Login or Register  to view this content.
    So basically remove the calcMode parameter and value

    edit using notepad, close the zip file, change back to .xlsx and try again
    If you like my contribution click the star icon!

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

    Re: UDF not calculating on workbook open in xl2010 only...xl2003 perfect

    Wouldn't it be easier to just change the calculation mode in the workbook and save?

  5. #5
    Forum Contributor
    Join Date
    05-24-2006
    Location
    Los Angeles
    MS-Off Ver
    2019
    Posts
    326

    Re: UDF not calculating on workbook open in xl2010 only...xl2003 perfect

    shg,

    Below is the code...not sure what you mean by passed all of its arguments. FYI, the UDF has worked for years in xl2003 as a way to use XIRR functionality on discontiguous ranges. Cool stuff.




    Please Login or Register  to view this content.

  6. #6
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: UDF not calculating on workbook open in xl2010 only...xl2003 perfect

    Quote Originally Posted by shg View Post
    Wouldn't it be easier to just change the calculation mode in the workbook and save?
    I understood from post #1 that this is not working for some reason. After opening the calculation method defaults to manual. Maybe I misunderstood the request?

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

    Re: UDF not calculating on workbook open in xl2010 only...xl2003 perfect

    What is the function xirr? If it's intended to refer to the worksheet function, it must be qualified.

  8. #8
    Forum Contributor
    Join Date
    05-24-2006
    Location
    Los Angeles
    MS-Off Ver
    2019
    Posts
    326

    Re: UDF not calculating on workbook open in xl2010 only...xl2003 perfect

    OllieB,

    This is an xls file. Should I save it as xml? Also, I don't understand your added code. It's to be added to the workbook_open sub in place of "Application.Calculation = xlCalculationAutomatic"?

    Thanks!

    Lawrence

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

    Re: UDF not calculating on workbook open in xl2010 only...xl2003 perfect

    if calculation were manual the function would not work fine after an initial f9 I wouldn't think

    @skysurfer
    are the inputs actually changing? are they formulas or values?

    you might want to test using
    Please Login or Register  to view this content.
    or
    Please Login or Register  to view this content.
    Last edited by JosephP; 01-09-2013 at 04:28 PM.
    Josie

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

  10. #10
    Forum Expert OllieB's Avatar
    Join Date
    12-20-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2007 (home) & 2010 (office)
    Posts
    1,542

    Re: UDF not calculating on workbook open in xl2010 only...xl2003 perfect

    Quote Originally Posted by skysurfer View Post
    OllieB,

    This is an xls file. Should I save it as xml? Also, I don't understand your added code. It's to be added to the workbook_open sub in place of "Application.Calculation = xlCalculationAutomatic"?

    Thanks!

    Lawrence
    Lawrence,

    Why don't you first try to save the workbook as a 2010 version (using SaveAs) and see if the automatic calculation method is kept properly on re-open.
    1. saveas 2010 version (so .xlsm in your case)
    2. close
    3. re-open the xlsm version
    4. set calculation method to automated
    5. save
    6. close
    7. re-open and check

    If it does not work than we will look at other options.

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

    Re: UDF not calculating on workbook open in xl2010 only...xl2003 perfect

    also remember that the calculation setting is determined by the first workbook opened irrespective of the settings saved with subsequently opened workbooks

  12. #12
    Forum Contributor
    Join Date
    05-24-2006
    Location
    Los Angeles
    MS-Off Ver
    2019
    Posts
    326

    Re: UDF not calculating on workbook open in xl2010 only...xl2003 perfect

    shg,

    XIRR is a financial function built into Excel that calculates the internal rate of return of a series of cash flows that occur at irregular intervals. Then function's arguments require the ranges for dates and cash flows to be contiguous. The myXIRR UDF makes discontiguous ranges contiguous so that XIRR will be able to use them.

    What do you mean by qualified? Is that new in xl2010?

    Lawrence

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

    Re: UDF not calculating on workbook open in xl2010 only...xl2003 perfect

    XIRR in Excel 2003 was an Analysis Toolpak function, and when you set a reference to aptvbaen in the VBE, you referenced it like a native VBA function (as in your code).

    In 2007, it became a native worksheet function, and to reference it in code, you need to use worksheetfunction.xirr.

  14. #14
    Forum Contributor
    Join Date
    05-24-2006
    Location
    Los Angeles
    MS-Off Ver
    2019
    Posts
    326

    Re: UDF not calculating on workbook open in xl2010 only...xl2003 perfect

    shg,

    Yes! That did the trick. But, it means that I will somehow have to qualify that line in the code to only run in xl2007 and higher? Otherwise, the UDF will fail in xl2003.

    Would the best solution to that be to include "Application.Version" in the UDF code? Say, using the Select/Case method?

    Thanks!!!

    Lawrence

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

    Re: UDF not calculating on workbook open in xl2010 only...xl2003 perfect

    Untested:

    Please Login or Register  to view this content.

+ 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