CODEWITHSUNDEEP
excelvbaexcel-formula
athos
athos

Reputation: 6405

Different ways to recalculate a cell or a range in VBA

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?

Upvotes: 3

Views: 4767

Answers (1)

SSlinky
SSlinky

Reputation: 488

  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.

Upvotes: 5

Related Questions