[Excel VBA] Macro to Convert Cell Formulas to Result Values (Paste as Values)

2025年12月29日

Introduction

When sharing calculation sheets created in Excel with others, you often want to “remove the formulas and send only the visible result values.”

Usually, you would copy the range and select “Values” from “Paste Special” (commonly known as “Paste as Values”). While this is a very useful feature, it can be a bit tedious if you do it frequently.

You can perform the same action as “Paste as Values” using a VBA macro more smartly, without any copy-and-paste operations. In this article, I will introduce a magic one-line code that instantly converts formulas in a selected range to their calculated values.

VBA Sample Code to Convert Formulas to Values

The basic idea of converting formulas to values in VBA is “assigning the cell’s own Value property to its Value property.”

In Excel VBA, Range.Value returns the calculated result of the cell. Therefore, this operation overwrites the formula with the calculation result.

The Completed Code

' Convert formulas in the selected range to their calculated values
Sub ConvertFormulasToValues()

    ' Define variables
    Dim targetRange As Range
    
    ' Check if the selection is a range (to handle cases like graphs being selected)
    If TypeName(Selection) <> "Range" Then
        MsgBox "Please select a cell range before running this macro.", vbExclamation
        Exit Sub
    End If
    
    ' Set the selection range
    Set targetRange = Selection
    
    ' Convert formulas in the selected range to values
    targetRange.Value = targetRange.Value

End Sub

How to Use

Open the VBE (Alt + F11) and paste the code above into a standard module.
Select the cell range where you want to convert formulas to values.
Run the ConvertFormulasToValues macro.
All formulas in the selected range will be replaced with their calculated numbers or text.

Explanation of the Code

`If TypeName(Selection) <> "Range" Then ...`

This is a safety mechanism to prevent errors if something other than a cell range (like a chart object) is selected when the macro is run.

`targetRange.Value = targetRange.Value`

This single line is the core of this article. At first glance, it looks like a meaningless process of “assigning A to A,” but due to VBA specifications, targetRange.Value on the right side returns the “calculation result” of the cell. By assigning that result to targetRange.Value (the cell value itself) on the left side, the formula is overwritten by the value.

This method does not use the clipboard, so it has the advantage of being faster than copy-and-pasting and keeping the code simple.

Summary

In this article, I introduced a very simple and effective way to convert formulas in a selected range to values using VBA.

You can achieve “Paste as Values” without copy-and-paste using the single line Selection.Value = Selection.Value.

If you register this macro to the Quick Access Toolbar, you can convert formulas to values with a single click at any time, making your daily work even more efficient. Please give it a try!

よかったらシェアしてね！