Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto recalc when VBA function changes cell values? (2000 SR-1 (9.0.4402))

    I have a VBA function that I reference in a cell formula. The VBA function uses values of cells elsewhere in the workbook (a table lookup) to calculate the function result. When I change a value in the table, this should cause the VBA function to calculate a new value for the cell containing that function in its formula. Even though I have autocalculate on (or even when I press F9), the recalculations aren't happening. If I edit one of the cells containing the VBA function, then everything recalculates the way it should.

    Is there a way to get Excel to recalculate everything automatically when values in the referenced table are changed?

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto recalc when VBA function changes cell values? (2000 SR-1 (9.0.440

    You can do either of the following:

    1. Add Application.Volatile at the beginning of the function. This forces the function to be recalculated whenever cells in the worksheet change.

    or

    2. Pass the range used for the lookup as an argument to the function.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto recalc when VBA function changes cell values? (2000 SR-1 (9.0.440

    Hans,

    Application.Volatile does the trick! (Needs to be in each user-defined function, right?)

    Thanks for the quick response!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto recalc when VBA function changes cell values? (2000 SR-1 (9.0.440

    Hi Tom,

    You don't need Application.Volatile in *each* user-defined function. The reason you need it in your function is that you're referring to cells that are not passed in as an argument to the function.

    Here is an example of a user-defined function (not a useful one, it's just meant as an illustration):

    Function MyVLookup(oCell As Range)
    MyVLookup = Application.WorksheetFunction.VLookup(oCell, Range("A1:B8"), 2, 0)
    End Function

    Used as =MyVLookup(D3)

    This function needs Application.Volatile because the lookup range A1:B8 is not passed in as an argument, but is a static part of the function. Unless you specify Application.Volatile, the function doesn't "know" it needs to re-evaluate A1:B8.

    The following function doesn't need Application.Volatile:

    Function MyVLookup(oCell As Range, oRange As Range)
    MyVLookup = Application.WorksheetFunction.VLookup(oCell, oRange, 2, 0)
    End Function

    Used as =MyVLookup(D3, A1:B8)

    The function is automatically recalculated when one of the cells referenced in its argument changes.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto recalc when VBA function changes cell values? (2000 SR-1 (9.0.440

    Thanks for the elaboration, Hans.

    My function referenced in the cell formula calls another function. When I put Application.Volatile in the parent function, it didn't appear to do anything. I then put it in the daughter function and then things started behaving. Maybe I needed it only in the daughter function. (It was the daughter function that was using the table so, based on your explanation, that's probably what I should have done in the first place). I haven't tried that since I noticed that the recalc time was much slower with the Application.Volatile statements, so I tried your other suggest of adding the table range to the argument list. This works fine, too, and it recalcs much faster.

    Case closed! Thanks again.

Posting Permissions

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