Different ways to recalculate a cell or a range in VBA
excel, excel-formula, vba
Solution
.Calculate: Tells Excel to recalculate the formula in the given range. I don't think this works for a UDF unless using Application.Volatile
.Formula: Resaves the formula in the given range. Similar to clicking into the cell and then pressing enter without making changes. Will fail if you have a range greater than one cell.
.Value: Overwrites the formula with the value. Similar to copy / paste values. Will fail if you have a range greater than one cell.
Problem
The first approach is suggested in is it possible to recalculate only a cell or a range in VBA 's reply: ``` Range("A1").Calculate ``` The second approach is in How do you get Excel to refresh data on sheet from within VBA? 's reply : ``` Cells(RowA,ColB).Formula = Cells(RowA,ColB).Formula ``` The third one ``` Range("XXX").Value = Range("XXX").Value ``` seems also work. Are there any difference among the three?