Formulas to Constants macro
Changing all the formulas in a workbook to values usually involves a tedious series of
Copy and Paste Special, Values and your doing each worksheet separately. Try using this macro, it processes the entire workbook in a few seconds. You don't need to know the first thing about macros to get it working. If you can copy and paste then that's all you need. This is what you need to do:
- Create a module in your Personal Macro workbook where the macro will be stored.
- Copy and Paste the macro code into your module.
- Save your Personal Macro workbook.
- Run the macro.
NB This version does not process array formulas, they remain untouched. Any protected sheets are ignored and the formulas are not converted. Please remember that you can not Undo a macro, once it's done, it's done. If you want to keep your original formulas then make sure that you save a copy of your original workbook file.
Create a module in your Personal Macro workbook
Turn on the macro recorder and record into your Personal Macro workbook. It doesn't matter what you record because we are going to replace the recording with our own code in a moment but you need to have a module (a storage area for macros):
Click View tab, Macros and Record Macro. Don't worry about the Macro name or anything else apart from the Store macro in setting which should be Personal Macro Workbook which you can choose from the drop down list.
Click OK to start the recording and then click Stop Recording from the Macros control. You now have your module.
Public Sub FormulasToConstants()
Dim wks As Worksheet
Dim rngFormulas As Range
Dim rngCell As Range
On Error Resume Next
'Outer loop; worksheets in workbook.
For Each wks In Worksheets
'Inner loop; formula cells on worksheet.
Set rngFormulas = wks.Cells.SpecialCells(xlCellTypeFormulas,23)
For Each rngCell In rngFormulas
'Reduce cell entry to a constant.
rngCell.Value = rngCell.Value
Next rngCell
Set rngFormulas = Nothing
Next wks
End Sub
Press ALT+F11 to switch over to the Visual Basic Editor, then look at the top left hand corner of the editor window and see if you can spot the Project Explorer window. Press CTRL+R if you can't see it. Look for PERSONAL.XLSB in the listing and keep clicking the plus signs until you can see the modules in your Modules folder. Double-click on Module 1 to open it. If you have more than one of them (you've already been using your Personal Macro workbook) then double-click the one with the highest number. Select your recorded macro and Paste over it.
This is what your macro will look like. Notice how some of the words turn blue or green, this is exactly what they should do.
Open the workbook file where you intend to convert your formulas and then click the View tab, Macros, View Macros. Choose PERSONAL.XLSB!FormulasToConstants from the list and then click Run. Quick as a flash all your formulas become numbers.
Your Personal Macro Workbook opens up automatically every time you start Excel so once you've saved it you can use it anytime you like. The Personal Macro workbook is a hidden workbook that's used to store handy macros that you would want to use on a regular basis, it's always open but it's not visible.
 |
| Recording into Personal |
Click OK to start the recording and then click Stop Recording from the Macros control. You now have your module.
Copy and Paste the macro code into your module
Select all the text from the section below and copy it (all the text starting with and including the words Public Sub down to and including the last line, End Sub) Now we need to switch over to your module and paste the code.
This is the code to copy and paste:
Public Sub FormulasToConstants()
Dim wks As Worksheet
Dim rngFormulas As Range
Dim rngCell As Range
On Error Resume Next
'Outer loop; worksheets in workbook.
For Each wks In Worksheets
'Inner loop; formula cells on worksheet.
Set rngFormulas = wks.Cells.SpecialCells(xlCellTypeFormulas,23)
For Each rngCell In rngFormulas
'Reduce cell entry to a constant.
rngCell.Value = rngCell.Value
Next rngCell
Set rngFormulas = Nothing
Next wks
End Sub
Press ALT+F11 to switch over to the Visual Basic Editor, then look at the top left hand corner of the editor window and see if you can spot the Project Explorer window. Press CTRL+R if you can't see it. Look for PERSONAL.XLSB in the listing and keep clicking the plus signs until you can see the modules in your Modules folder. Double-click on Module 1 to open it. If you have more than one of them (you've already been using your Personal Macro workbook) then double-click the one with the highest number. Select your recorded macro and Paste over it.
 |
| The Recorded macro viewed in the Visual Basic Editor |
This is what your macro will look like. Notice how some of the words turn blue or green, this is exactly what they should do.
 |
| Macro pasted into the Module |
Save your Personal Macro workbook
Do this while you are in the Visual Basic Editor as it's tricky to do back in Excel. Click File, Save in the main menu. Press ALT+F11 to exit the editor and return to Excel.Run the macro
 |
| Run the macro |
Your Personal Macro Workbook opens up automatically every time you start Excel so once you've saved it you can use it anytime you like. The Personal Macro workbook is a hidden workbook that's used to store handy macros that you would want to use on a regular basis, it's always open but it's not visible.
No comments:
Post a Comment