+ Reply to Thread
Results 1 to 3 of 3

Recalculation issues with UDF

  1. #1
    Registered User
    Join Date
    12-02-2012
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    40

    Recalculation issues with UDF

    This query cross-posted at http://www.ozgrid.com/forum/showthread.php?t=173141 and http://www.excelguru.ca/forums/showt...=6035#post6035

    For a workbook that records and calculates vehicle fuel consumption I've combined two UDFs into one that serves both
    functions, depending upon the first argument passed by the formula. However, I've now run into recalculation problems. Two
    of the nine worksheets have formulas which call the function, but only the sheet that's active when the workbook is opened
    calculates correctly; the other has to be forced to recalculate with F9. If neither sheet is active when the workbook is
    opened then both show incorrect figures.

    Having searched for answers I've incorporated Application.Volatile in the function but it makes little difference. The
    other suggestion was to pass all ranges needed for calculation as arguments, but how on earth could that be done?

    Here's the UDF. Its purpose is to find the previous row when the tank was filled, when there might be a number of blank
    cells (if the tank was only partially filled):
    Please Login or Register  to view this content.
    I'm attaching a slimmed-down sample of the workbook containing only the two sheets in question. Their layout is identical, with the exception of formulas in col. I. The obvious problem seems to lie with col. J ("Dist."), which calculates distance since the tank was last filled, but other calculations are also faulty. The two coloured rows at the foot of worksheet "Cefiro MPG" show fixed, correct values for the two similarly coloured rows with formulas, as a check for correct calculation.

    With grateful thanks for any help that may be forthcoming.

    Richard
    Attached Files Attached Files

  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: Recalculation issues with UDF

    You get the error because you aren't passing all of the arguments it requires (that's a cardinal sin in writing a UDF), and the code goes looking around the worksheet to find them. The Range references refer to the active worksheet, which means it's looking at the wrong sheet when the other sheet is active.

    It is rare that you need to make a UDF volatile, and it is certainly not needed here if you clean up the coding.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    12-02-2012
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    40

    Re: Recalculation issues with UDF

    Thanks, shg, for your speedy response, but I'm not sure what other arguments you have in mind or how to incorporate them.

    However, in the meantime I've received a suggestion from an Ozgrid MVP to make a small change to the code to pass the entire used range of the column instead of a single cell, and this seems to work perfectly. You're right, it works without Application.Volatile.

    R.
    Last edited by goneps; 12-27-2012 at 10:17 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