Hello Excellers and welcome back to another #MacroMonday blog post in my #Excel 2020 Excel tips series. Today we will write an Excel macro that will very quickly convert all of your formulas into values. Yes, that is correct, this macro will convert all Excel formulas into absolute values with one click of the mouse.
Prepare To Write The Macro.
First, open the Visual Basic Editor. There are two ways to do this.
- Hit ALT +F11 or
- Select the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
So, if you cannot see the Developer Tab you may need to enable it. Check out my very short YouTube video for instructions. I will show you how to enable the developer tab in just a few minutes.
Next, you need to decide where to save your Excel macro. So, you can either save the code in your current workbook or in your Personal Macro Workbook. If you want to reuse the macro over and over again (like this one) in many workbooks save it in the Personal Macro Workbook. If the code is only to be used in the current workbook, save it there.
Here Is The Code.
Sub ConvertFormulaToValue()
Dim MyRange As Range
Dim MyCell As Range
CheckUser = MsgBox("Do you want to run this macro?", vbYesNo, "HowToExcelAtExcel")
If CheckUser = vbYes Then
Set MyRange = Selection
For Each MyCell In MyRange
If MyCell.HasFormula Then
MyCell.Formula = MyCell.Value
End If
Next MyCell
End If
End Sub
Step 1. Insert A New Module.
The first step is to insert a new module into your Personal Macro Workbook. Just hit the Insert Menu | Module.
So, once the new module is inserted, type Sub then the name of the Excel macro. In this example, I have named my Macro ConvertFormulaToValue(). Excel automatically inserts the End Sub ending code once we create a new macro. Any other lines of code are placed in between these two. The bit of the code that will convert formulas to values.
Sub ConvertFormulaToValue()
End Sub
Step 2. Declare Variables.
Next step is to declare variables we will be using. This creates a memory container in Excel for any values we want Excel to store. In this macro two variables need to be declared.
Dim MyRange As Range
Dim MyCell As Range
Step 3. Use A Message Box To Check User Actions.
The next step is not essential but it is a nice way to get the user to think about if they REALLY want to convert all of the formulas to absolute values. After all, the process is irreversible with the macro. So, adding the message box to double-check with the user is a nice little additional small extra step to be sure. In this example, the message box prompts the user to confirm YES if they want to carry on with the macro.
CheckUser = MsgBox("Do you want to run this macro?", vbYesNo, "HowToExcelAtExcel")
If CheckUser = vbYes Then
'code to run is here if the user selects yes.
End If
When the user selects Yes, the next part of the code runs. When the user selects No then the macro runs straight to Line 20 (see in the full code above) End If code. The macro then ends with no formulas being converted to absolute values.
Step 3. Setting The Range.
So, if the user decides to continue with the code then the range to be converted is set. The range is set as the cells that the user has selected.
Set MyRange = Selection
Step 4. Use For Each Loop To Convert Formulas To Absolute Values.
Next, the For Each loop method is used to loop through each cell in the selected range. If the cells contain a formula then it is converted to an absolute value. This continues until all of the cells in the selected range are looped.
CheckUser = MsgBox("Do you want to run this macro?", vbYesNo, _
"HowToExcelAtExcel")
If CheckUser = vbYes Then
Set MyRange = Selection
For Each MyCell In MyRange
If MyCell.HasFormula Then
MyCell.Formula = MyCell.Value
End If
Next MyCell
Step 5. Ending The Macro. All Formulas Are Converted To Values.
Finally, the code ends when all of the worksheets have been password protected. This line of code already is in the module as part of the naming of the macro.
End Sub
All we need to do is try the macro. My favourite part of the process. Yes!. I now can easily use my macro convert formulas to values.