VBA – Excel Change Formulas to Values

Changing formulas to values is something really easy in MS Excel – what the majority of people (including me) would do the following – simply record a macro, where you select all cells and paste them as values. The result would look like this:

Sub Macro1()
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks :=False, Transpose:=False

End Sub

Actually, it is not that bad, as far as it functions. Anyway, there is a better and probably faster way to do the same operation:

sub CopyRangesWithFormulas
cells.Select
	For Each CurrentCell In Selection
		If CurrentCell.HasFormula = False Then
			CurrentCell.formula = CurrentCell.Value
		End If
	Next
end sub

With this macro we do not use copy and paste, but a better method simply assigning the value to the cell. I prefer it, because it is a little faster and when the file size is bigger than a few MB, then the speed is obvious.

Enjoy!