3

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?

Community
  • 1
  • 1
athos
  • 6,120
  • 5
  • 51
  • 95

1 Answers1

5

  1. .Calculate: Tells Excel to recalculate the formula in the given range. I don't think this works for a UDF unless using Application.Volatile

  2. .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.

  3. .Value: Overwrites the formula with the value. Similar to copy / paste values. Will fail if you have a range greater than one cell.

SSlinky
  • 427
  • 2
  • 9
  • Do 2&3 force the recalculate of the Range or the Cell? Is there any difference in the effect ? – athos Oct 25 '16 at 07:40
  • In two or three you can't have a range greater than one cell, so the range and the cell are the same thing. With three you will lose your formula as it will be overwritten with the value (which is not recalculated first). – SSlinky Oct 27 '16 at 07:17