Printing values instead of the formulas themselves can help protect your IP or prevent users from changing them. In this article, we’re going to show you how to have Excel convert formula to value using VBA.
How to have Excel convert formula to value
We can check each cell using a For Each…Next loop. Below you will find 3 examples using the loop that check cells in a selected area, in the entire worksheet, and all worksheets in a workbook.
After identifying the cells, we can check whether they contain formulas using the HasFormula property. This returns a Boolean value. If the HasFormula returns TRUE, then our code overwrites the formula by setting the cell value into the Formula property of the cell.
rng.Formula = rng.Value
First, you need to add the module into the workbook or the add-in file. Copy and paste the code into the module to run it. The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. Remember to save your file in either XLSM or XLAM format to save your VBA code.
Convert formulas to values in a selected range
Sub ConvertFormulasToValuesInSelection() Dim rng As Range For Each rng In Selection If rng.HasFormula Then rng.Formula = rng.Value End If Next rng End Sub
Convert formulas to values in active worksheet
Sub ConvertFormulasToValuesInActiveWorksheet() Dim rng As Range For Each rng In ActiveSheet.UsedRange If rng.HasFormula Then rng.Formula = rng.Value End If Next rng End Sub
Convert formulas to values in all worksheets
Sub ConvertFormulasToValuesAllWorksheets() Dim ws As Worksheet, rng As Range For Each ws In ActiveWorkbook.Worksheets For Each rng In ws.UsedRange If rng.HasFormula Then rng.Formula = rng.Value End If Next rng Next ws End Sub