Goal: Offer a more seamless, instant approach to convert formulas to values in a selected range of cells to their respective values.

This is an alternative to the standard copy and paste values to a selected range. As most Excel users are aware, there is the standard copy, paste values option, which is native to Excel.

We will write an alternative macro solution, which will enable a seamless conversion of formulas to text in a selected range. The macro will need to step over any cells, which don’t contain a formula and therefore avoid run-time errors.

The Formulas to Values macro

Go to Visual Basic for Applications, and insert a Module. Please remember to narrate the macro, because it will help users of the macro to better understand the formulas to values macro.

Don’t forget to add the Option Explicit syntax, because it forces you to declare all of the variables in a specific macro such as this one.

We will call this macro, rangeFormulasValues

First, we will disable the Excel properties before we run the formulas to values macro. The one object variable is selectedCells, which needs to be declared.

Next we need to add the loop code, which will loop through each cell in the selected range. It is prudent to add an error handler in the macro code, which will help to minimize run-time errors.

To convert each cell’s formula to the value itself, we can simply use this syntax.

If the cell in question is nothing, then we can move to the next cell, hence this code will move the macro to the next cell.

Otherwise, the macro formats on the underlying cell, and we can now move to the next cell. The macro is complete, we can now re-enable the three Excel properties, which we initially disabled.

We can now review the macro before we road-test the macro.

Let’s run the macro.

Example Workbook with the macro

In summary, here is the example workbook which the video showcased, in order to compose the formulas to values macro.