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

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

Hans,

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

Thanks for the quick response!

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote