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