Results 1 to 7 of 7

Thread: Recalculation issues with UDF

  1. #1
    Seeker goneps's Avatar
    Join Date
    Dec 2012
    Location
    Auckland, New Zealand
    Posts
    13
    Articles
    0

    Recalculation issues with UDF



    Register for a FREE account, and/
    or Log in to avoid these ads!

    This query cross-posted at Ozgrid forum (t=173141), where the workbook sample is also available.

    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?

    The purpose of the UDF is to find the previous row when the tank was filled, when there might be a number of blank cells, ie. if the tank was only partially filled on previous occasions. Here's the code:
    Code:
    Function Last(x As Integer, r As Range) 
         
         ' Last(1,CellAddress) finds row for last fill
         ' Last(4,CellAddress) finds row for last 4 fills
         ' Use with INDEX function in formula
         
        Application.Volatile 
         
        Dim i As Integer, n As Integer 
         
        If x = 1 Or x = 4 Then ' Check for correct parameter passed
             
            i = r.Row 
            n = 0 
             
            Do 
                i = i - 1 
                If Range("B" & i).Value > 0 Then n = n + 1 
            Loop While n < x 
             
            Last = i 
             
        Else: Exit Function 
        End If 
         
    End Function
    I've tried to attach a sample of the workbook. but am not allowed to since this is my first post on the forum, so apologies if this isn't as clear as it might be. As mentioned above the sample is available on the Ozgrid VBA forum.

    With grateful thanks for any help that may be forthcoming.

    Richard

  2. #2
    Seeker goneps's Avatar
    Join Date
    Dec 2012
    Location
    Auckland, New Zealand
    Posts
    13
    Articles
    0
    This query now also cross-posted at Excel Forum under the same title (3060363)

  3. #3
    Seeker goneps's Avatar
    Join Date
    Dec 2012
    Location
    Auckland, New Zealand
    Posts
    13
    Articles
    0
    Problem now seems to have been solved thanks to an Ozgrid MVP, who suggested a small change to the code.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,274
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi goneps,

    Glad you got your issue sorted out, and sorry for the frustration. I can't allow links or I get overwhelmed by spam. But you should have been able attach a workbook by using the "Go Advanced" feature when replying instead of the quick reply box. If you can't, that's certainly an issue...
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Seeker goneps's Avatar
    Join Date
    Dec 2012
    Location
    Auckland, New Zealand
    Posts
    13
    Articles
    0
    Ken,

    The message was to the effect that new members with fewer than 5 posts are not allowed to attach documents or include links. Since this will be my 5th post I won't be able to replicate the block, but I certainly did use the "Go Advanced" feature as I have on other forums using the same platform.

    I can see why you don't allow links for new members, but the reason for barring attachments is not so obvious.

    Cheers,

    R.

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,274
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    To be honest, I'm really stumped that you weren't able to upload an attachment. From day one I've worked to make sure that is possible, as it is a way to make sure that people don't need to post links.

    I'm curious if you attempted to use a different method than what I'm expecting though. Here's what should work:
    • Click Go Advanced
    • Click Manage
    • Follow the prompts

    That should get the workbook into the upload queue... and it should work.

    I'll test with a new account over the next few days to make sure it is.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Seeker goneps's Avatar
    Join Date
    Dec 2012
    Location
    Auckland, New Zealand
    Posts
    13
    Articles
    0
    Hullo Ken,

    That's the procedure I followed. As mentioned I've successfully uploaded that file and others on other forums, so I know the procedure, and indeed I uploaded that file to the Excel Help forum at the same time. I'm pretty certain that the message forbade links AND attachments. However, you're now starting to shake my confidence, and my memory's by no means infallible. Might be worth testing it as you suggest, though, just to satisfy yourself that it's working as you expect. I've no desire to give you additional work, so I'll apologise in advance in case I'm mistaken.

    R.

Posting Permissions

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