Excelguru Help Site - User Defined Function To Show Formulas In A Cell

Macro Purpose:

Function to show the formula in another cell as text.

Examples of where this function shines:

In Excel's View options (Tools|Options|View), you can check a box to show all formulas on the worksheet as formulas, rather than their resulting values. The problem is that this is worksheet level, and sometimes that may not be desireable. This function allows you to show the formulas on the worksheet if you desire.
As an example of a practical use for this function, I have used it throughout this site to show what formulas I use in my examples.

Macro Weakness(es):

None identified at this time.

Versions Tested:
This function has been tested extensively with Excel 97 through Excel 2010, and shouldn't have an issue in any version of Excel from 97 onwards.

Note that in Excel 2013 and higher, you can simply enter =FORMULATEXT(A1) to get the same results using native formulas.

VBA Code Required:

Place the following code in a standard module of the workbook you wish to use it in.

Code:

Function showformula(rng As Range)
'Author          : Ken Puls (www.excelguru.ca)
'Function Purpose: To show a range's formula in a cell
    If rng.HasArray = True Then
        showformula = "{" & rng.Formula & "}"
    Else
        showformula = rng.Formula
    End If
End Function

How to use the code:

Enter the formula in a worksheet cell in the format =SHOWFORMULA(Range)
The results will show similar to what is below

The End Result:

You can see both formula results and actual formulas on the same page.

Try it For Yourself!
A completed file with the formula already in place is ready for you to take for a spin!

I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!