Excel gives us the option to show all formulas of the cells of a sheet instead of its result, however it is likely that on some occasion you need to display only the formula contained in a specific cell.
To show formula of a cell in Excel we will create a (UDF) that will have as an argument the reference of the cell for which we want to know the formula.
Create the user-defined function
To start we must open the Visual Basic Editor and create a Code Module where we will place the following function:
Public Function MostrarFormula(Celda As Range) MostrarFormula = Celda.Formula End Function
The output in the Visual Basic Editor is as follows:
We must save the file as a macro-enabled Excel workbook so that we can use the newly created function.
Show the formula of a cell
The next step is to use the function and for this I have placed the formula in cell A1:
=SUMA(1, 2, 3, 4, 5)
Now in cell C1 we will use our VBA function as follows:
By indicating the address of cell A1 as an argument of our function, Excel will obtain the value of its property and it will display it in cell C1:
Cell C1 will always give us the formula that has been written in cell A1. Notice how changing the formula in cell A1 will automatically reflect the change in cell C1:
Show functions in Spanish
In the previous examples you can notice that the function it returns the name of the function in English. The only way to get the names of functions in Spanish is to use the property instead of property as follows:
Public Function MostrarFormula(Celda As Range) MostrarFormula = Celda.FormulaLocal End Function
If you make this small change to the UDF function, you will get the name of the Excel functions in Spanish.