Excel VBA: Convert Formula to Value Automatically (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

In this article, I’ll show you how you can convert a formula of a worksheet to its value automatically using Excel VBA.


Excel VBA: Convert Formula to Value Automatically (Quick View)

Sub Convert_Formula_to_Value_1()

Sheet_Name = "Sheet1"

Set Cell_Range = Worksheets(Sheet_Name).UsedRange

For i = 1 To Cell_Range.Rows.Count
    For j = 1 To Cell_Range.Columns.Count
        Cell_Range.Cells(i, j) = Cell_Range.Cells(i, j).Value
    Next j
Next i

End Sub

VBA Code to Convert Formula to Value Automatically using Excel VBA


Convert Formula to Value Automatically in Excel VBA: 2 Suitable Methods

So, without further delay, let’s go to our main discussion today. Here we’ve got a worksheet called Sheet1 that contains the names of some candidates, their marks in written and viva of a recruitment test, the average marks, and a recommendation mentioning whether they are selected or not.

The average marks and the recommendation columns contain formulas.

Worksheet to Convert Formula to Value Automatically in Excel

Our objective is to convert the formulas to values using a VBA code.

We can develop the VBA code in two methods.


Method 1: Convert Formula to Values Automatically by an Iteration in Excel VBA

You can convert all the formulas of a worksheet to values by iterating through a for-loop. We’ll replace the content in each cell of the worksheet with its value.

The complete VBA code will be:

⧭ VBA Code:

Sub Convert_Formula_to_Value_1()

Sheet_Name = "Sheet1"

Set Cell_Range = Worksheets(Sheet_Name).UsedRange

For i = 1 To Cell_Range.Rows.Count
    For j = 1 To Cell_Range.Columns.Count
        Rng.Cells(i, j) = Cell_Range.Cells(i, j).Value
    Next j
Next i

End Sub

VBA Code to Convert Formula to Value Automatically using Excel VBA

⧭ Output:

Run this code (Don’t forget to change the name of the worksheet according to your need). It’ll convert all the formulas of the worksheet to their values.

Read More: Convert Formula to Value in Multiple Cells in Excel


Method 2: Convert Formula to Values Automatically by Copying and Pasting in Excel VBA

You can also convert the formulas of a worksheet to their values by first copying them and then pasting them. For pasting, we’ll use the PasteSpecial method of VBA setting the Paste property to xlPasteValues.

The complete VBA code will be:

⧭ VBA Code:

Sub Convert_Formula_to_Value_2()
Sheet_Name = "Sheet1"
Set Rng = Worksheets(Sheet_Name).UsedRange
First_Cell = Rng.Cells(1, 1).Address
Rng.Copy
Worksheets(Sheet_Name).Range(First_Cell).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

VBA Code to Convert Formula to Value Automatically in Excel

⧭ Output:

Run this code (Again don’t forget to change the worksheet name). It’ll convert all the formulas of the worksheet to their values again.

Output to Convert Formula to Value Automatically in Excel


Developing a Macro to Convert Formula to Value Automatically Using the VBA Codes

We’ve seen two different VBA codes to convert formulas to values automatically in a worksheet in Excel. Now we’ll see how we can develop a Macro using the codes.

⧪ Step 1: Opening the VBA Window

Press ALT + F11 on your keyboard to open the Visual Basic window.

⧪ Step 2: Inserting a New Module

Go to Insert > Module in the toolbar. Click on Module. A new module called Module 1 (or anything else depending on your past history) will open.

Inserting a Module to Convert Formula to Value Automatically in Excel

⧪ Step 3: Putting the VBA Code

This is the most important step. Insert any of the two given VBA codes in the module.

Inserting VBA Code

⧪ Step 4: Running the Code

Click on the Run Sub / UserForm tool from the toolbar above.

The code will run. All the formulas of your input worksheet will be converted to their values automatically.


Things to Remember

  • In method 2 of developing the codes, we used the xlPasteValues property of VBA. But besides this, there are 11 more properties to paste in VBA. If interested, you can have a look here.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

Therefore, this is the process to develop a VBA code to convert the formulas of a worksheet to their values automatically. Do you have any questions? Feel free to ask us.


Related Articles


<< Go Back to Convert Formula to Value in Excel | Excel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo