Macro 46: Convert All Formulas in a Range to Values
In some situations, you may want to apply formulas in a certain workbook, but you don’t necessarily
want to keep or distribute the formulas with your workbook. In these situations, you may want to
convert all the formulas in a given range to values.
How it works
In this macro, we essentially use two Range object variables. One of the variables captures the scope
of data we are working with, whereas the other is used to hold each individual cell as we go through
the range. Then we use the For Each statement to activate or bring each cell in the target range
into focus. Every time a cell is activated, we check to see whether the cell contains a formula. If it
does, we replace the formula with the value that is shown in the cell.
Sub Macro46()
‘Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
‘Step 2: Save the Workbook before changing cells?
Select Case MsgBox(“Can’t Undo this action. ” & _
“Save Workbook First?”, vbYesNoCancel)
Case Is = vbYes
ThisWorkbook.Save
Case Is = vbCancel
Exit Sub
End Select
‘Step 3: Define the target Range.
Set MyRange = Selection
‘Step 4: Start looping through the range.
For Each MyCell In MyRange
‘Step 5: If cell has formula, set to the value shown.
If MyCell.HasFormula Then
MyCell.Formula = MyCell.Value
End If
Macro 46: Convert All Formulas in a Range to Values 141
142 Macro 46: Convert All Formulas in a Range to Values
‘Step 6: Get the next cell in the range
Next MyCell
End Sub
1. Step 1 declares two Range object variables, one called MyRange to hold the entire target
range, and the other called MyCell to hold each cell in the range as we enumerate through
them one by one.
2. When you run a macro, it destroys the undo stack. This means you can’t undo the changes a
macro makes. Because we are actually changing data, we need to give ourselves the option of
saving the workbook before running the macro. This is what Step 2 does.
Here, we call up a message box that asks if we want to save the workbook first. It then gives
us three choices: Yes, No, and Cancel. Clicking Yes saves the workbook and continues with
the macro. Clicking Cancel exits the procedure without running the macro. Clicking No runs
the macro without saving the workbook.
3. Step 3 fills the MyRange variable with the target range. In this example, we use the selected
range — the range that was selected on the spreadsheet. You can easily set the MyRange
variable to a specific range such as Range(“A1:Z100”). Also, if your target range is a
named range, you can simply enter its name: Range(“MyNamedRange”).
4. This step starts looping through each cell in the target range, activating each cell as it goes
through.
5. After a cell is activated, the macro uses the HasFormula property to check whether the cell
contains a formula. If it does, we set the cell to equal the value that is shown in the cell. This
effectively replaces the formula with a hard-coded value.
6. Step 6 loops back to get the next cell. After all cells in the target range are activated, the
macro ends.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11 on your keyboard.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
